An Advanced VBA Guide For MS Excel

Are you a seasoned expert? Read on

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

With these three examples, you should be able to write a variety of your own advanced Excel VBA code.

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.

Open the Excel VBA editing tool and select the Sheet1 object. From the object and method dropdown boxes, chooseWorksheetandActivate. This will run the code every time you open the spreadsheet.

This will create aSub Worksheet_Activate()function. Paste the code above into that function.

This sets the active worksheet toSheet1, clears the sheet, connects to the file using the file path you defined with thestrFilevariable, and then theWithloop 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, inSheet1.

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:

The following code will accomplish all of these steps.

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!

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 theDevelopertab, and selectInsertfrom theControlsgroup in the ribbon. Select thepush buttonActiveX 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 selectProperties. In the Properties window, change the Caption to what you’d like to display to the user. In this case it might beCalculate 5% Tax.

You’ll see this text reflected on the push button itself. Close thepropertieswindow, 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.

Now, repeat the process again, creating a second push button. Make the captionCalculate 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 theDevelopermenu, and selectDesign Modeform the Controls group in the ribbon to disableDesign Mode. This will activate the push buttons.

Try selecting each push button to see how the “taxes” result column changes.

What if you want to send the results on the spreadsheet to someone via email?

You could create another button calledEmail Sheet to Bossusing 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 selectTools and References. Scroll down toMicrosoft CDO for Windows 2000 Library, enable it, and selectOK.

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.

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.

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

Replaceemail@website.comand password with your own account details.

Finally, to initiate the email send, insert the following code.

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 theless secure apps settings pageand 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.

Ryan has been writing how-to and other technology-based articles online since 2007. He has a BSc degree in Electrical Engineering and he’s worked 13 years in automation engineering, 5 years in IT, and now is an Apps Engineer.Read Ryan’s Full Bio

Welcome to Help Desk Geek- a blog full of tech tips from trusted tech experts. We have thousands of articles and guides to help you troubleshoot any issue. Our articles have been read over 150 million times since we launched in 2008.

HomeAbout UsEditorial StandardsContact UsTerms of Use

An Advanced VBA Guide For MS Excel#

Advanced VBA Guide For Microsoft Excel#

An Advanced VBA Guide For MS Excel

Advanced VBA Guide For Microsoft Excel