Visual Basic for Applications or VBA used in conjunction with Microsoft Excel is a tool that many scientists and engineers have yet to take full advantage of. This article is the first in a series designed to provide you with the basic understanding of how to use VBA and Excel in your practice. 

So you wanna be a programmer?

How many of you had a programming class in school? Depending on how long it’s been, you might vaguely remember learning C on a mainframe, Fortran with punchcards, or binary calculations with vacuum tubes J.

But one thing many engineers and scientists have found out, is that programming in most languages means you are usually limited to a relatively simple input/output interfaces with text files, etc. Of course a professional programmer has no trouble with generating nicer GUI interfaces, but who has the time or money for that? And don’t forget that often times an expensive complier is needed and the compiled code may or may not run on other machines.

In today’s business environment, most companies utilize the Microsoft Office suite of which Excel is a part of. You can extend Excel using the Visual Basic for Applications (VBA) programming language making it an incredibly powerful tool in your practice. Some reasons for using Excel and VBA for your application include:
 

•         Ability to automate repetitive tasks or calculations

•         Implement complex models

•         Perform simulations

•         Simplify complicated mathematical functions

•         Eliminate nested IF statements in functions

•         Readily available programming environment

•         Implement graphical output and display of the data very easily

•         Easy to distribute your code within an XLS file

Although VBA is the name of the programming language we will be discussing, the word “Macro” is frequently used for code written in VBA. However, it usually implies something recorded by Excel’s built-in Macro recording functionality. Frequently the VBA code and Macro code are used almost interchangeably. I can tell you from experience that when you record a Macro, it doesn’t always end up performing the way you want it to

Excel and VBA Family Relatives

Visual Basic 6 and VB.net are full programming languages used for creating stand alone executable (EXE) applications. They require the use of separate (and relatively expensive) programming environments and compilers but are useful for creating polished, fully distributable stand-alone applications.

VBScript is the more modern equivalent of the DOS Batch Files. It can come in very handy for some maintenance tasks on your local computer. VBScript is also used for creating web applications on the ASP platform. If you’ve ever visited a website with the .ASP or .ASPX extension, this is the programming language they are using. The drawback to ASP is that it only runs on a Windows-based server.

VBA and Security

The first thing you will need to do before we get to any VBA coding is to check your security settings in Excel. You can do this by going to Tools-Macro-Security from the menu. You need to set it to MEDIUM, which compromises between asking you if you want to open spreadsheets that contain macros and not allowing VBA code to run at all.

Getting Started with VBA

The next thing you need to do is to start getting familiar with the various toolbars, menus, windows and other elements that you will need as you are writing VBA code. First, check out your Macro toolbar. You can turn it on by right-clicking on your toolbar area and checking it or you can simply use the Tools>Macro menu.

Once you find your Macro Toolbar or menu, launch the Visual Basic Editor (VBE) using the appropriate button or menu command. Notice a few important windows and areas.

The Menu Bar where you will find some important buttons like the play, stop, step into, and object browser buttons.

The Project Explorer where you manage multiple code modules etc. In this window, you will see that each open spreadsheet is listed. Any Add-ins (like Acrobat) that are running will also be displayed. You can expand the tree for each object. Each Excel worksheet, chart, code module, form or other object is listed here. This is where you manage the code and any user forms for your VBA project.

Your First VBA Code!

Ok, you’ve stuck with me for this far, the least I can do for you is show you how to implement the classic piece of code that every programmer starts with, “Hello World!”. 

In the VBE, go to the “Insert” menu, and choose to insert a “Module”. You will see a code window popup, and if you look in the Project Explorer, you will see that there is a new code module called “Module1”. Type Listing 1 into your code window.

Sub HelloWorld()
  MsgBox "Hello World!"
End Sub

Now, you can run it a couple different ways. The first way would be to simply put your cursor somewhere in the code, and press the play button on the menu bar of the VBE. Another way would be to switch back to the main Excel window, and go to click the play button on the Visual Basic toolbar, or from the Tools>Macro menu. Then select to run the “Hello World” subroutine. You should get a window that lists the name of your “Hello World” subroutine and click the “Run” button.

Closure and Next Article

In the next article, we will start to get into more of the VBA language. We will cover various types of containers for your code, variables, data types, functions, etc.

TrackBack URI for this entry

Comments (0)

Subscribe to this comment's feed

Show/Hide comment form