4 errori da evitare durante la programmazione di macro Excel con VBA

Microsoft Excel è già uno strumento di analisi dei dati capace, ma con la possibilità di automatizzare le attività ripetitive con le macro scrivendo un codice semplice in Visual Basic, Applications Edition (VBA) è molto più potente. Utilizzato in modo errato, tuttavia, VBA può causare problemi.

Anche se non sei un programmatore, VBA offre funzioni semplici che ti consentono di aggiungere funzionalità impressionanti ai tuoi fogli di calcolo.

Non importa se sei un guru VBA che crea dashboard in Excel o un principiante che scrive semplici script che eseguono calcoli di base sulle celle. Segui queste semplici tecniche di programmazione per imparare a scrivere codice pulito e privo di bug.

Introduzione a VBA

VBA può fare tutti i tipi di cose ordinate per te. Dalla scrittura di macro che modificano i fogli all'invio di e-mail da un foglio di calcolo Excel utilizzando gli script VBA, ci sono pochissimi limiti alla produttività.

Se non hai programmato in VBA in Excel prima dovrai abilitare gli strumenti per sviluppatori nel tuo programma Excel. La buona notizia è che abilitare gli strumenti per sviluppatori è in realtà abbastanza semplice. Basta andare su File > Opzioni e quindi su Personalizza barra multifunzione . Basta spostare la scheda Sviluppatore dal riquadro di sinistra verso destra.

Assicurati che la casella di controllo abbia questa scheda abilitata e ora la scheda Developer apparirà nel tuo menu Excel.

Il modo più semplice per accedere alla finestra dell'editor del codice da qui è semplicemente fare clic sul pulsante Visualizza codice in Controlli nel menu Sviluppatore.

Ora sei pronto per scrivere il codice VBA! Questa scheda consente di accedere a VBA e di registrare macro in Excel . Ora che Excel è pronto per il lavoro, esaminiamo alcuni errori comuni da evitare e i modi per prevenirli.

1. Nomi delle variabili orribili

Ora che sei nella finestra del codice, è tempo di iniziare a scrivere il codice VBA. Il primo passo importante nella maggior parte dei programmi, sia in VBA che in qualsiasi altra lingua, è la definizione delle variabili. Non nominare correttamente le variabili è uno degli errori di programmazione più comuni che fanno i nuovi sviluppatori.

Durante i miei decenni di scrittura del codice, ho incontrato molte scuole di pensiero quando si trattava di convenzioni di denominazione variabili e ho imparato alcune regole nel modo più duro. Ecco alcuni suggerimenti rapidi per la creazione di nomi di variabili:

• Rendili il più corti possibile.
• Renderli il più descrittivi possibile.
• Prefazione con il tipo variabile (booleano, intero, ecc …).

Ecco uno screenshot di esempio da un programma che utilizzo spesso per effettuare chiamate WMIC da Excel da Excel per raccogliere informazioni sul PC .

Quando si desidera utilizzare le variabili all'interno di una funzione all'interno del modulo o dell'oggetto (lo spiegherò di seguito), è necessario dichiararlo come variabile "pubblica", precedendo la dichiarazione con Pubblico . Altrimenti, le variabili vengono dichiarate prefigurandole con la parola Dim .

Come puoi vedere, se la variabile è un numero intero è preceduta da int . Se è una stringa, quindi str . Questa è una preferenza personale che ti aiuterà in seguito mentre stai programmando perché saprai sempre quale tipo di dati contiene la variabile dando un'occhiata al nome.

Inoltre, assicurati di nominare i fogli nella cartella di lavoro di Excel.

In questo modo, quando fai riferimento al nome del foglio nel tuo codice VBA di Excel, ti riferisci a un nome che ha senso. Nell'esempio sopra, ho un foglio in cui estraggo le informazioni di rete, quindi chiamo il foglio "Rete". Ogni volta che voglio fare riferimento al foglio di rete, posso farlo rapidamente senza cercare il numero di foglio che è.

2. Rottura anziché loop

Uno dei problemi più comuni che hanno i programmatori VBA più recenti quando iniziano a scrivere codice è gestire correttamente i loop.

Il looping è molto comune in Excel perché spesso si stanno elaborando valori di dati lungo un'intera riga o colonna, quindi è necessario eseguire il loop per elaborarli tutti.

I nuovi programmatori spesso vogliono semplicemente uscire da un loop (VBA per loop o VBA Do While loop ) istantaneamente quando una certa condizione è vera.

Ecco un esempio di questo metodo utilizzato per interrompere un loop VBA.

 For x = 1 To 20 If x = 6 Then Exit For y = x + intRoomTemp Next i 

I nuovi programmatori adottano questo approccio perché è facile. Cerca di evitare di interrompere esplicitamente un ciclo.

Molto spesso, il codice che viene dopo quella "rottura" è importante da elaborare. Un modo molto più pulito e professionale per gestire le condizioni in cui si desidera lasciare un circuito a metà strada è solo quello di includere quella condizione di uscita in qualcosa come un'istruzione VBA While.

 While (x>=1 AND x<=20 AND x<>6) For x = 1 To 20 y = x + intRoomTemp Next i Wend 
 Ciò consente un flusso logico del codice. Ora il codice scorrerà e si fermerà una volta raggiunto 6. Non è necessario includere comandi EXIT o BREAK scomodi a metà ciclo. 
 3. Non utilizzo di array 
 Un altro errore interessante che fanno i nuovi programmatori VBA sta provando a elaborare tutto all'interno di numerosi cicli nidificati che filtrano attraverso righe e colonne durante il processo di calcolo. 
 Ciò potrebbe anche comportare gravi problemi di prestazioni. Scorrere una colonna ed estrarre i valori ogni volta è un killer sul tuo processore. Un modo più efficiente per gestire lunghi elenchi di numeri consiste nell'utilizzare un array. 
 Se non hai mai usato un array prima, non aver paura. Immagina un array come un vassoio per cubetti di ghiaccio con un certo numero di "cubetti" in cui puoi inserire informazioni. I cubi sono numerati da 1 a 12, ed è così che si "inseriscono" i dati in essi. 
 Puoi facilmente definire un array semplicemente digitando Dim arrMyArray (12) come Intero . 
 Questo crea un "vassoio" con 12 slot disponibili per il riempimento. 
 Ecco come potrebbe apparire un codice di loop di riga senza un array: 
 Sub Test1() Dim x As Integer intNumRows = Range("A2", Range("A2").End(xldown)).Rows.Count Range("A2").Select For x = 1 To intNumRows If Range("A" & str(x)).value < 100 then intTemp = (Range("A" & str(x)).value) * 32 - 100 End If ActiveCell.Offset(1, 0).Select Next End Sub 
 In questo esempio, il codice viene elaborato verso il basso attraverso ogni singola cella dell'intervallo ed esegue il calcolo della temperatura.
 Se mai vuoi eseguire qualche altro calcolo su questi stessi valori, il processo sarebbe grosso. Dovresti duplicare questo codice, elaborare tutte queste celle ed eseguire il tuo nuovo calcolo. Tutto per un cambiamento!
 Ecco un esempio migliore, usando un array. Innanzitutto, creiamo l'array.
 Sub Test1() Dim x As Integer intNumRows = Range("A2", Range("A2").End(xldown)).Rows.Count Range("A2").Select For x = 1 To intNumRows arrMyArray(x-1) = Range("A" & str(x)).value) ActiveCell.Offset(1, 0).Select Next End Sub 
 L' x-1 per puntare all'elemento array è necessario solo perché il ciclo For inizia da 1. Gli elementi dell'array devono iniziare da 0.
 Ora che hai l'array è molto semplice elaborare i contenuti.
 Sub TempCalc() For x = 0 To UBound(arrMyArray) arrMyTemps(y) = arrMyArray(x) * 32 - 100 Next End Sub 
 Questo esempio esamina l'intero array di righe ( UBound fornisce il numero di valori di dati nell'array), esegue il calcolo della temperatura e quindi lo inserisce in un altro array chiamato arrMyTemps .
 4. Utilizzo di troppi riferimenti
 Sia che si stia programmando in Visual Basic o VBA a tutti gli effetti, è necessario includere "riferimenti" per accedere a determinate funzionalità.
 I riferimenti sono una specie di "librerie" piene di funzionalità che puoi attingere se abiliti quel file. Puoi trovare i riferimenti nella vista Sviluppo facendo clic su Strumenti nel menu e quindi facendo clic su Riferimenti .
 Ciò che troverai in questa finestra sono tutti i riferimenti attualmente selezionati per il tuo attuale progetto VBA.
 È necessario controllare questo elenco perché riferimenti non necessari possono sprecare risorse di sistema. Se non usi alcuna manipolazione di file XML, perché mantenere selezionato Microsoft XML? Se non si comunica con un database, rimuovere Microsoft DAO, ecc.
 Se non sei sicuro di cosa facciano questi riferimenti selezionati, premi F2 e vedrai Esplora oggetti. Nella parte superiore di questa finestra, puoi scegliere la libreria di riferimento da sfogliare.
 Una volta selezionato, vedrai tutti gli oggetti e le funzioni disponibili, su cui puoi fare clic per saperne di più.
 Ad esempio, quando faccio clic sulla libreria DAO diventa subito chiaro che si tratta di connettersi e comunicare con i database.
 Ridurre il numero di riferimenti che usi nel tuo progetto di programmazione è semplicemente un buon senso e contribuirà a rendere la tua applicazione generale più efficiente.
 Programmazione in Excel VBA
 L'idea di scrivere effettivamente codice in Excel fa paura a molte persone, ma questa paura non è davvero necessaria. Visual Basic, Applications Edition è un linguaggio molto semplice da imparare e se segui le pratiche comuni di base sopra menzionate, ti assicurerai che il tuo codice sia pulito, efficiente e di facile comprensione.
 Non fermarti qui però. Crea una solida base di competenze Excel con un tutorial VBA per principianti . Quindi continua a conoscere VBA e le macro con le risorse per aiutarti ad automatizzare i tuoi fogli di calcolo . 
 Leggi l'articolo completo: 4 errori da evitare durante la programmazione di macro Excel con VBA