Se hai appena iniziato con VBA , allora ti consigliamo di iniziare a studiare la nostra guida VBA per principianti^{(VBA guide for beginners)} . 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 ^(VBA)Excel apre un intero mondo di automazione. Puoi automatizzare i calcoli in Excel , i pulsanti e persino inviare e-mail. Ci sono più possibilità per automatizzare il tuo lavoro quotidiano con VBA di quante tu possa immaginare.

Guida VBA avanzata per Microsoft Excel^{(Advanced VBA Guide For Microsoft Excel)}

L'obiettivo principale della scrittura del codice VBA in ^(VBA)Excel è quello di poter estrarre informazioni da un foglio di calcolo, eseguire una serie di calcoli su di esso e quindi riscrivere i risultati nel foglio di calcolo

Di seguito sono riportati gli usi più comuni di VBA in Excel .

• Importa^(Import) dati ed esegui calcoli
• Calcola^(Calculate) i risultati di un utente che preme un pulsante
• Invia^(Email) i risultati del calcolo via email a qualcuno

Con questi tre esempi, dovresti essere in grado di scrivere una varietà del tuo codice VBA avanzato di Excel .^{(Excel VBA)}

Importazione di dati ed esecuzione di calcoli^{(Importing Data and Performing Calculations)}

Una delle cose più comuni per cui le persone usano Excel è l'esecuzione di calcoli sui dati che esistono al di fuori di Excel . Se non usi VBA , significa che devi importare manualmente i dati, eseguire i calcoli e restituire quei valori a un altro foglio o report.

Con VBA puoi automatizzare l'intero processo. Ad esempio, se hai un nuovo file CSV scaricato in una directory del tuo computer ogni lunedì^(Monday) , puoi configurare il tuo codice VBA in modo che venga eseguito quando apri il foglio di calcolo per la prima volta martedì^(Tuesday) mattina.

Il seguente codice di importazione verrà eseguito e importerà il file CSV nel foglio di calcolo di ^(CSV)Excel .

Dim ws As Worksheet, strFile As String

Set 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^{(Excel VBA)} e seleziona l' oggetto Sheet1 . Dalle caselle a discesa dell'oggetto e del metodo, scegli Foglio^(Worksheet) di lavoro e Attiva^(Activate) . Questo eseguirà il codice ogni volta che apri il foglio di calcolo.

Questo creerà una funzione Sub Worksheet_Activate() . Incolla il codice sopra in quella funzione.

Questo imposta il foglio di lavoro attivo su Sheet1 , cancella il foglio, si connette al file usando il percorso del file che hai definito con la variabile strFile , quindi il ciclo With scorre 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 vengono importati nel tuo foglio di lavoro vuoto, in Sheet1 .

L'importazione è solo il primo passo. Successivamente, vuoi creare una nuova intestazione per la colonna che conterrà i risultati del calcolo. In questo esempio, supponiamo di voler calcolare il 5% di tasse pagate sulla vendita di ciascun articolo.

L'ordine delle azioni che il tuo codice dovrebbe intraprendere è:

1. Crea una nuova colonna di risultati chiamata tasse^(taxes) .
2. Scorri la colonna delle unità vendute^{(units sold)} e calcola l'imposta sulle vendite.
3. Scrivi i risultati del calcolo 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")

'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 tuo 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 queste celle, esegue il calcolo delle tasse 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 , questo 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 i risultati dalla pressione del pulsante^{(Calculate Results From Button Press)}

Se preferisci avere un controllo più diretto sull'esecuzione dei calcoli, piuttosto che essere eseguito automaticamente all'apertura del foglio, puoi invece utilizzare un pulsante di controllo.

I pulsanti di controllo^(Control) sono utili se si desidera controllare quali calcoli vengono utilizzati. Ad esempio, nello stesso caso di cui sopra, 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 l'esecuzione automatica dello stesso codice di importazione CSV , ma lasciare che il codice di calcolo delle tasse venga eseguito quando si preme il pulsante appropriato.

Utilizzando lo stesso foglio di calcolo di cui sopra, seleziona la scheda Sviluppatore e seleziona ^(Developer)Inserisci^(Insert) dal gruppo Controlli^(Controls) nella barra multifunzione. Selezionare il pulsante ^{(push button)} ActiveX Control dal menu a tendina.

Disegna il pulsante su qualsiasi parte del foglio lontano da dove andranno i dati.

Fare clic con il pulsante destro del mouse e selezionare Proprietà^(Properties) . Nella finestra Proprietà^(Properties) , cambia la didascalia in ciò che desideri mostrare all'utente. In questo caso potrebbe essere Calculate 5% Tax .

Vedrai questo testo riflesso sul pulsante stesso. Chiudere la finestra delle proprietà^(properties) e fare doppio clic sul pulsante stesso. Questo aprirà la finestra dell'editor di codice e il tuo cursore sarà all'interno della funzione che verrà eseguita quando l'utente premerà il pulsante.

Incolla il codice di calcolo dell'imposta dalla sezione precedente in questa funzione, mantenendo il moltiplicatore dell'aliquota fiscale a 0,05. Ricordarsi 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 di nuovo il processo, creando un secondo pulsante. Fai la didascalia Calculate 7% Tax .

Fare doppio clic su^{(Double-click)} quel pulsante e incollare lo stesso codice, ma impostare il moltiplicatore d'imposta 0,07.

Ora, a seconda del pulsante che premi, la colonna delle tasse verrà calcolata di conseguenza.

Una volta che hai finito, avrai entrambi i pulsanti sul foglio. Ognuno di loro avvierà un diverso calcolo delle tasse e scriverà risultati diversi nella colonna dei risultati. 

Per scrivere questo testo, seleziona il menu Sviluppatore e seleziona ^(Developer)Modalità di progettazione^{(Design Mode)} dal gruppo Controlli^(Controls) nella barra multifunzione per disabilitare la Modalità di progettazione^{(Design Mode)} . Questo attiverà i pulsanti. 

Prova a selezionare ciascun pulsante per vedere come cambia la colonna dei risultati "tasse".

Invia i risultati del calcolo via email a qualcuno^{(Email Calculation Results to Someone)}

Cosa succede se si desidera inviare i risultati sul foglio di calcolo a qualcuno tramite e-mail?

Puoi creare un altro pulsante chiamato Foglio di posta elettronica al capo^{(Email Sheet to Boss)} usando la stessa procedura sopra. Il codice per questo pulsante comporterà l'utilizzo dell'oggetto CDO di Excel per configurare le impostazioni di posta elettronica ^{(Excel CDO)}SMTP e l'invio dei risultati tramite posta elettronica in un formato leggibile dall'utente.

Per abilitare questa funzione, devi selezionare Strumenti e Riferimenti^{(Tools and References)} . Scorri verso il basso fino a Microsoft CDO per Windows 2000 Library^{(Microsoft CDO for Windows 2000 Library)} , abilitalo e seleziona OK .

Ci sono tre sezioni principali del codice che devi creare per inviare un'e-mail e incorporare i risultati del foglio di calcolo.

Il primo è l'impostazione delle variabili per contenere l'oggetto, gli indirizzi A e Da^(From) e il corpo dell'e-mail.

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."

Ovviamente, il corpo deve essere dinamico a seconda dei risultati che si trovano nel foglio, quindi qui dovrai aggiungere un ciclo che attraversi l'intervallo, estragga i dati e scriva 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 riguarda la configurazione delle impostazioni SMTP in modo da poter inviare e-mail tramite il server SMTP . Se utilizzi Gmail , questo è in genere il tuo indirizzo email Gmail , la tua password Gmail e il server ^(Gmail)SMTP^{(Gmail SMTP)} di 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 la password con i dettagli del tuo account.

Infine, per avviare l'invio dell'e-mail, inserire 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^(Note) : se viene visualizzato un errore di trasporto durante il tentativo di eseguire questo codice, è probabile che il tuo account Google stia bloccando l'esecuzione di "app meno sicure". Dovrai visitare la pagina delle impostazioni delle app meno sicure^{(less secure apps settings page)} e attivare questa funzione.

Dopo che è abilitato, la tua email verrà inviata. Ecco come appare alla persona che riceve l'e-mail dei risultati generata automaticamente.

Come puoi vedere, c'è molto che puoi effettivamente automatizzare con Excel VBA . Prova a giocare con i frammenti di codice che hai appreso in questo articolo e crea le tue automazioni VBA uniche.^(VBA)

An Advanced VBA Guide For MS Excel

If you are just getting started with VBA, then you’ll want to start oυt studying our VBA guide for beginners. But if you’re a seasoned VBA expert and you’re looking for more advanced things you can do with VBA in Excel, then keep reading.

The ability to use VBA coding in Excel opens up a whole world of automation. You can automate calculations in Excel, pushbuttons, and even send email. There are more possibilities to automate your daily work with VBA than you may realize.

Advanced VBA Guide For Microsoft Excel

The main goal of writing VBA code in Excel is so that you can extract information from a spreadsheet, perform a variety of calculations on it, and then write the results back to the spreadsheet

The following are the most common uses of VBA in Excel.

• Import data and perform calculations
• Calculate results from a user pressing a button
• Email calculation results to someone

With these three examples, you should be able to write a variety of your own advanced Excel VBA code.

Importing Data and Performing Calculations

One of the most common things people use Excel for is performing calculations on data that exists outside of Excel. If you don’t use VBA, that means you have to manually import the data, run the calculations and output those values to another sheet or report.

With VBA, you can automate the entire process. For example, if you have a new CSV file downloaded into a directory on your computer every Monday, you can configure your VBA code to run when you first open your spreadsheet on Tuesday morning.

The following import code will run and import the CSV file into your Excel spreadsheet.

Dim ws As Worksheet, strFile As String

Set 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

Open the Excel VBA editing tool and select the Sheet1 object. From the object and method dropdown boxes, choose Worksheet and Activate. This will run the code every time you open the spreadsheet.

This will create a Sub Worksheet_Activate() function. Paste the code above into that function.

This sets the active worksheet to Sheet1, clears the sheet, connects to the file using the file path you defined with the strFile variable, and then the With loop cycles through every line in the file and places the data into the sheet starting at cell A1.

If you run this code, you’ll see that the CSV file data is imported into your blank spreadsheet, in Sheet1.

Importing is only the first step. Next, you want to create a new header for the column that will contain your calculation results. In this example, let’s say you want to calculate the 5% taxes paid on the sale of each item.

The order of actions your code should take is:

1. Create new results column called taxes.
2. Loop through the units sold column and calculate the sales tax.
3. Write the calculate results to the appropriate row in the sheet.

The following code will accomplish all of these steps.

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

This code finds the last row in your sheet of data, and then sets the range of cells (the column with the sales prices) according to the first and last row of data. Then the code loops through each of those cells, performs the tax calculation and writes the results into your new column (column 5).

Paste the above VBA code below the previous code, and run the script. You will see the results show up in column E.

Now, every time you open your Excel worksheet, it’ll automatically go out and get the freshest copy of data from the CSV file. Then, it will perform the calculations and write the results to the sheet. You don’t have to do anything manually anymore!

Calculate Results From Button Press

If you’d rather have more direct control over when calculations run, rather than running automatically when the sheet opens, you can use a control button instead.

Control buttons are useful if you want to control which calculations are used. For example, in this same case as above, what if you want to use a 5% tax rate for one region, and a 7% tax rate for another?

You could allow the same CSV import code to run automatically, but leave the tax calculation code to run when you press the appropriate button.

Using the same spreadsheet as above, select the Developer tab, and select Insert from the Controls group in the ribbon. Select the push button ActiveX Control from the dropdown menu.

Draw the pushbutton onto any part of the sheet away from where any data will go.

Right-click the push button, and select Properties. In the Properties window, change the Caption to what you’d like to display to the user. In this case it might be Calculate 5% Tax.

You’ll see this text reflected on the push button itself. Close the properties window, and double-click the pushbutton itself. This will open the code editor window, and your cursor will be inside the function that will run when the user presses the pushbutton.

Paste the tax calculation code from the section above into this function, keeping the tax rate multiplier at 0.05. Remember to include the following 2 lines to define the active sheet.

Dim ws As Worksheet, strFile As String

Set ws = ActiveWorkbook.Sheets("Sheet1")

Now, repeat the process again, creating a second push button. Make the caption Calculate 7% Tax.

Double-click that button and paste the same code, but make the tax multiplier 0.07.

Now, depending which button you press, the taxes column will be calculated accordingly.

Once you’re done, you’ll have both push buttons on your sheet. Each of them will initiate a different tax calculation and will write different results into the result column. 

To text this, select the Developer menu, and select Design Mode form the Controls group in the ribbon to disable Design Mode. This will activate the push buttons. 

Try selecting each push button to see how the “taxes” result column changes.

Email Calculation Results to Someone

What if you want to send the results on the spreadsheet to someone via email?

You could create another button called Email Sheet to Boss using the same procedure above. The code for this button will involve using the Excel CDO object to configure SMTP email settings, and emailing the results in a user-readable format.

To enable this feature, you need to select Tools and References. Scroll down to Microsoft CDO for Windows 2000 Library, enable it, and select OK.

There are three main sections to the code you need to create to send out an email and embed spreadsheet results.

The first is setting up variables to hold the subject, To and From addresses, and the email body.

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."

Of course, the body needs to be dynamic depending on what results are in the sheet, so here you’ll need to add a loop that goes through the range, extracts the data, and writes a line at a time to the body.

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

The next section involves setting up the SMTP settings so that you can send email through your SMTP server. If you use Gmail, this is typically your Gmail email address, your Gmail password, and the Gmail SMTP server (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

Replace [email protected] and password with your own account details.

Finally, to initiate the email send, insert the following code.

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

Note: If you see a transport error when trying to run this code, it’s likely because your Google account is blocking “less secure apps” from running. You’ll need to visit the less secure apps settings page and turn this feature ON.

After that’s enabled, your email will be sent. This is what it looks like to the person who receives your automatically generated results email.

As you can see there is a lot you can actually automate with Excel VBA. Try playing around with the code snippets you’ve learned about in this article and create your own unique VBA automations.

Related posts

• La migliore guida VBA (per principianti) di cui avrai mai bisogno

• Come creare una macro o uno script VBA in Excel

• Una guida a tutte le estensioni dei file di Excel e al loro significato

• Come eliminare le righe vuote in Excel

• Risolto il messaggio di errore "Impossibile verificare la licenza" di MS Office

• Come ordinare per data in Excel

• Come creare più elenchi a discesa collegati in Excel

• Come unire celle, colonne e righe in Excel

• Come utilizzare i riferimenti assoluti in Excel

• Fogli Google vs Microsoft Excel: quali sono le differenze?

• Collegamento di Excel a MySQL

• Come aprire Word ed Excel in modalità provvisoria

• Come cercare in Excel

• Controlla tutti i collegamenti ipertestuali in un documento MS Word in pochi secondi

• Utilizzo dello strumento di ricerca degli obiettivi di analisi What-If di Excel

• 2 modi per utilizzare la funzione di trasposizione di Excel

• Come utilizzare le istruzioni If e Nested If in Excel

• Usa i nomi degli intervalli dinamici in Excel per i menu a discesa flessibili

• Scrivi rapidamente i numeri in Word ed Excel

• Come spostare le colonne in Excel