Visual Basic Editor – Unleashing the Full Potential of Excel

Microsoft Excel is the undisputable leader when it comes to working with spreadsheets, so there’s no wonder that most companies list ‘Excel knowledge” as a must-have skill for pretty much any open position. However, while knowing the mere basics of opening a spreadsheet and entering data into cells might cut it for some entry-level positions, you will need to know a bit more than that if you want to land a better-paid position.

The Visual Basic Editor is one of the most powerful tools to master in Microsoft Excel, as it will allow you to create strings of commands in order to automate or speed up the tasks you perform regularly. In order to be able to use the Visual Basic Editor to its full potential, consider taking an online course to familiarize yourself with some basic and advanced features of Microsoft Excel. Ready? Let’s get proficient!

Introduction to Visual Basic Editor

Microsoft has a nice habit of creating its software programs to integrate seamlessly with one another. In this case, we are talking about a stripped down programming language – Visual Basic for Application – that’s integrated into Microsoft Excel, with the purpose of allowing users to write pieces of code (known as macros) to automate certain tasks. The Visual Basic Editor is the interface through which all this is possible.

Learn more about Visual Basic for Excel from this online course.

Working with Macros

You can create a macro in two ways:

·         write the code manually

·         record your actions

Method 1

Let’s start with the first method: open the Visual Basic Editor (Alt + F11), click on Sheet1 (from the Project Explorer, on the left), click Insert (top menu bar) and select Module. This will create a new module (also visible in the Project Explorer panel) and you will be able to start entering your code in the Code Window.

Enter the following piece of code:

Sub Welcome()

    MsgBox "Hello World!"

End Sub

Press the Run (or press F5) in the Visual Basic Editor and the Macros window will come up, allowing you to run the “Welcome” macro that you just created. If you would like to bring up the Macros window directly from Excel, use the Alt+F8 key combination.

Click Run once again and your macro command will be executed.

Method 2

The second method of creating macros requires you to perform a certain string of actions that will be recorded and performed again in a very quick succession every time the macro is activated. To record a macro, go to the View menu, click on the arrow under Macros and select Record Macro (in Excel 2007 you can find the Macros menu under the Developer tab).

Give your macro a name in the Record Macro window that will open and click Ok to start recording the macro sequence.

The macro we will be recording will consists of selecting a certain background color for cells. Once the recording starts, go to the Fill Color option and choose a color, then click on the Stop button on the status bar at the bottom of the window.

Your macro is now recorded and you can access it by opening the Macros window. To use the macro, simply select some cells from your spreadsheet, press Alt+F8 to bring up the Macros window, select your macro from the list and hit run – voila, your cells have the background color you previously selected.

If you want to see the code your recording has generated, simply bring up the Visual Basic Editor and go to the module from the Project Explorer panel. The result should be something like this:

Practical Example of Macro

For this example, let’s imagine you have a spreadsheet with information about some people. You have their first names in one column, their last names in another, email address in the next and you want to create a macro that will display their complete name and email address in just one cell.

Open the Visual Basic Editor and type the following code:

PressRun, select your macro from the list and hit Run once again. Your macro should be executed and you should see something like this:

As you can see, the information for the first 6 entries was correctly merged into the 4th column, but the script stopped at the 7th entry because the e-mail address was missing. The piece of code responsible for verifying whether the e-mail address is present or not is the following:

If Selection.Offset(0, -1).Value = "" Then MsgBox "Invalid E-mail"

Once you click the Ok button on the message box, the script will continue to run, until it runs into a missing e-mail address again or the end of the list.

When the script reaches the end of the list, it will display a notification once again.

As you may have noticed, the macro allowed Excel to go through the list in a matter of seconds, something that wouldn’t be possible if you were to do everything manually, especially if the list contained hundreds or even thousands of entries. This is a clear example of how just a few lines of code can spare you the effort and time it would take to do something manually. To learn more about macros and how to make your work even easier, give this 2-part Visual Basic and Macros Course a try.

Excel is not the only product from the Microsoft Office suite to benefit from the power of Visual Basic. Learn how to use macros in Microsoft Word from this Microsoft Word 2010 Intermediate course or check out this Visual Basic for Access Tutorial to learn how to do it in Microsoft Access.