Se hai appena iniziato a utilizzare VBA, allora vorrai iniziare a studiare i nostri Guida VBA per principianti. Ma se sei un esperto VBA esperto e stai cercando cose più avanzate che puoi fare con VBA in Excel, continua a leggere.
La possibilità di utilizzare la codifica VBA in Excel apre un mondo intero di automazione. Puoi automatizzare i calcoli in Excel, i pulsanti e persino inviare e-mail. Ci sono più possibilità di automatizzare il tuo lavoro quotidiano con VBA di quanto tu possa immaginare.
Guida VBA avanzata per Microsoft Excel
L'obiettivo principale di scrivere il codice VBA in Excel è di estrarre informazioni da un foglio di calcolo, esegui una varietà di calcoli su di esso, quindi riscrivi i risultati sul foglio di calcolo
I seguenti sono gli usi più comuni di VBA in Excel.
Con questi tre esempi, dovresti essere in grado di scrivere una varietà del proprio codice VBA di Excel avanzato.
Importazione di dati ed esecuzione di calcoli
Una delle cose più comuni per cui le persone usano Excel sta eseguendo calcoli su dati esistenti al di fuori di Excel. Se non si utilizza VBA, ciò significa che è necessario importare manualmente i dati, eseguire i calcoli e generare tali valori su un altro foglio o report.
In_content_1 all: [300x250] / dfp : [640x360]->Con VBA puoi automatizzare l'intero processo. Ad esempio, se hai un nuovo file CSV scaricato in una directory sul tuo computer ogni lunedì, puoi configurare il tuo codice VBA per l'esecuzione quando apri il foglio di calcolo per la prima volta martedì mattina.
Il seguente codice di importazione verrà eseguire e importare il file CSV nel foglio di calcolo di Excel.
Dim ws As Worksheet, strFile As StringSet ws = ActiveWorkbook.Sheets("Sheet1") Cells.ClearContents strFile = “c:\temp\purchases.csv” With ws.QueryTables.Add(Connection:="TEXT;" & strFile, Destination:=ws.Range("A1")) .TextFileParseType = xlDelimited .TextFileCommaDelimiter = True .Refresh End With
Apri lo strumento di modifica VBA di Excel e seleziona l'oggetto Foglio1. Dalle caselle a discesa oggetto e metodo, scegli Foglio di lavoroe Attiva. Questo eseguirà il codice ogni volta che apri il foglio di calcolo.
Questo creerà una funzione Sottotitolo_Attiva (). Incolla il codice sopra in quella funzione.
Imposta il foglio di lavoro attivo su Foglio1, cancella il foglio, si collega al file usando il percorso del file definito con la variabile strFile, quindi il Il ciclo Conscorre ciclicamente ogni riga del file e inserisce i dati nel foglio a partire dalla cella A1.
Se esegui questo codice, vedrai che i dati del file CSV viene importato nel foglio di calcolo vuoto, in Foglio1.
L'importazione è solo il primo passo . Successivamente, si desidera creare una nuova intestazione per la colonna che conterrà i risultati del calcolo. In questo esempio, supponiamo che tu voglia calcolare il 5% di tasse pagate sulla vendita di ogni articolo.
L'ordine delle azioni che il tuo codice dovrebbe intraprendere è:
Il seguente codice 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")
'Find Last Row and Column
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) = "taxes"
For Each cell In rng
fltTax = cell.Value * 0.05
Cells(rowCounter, 5) = fltTax
rowCounter = rowCounter + 1
Next cell
Questo codice trova l'ultima riga nel foglio di dati, quindi imposta l'intervallo di celle (la colonna con i prezzi di vendita) in base alla prima e all'ultima riga di dati. Quindi il codice scorre attraverso ciascuna di quelle celle, esegue il calcolo delle imposte e scrive i risultati nella nuova colonna (colonna 5).
Incolla il codice VBA sopra sotto il codice precedente ed esegui lo script. Vedrai i risultati mostrati nella colonna E.
Ora, ogni volta che apri il foglio di lavoro di Excel, uscirà automaticamente e otterrà la copia più recente dei dati dal file CSV. Quindi eseguirà i calcoli e scriverà i risultati sul foglio. Non devi più fare nulla manualmente!
Calcola risultati dal pulsante Premi
Se preferisci avere un controllo più diretto su quando vengono eseguiti i calcoli , anziché eseguire automaticamente quando si apre il foglio, è possibile utilizzare invece un pulsante di controllo.
I pulsanti di controllo sono utili se si desidera controllare quali calcoli vengono utilizzati. Ad esempio, nello stesso caso sopra riportato, cosa succede se si desidera utilizzare un'aliquota fiscale del 5% per una regione e un'aliquota fiscale del 7% per un'altra?
È possibile consentire lo stesso codice di importazione CSV per eseguito automaticamente, ma lascia eseguire il codice di calcolo fiscale quando si preme il pulsante appropriato.
Utilizzando lo stesso foglio di calcolo sopra, selezionare la scheda Sviluppatoree selezionare Inseriscidal gruppo Controllinella barra multifunzione. Seleziona il pulsanteControllo ActiveX dal menu a discesa.
Disegna il pulsante su qualsiasi parte del foglio lontano da dove andranno i dati.
Fai clic con il pulsante destro del mouse sul pulsante e seleziona Proprietà. Nella finestra Proprietà, modifica la didascalia in ciò che desideri visualizzare all'utente. In questo caso potrebbe essere Calcola imposta 5%.
Vedrai questo testo riflesso sul pulsante stesso. Chiudi la finestra Proprietàe fai doppio clic sul pulsante stesso. Questo aprirà la finestra dell'editor di codice e il cursore si troverà all'interno della funzione che verrà eseguita quando l'utente preme il pulsante.
Incolla il codice di calcolo fiscale dalla sezione precedente in questa funzione, mantenendo il moltiplicatore dell'aliquota fiscale a 0,05. Ricorda di includere le seguenti 2 righe per definire il foglio attivo.
Dim ws As Worksheet, strFile As String
Set ws = ActiveWorkbook.Sheets("Sheet1")
Ora, ripeti nuovamente il processo, creando un secondo pulsante. Imposta la didascalia Calcola imposta 7%.
Fai doppio clic su quel pulsante e incolla lo stesso codice, ma rendi il moltiplicatore di imposta 0,07.
Ora, a seconda del pulsante che premi, la colonna delle imposte sarà essere calcolato di conseguenza.
Al termine, avrai entrambi i pulsanti sul foglio. Ognuno di essi avvierà un diverso calcolo fiscale e scriverà risultati diversi nella colonna dei risultati.
Per scrivere questo, seleziona il menu Sviluppatore, quindi seleziona Modalità disegnodal gruppo Controlli nella barra multifunzione per disabilitare Modalità disegno. Questo attiverà i pulsanti.
Prova a selezionare ciascun pulsante per vedere come cambia la colonna del risultato "tasse".
Risultati del calcolo e-mail a qualcuno
Cosa se vuoi inviare i risultati sul foglio di lavoro a qualcuno via email?
Puoi creare un altro pulsante chiamato Email Sheet to Bossusando la stessa procedura sopra. Il codice per questo pulsante prevede l'utilizzo dell'oggetto Excel CDO per configurare le impostazioni e-mail SMTP e l'invio tramite e-mail dei risultati in un formato leggibile dall'utente.
Per abilitare questa funzione, devi selezionare Strumenti e riferimenti. Scorri verso il basso fino a Microsoft CDO per Windows 2000 Library, abilitalo e seleziona OK.
Esistono tre sezioni principali del codice che è necessario creare per inviare un'e-mail e incorporare i risultati del foglio di calcolo.
Il primo è impostare le variabili da conservare l'oggetto, gli indirizzi To e From e il corpo dell'email.
Dim CDO_Mail As Object
Dim CDO_Config As Object
Dim SMTP_Config As Variant
Dim strSubject As String
Dim strFrom As String
Dim strTo As String
Dim strCc As String
Dim strBcc As String
Dim strBody As String
Dim LastRow As Long
Dim StartCell As Range
Dim rowCounter As Integer
Dim rng As Range, cell As Range
Dim fltTax As Double
Set ws = ActiveWorkbook.Sheets("Sheet1")
strSubject = "Taxes Paid This Quarter"
strFrom = "[email protected]"
strTo = "[email protected]"
strCc = ""
strBcc = ""
strBody = "The following is the breakdown of taxes paid on sales this quarter."
Naturalmente, il corpo deve essere dinamico a seconda dei risultati nel foglio, quindi qui dovrai aggiungere un ciclo che attraversa l'intervallo, estrae i dati e scrive una riga alla volta nel corpo.
Set StartCell = Range("A1")
'Find Last Row and Column
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
strBody = strBody & vbCrLf
For Each cell In rng
strBody = strBody & vbCrLf
strBody = strBody & "We sold " & Cells(rowCounter, 3).Value & " of " & Cells(rowCounter, 1).Value _
& " for " & Cells(rowCounter, 4).Value & " and paid taxes of " & Cells(rowCounter, 5).Value & "."
rowCounter = rowCounter + 1
Next cell
La sezione successiva prevede la configurazione delle impostazioni SMTP in modo da poter inviare e-mail tramite il server SMTP. Se usi Gmail, in genere questo è il tuo indirizzo email Gmail, la tua password Gmail e il server SMTP Gmail (smtp.gmail.com).
Set CDO_Mail = CreateObject("CDO.Message")
On Error GoTo Error_Handling
Set CDO_Config = CreateObject("CDO.Configuration")
CDO_Config.Load -1
Set SMTP_Config = CDO_Config.Fields
With 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 protected]"
.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
.Update
End With
With CDO_Mail
Set .Configuration = CDO_Config
End With
Sostituisci [email protected] e password con i dettagli del tuo account.
Infine, per iniziare l'invio di 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
Error_Handling:
If Err.Description <> "" Then MsgBox Err.Description
Nota: se viene visualizzato un errore di trasporto quando si tenta di eseguire questo codice, è probabile che il tuo account Google stia bloccando l'esecuzione di "app meno sicure". Dovrai visitare pagina delle impostazioni delle app meno sicura e attivare questa funzione.
Dopo averlo abilitato, la tua email verrà inviata. Ecco come appare la persona che riceve l'email dei risultati generati automaticamente.
Come puoi vedere, puoi davvero automatizzare con Excel VBA. Prova a giocare con gli snippet di codice che hai appreso in questo articolo e crea le tue automazioni VBA uniche.