Excel Visual Basic for Applications (VBA): How to Get Started

April 9, 2014 Excel  No comments

Microsoft Excel has a built-in Visual Basic tool called Visual Basic for Applications (VBA), which allows you to customize your workbook and save time on repetitive tasks. Instead of repeating the same thing over and over, you can write a short code to replace the repetitive task. Check out these tips for using VBA to make using Excel easier.

Using VBA in Excel

First, you will need to open the Visual Basic Editor (VBE), to do this you can either go to the “Developer” tab and click on “Visual Basics” or click “Alt” + “F11.” In the VBE, click on “Insert” and select “Module” to open a blank window. Now you need to decide if your program is a “Sub” (subroutine, a task that doesn’t return a result) or a “Function” (a task that returns a result). Once you have decided which one best defines your program, enter either “Sub” or “Function” on the first line follow by the name you want to give the program. Press “Enter,” and on the new line type an apostrophe follow by a description of what the program does. This should turn green after you click “Enter” and it is not a part of the code. On a new line, type the body of your code (ie. What you want the code to do). Finally, on the next line type either “End Sub” or “End Function.” To run the whole program press “F5.” Or to run one line of the program press “F8.”

The Hierarchy when Using VBA

When you are entering the code to perform a specific task, you have to follow certain hierarchy, so the program runs correctly. First you much enter the “Application” followed by the “Workbook” followed by the “Worksheet” followed by the “Range” and finally ending with the “Font.”

For example:

The Immediate Pane

The “Immediate Pane” is great for testing a single line of code to make sure you are referencing the correct information. The “Immediate Pane” should be at the bottom of your VBE. If it’s not there, go to “View” and select “Immediate Pane” or press “Ctrl” + “G” and it should appear. To verify that the location you are using is the one you want, go to the “Immediate Pane” and enter: Print NameOfWhatYouWantToReference. Name. After you press enter, the name of the thing you want to reference will appear and you can verify that it is the right thing. *Make sure you don’t forget to keep one space between “Print” and the name of the thing you want to reference!

We here at DTA hope you now feel confident in your ability to use Excel’s Visual Basic for Applications (VBA). It is our desire to bring you the best advice possible to effectively and efficiently use your desktop features so you can focus on your work. Subscribe to our blog to receive weekly Desktop tips. If you’re looking for more in-depth training check out our upcoming instructor-led, live online trainings.

Liked these Excel Database tips? Have questions? Leave your comments below; we’d love to hear from you.

Getting Social with Excel: Spread the word and share the knowledge!

Kevin – Desktop Training Academy

Leave a reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>