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 con script VBA Come inviare e-mail da un foglio di calcolo Excel Usando gli script VBA Ti mostreremo come configurare e-mail automatizzate da Excel utilizzando Collaboration Data Objects, (CDO) e script VBA. I nostri modelli di codice rendono questo molto più semplice di quanto sembri! 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 lasciarlo 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 del tempo 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. Vai su File > Opzioni e poi Personalizza barra multifunzione . Sposta semplicemente il gruppo di comandi dello sviluppatore dal riquadro 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 pulsante Visualizza codice 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), è necessario dichiararlo come variabile "pubblica" precedendo la dichiarazione con Public . Altrimenti, le variabili vengono dichiarate precedendole con la parola Dim .
Come puoi vedere, se la variabile è un intero, è preceduta da int . Se si tratta di una stringa, quindi 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, la variabile è chiamata 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 "Foglio1", "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 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:
For x = 1 To 20 If x = 6 Then Exit For y = x + intRoomTemp Next i I nuovi programmatori adottano questo approccio perché è facile. Quando si verifica una condizione che stai aspettando per uscire da un ciclo, la tentazione di saltare immediatamente fuori da esso è forte, ma non farlo.
Più spesso che no, il codice che viene dopo questa "interruzione" è importante da 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 x6) For x = 1 To 20 y = x + intRoomTemp Next 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 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 una vaschetta per i cubetti di ghiaccio con un certo numero di "cubi" in cui inserire le 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 Integer .
Questo crea un "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("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.
Più avanti nel programma, se vuoi eseguire qualche altro calcolo su questi stessi valori, devi duplicare questo codice, elaborare tutte queste celle ed eseguire il 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)).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 La "x-1" per indicare l'elemento dell'array è necessaria solo perché il ciclo For parte da 1. Gli elementi dell'array devono iniziare da 0.
Ma una volta che tutti gli array sono stati caricati con i valori della riga, in seguito nel programma è possibile semplicemente combinare tutti i calcoli desiderati utilizzando l'array.
Sub TempCalc() For x = 0 To UBound(arrMyArray) arrMyTemps(y) = arrMyArray(x) * 32 - 100 Next End Sub Questo esempio attraversa l'intero array di righe (UBound fornisce il numero di valori di dati nell'array), calcola la temperatura e quindi la inserisce 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 si desidera eseguire più calcoli sullo stesso set di numeri, l'array è già precaricato e pronto per l'uso.
4. Utilizzo di troppi riferimenti
Che tu stia programmando in Visual Basic a tutti gli effetti, o VBA, dovrai 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 una specie di "librerie" piene di funzionalità alle quali puoi attingere, se abiliti quel file. Puoi trovare riferimenti nella vista Sviluppatore facendo clic su Strumenti 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 o se alcuni altri programmatori potrebbero aver utilizzato determinati riferimenti nei progetti passati.
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, rimuovere Microsoft Scripting Runtime .
Se non sei sicuro di cosa facciano questi riferimenti selezionati, premi 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