Skip to content

How To Blog

  • Best ti83 plus graphing calculator 2020

    Best ti83 plus graphing calculator 2020

  • How to Download Images from Google Docs How-To
  • Restore Lost Files in Windows with Shadow Explorer Tools Review
  • How To Play Old Game Boy Advance Games On Your PC Gaming
  • How To Make a Discord Server Computer Tips
  • How to enable nvidia reflex? Gaming
  • How Minecraft’s Customize World Settings Work Gaming
  • How To Connect AirPods To a Mac or iOS Device Hardware

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

  • Why You Should Be Using Named Ranges in Excel Office Tips
  • Use the Keyboard to Change Row Height and Column Width in Excel Office Tips
  • Center Your Worksheet Data in Excel for Printing Office Tips
  • How to Use If and Nested If Statements in Excel Office Tips
  • Show/Hide Field Codes in Word and Convert Fields to Text Office Tips
  • Fix “Word Cannot Start the Converter MSWRD632.WPC/SSPDFCG_x64.CNV” Error Office Tips

Leave a Reply Cancel reply

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

Archives

  • May 2023
  • April 2023
  • 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
  • March 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
  • September 2018
  • August 2018

Categories

  • Amazon Web Services
  • Apple Watch
  • Computer Tips
  • Cool Websites
  • Featured Posts
  • 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
  • OS X
  • Product Reviews
  • Reviews
  • Safari
  • Smart Home
  • Smartphones
  • Software Reviews
  • technology
  • text
  • Tools Review
  • Troubleshooting
  • Tutorials
  • Uncategorized
  • Urdu Books PDF
  • Web Site Tips
  • Windows
  • Windows 10
  • Windows 7
  • Wordpress

Recent Posts

  • How to Find Hidden Apps on iPhone 2021
  • How to Create or Generate Keyboard Shortcuts for Microsoft Office
  • Overview of indoor and outdoor CCTV cameras LaView
  • How to Set Up Response Validation in Google Forms
  • How to update games from PS4 to PS5

Recent Comments

  1. How to Minimize a Windows Program to the System Tray 2023 - How To Blog on How to Fix System Tray or Icons Missing in Windows 10
  2. How can I unlock my Macbook using Apple Watch - How To Blog on How to Install Chrome OS on Macbook or iMac
  3. 4k hdmi switch with optical out - How To Blog on Is A Gaming PC Really More Expensive Than A Console?
  4. online video maker with music and pictures 2023 - How To Blog on 6 Video Editing Tips For Beginners
  5. 5 New Ways to Clean Electronics Safely - How To Blog on How to Clean Your Computer Properly, Inside and Out
  • WWDC 2020 – How to Add Face Mask to Memoji in iOS 14 How-To
  • How to Fix If iPad Screen Not Rotating? iPad
  • Fix Registry Editing has been Disabled by Your Administrator Error

    Fix Registry Editing has been Disabled by Your Administrator Error

  • How to Use Azure VM Scale Sets Microsoft Azure
  • How To Make a MacOS Installer On a USB Stick OS X
  • How to Change Key Functions on Windows 10/8/7 How-To
  • AutoFit Column Widths and Row Heights in Excel MS Office Tips
  • How To Log Out Of Facebook Messenger On iOS & Android Computer Tips

Copyright © 2023 How To Blog.

Powered by PressBook News WordPress theme

Go to mobile version