If you’re just getting started with VBA, then you should start by exploring our VBA Beginner’s Guide. But if you’re a seasoned VBA expert looking for more advanced VBA features in Excel, keep reading.
The ability to use VBA encoding in Excel opens up a whole world of automation. You can automate calculations in Excel, buttons, and even send email. There are more possibilities for automating your day-to-day VBA work than you might imagine.
VBA Advanced Guide for Microsoft Excel
The main purpose of writing VBA code in Excel is so that you can extract information from a spreadsheet, perform various calculations on it, and then write the results back to the spreadsheet
Following are the most common use cases for VBA in Excel.
- Importing data and performing calculations
- Calculating results when the user clicks a button.
- Email calculation results to someone
With these three examples, you’ll be able to write your own advanced VBA code for Excel.
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 are not using VBA, this means that you have to manually import data, perform 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 loaded into a directory on your computer every Monday, you can configure the VBA code to run when you first open the 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” using 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 drop-down lists of objects and methods, select Worksheet and Activate. This will run the code every time you open the spreadsheet.
This will create the Sub Worksheet_Activate () function. Paste the above code into this function.
This sets the active worksheet to Sheet1, clears the sheet, connects to the file using the file path that you specified with the strFile variable, and then a With loop goes through each line in the file and puts the data into the sheet starting at cell A1.
If you run this code, you will see the CSV file data imported into your blank spreadsheet on Sheet1.
Import is just the first step. Then you want to create a new column heading that will contain the results of your calculations. For this example, let’s say you want to calculate 5% of the taxes paid on the sale of each item.
Your code should do the following:
- Create a new column of results called taxes.
- Scroll through the units sold column and calculate sales tax.
- Write the calculation results in the appropriate line of the worksheet.
The following code will complete all 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 the 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) = “Tax”
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 data sheet and then sets the range of cells (the sales price column) to match the first and last rows of data. The code then walks through each of these cells, calculates the tax, and writes the results to a new column (column 5).
Paste the above VBA code below the previous code and run the script. You will see the results in column E.
Now every time you open your Excel sheet, it automatically exits and gets the most recent copy of the data from the CSV file. Then it will perform the calculations and write the results to the sheet. You don’t need to do anything manually anymore!
Calculation of results by pressing the button
If you would prefer to have more direct control over how calculations are started, rather than automatically starting them when you open a sheet, you can use the control button instead.
Control buttons are useful if you want to control what calculations are being used. For example, in the same case as above, what if you want to use a tax rate of 5% for one region and a tax rate of 7% for another?
You can enable the automatic launch of the same CSV import code, but leave the tax calculation code running when you click the corresponding button.
Using the same table as above, select the Developer tab and select Paste in the Controls group on the Ribbon. Select the ActiveX Control button from the drop-down menu.
Draw a button anywhere on the sheet, away from where the data will be sent.
Right-click the button and select Properties. In the Properties window, change the title to whatever you want to display to the user. In this case, it can be a tax calculation of 5%.
You will see this text reflected on the button itself. Close the properties window and double-click the button itself. This will open a code editor window and your cursor will be inside a function that will run when the user clicks the button.
Paste the tax calculation code from the section above into this function, keeping the tax rate multiplier 0.05. Don’t forget to include the next 2 lines to define the active sheet.
Dim ws As Worksheet, strFile As String
Set ws = ActiveWorkbook.Sheets (“Sheet1”)
Now repeat the process one more time, creating a second button. Make a headline “Calculate Tax 7%”.
Double click this button and paste the same code, but make the tax multiplier 0.07.
Now, depending on which button you click, the tax column will be calculated accordingly.
When you’re done, both buttons will appear on your sheet. Each will initiate its own tax calculation and write different results to the results column.
To enter text, choose the Developer menu and choose Design Mode from the Controls group on the Ribbon to turn off Design Mode. This will activate the buttons.
Try clicking each button to see how the “taxes” result column changes.
Send calculation results by email
What if you want to email the results in a spreadsheet to someone?
You can create another button called “Send Form by Email” using the same procedure outlined above. The code for this button would involve using the Excel CDO object to configure SMTP email settings and email the results in a human readable format.
To enable this feature, you need to select Tools & Links. Scroll down to Microsoft CDO for Windows 2000 Library, enable it and click OK.
There are three main sections in the code that you need to create in order to send email and embed the results in a spreadsheet.
The first is setting variables to store the subject, recipient and sender addresses, and the body of the message.
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 = “MyEmail@gmail.com”
strTo = “BossEmail@gmail.com”
strCc = “”
strBcc = “”
strBody = “Below is a breakdown of taxes paid with sales this quarter. ”
Of course, the body needs to be dynamic depending on what results are in the sheet, so here you will need to add a loop that loops through the range, retrieves the data, and writes a line at a time to the body. Set StartCell = Range (“A1”) ‘Find the 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 taxes paid” & Cells (rowCounter, 5) .Value & “.” rowCounter = rowCounter + 1 Next cell
The next section includes configuring SMTP settings so that you can send email through your SMTP server. If you use Gmail, this is usually your Gmail email address, Gmail password, and 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 using SMTP_Config .Item (” //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@website.com “.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 Finish with CDO_Mail Set .Configuration = CDO_Config Finish with
Replace email@website.com“> email@website.com and password with your account information.
Finally, to start sending email, paste 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 you try to run this code, it is likely that your Google account is blocking “less secure applications” from running. You will need to go to the settings page for less secure apps and enable this feature.
After that, your letter will be sent. This is what it looks like to the person who receives your email with the automatically generated results.
As you can see, you can automate a lot with Excel VBA. Try playing around with the snippets you learned about in this article and create your own unique VBA automation.
–