Se stai appena iniziando a usare VBA, dovrai iniziare studiando Una guida per principianti a VBAMa se sei un esperto di VBA e stai cercando cose più avanzate che puoi fare con VBA in Excel, continua a leggere.
La possibilità di utilizzare il markup VBA in Excel apre le porte a un mondo di automazione. È possibile automatizzare calcoli, pulsanti e persino l'invio di email in Excel. Le possibilità di automatizzare il lavoro quotidiano con VBA sono più numerose di quanto si possa immaginare.
Guida VBA avanzata per Microsoft Excel
Lo scopo principale della scrittura di codice VBA in Excel è consentire di estrarre informazioni da un foglio di calcolo, eseguire vari calcoli su di esso e quindi scrivere i risultati nel foglio di calcolo.
Di seguito sono riportati gli usi più comuni di VBA in Excel.
- Importazione di dati ed esecuzione di calcoli
- Calcolo dei risultati in base ai clic sui pulsanti dell'utente
- Inviare i risultati dei calcoli via e-mail a qualcuno
Utilizzando questi tre esempi, dovresti essere in grado di scrivere una varietà di codice VBA Excel avanzato.
Importazione di dati ed esecuzione di calcoli
Uno degli scopi più comuni per cui Excel viene utilizzato è l'esecuzione di calcoli su dati esterni a Excel. Se non si utilizza VBA, è necessario importare manualmente i dati, eseguire i calcoli e visualizzare i valori in un altro foglio o report.
Utilizzando VBA, è possibile automatizzare l'intero processo. Ad esempio, se ogni lunedì scarichi un nuovo file CSV in una directory del computer, puoi configurare il codice VBA in modo che venga eseguito alla prima apertura del foglio di calcolo il martedì mattina.
Verrà eseguito il seguente codice di importazione e il file CSV verrà importato nel foglio di calcolo Excel.
Dim ws As Worksheet, strFile As String Set ws = ActiveWorkbook.Sheets("Sheet1") Cells.ClearContents strFile = "c:temppurchases.csv" With ws.QueryTables.Add(Connection:="TEXT;" & strFile, Destination:=ws.Range("A1")).TextFileParseType = xlDelimited .TextFileCommaDelimiter = True .Refresh End With
Aprire l'editor VBA di Excel e selezionare l'oggetto Sheet1. Dalle caselle di riepilogo a discesa Oggetto e Metodo, scegliere Foglio di lavoro e AttivareCiò farà sì che il codice venga eseguito ogni volta che si apre il foglio di calcolo.
Questo creerà una funzione Sub Worksheet_Activate()Incolla il codice sopra in questa funzione.
Questo imposta il foglio di lavoro attivo su Sheet1Scansiona il foglio e connettiti al file utilizzando il percorso specificato nella variabile. strFilePoi il ciclo Con Esaminando ogni riga del file e inserendo i dati sul foglio a partire dalla cella A1.
Se esegui questo codice, vedrai che i dati del file CSV vengono importati nel tuo foglio di calcolo vuoto. Sheet1.
L'importazione è solo il primo passo. Successivamente, dovrai creare una nuova intestazione di colonna che conterrà i risultati dei tuoi calcoli. In questo esempio, supponiamo di voler calcolare l'imposta del 5% pagata sulla vendita di ciascun articolo.
L'ordine delle azioni che il codice dovrebbe intraprendere è:
- Crea una nuova colonna di risultati denominata le tasse.
- Ripeti su una colonna unità vendute E calcola l'imposta sulle vendite.
- Scrivi i risultati dei calcoli nella riga appropriata del foglio.
Il codice seguente eseguirà tutti questi passaggi.
Dim LastRow As Long Dim StartCell As Range Dim rowCounter As Integer Dim rng As Range, cell As Range Dim fltTax As Double Set StartCell = Range("A1") 'Trova l'ultima riga e colonna LastRow = ws.Cells(ws.Rows.Count, StartCell.Column).End(xlUp).Row Set rng = ws.Range(ws.Cells(2, 4), ws.Cells(LastRow, 4)) rowCounter = 2 Cells(1, 5) = "tasse" per ogni cella in rng fltTax = cell.Value * 0.05 Cells(rowCounter, 5) = fltTax rowCounter = rowCounter + 1 Cella successiva
Questo codice trova l'ultima riga del foglio dati, quindi imposta l'intervallo di celle (la colonna contenente i prezzi di vendita) in base alla prima e all'ultima riga di dati. Il codice quindi scorre ciascuna di queste celle, calcola l'imposta e scrive i risultati nella nuova colonna (colonna 5).
Incolla il codice VBA qui sopra sotto il codice precedente ed esegui lo script. I risultati appariranno nella colonna E.
Ora, ogni volta che apri il tuo foglio di lavoro Excel, il programma recupererà automaticamente la versione più recente dei dati dal file CSV. Eseguirà quindi i calcoli e scriverà i risultati nel foglio di lavoro. Non dovrai più fare nulla manualmente!
Calcola i risultati premendo il pulsante
Se preferisci avere un controllo diretto sull'esecuzione dei calcoli, anziché eseguirli automaticamente all'apertura del foglio di lavoro, puoi utilizzare un pulsante di controllo.
I pulsanti di controllo sono utili se si desidera controllare i calcoli utilizzati. Ad esempio, nello stesso scenario menzionato sopra, cosa succederebbe se si volesse utilizzare un'aliquota fiscale del 5% per una regione e del 7% per un'altra?
È possibile consentire l'esecuzione automatica dello stesso codice di importazione CSV, ma lasciare che il codice di calcolo delle imposte venga eseguito quando si preme il pulsante appropriato.
Utilizzando la stessa tabella dati menzionata sopra, selezionare la scheda Sviluppatoree selezionare inserire Da un gruppo Controls Nella barra multifunzione, seleziona un controllo. premi il bottone Selezionare ActiveX dal menu a discesa.
Disegna il pulsante su un punto qualsiasi del foglio, lontano dalla zona in cui andrebbero inseriti i dati.
Fare clic con il pulsante destro del mouse sul pulsante e selezionare Proprietà a ConfrontoNella finestra Proprietà, modifica la Didascalia in base a ciò che desideri visualizzare all'utente. In questo caso, potrebbe essere Calcola l'imposta del 5%.
Vedrai questo testo riflesso sul pulsante stesso. Chiudi finestra ProprietàFare doppio clic sullo stesso pulsante. Si aprirà una finestra dell'editor di codice e il cursore si troverà all'interno della funzione che verrà eseguita quando l'utente clicca sul pulsante.
Incolla il codice di calcolo delle imposte dalla sezione precedente in questa funzione, mantenendo il moltiplicatore dell'aliquota fiscale a 0.05. Ricordati di includere le due righe seguenti per identificare il foglio attivo.
Dim ws come foglio di lavoro, strFile come stringa Imposta ws = ActiveWorkbook.Sheets("Sheet1")
Ora ripeti il processo e crea un secondo pulsante. Etichettalo Calcolo dell'imposta del 7%.
Fare doppio clic su questo pulsante e incollare lo stesso codice, ma impostando il moltiplicatore fiscale su 0.07.
Ora, in base al pulsante premuto, la colonna delle imposte verrà calcolata di conseguenza.
Una volta terminato, avrai entrambi i pulsanti sul tuo foglio. Ognuno di essi avvierà un calcolo delle imposte diverso e visualizzerà risultati diversi nella colonna dei risultati.
Per testarlo, seleziona un elenco Sviluppatoree selezionare Modalità di progettazione Dal gruppo di controlli nella barra multifunzione da disabilitare Modalità di progettazioneQuesto attiverà i pulsanti.
Prova a selezionare ciascun pulsante per vedere come cambia la colonna dei risultati "Tasse".
Inviare i risultati dell'account a qualcuno tramite e-mail
Cosa succederebbe se volessi inviare i risultati del foglio di calcolo a qualcuno tramite e-mail?
Puoi creare un altro pulsante chiamato Invia il foglio via email al capo Utilizzando la stessa procedura descritta sopra, il codice per questo pulsante comporterà l'utilizzo di un oggetto CDO di Excel per configurare le impostazioni di posta elettronica SMTP e inviare i risultati tramite posta elettronica in un formato intuitivo.
Per abilitare questa funzione, è necessario selezionare Strumenti e riferimenti. Scorri verso il basso fino a Libreria Microsoft CDO per Windows 2000Abilitalo e seleziona OK.
Per inviare un'e-mail e includere i risultati del foglio di calcolo, è necessario creare tre sezioni principali di codice.
Il primo è impostare variabili per conservare l'oggetto, gli indirizzi "a" e "da" e il testo dell'e-mail.
Dim CDO_Mail come oggetto Dim CDO_Config come oggetto Dim SMTP_Config come variante Dim strSubject come stringa Dim strFrom come stringa Dim strTo come stringa Dim strCc come stringa Dim strBcc come stringa Dim strBody come stringa Dim LastRow come lungo Dim StartCell come intervallo Dim rowCounter come intero Dim rng come intervallo, cella come intervallo Dim fltTax come doppio
Imposta ws = ActiveWorkbook.Sheets("Sheet1") strSubject = "Imposte pagate in questo trimestre" strFrom = "MyEmail@gmail.com" strTo = "BossEmail@gmail.com" strCc = "" strBcc = "" strBody = "Di seguito è riportata la ripartizione delle imposte pagate sulle vendite in questo trimestre."
Naturalmente, il testo deve essere dinamico a seconda dei risultati nel documento, quindi qui sarà necessario aggiungere un ciclo che attraversa l'intervallo, estrae i dati e scrive una riga ogni volta nel testo.
Imposta StartCell = Range("A1"). vbCrLf per ogni cella in rng strBody = strBody & vbCrLf strBody = strBody & "Abbiamo venduto " & Cells(rowCounter, 3).Value & " di " & Cells(rowCounter, 1).Value _ & " per " & Cells(rowCounter, 4).Value & " e pagato le tasse di " & Cells(rowCounter, 5).Value & "." rowCounter = rowCounter + 1 Cella successiva
La sezione successiva riguarda la configurazione del protocollo SMTP per inviare email tramite il server SMTP. Se utilizzi Gmail, solitamente si tratta del tuo indirizzo email Gmail, della tua password Gmail e del server SMTP Gmail (smtp.gmail.com).
Imposta CDO_Mail = CreateObject("CDO.Message") In caso di errore Vai a Gestione errori Imposta CDO_Config = CreateObject("CDO.Configuration") CDO_Config.Load -1 Imposta SMTP_Config = CDO_Config.Fields con SMTP_Config .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com" .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1 .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "email@website.com" .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "password" .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465 .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True Imposta .Configuration = CDO_Config Termina con
sostituire email@sitoweb.com La password contiene i dettagli del tuo account.
Infine, per iniziare a inviare l'e-mail, inserisci il seguente codice.
CDO_Mail.Subject = strSubject CDO_Mail.From = strFrom CDO_Mail.To = strTo CDO_Mail.TextBody = strBody CDO_Mail.CC = strCc CDO_Mail.BCC = strBcc CDO_Mail.Send Gestione degli errori: se Err.Description <> "" allora MsgBox Err.Description
ملاحظةSe riscontri un errore di trasferimento quando provi a eseguire questo codice, è probabile che il tuo account Google stia bloccando l'esecuzione di "app meno sicure". Dovrai visitare [link/riferimento]. Pagina delle impostazioni delle app meno sicure E abilita questa opzione.
Dopo aver abilitato questa opzione, la tua email verrà inviata. Ecco come apparirà alla persona che riceverà la tua email generata automaticamente.
Come puoi vedere, c'è molto che puoi effettivamente automatizzare usando Excel VBA. Prova a sperimentare con i frammenti di codice che hai imparato in questo articolo e crea le tue automazioni VBA personalizzate.
I commenti sono chiusi.