Skip to content

How To Blog

how to

  • SP Armor A62 Game Drive Review Gadgets
  • Top 5 Ways to Extract APK File of Any App on Your Android Phone How-To
  • How to Delete a User on Mac macOS
  • 8 Safe Free Software Download Sites for Windows Free Software Downloads
  • Hide/Unhide Extension Icons From Chrome Toolbar How-To
  • 7 Best Nintendo Switch Skins To Buy Gaming
  • How to Send Secure Encrypted Email for Free Cool Websites
  • Best calculator printer 2020

    Best calculator printer 2020

An Advanced VBA Guide For MS Excel

Posted on October 7, 2020 By blog_786 No Comments on An Advanced VBA Guide For MS Excel

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.

An Advanced VBA Guide For MS Excel

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.

An Advanced VBA Guide For MS Excel

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.

An Advanced VBA Guide For MS Excel

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:

  1. Create a new column of results called taxes.
  2. Scroll through the units sold column and calculate sales tax.
  3. 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.

An Advanced VBA Guide For MS Excel

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.

An Advanced VBA Guide For MS Excel

Draw a button anywhere on the sheet, away from where the data will be sent.

An Advanced VBA Guide For MS Excel

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

An Advanced VBA Guide For MS Excel

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%”.

An Advanced VBA Guide For MS Excel

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.

An Advanced VBA Guide For MS Excel

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?

An Advanced VBA Guide For MS Excel

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 = “[email protected]”
strTo = “[email protected]”
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 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 Finish with CDO_Mail Set .Configuration = CDO_Config Finish with

Replace [email protected]“> [email protected] 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.

An Advanced VBA Guide For MS Excel

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.

–

Office Tips

Post navigation

Previous Post: How To Set Up & Use a Browser Sandbox On Windows
Next Post: How To Get Ready For Windows 7 End Of Life

Related Posts

  • Using Excel’s What-If Analysis Goal Seek Tool Office Tips
  • Where to Find Character/Word Count in Word 2010 Office Tips
  • How to Take and Insert Screenshots using OneNote Office Tips
  • How To Bulk-Convert Outlook PST Files Into Another Format Office Tips
  • How to Layer Objects in a PowerPoint Presentation Office Tips
  • How to create and run a macro in MS Word Office Tips

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

  • Amazon Web Services
  • Apple Watch
  • Computer Tips
  • Cool Websites
  • Free Software Downloads
  • Gadgets
  • Gaming
  • General Software
  • Google Software/Tips
  • Hardware
  • Help Desk
  • How-To
  • iOS
  • iPad
  • iPhone
  • Linux
  • Linux Tips
  • macOS
  • MS Office Tips
  • Networking
  • Office Tips
  • Product Reviews
  • Reviews
  • Safari
  • Smart Home
  • Smartphones
  • Software Reviews
  • technology
  • text
  • Troubleshooting
  • Tutorials
  • Uncategorized
  • Urdu Books PDF
  • Web Site Tips
  • Windows
  • Windows 10
  • Windows 7
  • February 2023
  • January 2023
  • December 2022
  • November 2022
  • September 2022
  • August 2022
  • July 2022
  • June 2022
  • May 2022
  • April 2022
  • March 2022
  • February 2022
  • January 2022
  • December 2021
  • November 2021
  • October 2021
  • September 2021
  • August 2021
  • July 2021
  • April 2021
  • March 2021
  • February 2021
  • January 2021
  • December 2020
  • November 2020
  • October 2020
  • September 2020
  • August 2020
  • July 2020
  • June 2020
  • May 2020
  • April 2020
  • March 2020
  • December 2019
  • July 2019
  • May 2019
  • April 2019
  • January 2019
  • December 2018
  • November 2018
  • October 2018

2021 Android ) Apple apps Best black browser change Chrome Closing words Concluding remarks Conclusion Delete Download Error Facebook Files Find Free) From games Google Keyboard Mac make Music Netflix ( online Open Packaging Password phone? Screen sites Tips using Video Videos Watch Websites What With Working? your YouTube

  • How to Track Multiple Package Tracking Numbers At Once

    How to Track Multiple Package Tracking Numbers At Once

  • How to Connect a PS4 Controller to Android How-To
  • How to Make a Glitch Effect in Adobe Premiere Pro Computer Tips
  • How to fix Touch ID that doesn’t work on Mac Uncategorized
  • Free Up Disk Space using TreeSize Computer Tips
  • How to Split a Large File into Multiple Smaller Pieces Computer Tips
  • Best tax calculator 2020

    Best tax calculator 2020

  • How to Manage Bookmarks in Google Chrome Google Software/Tips

Copyright © 2023 How To Blog.

Powered by PressBook News WordPress theme

Manage Cookie Consent
To provide the best experiences, we use technologies like cookies to store and/or access device information. Consenting to these technologies will allow us to process data such as browsing behavior or unique IDs on this site. Not consenting or withdrawing consent, may adversely affect certain features and functions.
Functional Always active
The technical storage or access is strictly necessary for the legitimate purpose of enabling the use of a specific service explicitly requested by the subscriber or user, or for the sole purpose of carrying out the transmission of a communication over an electronic communications network.
Preferences
The technical storage or access is necessary for the legitimate purpose of storing preferences that are not requested by the subscriber or user.
Statistics
The technical storage or access that is used exclusively for statistical purposes. The technical storage or access that is used exclusively for anonymous statistical purposes. Without a subpoena, voluntary compliance on the part of your Internet Service Provider, or additional records from a third party, information stored or retrieved for this purpose alone cannot usually be used to identify you.
Marketing
The technical storage or access is required to create user profiles to send advertising, or to track the user on a website or across several websites for similar marketing purposes.
Manage options Manage services Manage vendors Read more about these purposes
View preferences
{title} {title} {title}
Go to mobile version