Hvis du lige er begyndt med VBA , så vil du gerne starte med at studere vores VBA-guide for begyndere^{(VBA guide for beginners)} . Men hvis du er en erfaren VBA -ekspert, og du leder efter mere avancerede ting, du kan gøre med VBA i Excel , så fortsæt med at læse.

Muligheden for at bruge VBA -kodning i Excel åbner op for en hel verden af ​​automatisering. Du kan automatisere beregninger i Excel , trykknapper og endda sende e-mail. Der er flere muligheder for at automatisere dit daglige arbejde med VBA , end du måske er klar over.

Avanceret VBA-vejledning til Microsoft Excel^{(Advanced VBA Guide For Microsoft Excel)}

Hovedmålet med at skrive VBA -kode i Excel er, at du kan udtrække information fra et regneark, udføre en række forskellige beregninger på det og derefter skrive resultaterne tilbage til regnearket.

Følgende er de mest almindelige anvendelser af VBA i Excel .

• Importer^(Import) data og udfør beregninger
• Beregn^(Calculate) resultater fra en bruger, der trykker på en knap
• E- mail beregningsresultater til nogen

Med disse tre eksempler bør du være i stand til at skrive en række af din egen avancerede Excel VBA -kode.

Import af data og udførelse af beregninger^{(Importing Data and Performing Calculations)}

En af de mest almindelige ting, folk bruger Excel til, er at udføre beregninger på data, der findes uden for Excel . Hvis du ikke bruger VBA , betyder det, at du manuelt skal importere dataene, køre beregningerne og udlæse disse værdier til et andet ark eller rapport.

Med VBA kan du automatisere hele processen. For eksempel, hvis du har en ny CSV -fil downloadet til en mappe på din computer hver mandag^(Monday) , kan du konfigurere din VBA -kode til at køre, når du først åbner dit regneark tirsdag^(Tuesday) morgen.

Følgende importkode vil køre og importere CSV -filen til dit Excel - regneark.

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

Åbn Excel VBA- redigeringsværktøjet, og vælg Sheet1- objektet. Fra objekt- og metoderullelisten skal du vælge Arbejdsark^(Worksheet) og Aktiver^(Activate) . Dette vil køre koden, hver gang du åbner regnearket.

Dette vil oprette en Sub Worksheet_Activate() funktion. Indsæt koden ovenfor i den funktion.

Dette indstiller det aktive regneark til Sheet1 , rydder arket, opretter forbindelse til filen ved hjælp af den filsti, du definerede med strFile- variablen, og derefter bladrer With -løkken gennem hver linje i filen og placerer dataene i arket startende ved celle A1 .

Hvis du kører denne kode, vil du se, at CSV -fildataene importeres til dit tomme regneark i Sheet1 .

Import er kun det første skridt. Dernæst vil du oprette en ny overskrift til kolonnen, der skal indeholde dine beregningsresultater. Lad os i dette eksempel sige, at du vil beregne de 5 % afgifter, der betales ved salget af hver vare.

Rækkefølgen af ​​handlinger din kode skal udføre er:

1. Opret ny resultatkolonne kaldet skatter^(taxes) .
2. Gå gennem kolonnen solgte enheder^{(units sold)} og beregn momsen.
3. Skriv de beregnede resultater til den relevante række i arket.

Den følgende kode vil udføre alle disse trin.

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

Denne kode finder den sidste række i dit dataark og indstiller derefter celleområdet (kolonnen med salgspriserne) i henhold til den første og sidste række af data. Derefter går koden gennem hver af disse celler, udfører skatteberegningen og skriver resultaterne i din nye kolonne (kolonne 5).

Indsæt ovenstående VBA -kode under den forrige kode, og kør scriptet. Du vil se resultaterne vises i kolonne E.

Nu, hver gang du åbner dit Excel -regneark, vil det automatisk gå ud og få den nyeste kopi af data fra CSV -filen. Derefter udfører den beregningerne og skriver resultaterne til arket. Du behøver ikke at gøre noget manuelt længere!

Beregn resultater fra knaptryk^{(Calculate Results From Button Press)}

Hvis du hellere vil have mere direkte kontrol over, hvornår beregninger kører, frem for at køre automatisk, når arket åbnes, kan du bruge en kontrolknap i stedet for.

Kontrolknapper^(Control) er nyttige, hvis du vil kontrollere, hvilke beregninger der skal bruges. For eksempel, i samme tilfælde som ovenfor, hvad hvis du vil bruge en skattesats på 5 % for én region og en skattesats på 7 % for en anden?

Du kan tillade, at den samme CSV -importkode kører automatisk, men lad afgiftsberegningskoden køre, når du trykker på den relevante knap.

Brug det samme regneark som ovenfor, vælg fanen Udvikler^(Developer) og vælg Indsæt^(Insert) fra gruppen Kontrolelementer^(Controls) på båndet. Vælg trykknappen ^{(push button)} ActiveX Control fra rullemenuen.

Træk trykknappen på en hvilken som helst del af arket væk fra, hvor alle data vil gå.

Højreklik på trykknappen, og vælg Egenskaber^(Properties) . I vinduet Egenskaber^(Properties) skal du ændre billedteksten til det, du vil have vist til brugeren. I dette tilfælde kan det være Calculate 5% Tax .

Du vil se denne tekst afspejlet på selve trykknappen. Luk egenskabsvinduet^(properties) , og dobbeltklik på selve trykknappen. Dette åbner koderedigeringsvinduet, og din markør vil være inde i den funktion, der kører, når brugeren trykker på trykknappen.

Indsæt momsberegningskoden fra afsnittet ovenfor i denne funktion, og hold skattesatsmultiplikatoren på 0,05. Husk at inkludere følgende 2 linjer for at definere det aktive ark.

Dim ws As Worksheet, strFile As String

Set ws = ActiveWorkbook.Sheets("Sheet1")

Gentag nu processen igen, opret en anden trykknap. Lav billedteksten Calculate 7% Tax .

Dobbeltklik på^{(Double-click)} den knap og indsæt den samme kode, men gør skattemultiplikatoren til 0,07.

Nu, afhængigt af hvilken knap du trykker på, vil skattekolonnen blive beregnet i overensstemmelse hermed.

Når du er færdig, har du begge trykknapper på dit ark. Hver af dem vil starte en anden skatteberegning og vil skrive forskellige resultater i resultatkolonnen. 

For at skrive dette skal du vælge Udviklermenuen^(Developer) og vælge Designtilstand^{(Design Mode)} fra gruppen Kontrolelementer^(Controls) på båndet for at deaktivere Designtilstand^{(Design Mode)} . Dette vil aktivere trykknapperne. 

Prøv at vælge hver enkelt trykknap for at se, hvordan resultatkolonnen for "afgifter" ændres.

E-mail beregningsresultater til nogen^{(Email Calculation Results to Someone)}

Hvad hvis du vil sende resultaterne på regnearket til nogen via e-mail?

Du kan oprette en anden knap kaldet Email Sheet to Boss ved at bruge samme procedure ovenfor. Koden til denne knap involverer brug af Excel CDO - objektet til at konfigurere SMTP -e-mail-indstillinger og e-mail med resultaterne i et brugerlæsbart format.

For at aktivere denne funktion skal du vælge Værktøjer og referencer^{(Tools and References)} . Rul ned til Microsoft CDO for Windows 2000 Library , aktiver det, og vælg OK .

Der er tre hovedafsnit til den kode, du skal oprette for at sende en e-mail og integrere regnearksresultater.

Den første er opsætning af variabler til at indeholde emnet, Til- og Fra^(From) -adresser og e-mail-brødteksten.

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

Selvfølgelig skal kroppen være dynamisk afhængig af hvilke resultater der er i arket, så her skal du tilføje en løkke, der går gennem området, udtrækker dataene og skriver en linje ad gangen til kroppen.

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

Det næste afsnit involverer opsætning af SMTP - indstillingerne, så du kan sende e-mail via din SMTP -server. Hvis du bruger Gmail , er dette typisk din Gmail -e-mailadresse, din Gmail - adgangskode og Gmail SMTP -serveren (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

Erstat [email protected] og adgangskode med dine egne kontooplysninger.

Til sidst, for at starte e-mail-afsendelsen, skal du indsætte følgende kode.

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

Bemærk^(Note) : Hvis du ser en transportfejl, når du forsøger at køre denne kode, er det sandsynligvis fordi din Google - konto blokerer "mindre sikre apps" i at køre. Du skal besøge siden med mindre sikre apps-indstillinger^{(less secure apps settings page)} og slå denne funktion TIL.

Når det er aktiveret, vil din e-mail blive sendt. Sådan ser det ud for den person, der modtager din automatisk genererede resultatmail.

Som du kan se, er der meget, du faktisk kan automatisere med Excel VBA . Prøv at lege med de kodestykker, du har lært om i denne artikel, og lav dine egne unikke VBA - automatiseringer.

An Advanced VBA Guide For MS Excel

If you are just getting started with VBA, then you’ll want to start out studying оur 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

• Den bedste VBA-guide (for begyndere), du nogensinde har brug for

• Sådan opretter du en VBA-makro eller et script i Excel

• En guide til alle Excel-filudvidelser og hvad de betyder

• Sådan slettes tomme linjer i Excel

• Ret MS Office "Cannot Verify License" fejlmeddelelse

• Skriv hurtigt tal i Word og Excel

• Sådan sorteres efter dato i Excel

• Sådan søger du i Excel

• Generer tilfældig tekst eller Lorem Ipsum-tekst i MS Word

• 2 måder at bruge Excels transponeringsfunktion

• Sådan bruges If og Nested If-sætninger i Excel

• Sådan bruges Excels What-If-analyse

• Sådan bruges PMT-funktionen i Excel

• Tilslutning af Excel til MySQL

• Sådan flettes celler, kolonner og rækker i Excel

• Sådan spores afhængige i Excel

• Brug tastaturet til at ændre rækkehøjde og kolonnebredde i Excel

• Sådan bruger du Excels Tal Cells-funktion

• Gruppér rækker og kolonner i et Excel-regneark

• Sådan beregnes Z-score i Excel