VBA and Excel for Engineers and Scientists

vba toolbar

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

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

If you’re having trouble justifying taking the effort to learn some VBA, you may be interested to know that there are several related flavors and implementations of the language built on the Visual Basic platform. They may not be identical, but you can pick them up much easier because of their familiarity. If you can use the VB/VBA skill set in more than one application, maybe you can justify the effort a little easier.

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.

A number of other software applications use VBA or a flavor of it as well, including; Microsoft Word, Microsoft Access, Microsoft Outlook, AutoCAD, MicroStation and gINT to name a few.