Als je net begint met VBA , dan wil je misschien beginnen met het bestuderen van onze VBA-gids voor beginners^{(VBA guide for beginners)} . Maar als u een doorgewinterde VBA- expert bent en op zoek bent naar meer geavanceerde dingen die u met VBA in Excel kunt doen , lees dan verder.

De mogelijkheid om VBA - codering in Excel te gebruiken, opent een hele wereld van automatisering. U kunt berekeningen automatiseren in Excel , drukknoppen en zelfs e-mail verzenden. Er zijn meer mogelijkheden om je dagelijkse werk met VBA te automatiseren dan je misschien denkt.

Geavanceerde VBA-gids voor Microsoft Excel^{(Advanced VBA Guide For Microsoft Excel)}

Het belangrijkste doel van het schrijven van VBA -code in Excel is dat u informatie uit een spreadsheet kunt extraheren, er verschillende berekeningen op kunt uitvoeren en de resultaten vervolgens terug naar de spreadsheet kunt schrijven.

Hieronder volgen de meest voorkomende toepassingen van VBA in Excel .

• Gegevens importeren^(Import) en berekeningen uitvoeren
• Bereken^(Calculate) resultaten van een gebruiker die op een knop drukt
• ^(Email)Rekenresultaten naar iemand e- mailen

Met deze drie voorbeelden zou u in staat moeten zijn om verschillende van uw eigen geavanceerde Excel VBA -code te schrijven.

Gegevens importeren en berekeningen uitvoeren^{(Importing Data and Performing Calculations)}

Een van de meest voorkomende dingen waarvoor mensen Excel gebruiken , is het uitvoeren van berekeningen op gegevens die buiten Excel bestaan . Als u geen VBA gebruikt , betekent dit dat u de gegevens handmatig moet importeren, de berekeningen moet uitvoeren en die waarden naar een ander blad of rapport moet uitvoeren.

Met VBA kunt u het hele proces automatiseren. Als u bijvoorbeeld elke maandag een nieuw ^(Monday)CSV -bestand in een map op uw computer hebt gedownload , kunt u uw VBA -code zo configureren dat deze wordt uitgevoerd wanneer u uw spreadsheet op dinsdagochtend^(Tuesday) voor het eerst opent .

De volgende importcode wordt uitgevoerd en importeert het CSV -bestand in uw 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 de Excel VBA -bewerkingstool en selecteer het object Blad1 . ^(Sheet1)Kies in de vervolgkeuzelijsten voor objecten en methoden de optie Werkblad^(Worksheet) en Activeer^(Activate) . Hierdoor wordt de code elke keer uitgevoerd wanneer u de spreadsheet opent.

Hiermee wordt een functie Sub Worksheet_Activate() gemaakt. Plak de bovenstaande code in die functie.

Dit stelt het actieve werkblad in op Blad1^(Sheet1) , wist het blad, maakt verbinding met het bestand met behulp van het bestandspad dat u hebt gedefinieerd met de variabele strFile , en vervolgens doorloopt de With -lus elke regel in het bestand en plaatst de gegevens in het blad vanaf cel A1 .

Als u deze code uitvoert, ziet u dat de CSV -bestandsgegevens worden geïmporteerd in uw lege spreadsheet, in Blad1^(Sheet1) .

Importeren is slechts de eerste stap. Vervolgens wilt u een nieuwe koptekst maken voor de kolom die uw berekeningsresultaten zal bevatten. Laten we in dit voorbeeld zeggen dat u de 5% belasting wilt berekenen die is betaald over de verkoop van elk artikel.

De volgorde van acties die uw code moet nemen is:

1. Maak een nieuwe resultatenkolom met de naam belastingen^(taxes) .
2. Loop door de kolom verkochte eenheden^{(units sold)} en bereken de omzetbelasting.
3. Schrijf de berekeningsresultaten naar de juiste rij in het blad.

Met de volgende code worden al deze stappen uitgevoerd.

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

Deze code zoekt de laatste rij in uw gegevensblad en stelt vervolgens het cellenbereik (de kolom met de verkoopprijzen) in op basis van de eerste en laatste gegevensrij. Vervolgens loopt de code door elk van die cellen, voert de belastingberekening uit en schrijft de resultaten in uw nieuwe kolom (kolom 5).

Plak de bovenstaande VBA -code onder de vorige code en voer het script uit. U ziet de resultaten verschijnen in kolom E.

Elke keer dat u uw Excel -werkblad opent, gaat het automatisch uit en krijgt u de nieuwste kopie van de gegevens uit het CSV -bestand. Vervolgens voert het de berekeningen uit en schrijft het de resultaten naar het blad. U hoeft niets meer handmatig te doen!

Bereken resultaten van druk op de knop^{(Calculate Results From Button Press)}

Als u liever meer directe controle heeft over wanneer berekeningen worden uitgevoerd, in plaats van automatisch wanneer het werkblad wordt geopend, kunt u in plaats daarvan een bedieningsknop gebruiken.

Bedieningsknoppen^(Control) zijn handig als u wilt bepalen welke berekeningen worden gebruikt. Bijvoorbeeld, in hetzelfde geval als hierboven, wat als u een belastingtarief van 5% wilt gebruiken voor de ene regio en een belastingtarief van 7% voor een andere?

U kunt dezelfde CSV -importcode automatisch laten uitvoeren, maar de belastingberekeningscode laten lopen wanneer u op de juiste knop drukt.

Gebruik dezelfde spreadsheet als hierboven, selecteer het tabblad Ontwikkelaar^(Developer) en selecteer Invoegen^(Insert) in de groep Besturingselementen^(Controls) op het lint. Selecteer de drukknop ^{(push button)} ActiveX-besturing^{(ActiveX Control)} in het vervolgkeuzemenu.

Teken de drukknop op een willekeurig deel van het blad, weg van de plaats waar de gegevens naartoe gaan.

Klik met de rechtermuisknop op de drukknop en selecteer Eigenschappen^(Properties) . Wijzig in het venster Eigenschappen^(Properties) het bijschrift in wat u aan de gebruiker wilt laten zien. In dit geval is het misschien Calculate 5% Tax .

Je ziet deze tekst weerspiegeld op de drukknop zelf. Sluit het eigenschappenvenster^(properties) en dubbelklik op de drukknop zelf. Dit opent het code-editorvenster en uw cursor bevindt zich in de functie die wordt uitgevoerd wanneer de gebruiker op de drukknop drukt.

Plak de belastingberekeningscode uit het bovenstaande gedeelte in deze functie, waarbij u de multiplier van het belastingtarief op 0,05 houdt. Vergeet niet om de volgende 2 regels op te nemen om het actieve blad te definiëren.

Dim ws As Worksheet, strFile As String

Set ws = ActiveWorkbook.Sheets("Sheet1")

Herhaal het proces nu opnieuw en maak een tweede drukknop. Maak het bijschrift Calculate 7% Tax .

Dubbelklik op^{(Double-click)} die knop en plak dezelfde code, maar maak de belastingvermenigvuldiger 0,07.

Nu, afhankelijk van op welke knop u drukt, wordt de kolom belastingen dienovereenkomstig berekend.

Als je klaar bent, heb je beide drukknoppen op je blad. Elk van hen start een andere belastingberekening en schrijft verschillende resultaten in de resultatenkolom. 

Om dit te sms'en, selecteert u het menu Ontwikkelaars^(Developer) en selecteert u Ontwerpmodus^{(Design Mode)} uit de groep Besturingselementen^(Controls) in het lint om Ontwerpmodus^{(Design Mode)} uit te schakelen . Hierdoor worden de drukknoppen geactiveerd. 

Probeer elke drukknop te selecteren om te zien hoe de resultaatkolom "belastingen" verandert.

Berekeningsresultaten naar iemand e-mailen^{(Email Calculation Results to Someone)}

Wat als u de resultaten op de spreadsheet per e-mail naar iemand wilt verzenden?

U kunt met dezelfde procedure hierboven een andere knop maken met de naam E-mailblad naar baas . ^{(Email Sheet to Boss)}De code voor deze knop omvat het gebruik van het Excel CDO -object om SMTP -e-mailinstellingen te configureren en de resultaten te e-mailen in een door de gebruiker leesbaar formaat.

Om deze functie in te schakelen, moet u Tools and References selecteren . Scroll naar beneden naar Microsoft CDO voor Windows 2000 Library^{(Microsoft CDO for Windows 2000 Library)} , schakel het in en selecteer OK .

Er zijn drie hoofdsecties voor de code die u moet maken om een ​​e-mail te verzenden en spreadsheetresultaten in te sluiten.

De eerste is het instellen van variabelen voor het onderwerp, de aan- en van^(From) -adressen en de hoofdtekst van de 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."

Natuurlijk moet de body dynamisch zijn, afhankelijk van de resultaten in het blad, dus hier moet je een lus toevoegen die door het bereik gaat, de gegevens extraheert en regel voor regel naar de body schrijft.

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

Het volgende gedeelte betreft het instellen van de SMTP -instellingen, zodat u e-mail kunt verzenden via uw SMTP -server. Als u Gmail gebruikt , is dit meestal uw Gmail -e-mailadres, uw Gmail - wachtwoord en de 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

Vervang [email protected] en wachtwoord door uw eigen accountgegevens.

Ten slotte, om het verzenden van de e-mail te starten, voert u de volgende code in.

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

Opmerking^(Note) : als u een transportfout ziet wanneer u deze code probeert uit te voeren, komt dit waarschijnlijk omdat uw Google - account de uitvoering van "minder veilige apps" blokkeert. U moet naar de instellingenpagina voor minder veilige apps^{(less secure apps settings page)} gaan en deze functie AAN zetten.

Nadat dat is ingeschakeld, wordt uw e-mail verzonden. Dit is hoe het eruit ziet voor de persoon die uw automatisch gegenereerde e-mail met resultaten ontvangt.

Zoals u kunt zien, kunt u veel automatiseren met Excel VBA . Probeer te spelen met de codefragmenten waarover u in dit artikel hebt geleerd en maak uw eigen unieke VBA - automatiseringen.

An Advanced VBA Guide For MS Excel

Іf yoυ are just getting started with VΒA, then you’ll want to start out 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

• De beste VBA-gids (voor beginners) die u ooit nodig zult hebben

• Een VBA-macro of -script maken in Excel

• Een gids voor alle Excel-bestandsextensies en wat ze betekenen

• Hoe lege regels in Excel te verwijderen

• Fix MS Office "Kan licentie niet verifiëren" Foutbericht

• Hoe If en Geneste If-statements in Excel te gebruiken?

• Waarom u benoemde bereiken in Excel zou moeten gebruiken?

• Hoe de Z-score in Excel te berekenen

• Hoe een IF-formule / verklaring in Excel te schrijven?

• Centreer uw werkbladgegevens in Excel om af te drukken

• Een Excel-werkblad invoegen in een Word-document

• 4 manieren om een ​​vinkje te gebruiken in Excel

• Excel's What-If Analysis Goal Seek Tool gebruiken

• Hoe rasterlijnen in Excel te verwijderen

• Wat is Microsoft Publisher? Een beginnershandleiding

• CSV of TSV invoegen in een Excel-werkblad

• Kolommen verplaatsen in Excel

• Hoe maak je een cirkeldiagram in Excel

• Dynamische bereiknamen gebruiken in Excel voor flexibele vervolgkeuzelijsten

• 3 manieren om een ​​cel in Excel te splitsen