Esercitazione VBA di Excel - Come scrivere codice in un foglio di calcolo utilizzando Visual Basic

introduzione

Questo è un tutorial sulla scrittura di codice in fogli di calcolo Excel utilizzando Visual Basic for Applications (VBA).

Excel è uno dei prodotti più popolari di Microsoft. Nel 2016, il CEO di Microsoft ha dichiarato: "Pensa a un mondo senza Excel. Per me è semplicemente impossibile". Ebbene, forse il mondo non può pensare senza Excel.

  • Nel 1996 c'erano oltre 30 milioni di utenti di Microsoft Excel (fonte).
  • Oggi ci sono circa 750 milioni di utenti di Microsoft Excel. È un po 'più della popolazione europea e un numero di utenti 25 volte superiore rispetto al 1996.

Siamo una grande famiglia felice!

In questo tutorial imparerai a conoscere VBA e come scrivere codice in un foglio di calcolo Excel utilizzando Visual Basic.

Prerequisiti

Non è necessaria alcuna esperienza di programmazione precedente per comprendere questo tutorial. Tuttavia, avrai bisogno di:

  • Conoscenza da base a intermedia con Microsoft Excel
  • Se vuoi seguire gli esempi VBA in questo articolo, avrai bisogno di accedere a Microsoft Excel, preferibilmente l'ultima versione (2019), ma Excel 2016 ed Excel 2013 funzioneranno perfettamente.
  • Una volontà di provare cose nuove

obiettivi formativi

Nel corso di questo articolo imparerai:

  1. Cos'è VBA
  2. Perché dovresti usare VBA
  3. Come impostare in Excel per scrivere VBA
  4. Come risolvere alcuni problemi del mondo reale con VBA

Concetti importanti

Ecco alcuni concetti importanti con cui dovresti avere familiarità per comprendere appieno questo tutorial.

Oggetti : Excel è orientato agli oggetti, il che significa che tutto è un oggetto: la finestra di Excel, la cartella di lavoro, un foglio, un grafico, una cella. VBA consente agli utenti di manipolare ed eseguire azioni con oggetti in Excel.

Se non hai alcuna esperienza con la programmazione orientata agli oggetti e questo è un concetto nuovo di zecca, prenditi un secondo per lasciarlo capire!

Procedure : una procedura è un pezzo di codice VBA, scritto in Visual Basic Editor, che esegue un'attività. A volte, questo viene anche definito macro (maggiori informazioni sulle macro di seguito). Esistono due tipi di procedure:

  • Subroutine: un gruppo di istruzioni VBA che esegue una o più azioni
  • Funzioni: un gruppo di istruzioni VBA che esegue una o più azioni e restituisce uno o più valori

Nota: è possibile avere funzioni che operano all'interno dei sottoprogrammi. Lo vedrai dopo.

Macro : se hai passato del tempo ad apprendere funzionalità di Excel più avanzate, probabilmente hai incontrato il concetto di "macro". Gli utenti di Excel possono registrare macro, costituite da comandi utente / sequenze di tasti / clic, e riprodurle alla velocità della luce per eseguire attività ripetitive. Le macro registrate generano codice VBA, che puoi quindi esaminare. In realtà è abbastanza divertente registrare una semplice macro e poi guardare il codice VBA.

Tieni presente che a volte può essere più facile e veloce registrare una macro piuttosto che codificare manualmente una procedura VBA.

Ad esempio, forse lavori nella gestione dei progetti. Una volta alla settimana, devi trasformare un rapporto esportato non elaborato dal tuo sistema di gestione dei progetti in un rapporto pulito e ben formattato per la leadership. È necessario formattare i nomi dei progetti fuori budget in grassetto rosso. È possibile registrare le modifiche alla formattazione come macro ed eseguirle ogni volta che è necessario apportare la modifica.

Cos'è VBA?

Visual Basic for Applications è un linguaggio di programmazione sviluppato da Microsoft. Ogni programma software della suite Microsoft Office è fornito in bundle con il linguaggio VBA senza costi aggiuntivi. VBA consente agli utenti di Microsoft Office di creare piccoli programmi che operano all'interno dei programmi software di Microsoft Office.

Pensa a VBA come un forno per pizza all'interno di un ristorante. Excel è il ristorante. La cucina è dotata di elettrodomestici commerciali standard, come grandi frigoriferi, stufe e normali forni ole: queste sono tutte le caratteristiche standard di Excel.

Ma cosa succede se vuoi fare la pizza a legna? Non posso farlo in un normale forno commerciale. VBA è il forno per pizza.

Yum.

Perché utilizzare VBA in Excel?

Perché la pizza a legna è la migliore!

Ma sul serio.

Molte persone trascorrono molto tempo in Excel come parte del proprio lavoro. Anche il tempo in Excel si muove in modo diverso. A seconda delle circostanze, 10 minuti in Excel possono sembrare un'eternità se non sei in grado di fare ciò di cui hai bisogno o 10 ore possono passare molto velocemente se tutto sta andando alla grande. Quando dovresti chiederti, perché diavolo sto trascorrendo 10 ore in Excel?

A volte, quei giorni sono inevitabili. Ma se trascorri 8-10 ore ogni giorno in Excel facendo attività ripetitive, ripetendo molti degli stessi processi, provando a ripulire dopo altri utenti del file o persino aggiornando altri file dopo che sono state apportate modifiche al file Excel, una procedura VBA potrebbe essere la soluzione per te.

Dovresti considerare l'utilizzo di VBA se hai bisogno di:

  • Automatizza le attività ripetitive
  • Crea modi semplici per consentire agli utenti di interagire con i tuoi fogli di lavoro
  • Manipola grandi quantità di dati

Prepararsi a scrivere VBA in Excel

Scheda sviluppatore

Per scrivere VBA, dovrai aggiungere la scheda Sviluppatore alla barra multifunzione, quindi vedrai la barra multifunzione in questo modo.

Per aggiungere la scheda Sviluppatore alla barra multifunzione:

  1. Nella scheda File, vai a Opzioni> Personalizza barra multifunzione.
  2. In Personalizza la barra multifunzione e in Schede principali selezionare la casella di controllo Sviluppatore.

Dopo aver visualizzato la scheda, la scheda Sviluppo rimane visibile, a meno che non si deselezioni la casella di controllo o si debba reinstallare Excel. Per ulteriori informazioni, vedere la documentazione della guida di Microsoft.

Editor VBA

Passa alla scheda Sviluppatore e fai clic sul pulsante Visual Basic. Apparirà una nuova finestra: questo è l'editor di Visual Basic. Ai fini di questo tutorial, è sufficiente acquisire familiarità con il riquadro Esplora progetti e il riquadro Proprietà proprietà.

Esempi VBA di Excel

Per prima cosa, creiamo un file per farci giocare.

  1. Apri un nuovo file Excel
  2. Salvalo come cartella di lavoro abilitata per le macro (. Xlsm)
  3. Seleziona la scheda Sviluppatore
  4. Apri l'editor VBA

Facciamo rock and roll con alcuni semplici esempi per farti scrivere codice in un foglio di calcolo utilizzando Visual Basic.

Esempio n. 1: visualizzare un messaggio quando gli utenti aprono la cartella di lavoro di Excel

Nell'editor VBA, seleziona Inserisci -> Nuovo modulo

Scrivi questo codice nella finestra del modulo (non incollare!):

Sub Auto_Open ()

MsgBox ("Benvenuto nella cartella di lavoro XYZ.")

End Sub

Salva, chiudi la cartella di lavoro e riapri la cartella di lavoro. Dovrebbe essere visualizzata questa finestra di dialogo.

Ta da!

Come lo sta facendo?

A seconda della tua familiarità con la programmazione, potresti avere delle ipotesi. Non è particolarmente complesso, ma stanno succedendo molte cose:

  • Sub (abbreviazione di "Subroutine): ricorda dall'inizio," un gruppo di istruzioni VBA che esegue una o più azioni ".
  • Auto_Open: questa è la subroutine specifica. Esegue automaticamente il codice quando si apre il file Excel: questo è l'evento che attiva la procedura. Auto_Open verrà eseguito solo quando la cartella di lavoro viene aperta manualmente; non verrà eseguito se la cartella di lavoro viene aperta tramite codice da un'altra cartella di lavoro (Workbook_Open lo farà, scopri di più sulla differenza tra i due).
  • Per impostazione predefinita, l'accesso a una subroutine è pubblico. Ciò significa che qualsiasi altro modulo può utilizzare questa subroutine. Tutti gli esempi in questo tutorial saranno subroutine pubbliche. Se necessario, puoi dichiarare le subroutine come private. Potrebbe essere necessario in alcune situazioni. Ulteriori informazioni sui modificatori di accesso alle subroutine.
  • msgBox: questa è una funzione - un gruppo di istruzioni VBA che esegue una o più azioni e restituisce un valore. Il valore restituito è il messaggio "Benvenuto nella cartella di lavoro XYZ".

In breve, questa è una semplice subroutine che contiene una funzione.

Quando potrei usarlo?

Forse hai un file molto importante a cui si accede raramente (diciamo, una volta a trimestre), ma aggiornato automaticamente ogni giorno da un'altra procedura VBA. Quando vi accede, molte persone in più reparti, in tutta l'azienda.

  • Problema: la maggior parte delle volte, quando gli utenti accedono al file, sono confusi sullo scopo di questo file (perché esiste), come viene aggiornato così spesso, chi lo gestisce e come dovrebbero interagire con esso. I nuovi assunti hanno sempre tonnellate di domande e devi rispondere a queste domande ancora e ancora e ancora.
  • Soluzione: creare un messaggio utente che contenga una risposta concisa a ciascuna di queste domande frequenti.

Esempi del mondo reale

  • Utilizzare la funzione MsgBox per visualizzare un messaggio quando si verifica un evento: l'utente chiude una cartella di lavoro di Excel, l'utente stampa, un nuovo foglio viene aggiunto alla cartella di lavoro, ecc.
  • Utilizzare la funzione MsgBox per visualizzare un messaggio quando un utente deve soddisfare una condizione prima di chiudere una cartella di lavoro di Excel
  • Utilizzare la funzione InputBox per ottenere informazioni dall'utente

Esempio # 2: Consenti all'utente di eseguire un'altra procedura

Nell'editor VBA, seleziona Inserisci -> Nuovo modulo

Scrivi questo codice nella finestra del modulo (non incollare!):

Sub UserReportQuery ()

Dim UserInput As Long

Risposta debole come numero intero

UserInput = vbYesNo

Risposta = MsgBox ("Process the XYZ Report?", UserInput)

If Answer = vbYes Then ProcessReport

End Sub

Sub ProcessReport ()

MsgBox ("Grazie per aver elaborato il rapporto XYZ.")

End Sub

Salva e torna alla scheda Sviluppatore di Excel e seleziona l'opzione "Pulsante". Fare clic su una cella e assegnare la macro UserReportQuery al pulsante.

Ora fai clic sul pulsante. Questo messaggio dovrebbe visualizzare:

Fare clic su "sì" o premere Invio.

Ancora una volta, tada!

Si noti che la subroutine secondaria, ProcessReport, potrebbe essere qualsiasi cosa . Dimostrerò più possibilità nell'esempio n. 3. Ma prima...

Come lo sta facendo?

Questo esempio si basa sull'esempio precedente e contiene alcuni nuovi elementi. Esaminiamo le nuove cose:

  • Dim UserInput As Long: Dim è l'abbreviazione di "dimensione" e consente di dichiarare i nomi delle variabili. In questo caso, UserInput è il nome della variabile e Long è il tipo di dati. In parole povere, questa riga significa "Ecco una variabile chiamata" UserInput ", ed è di tipo Long."
  • Dim Answer As Integer: dichiara un'altra variabile chiamata "Answer", con un tipo di dati di Integer. Ulteriori informazioni sui tipi di dati qui.
  • UserInput = vbYesNo: assegna un valore alla variabile. In questo caso, vbYesNo, che visualizza i pulsanti Sì e No. Ci sono molti tipi di pulsanti, scopri di più qui.
  • Risposta = MsgBox ("Process the XYZ Report?", UserInput): assegna il valore della variabile Answer a una funzione MsgBox e la variabile UserInput. Sì, una variabile all'interno di una variabile.
  • If Answer = vbYes Then ProcessReport: questa è una "istruzione If", un'istruzione condizionale, che ci permette di dire se x è vera, allora fai y. In questo caso, se l'utente ha selezionato "Sì", esegue la subroutine ProcessReport.

Quando potrei usarlo?

Questo potrebbe essere utilizzato in molti, molti modi. Il valore e la versatilità di questa funzionalità sono maggiormente definiti da ciò che fa la subroutine secondaria.

Ad esempio, potresti avere un file che viene utilizzato per generare 3 diversi rapporti settimanali. Questi rapporti sono formattati in modi notevolmente diversi.

  • Problema: ogni volta che è necessario generare uno di questi rapporti, un utente apre il file e modifica la formattazione e i grafici; Così via. Questo file viene modificato ampiamente almeno 3 volte a settimana e ogni volta che viene modificato sono necessari almeno 30 minuti.
  • Soluzione: crea 1 pulsante per tipo di rapporto, che riformatta automaticamente i componenti necessari dei rapporti e genera i grafici necessari.

Esempi del mondo reale

  • Crea una finestra di dialogo per consentire all'utente di inserire automaticamente determinate informazioni su più fogli
  • Utilizzare la funzione InputBox per ottenere informazioni dall'utente, che vengono quindi popolate su più fogli

Esempio n. 3: aggiungi numeri a un intervallo con un ciclo For-Next

I cicli For sono molto utili se è necessario eseguire attività ripetitive su uno specifico intervallo di valori: array o intervalli di celle. In parole povere, un ciclo dice "per ogni x, fai y".

Nell'editor VBA, seleziona Inserisci -> Nuovo modulo

Scrivi questo codice nella finestra del modulo (non incollare!):

Sub LoopExample ()

Dim X come numero intero

Per X = da 1 a 100

Intervallo ("A" e X). Valore = X

Avanti X

End Sub

Salva e torna alla scheda Sviluppatore di Excel e seleziona il pulsante Macro. Esegui la macro LoopExample.

Questo dovrebbe accadere:

Ecc, fino alla centesima riga.

Come lo sta facendo?

  • Dim X As Integer: dichiara la variabile X come un tipo di dati di Integer.
  • For X = 1 To 100: questo è l'inizio del ciclo For. In poche parole, dice al ciclo di continuare a ripetersi fino a X = 100. X è il contatore . Il ciclo continuerà a essere eseguito fino a X = 100, verrà eseguito un'ultima volta e quindi si interromperà.
  • Range ("A" & X) .Value = X: dichiara l'intervallo del ciclo e cosa inserire in quell'intervallo. Poiché X = 1 inizialmente, la prima cella sarà A1, a quel punto il ciclo inserirà X in quella cella.
  • X successiva: indica al ciclo di eseguire di nuovo

Quando potrei usarlo?

Il ciclo For-Next è una delle funzionalità più potenti di VBA; ci sono numerosi potenziali casi d'uso. Questo è un esempio più complesso che richiederebbe più livelli di logica, ma comunica il mondo delle possibilità nei cicli For-Next.

Forse hai un elenco di tutti i prodotti venduti nella tua panetteria nella colonna A, il tipo di prodotto nella colonna B (torte, ciambelle o muffin), il costo degli ingredienti nella colonna C e il costo medio di mercato di ciascun tipo di prodotto in un altro foglio.

Devi capire quale dovrebbe essere il prezzo al dettaglio di ogni prodotto. Stai pensando che dovrebbe essere il costo degli ingredienti più il 20%, ma anche l'1,2% al di sotto della media di mercato, se possibile. Un ciclo For-Next ti consentirebbe di eseguire questo tipo di calcolo.

Esempi del mondo reale

  • Utilizzare un ciclo con un'istruzione if nidificata per aggiungere valori specifici a un array separato solo se soddisfano determinate condizioni
  • Eseguire calcoli matematici su ciascun valore in un intervallo, ad esempio calcolare costi aggiuntivi e aggiungerli al valore
  • Fai scorrere ogni carattere in una stringa ed estrai tutti i numeri
  • Seleziona casualmente un numero di valori da un array

Conclusione

Ora che abbiamo parlato di pizza e muffin e, oh sì, di come scrivere codice VBA nei fogli di calcolo Excel, facciamo un controllo di apprendimento. Vedi se puoi rispondere a queste domande.

  • Cos'è VBA?
  • Come posso impostare per iniziare a utilizzare VBA in Excel?
  • Perché e quando useresti VBA?
  • Quali sono alcuni problemi che potrei risolvere con VBA?

Se hai una buona idea di come rispondere a queste domande, allora ha avuto successo.

Che tu sia un utente occasionale o un utente esperto, spero che questo tutorial abbia fornito informazioni utili su ciò che può essere realizzato con solo un po 'di codice nei tuoi fogli di calcolo Excel.

Buona programmazione!

Risorse di apprendimento

  • Programmazione Excel VBA per manichini, John Walkenbach
  • Inizia con VBA, documentazione Microsoft
  • Imparare VBA in Excel, Lynda

Un po 'di me

Sono Chloe Tucker, un'artista e sviluppatrice di Portland, Oregon. In qualità di ex educatore, cerco continuamente l'intersezione tra apprendimento e insegnamento, o tecnologia e arte. Contattami su Twitter @_chloetucker e controlla il mio sito web su chloe.dev.