First I want to congratulate you because if you are taking the time to read this series I am assuming two things:
- You have realized that Excel and the rest of Microsoft's Office Suite have an enormous amount power that is just waiting for you to unlock
- You have noticed that most of your peers don't understand these capabilities and you want to differentiate yourself from them
Understanding those two points really gave me the motivation to dig deep into the Excel universe (and eventually into Word and PowerPoint) and add every technique I could find to my virtual tool belt. Before we get our hands messy and try to build a foundation that will allow you to do anything you could ever imagine within these programs, I think it is really important to take some time and define what exactly VBA and those Macro thingys are that you've probably heard or seen floating around.
What Is VBA?
The acronym VBA stands for Visual Basic for Applications. This is essentially an offshoot of the Visual Basic computer language that Microsoft created way back in the 90's that allows Microsoft programs to communicate with each other based on events or actions that take place within those programs. This language is not only used in Office programs like Excel and PowerPoint, but also in programs like NotePad and Paint. Since Microsoft created this language to go along with its own applications, the code is very user intuitive. For example, if you read a code line in Excel that states Range("A1:B4").ClearContents you can make an educated guess that the line of code tells Excel to clear the contents of cells A1 through B4. This is HUGE because it allows users with very limited or no computer programming knowledge to easily pick up on how the VBA language works.
What Are Macros?
Macros are what most people who write VBA code use. A macro (also can be referred to as a Procedure or Subroutine) is a grouping of code that performs a series of tasks or commands within a targeted computer program (aka Application). Macros can contain code that perform calculations, copy & pastes, changes formatting, and a bunch of other nifty things; all with in milliseconds! Most Office users use macros to automate routine tasks that take them a long period of time to perform manually (by keyboard & mouse).
What The Function Is "Dim"?
I threw this section in especially for a friend of mine who was frustrated with the lack of introductory information on VBA. He said, "Chris, I've been searching everywhere and I wish there was a simple introduction to VBA that could get me started. I mean, I can't even figure out what the heck a Dim is!" So below I will list out a couple terminologies that you might have come across if you have ever recorded a macro of seen VBA code. Leave a comment below if there are any other words that you have come across and would like defined.
Dim - This stands for Dimension and is a statement used to declare a variable name and type that you want to create.
Sub - This is short for Subroutine and is your opening statement for your code. Every time Sub is typed a new macro or procedure is created. The words "End Sub" must be placed as the last line of code for your macro in order to tell VBA that your procedure is finished
Module - This is an area where you can your write function and macro codes. This is also where any macros that you record are stored.
Class Module - This is an area for really advanced VBA users. In this area you can write your own custom classes, methods, and collections into the VBA library. If you are a beginner I would stay away from these for know. Most VBA writers won't ever use this type of functionality during their career but it is a very powerful option to have if needed.
Function - VBA gives you the ability to create your own custom functions. These can either be used by your macros to obtain a certain output or they can be used in the Excel Formula Bar to perform calculations on your cell's values.
Userforms - These are pop-up boxes that allow users to enter inputs or choose options. Microsoft uses these all the time in their applications. Some examples of these are Error Message Boxes, Dialog Boxes, and the Macro Recorder. The cool thing is that VBA gives you the ability to create your own custom Userforms!