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

Microsoft Excel è già uno strumento di analisi dei dati molto capace, ma con la capacità di automatizzare attività ripetitive con macro Come inviare e-mail da un foglio di calcolo Excel utilizzando gli script VBA Come inviare e-mail da un foglio di calcolo Excel con gli script VBA Il nostro modello di codice ti aiuterà a impostare posta elettronica automatica da Excel utilizzando Collaboration Data Objects (CDO) e script VBA. Leggi di più scrivendo codice semplice in (VBA), è molto più potente. Tuttavia, utilizzato in modo errato, VBA può causare un sacco di problemi.

Anche se non sei un programmatore, VBA offre semplici funzioni che ti consentono di aggiungere alcune funzionalità davvero impressionanti ai tuoi fogli di lavoro, quindi non lasciarti ancora!

Se sei un guru di VBA, chi crea cruscotti in Excel Crea il tuo cruscotto meteo in questo Master Class Google Google Crea il tuo cruscotto meteorologico in questo Master Class Google Google Vuoi trasformare il tuo account Google Drive in un intelligente sistema di analisi meteorologica e vivi una fantasia di Star Trek? Ok! Leggi altro, o un principiante, che sa solo come scrivere semplici script che eseguono calcoli di base sulle celle, puoi seguire semplici tecniche di programmazione, che ti aiuteranno a migliorare le probabilità di scrivere codice pulito e privo di errori.

Guida introduttiva a VBA

Se non hai ancora programmato in VBA in Excel, abilitare gli strumenti per sviluppatori a farlo è in realtà piuttosto semplice. Basta andare File > Opzioni e poi Personalizza il nastro. Basta spostare il Sviluppatore gruppo di comandi dal pannello di sinistra a destra.

Assicurati che la casella di controllo sia abilitata e ora la scheda Sviluppatore apparirà nel menu di Excel.

Il modo più semplice per entrare nella finestra dell'editor di codice a questo punto è semplicemente cliccare sul Visualizza codice pulsante sotto controlli nel menu Sviluppatore.

1. Nomi variabili orribili

Ora che sei nella finestra del codice, è il momento di iniziare a scrivere il codice VBA Come puoi creare la tua semplice app con VBA Come puoi creare la tua semplice app con VBA Per quelli di voi che amerebbero davvero essere in grado di scrivere la tua applicazione, ma non hai mai digitato prima una singola riga di codice, ti spiegherò come rendere il tuo ... Per saperne di più. Il primo passo importante nella maggior parte dei programmi, che sia in VBA o in qualsiasi altra lingua, è la definizione delle variabili.

Durante i miei due decenni di scrittura del codice, mi sono imbattuto in molte scuole di pensiero quando si trattava di convenzioni di denominazione variabili e ho imparato alcune cose nel modo più difficile. Ecco i suggerimenti rapidi per la creazione di nomi di variabili:

• Rendili il più corti possibile.
• Rendili il più descrittivi possibile.
• Prefiggili con tipo variabile (booleano, intero, ecc ...).
• Ricordati di usare il mirino giusto (vedi sotto).

Ecco uno screenshot di esempio da un programma che uso spesso per effettuare chiamate Windows WMIC da Excel per raccogliere informazioni sul PC Come visualizzare tutte le informazioni del PC utilizzando uno script VBA Excel semplice Come visualizzare tutte le informazioni del PC utilizzando un semplice script Excel VBA Excel e 10 minuti di lavoro ti forniranno informazioni più dettagliate sul tuo computer di quante tu non abbia mai creduto possibile. Sembra troppo bello per essere vero? Quello è Windows, se sai come usarlo. Leggi di più .

Quando si desidera utilizzare le variabili all'interno di qualsiasi funzione all'interno del modulo o dell'oggetto (lo spiegherò di seguito), quindi è necessario dichiararlo come “pubblico” variabile inizializzando la dichiarazione con Pubblico. Altrimenti, le variabili vengono dichiarate precedendole con la parola offuscare.

Come puoi vedere, se la variabile è un intero, è preceduta da int. Se è una stringa, allora str. Questo ti aiuta in un secondo momento mentre programmi, perché saprai sempre quale tipo di dati contiene la variabile, semplicemente dando uno sguardo al nome. Noterai anche che se c'è qualcosa come una stringa che contiene il nome di un computer, allora viene chiamata la variabile strComputerName.

Evita di fare nomi di variabili molto contorte o confuse che solo tu capisci. Rendi più facile per un altro programmatore venire dietro di te e capire cosa significa tutto ciò!

Un altro errore che le persone fanno è lasciare i nomi dei fogli come predefiniti “sheet1”, “Foglio2”, ecc. Ciò aggiunge ulteriore confusione a un programma. Invece, dai un nome ai fogli in modo che abbiano un senso.

In questo modo, quando si fa riferimento al nome del foglio nel codice VBA di Excel Utilizzo di VBA per automatizzare le sessioni di Internet Explorer da un foglio di calcolo Excel mediante VBA per automatizzare le sessioni di Internet Explorer da un foglio di calcolo di Excel La sua integrazione con Windows consente il controllo di Internet Explorer in un numero di modi sorprendenti utilizzando lo script VBA (Visual Basic for Applications) da qualsiasi applicazione che lo supporta, come Word, Outlook o Excel. Per saperne di più, ti stai riferendo a un nome che ha un senso. Nell'esempio sopra, ho un foglio in cui inserisco le informazioni di rete, quindi chiamo il foglio “Rete”. Ora nel codice, ogni volta che voglio fare riferimento al foglio di rete, posso farlo rapidamente senza cercare quale numero di foglio sia.

2. Rottura al posto del loop

Uno dei problemi più comuni che i programmatori più recenti hanno 6 abitudini di vita che la programmazione potrebbe insegnarti oggi 6 abitudini di vita che la programmazione potrebbe insegnarti oggi Tutto ciò che è importante sapere per vivere una vita di successo, puoi ottenere da un programma per computer. Non mi credi? Continuare a leggere. Per saperne di più, quando iniziano a scrivere il codice, tratta correttamente i loop. E dal momento che così tante persone che usano Excel VBA sono molto novizi di codice, il ciclo lento è epidemico.

Looping è molto comune in Excel perché spesso si elaborano i valori dei dati su un'intera riga o su una colonna, quindi è necessario eseguire il ciclo per elaborarli tutti. I nuovi programmatori spesso vogliono uscire da un loop (sia un ciclo For o un look While) istantaneamente quando una certa condizione è vera.

Puoi ignorare la complessità del codice precedente, nota che all'interno dell'istruzione IF interna, c'è un'opzione per uscire dal ciclo For se la condizione è vera. Ecco un esempio più semplice:

Per x = 1 A 20 Se x = 6 Quindi Esci per y = x + intRoomTemp Avanti i 

I nuovi programmatori adottano questo approccio perché è facile. Quando si verifica una condizione che stai aspettando per uscire da un loop, la tentazione di saltare immediatamente fuori da esso è forte, ma non farlo.

Più spesso che no, il codice che viene dopo “rompere” è importante elaborare, anche l'ultima volta attraverso il ciclo prima di uscire. Un modo molto più pulito e professionale per gestire le condizioni in cui si desidera lasciare un ciclo a metà, è solo per includere quella condizione di uscita in qualcosa come un'istruzione While.

While (x> = 1 AND x<=20 AND x<>6) Per x = 1 A 20 y = x + intRoomTemp Avanti i Wend 

Ciò consente un flusso logico del codice, con l'ultima esecuzione quando x è 5, e quindi si chiude con grazia una volta che il ciclo For conta fino a 6. Non è necessario includere comandi EXIT o BREAK di awkward mid-loop.

3. Non usare le matrici

Un altro errore interessante che i nuovi programmatori VBA Le nozioni di base sulla programmazione computerizzata 101 - Variabili e tipi di dati Le basi della programmazione computerizzata 101 - Variabili e tipi di dati Avendo introdotto e parlato un po 'di programmazione orientata agli oggetti prima e da dove proviene il suo omonimo, pensavo fosse ora che passare attraverso le basi assolute della programmazione in un modo specifico non linguistico. Questo ... Leggi di più make sta tentando di elaborare tutto all'interno di numerosi cicli nidificati che filtrano attraverso righe e colonne durante il processo di calcolo.

Mentre questo può funzionare, potrebbe anche portare a problemi di prestazioni importanti, se devi costantemente eseguire gli stessi calcoli sugli stessi numeri nella stessa colonna. Passare attraverso quella colonna ed estrarre i valori ogni volta non è solo noioso da programmare, è un killer sul tuo processore. Un modo più efficiente di 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 i cubetti di ghiaccio con un certo numero di “cubi” puoi mettere le informazioni in I cubi sono numerati da 1 a 12, ed è così che tu “mettere” dati in loro.

Puoi facilmente definire un array semplicemente digitando Dim arrMyArray (12) as Integer.

Questo crea a “vassoio” con 12 slot disponibili per il tuo 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 (" Un valore "& str (x)) < 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.

Più avanti nel programma, se vuoi eseguire qualche altro calcolo su questi stessi valori, devi duplicare questo codice, elaborare in basso tutte queste celle ed eseguire il tuo nuovo calcolo.

Ora, se invece si utilizza un array, è possibile memorizzare i 12 valori nella riga nel proprio comodo array di archiviazione. Successivamente, ogni volta che si desidera eseguire calcoli su tali numeri, sono già in memoria e pronti per essere utilizzati.

Sub Test1 () Dim x As Integer intNumRows = Range ("A2", Range ("A2"). End (xldown)) .Riga.Count Range ("A2"). Select For x = 1 To intNumRows arrMyArray (x- 1) = Intervallo (valore "A" & str (x)).) ActiveCell.Offset (1, 0). Seleziona Sottotitoli Successivo 

Il “x-1” per indicare l'elemento dell'array è necessario solo perché il ciclo For parte da 1. Gli elementi dell'array devono iniziare da 0.

Ma una volta che tutti i tuoi array sono stati caricati con i valori della riga, più avanti nel programma puoi semplicemente combinare tutti i calcoli desiderati usando l'array.

Sub TempCalc () Per x = 0 A UBound (arrMyArray) arrMyTemps (y) = arrMyArray (x) * 32 - 100 Successivo Fine Sottotitoli 

Questo esempio attraversa l'intera schiera di righe (UBound fornisce il numero di valori di dati nell'array), calcola la temperatura e quindi la colloca in un altro array chiamato arrMyTemps.

Puoi vedere quanto è più semplice il secondo codice di calcolo. E da questo momento in poi, ogni volta che vuoi eseguire più calcoli sullo stesso set di numeri, il tuo array è già pre-caricato e pronto per andare.

4. Utilizzo di troppi riferimenti

Sia che stiate programmando in Visual Basic a tutti gli effetti, o VBA, avrete bisogno di includere “Riferimenti” per accedere a determinate funzionalità, come accedere a un database di Access o scrivere output in un file di testo.

I riferimenti sono simili “librerie” pieno di funzionalità che puoi attingere, se abiliti quel file. Puoi trovare Referenze nella vista Developer cliccando su Utensili nel menu e quindi facendo clic su Riferimenti.

Quello che troverai in questa finestra sono tutti i riferimenti attualmente selezionati per il tuo attuale progetto VBA.

Nella mia esperienza, le selezioni qui possono cambiare da un'installazione di Excel a un'altra e da un PC a un altro. Molto dipende da ciò che altre persone hanno fatto con Excel su questo PC, se alcuni componenti aggiuntivi o funzionalità sono stati aggiunti o abilitati oppure se alcuni altri programmatori potrebbero aver utilizzato determinati riferimenti in progetti precedenti.

Il motivo per cui è opportuno controllare questo elenco è perché i riferimenti non necessari sprecano risorse di sistema. Se non si utilizza alcuna manipolazione di file XML, allora perché mantenere selezionato Microsoft XML? se non comunichi con un database, rimuovi Microsoft DAO. Se non si scrive l'output in un file di testo, quindi rimuovere Microsoft Scripting Runtime.

Se non si è sicuri di cosa facciano questi riferimenti selezionati, premere F2 e vedrai l'Object Explorer. 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 cliccare 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 utilizzi nel tuo progetto di programmazione è solo di buon senso e ti aiuterà a rendere l'applicazione generale più efficiente.

Programmazione in VBA di Excel

L'intera idea di scrivere codice in Excel Risparmio di tempo con le operazioni di testo in Excel Risparmio di tempo con le operazioni di testo in Excel Excel può fare magie con i numeri e può gestire i personaggi altrettanto bene. Questo manuale mostra come analizzare, convertire, sostituire e modificare il testo all'interno di fogli di calcolo. Queste basi ti consentiranno di eseguire trasformazioni complesse. Per saperne di più, spaventa molte persone, ma questa paura non è davvero necessaria. Visual Basic for Applications è un linguaggio molto semplice da apprendere e, se segui le pratiche comuni di base menzionate sopra, ti assicurerai che il tuo codice sia pulito, efficiente e facile da capire.

Esegui il codice in VBA? Che tipo di lezioni hai imparato negli anni che puoi condividere con altri lettori che apprendono VBA per la prima volta? Condividi i tuoi suggerimenti nella sezione commenti qui sotto!

Crediti immagine: Computer di www.BillionPhotos.com via Shutterstock, Colorful Ice Cube Vassoi di hahauk tramite Shutterstock.com

Scopri di più su: Microsoft Excel, programmazione, programmazione Visual Basic.