Om du precis har börjat med VBA , då vill du börja studera vår VBA-guide för nybörjare^{(VBA guide for beginners)} . Men om du är en erfaren VBA- expert och letar efter mer avancerade saker du kan göra med VBA i Excel , fortsätt läsa.

Möjligheten att använda VBA- kodning i Excel öppnar upp för en hel värld av automatisering. Du kan automatisera beräkningar i Excel , tryckknappar och till och med skicka e-post. Det finns fler möjligheter att automatisera ditt dagliga arbete med VBA än du kanske inser.

Avancerad VBA-guide för Microsoft Excel^{(Advanced VBA Guide For Microsoft Excel)}

Huvudmålet med att skriva VBA -kod i Excel är så att du kan extrahera information från ett kalkylblad, utföra en mängd olika beräkningar på det och sedan skriva tillbaka resultaten till kalkylarket

Följande är de vanligaste användningsområdena för VBA i Excel .

• Importera^(Import) data och utför beräkningar
• Beräkna^(Calculate) resultat från att en användare trycker på en knapp
• E-posta^(Email) beräkningsresultat till någon

Med dessa tre exempel bör du kunna skriva en mängd olika din egen avancerade Excel VBA -kod.

Importera data och utföra beräkningar^{(Importing Data and Performing Calculations)}

En av de vanligaste sakerna som människor använder Excel till är att utföra beräkningar på data som finns utanför Excel . Om du inte använder VBA betyder det att du måste importera data manuellt, köra beräkningarna och mata ut dessa värden till ett annat ark eller rapport.

Med VBA kan du automatisera hela processen. Om du till exempel har en ny CSV -fil nedladdad till en katalog på din dator varje måndag^(Monday) , kan du konfigurera din VBA -kod så att den körs när du öppnar ditt kalkylblad på tisdag^(Tuesday) morgon.

Följande importkod körs och importerar CSV -filen till ditt Excel - kalkylblad.

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

Öppna Excel VBA- redigeringsverktyget och välj Sheet1- objektet. I listrutorna för objekt och metod väljer du Kalkylblad^(Worksheet) och Aktivera^(Activate) . Detta kommer att köra koden varje gång du öppnar kalkylarket.

Detta kommer att skapa en Sub Worksheet_Activate() funktion. Klistra in koden ovan i den funktionen.

Detta ställer in det aktiva kalkylbladet till Sheet1 , rensar arket, ansluter till filen med hjälp av filsökvägen du definierade med strFile- variabeln, och sedan cyklar With -loopen genom varje rad i filen och placerar data i arket med början i cell A1 .

Om du kör den här koden kommer du att se att CSV -fildata importeras till ditt tomma kalkylark, i Sheet1 .

Import är bara det första steget. Därefter vill du skapa en ny rubrik för kolumnen som kommer att innehålla dina beräkningsresultat. I det här exemplet, låt oss säga att du vill beräkna 5 % skatt som betalas vid försäljningen av varje artikel.

Åtgärdsordningen din kod ska utföra är:

1. Skapa ny resultatkolumn som heter skatter^(taxes) .
2. Gå igenom kolumnen för sålda enheter^{(units sold)} och beräkna momsen.
3. Skriv beräkningsresultaten till lämplig rad i bladet.

Följande kod kommer att utföra alla dessa steg.

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

Den här koden hittar den sista raden i ditt datablad och ställer sedan in cellintervallet (kolumnen med försäljningspriserna) enligt den första och sista raden med data. Sedan går koden genom var och en av dessa celler, utför skatteberäkningen och skriver resultaten i din nya kolumn (kolumn 5).

Klistra in ovanstående VBA -kod under föregående kod och kör skriptet. Du kommer att se resultaten visas i kolumn E.

Nu, varje gång du öppnar ditt Excel -kalkylblad, kommer det automatiskt att gå ut och hämta den senaste kopian av data från CSV -filen. Sedan kommer den att utföra beräkningarna och skriva resultaten till bladet. Du behöver inte göra något manuellt längre!

Beräkna resultat från knapptryckning^{(Calculate Results From Button Press)}

Om du hellre vill ha mer direkt kontroll över när beräkningar körs, istället för att köras automatiskt när arket öppnas, kan du använda en kontrollknapp istället.

Kontrollknappar^(Control) är användbara om du vill styra vilka beräkningar som används. Till exempel, i samma fall som ovan, vad händer om du vill använda en skattesats på 5 % för en region och en skattesats på 7 % för en annan?

Du kan tillåta att samma CSV -importkod körs automatiskt, men låter skatteberäkningskoden köras när du trycker på lämplig knapp.

Använd samma kalkylblad som ovan, välj fliken Utvecklare^(Developer) och välj Infoga^(Insert) från gruppen Kontroller^(Controls) i menyfliksområdet. Välj tryckknappen ^{(push button)} ActiveX Control från rullgardinsmenyn.

Dra tryckknappen på valfri del av arket bort från där all data kommer att gå.

Högerklicka på tryckknappen och välj Egenskaper^(Properties) . I fönstret Egenskaper^(Properties) ändrar du bildtexten till vad du vill visa för användaren. I det här fallet kan det vara Calculate 5% Tax .

Du kommer att se den här texten på själva tryckknappen. Stäng egenskapsfönstret^(properties) och dubbelklicka på själva tryckknappen. Detta öppnar kodredigeringsfönstret och markören kommer att vara inne i funktionen som körs när användaren trycker på tryckknappen.

Klistra in skatteberäkningskoden från avsnittet ovan i den här funktionen och håll skattesatsmultiplikatorn på 0,05. Kom ihåg att inkludera följande 2 rader för att definiera det aktiva arket.

Dim ws As Worksheet, strFile As String

Set ws = ActiveWorkbook.Sheets("Sheet1")

Upprepa nu processen igen och skapa en andra tryckknapp. Gör bildtexten Calculate 7% Tax .

Dubbelklicka på^{(Double-click)} den knappen och klistra in samma kod, men gör skattemultiplikatorn 0,07.

Nu, beroende på vilken knapp du trycker på, kommer skattekolumnen att beräknas därefter.

När du är klar har du båda tryckknapparna på ditt ark. Var och en av dem kommer att initiera en annan skatteberäkning och kommer att skriva olika resultat i resultatkolumnen. 

För att texta detta, välj menyn Utvecklare^(Developer) och välj Designläge^{(Design Mode)} från gruppen Kontroller^(Controls) i menyfliksområdet för att inaktivera Designläge^{(Design Mode)} . Detta kommer att aktivera tryckknapparna. 

Försök att välja varje tryckknapp för att se hur resultatkolumnen "skatter" ändras.

E-posta beräkningsresultat till någon^{(Email Calculation Results to Someone)}

Vad händer om du vill skicka resultaten på kalkylarket till någon via e-post?

Du kan skapa en annan knapp som heter Email Sheet to Boss med samma procedur ovan. Koden för den här knappen kommer att innebära att Excel CDO- objektet används för att konfigurera SMTP -e-postinställningar och att resultaten skickas via e-post i ett användarläsbart format.

För att aktivera den här funktionen måste du välja Verktyg och referenser^{(Tools and References)} . Rulla ned till Microsoft CDO för Windows 2000 Library^{(Microsoft CDO for Windows 2000 Library)} , aktivera det och välj OK .

Det finns tre huvudavsnitt i koden du behöver skapa för att skicka ut ett e-postmeddelande och bädda in kalkylbladsresultat.

Den första är att ställa in variabler för att hålla ämnet, till och från^(From) -adresser och e-postmeddelandet.

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

Självklart måste kroppen vara dynamisk beroende på vilka resultat som finns i arket, så här måste du lägga till en slinga som går igenom intervallet, extraherar data och skriver en rad åt gången till 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

Nästa avsnitt handlar om att ställa in SMTP- inställningarna så att du kan skicka e-post via din SMTP -server. Om du använder Gmail är detta vanligtvis din Gmail- e- postadress, ditt Gmail - lösenord och Gmails SMTP^{(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

Ersätt [email protected] och lösenord med dina egna kontouppgifter.

Slutligen, för att starta e-postsändningen, infoga följande kod.

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

Obs!^(Note) Om du ser ett transportfel när du försöker köra den här koden, beror det troligen på att ditt Google - konto blockerar "mindre säkra appar" från att köras. Du måste besöka inställningssidan för mindre säkra appar^{(less secure apps settings page)} och aktivera den här funktionen.

När det har aktiverats kommer din e-post att skickas. Så här ser det ut för den person som får ditt automatiskt genererade resultatmail.

Som du kan se finns det mycket du faktiskt kan automatisera med Excel VBA . Testa att leka med kodavsnitten som du har lärt dig om i den här artikeln och skapa dina egna unika VBA- automatiseringar.

An Advanced VBA Guide For MS Excel

If you are just gettіng started wіth VBA, 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

• Den bästa VBA-guiden (för nybörjare) du någonsin kommer att behöva

• Hur man skapar ett VBA-makro eller skript i Excel

• En guide till alla Excel-filtillägg och vad de betyder

• Hur man tar bort tomma rader i Excel

• Åtgärda felmeddelandet "Kan inte verifiera licensen" i MS Office

• Hur man använder Flash Fill In Excel

• Generera slumpmässig text eller Lorem Ipsum-text i MS Word

• Gruppera rader och kolumner i ett Excel-kalkylblad

• Hur man spårar beroende i Excel

• 4 sätt att konvertera Excel till Google Sheets

• Hur man beräknar Z-poäng i Excel

• Grundläggande datasortering med en kolumn och flera kolumner i Excel-kalkylblad

• Kontrollera alla hyperlänkar i ett MS Word-dokument på några sekunder

• Använda Excels What-If Analysis Goal Seek Tool

• Hur man öppnar Word och Excel i felsäkert läge

• Hur man snabbt infogar flera rader i Excel

• Hur man skapar flera länkade rullgardinslistor i Excel

• Hur man skriver en IF-formel/påstående i Excel

• Hur man använder Excels Speak Cells-funktion

• Hur man slår samman celler, kolumner och rader i Excel