Maximizing Your Efficiency With VBA

Written By Alex Fleming

Whether you are sending emails through Outlook, documenting your work in Word, putting together some tables and graphs in Excel, or creating presentations using PowerPoint, there is one common skill that you can learn to make yourself more efficient while doing almost anything in these applications: learn Visual Basic for Applications (VBA).

VBA is a programming language created primarily for allowing users to automate repetitive tasks in Microsoft Office (MS Office) products.

“But Alex,” you say, “learning VBA sounds like a lot of work. How do I know if it will pay off?”

Given the fast-pace of technological innovation, the increasing automation trend, and increasing demand for making sense of Big Data, wouldn’t you rather spend your time on the more complex and important parts of your job rather than inputting data entry or formatting some word document?

Do you think automation would be a bonus, rather than a threat to you, if you knew how to automate your own job? Don’t you think your time is better spent by analyzing complex and important business problems rather than mindless busy work?

If you screamed, “YES” to any of these questions, then VBA is exactly what you are after. If not, then stop reading now and go back to reformatting that table and entering the same V-Lookups for the thousandth time.

Breaking into the world of programming is a bit like starting to work out at the gym; it’s tiring and frustrating at first and it might take a while to see results that you are happy with.

While I can’t help you with your workout, I have learned VBA (the hard way), and I’m here to share what I’ve learned so that you do not have to experience the frustration that I had to.

Learning any programming language can consume a large amount of time and money depending on how you approach it. VBA is the best language for most beginners because it offers one of the most efficient ways to learn a programming language in terms of time and money.

We’re going to talk about one specific strategy for utilizing the self-taught approach to learn VBA.

I discovered an interest in programming through VBA and realized that I could become way more effective at my job by learning some other programming concepts. You don’t need to be a full blown software developer to learn and appreciate the uses of VBA.

Think of VBA like finance: “If I learn a bit about finance, then do I need to become a financial advisor?” No, but that knowledge can certainly help you later in your career. The same is true for VBA.

However, if you do want to become a developer, especially if getting started has been a daunting task, then VBA might be the perfect way to jump in. Whether you are a casual Excel user, entry-level programmer, or experienced programmer, there are benefits to learning VBA.

Skeptical?

Is learning VBA worth the time investment? For starters, it will allow you to increase your efficiency for 99 percent of tasks that could be performed in Excel–plus other Microsoft Office products.

For example, let’s look at Bob. He’s a business analyst who spends the majority of his time on data entry, making pivot tables, V-Lookups, and using simple formulas to manage his clients’ financial data. Bob really likes this job, but sometimes he gets frustrated by the amount of time he needs to spend on data entry. He gets the same report from the client’s RDBMS through an FTP in a text file, then Bob opens it in Excel and has to reorganize the data, then rename some of the columns before he can get started on the analysis.

VBA would make Bob’s life easier because he could automate those tasks and do them all with the click of a button. Bob would definitely say learning VBA is worth it.

Whether you are creating functions and assigning them to hotkeys or fully automating a process that turns raw data into a formatted table with calculations, there are a plethora of opportunities for VBA programmers to save an incredible amount of time and money.

When I started using VBA, my job became exponentially more fun and rewarding, and I became a more valuable employee, too.

For example, as my knowledge of the data and business grew, I was able to find solutions to business problems within my current role and even within other departments.

VBA is a glowing opportunity to take any non-programming job and turn it into one while impressing your boss and your team by putting your programming skills to use immediately. For experienced programmers, it’s an additional tool that might be the fastest way to solve certain problems and allow you to really maximize the return on investment for any MS Office product that you touch.

If you already have advanced knowledge of at least one programming language, then you will benefit the most from the information in this article if you are interested in finding out how to customize MS Office products or if you are interested in teaching someone else how to get started in programming.

And if you’re a beginner, you may be wondering: Why not just skip to the programming language that I want to learn?”

My answer to this is as simple as it is important: by learning VBA first, you can immediately see what your code is doing and watch it manipulate objects that you are already familiar with (assuming that you’ve used Excel before).

Finally, for all of the Microsoft critics and those who think that it’s a bad idea to focus on Excel for any reason, consider the following: Approximately 81 percent of businesses use Excel, which means Excel isn’t going away anytime soon. Anything you learn in this article could help you find a job at one of those businesses because implementing these skills will let you get more done in less time, which makes you a valuable team member.

Or, if you use Microsoft Outlook, PowerPoint, Word, Access, and Visio, then you can apply most of the concepts here that are not specific to the Excel object hierarchy. The more you utilize these applications, the more likely you will be able to find ways to use VBA to solve practical problems. Think about your current role, the business needs, and the Microsoft Office product functionality.

Now ask yourself one of the most important questions any VBA developer should ask himself: “What repetitive tasks am I doing?”

Requirements and Preparation

Before you get started writing VBA, you should probably think about the requirements and get used to understanding what makes problems VBA-compatible.

Let’s say that you already know how to do a V-Lookup and you understand how to nest “IF” functions. Your boss asked you to analyze an ad hoc report that comes over as a CSV file. He wants you to reconcile data to see how many records from the client match those in your updated worksheet.

You know how to do this manually through Excel, but the analysis and reconciliation are time-sensitive and you need the answers quickly. You’re using Excel 2010 and 2013 (which is good, because VBA works best with post-2010 MS Office), and you know that the client uses a reporting tool to pull data directly from their database daily.

All of these details about the problem suggest that your task is what I call “VBA-friendly.” In other words, writing code in VBA can solve most, if not all, of the steps involved in the process1 for the following reasons:

  1. The process “lives” in Excel (i.e., all manual tasks are performed or can be performed in Excel)
  2. You understand how to perform the tasks using existing functionality in Excel (for example, the V-Lookup function)
  3. The process does not require crossing domains2
  4. You have access to all of the required worksheets and text files3

It is usually a good idea to know ahead of time what the priorities are. Is this a project that needs to be completed quickly and may be fine-tuned later, or is it more important to account for as many potential errors as possible?

Since you are the only user and you understand the manual process, there is no risk to incrementally automating it. As a result, you can structure your code to reflect the chronological order of manual steps and work on them as you have time. It is not necessary to fully-automate the process and incorporate forms and error-handling until you have written the primary blocks of code and tested them.

If you were dealing with other users, then it might be a good idea to ask them for the file paths containing their workbooks, how frequently they update them, and what factors determine the updates (e.g., an external client changes the number of columns on a report).

You would also have to be clear about changes; if you are hardcoding any values, then let the user know that your macro will not run properly if they make changes that affect the values that you hardcoded without your knowledge.

One of the most important topics that requires the most attention is this: “What parts of the process should remain consistent?” Be sure to record any factors that affect the process on a schedule. Are there certain days of the week or times that affect the process? What about holidays? You don’t want to find yourself being hunted down because a user added an extra column the day after a holiday that screwed up the poorly scheduled job that is now failing.

VBA Solution Options

After clearly defining what the problem is and understanding its context, you need to decide what type of VBA solution you want to create. The VBA language may be stored and compiled as one of two types of code: a Function or a Sub Procedure.

A Function is essentially a block of code that, ideally, solves a very specific problem by accepting parameters and returning a result. Think of it as a way to create your own “SUM” or “VLOOKUP.” All of the out-of-the-box system functions that come with Excel, for example, illustrate what ideal functions should do and how they are used.

When deciding whether or not to create your own function, think about narrowing down the types of parameters you want it to accept and comparing it to existing functions. For the majority of this article, however, it is the Sub Procedure that we are interested in.

Sub Procedures are basically blocks of code that consist of one or more lines of code. You can write a Sub Procedure that consists of a single line of code–in fact, I encourage you to do that for testing code (it works for me).

In general, you will see that the main difference between Functions and Sub Procedures is that Sub Procedures are more flexible and complex, while Functions should serve a very specific purpose.

Sub Procedures are the focus of this article and most of what you will probably be using in VBA. A macro is a way to store and execute a Sub Procedure, and more broadly speaking, a macro is a way to map input parameters to output parameters, often for the purpose of automating work.

A Sub procedure is the format that we will use in VBA to write a macro that the Excel application understands. You may record macros, as we will in a minute, and watch the Macro Recorder write VBA code before your very eyes.

Now that you have some essential contextual information, let’s actually get started!

Recording Your First Macro

  1. Enable your Developer tab by navigating to Options – Customize Ribbon. In this particular example, I am using MS Word, as indicated in the upper-left corner. (All MS Office applications have the same style of window, though.) Then check the “Developer” check box in the lower-right corner of the new window:vba
  2. Press “Alt + F11” to bring up the VB Editor window.
  3. Drag this window to your second monitor. If you do not have a second monitor, split your screen in half so that you can see the open Worksheet on one side and the VB Editor on the other, like this:vba
  4. Click the “Record Macro” button, give it a name if you are so inclined, and then find the workbook name in the object explorer browser in the VB Editor and make sure that “Module1” is selected and opened.
  5. Copy and paste, type values into cells, then enter formulas and comments on the open Worksheet. As you perform these tasks, you can watch the code appear in the VB Editor in real time! This is one of the easiest ways to introduce yourself to some of the basic Object-Oriented Programming (OOP) concepts.

Feel free to play around with the macro recorder for a while and perform all of the typical Excel tasks manually so that you can understand how it writes and interprets your actions as VBA.

Please also keep in mind that there are a couple of flaws with the way the recorder writes VBA, and you will discover these flaws as you look up code that more experienced VBA programmers write. More specifically, there are a few major points I think you should consider while learning from the code written by the macro recorder.

  1. Avoid using the “select” method and “selection” property. When you record actions, it’s hard to avoid learning about objects without selecting them. In VBA, using the select method contributes to poor performance by increasing the number of objects that you are referencing. Your code also becomes harder to read by cluttering it with unnecessary objects.
  2. It will often resort to hardcoded answers, so you may not understand how to shift the value in a cell to the right by two, but it will tell you how to move the value from “A3” to “C3.” One workaround to this is to use the “Relative References” button (located below the “Record Macro” button on the Developer tab in the top left). Unfortunately, relative references are often expressed in a different notation than the more common Range.Cells(#,#) method that I am going to demonstrate. The “different notation” is called “R1-C1” style references, which are covered in more detail in the resources below. I recommend learning how they work, but not until you understand the other basic concepts in this article.

Despite these limitations, it is still an incredibly useful starting point because of the way it introduces relevant objects and methods. The macro recorder is enough to to get you started with the basics.

I actually wrote my first macro using the macro recorder in Word and then removed some of the unnecessary lines of code after reading about it. If it worked for me, then it can work for you.

Additional Resources

Here are the best additional resources to supplement your learning as you familiarize yourself with VBA code:

  1. Go to MSDN website https://msdn.microsoft.com/en-us/library/office/ and navigate to the VBA sections, or just search for whatever keywords you are interested in.
  2. John Walkenbach’s book, “Excel 2013 Power Programming with VBA.” It was a key ancillary resource for me early on and retains its value.
  3. In the VB Editor window, press F2 and search for your object or method here (refer to the Object Browser screenshot below).
  4. Visit https://stackoverflow.com/ – there are many helpful posts that solve common and practical VBA problems here.

Before we write a macro, you should know what the Object Browser looks like, so here it is (in Excel):

vba

Sample VBA Macro

Here is a simple example, using Excel 2013, that should help you get started by allowing you to apply these concepts. I am going to give you the problem first and then the answer at the end.

Here’s the problem: You have 10 subjects in a sleep study with a corresponding ID and a number between one and 50, indicating the number of “Sheep” a subject counted. You want to create a new key that links the ID to this particular Sheep count for archiving purposes. Combine the Record number (which happens to be the SubjectId in this case, to make things simple), Sheep number, the capital letter “G,” and then a random three-digit number into the right of the Sheep column.

For example, if the number of sheep counted in the record is 24, then concatenate 24, the uppercase letter “G,” and a random 3-digit number, like this: 24G132. Then highlight the new code in a different color4.

Here’s a sample table (create it manually in a blank Workbook, preferably in Columns “A” and “B”):

vba

Ok, got the answer? Alright, here’s my answer:

Since there is a lot going on in here, I’ll break it down to illustrate several points:

Here is the output:

vba

NOTE: The “LoopThroughColumn” Sub Procedure above is not my first working answer.

Here is my first answer (notice that I added the suffix “_test” – a nice habit to separate an answer you are working on from the last piece of code that you do not want to risk breaking):

And the output is exactly the same as my final answer. Let’s walk through the “_test” version (above) so that you understand what’s going on:

  1. First, describe what the code actually does in a short comment section and use a space followed by an underscore to continue the comment on the next line if you have to (lines 2-3).
  2. Then turn off ScreenUpdating for performance reasons. Copy the exact code on line 4. It’s a very common and important step as you work with larger workbooks and more complex code.
  3. Declare variables that you think you will need now, but feel free to change them as you need to (lines 6-8).
  4. Then establish the scope of the macro–tell it what worksheet and workbook you want it to run on (line 10).
  5. Create a variable to reference the single header that we are interested in (refer to line 12). Yes, I hard coded it for this example for simplicity purposes.
  6. Write the “FinalRow” formula as shown on line 14 prior to any loop expression that involves performing any action on cells in a column of a table5.

Using the Object Explorer

I find object explorer to be quite handy in these scenarios, so I’ll give you my explanation of it by looking up these terms in it. Here is a list of all of the terms and properties in the Object Explorer that we need to look at:

vba

At this point, I should quickly explain the highest levels of the object hierarchy in VBA to relate these objects you are using back to the relevant theoretical concepts.

The Application object is the highest in the Excel class library (and in most, if not all, other Microsoft Office application libraries). “Workbooks” is a collection of objects and acts as a read-only property of the Application object.

Within the “Workbooks” collection exists the “Worksheets” collection, which has a “Range” property, which has a “Cells” property. A property can be either a method or an object. Usually, a method is an action performed on a property or object. Think of properties and objects as nouns and methods as verbs. Most of what you need to know for now will involve those objects.

Referencing Ranges

Here is what it would look like to fully-qualify a reference to cell “A1” on a worksheet:

But this is for educational purposes; if you want a faster way of referencing cell “A1”, this is the way to go:

Or, try this:

Just keep in mind that this reference style only works as long as the Range that was activated prior to this statement contains a cell with the address. I have had to troubleshoot errors in my own macros as a result of improperly qualifying Excel objects before, and, let me tell you, that is not fun.

When in doubt, it is far better to start off by being too specific because you can always remove objects and test it later. For more information, please refer to this well-written article on Microsoft’s website, “Understanding Objects, Properties, and Methods”.

The Count Method

The Count method is invoked on the Rows property. Notice that both Cells and Rows are members of the Range class in the Excel Application. The count of rows is then used as the row argument of the cell address, and the second value is one that you would normally hard code (unless you have a reason to make it dynamic), since the position of the columns in your table will remain the same in most use cases.

The number you choose is the number of columns to the right. Then the “End” method is invoked on the resulting cell address with the “xlUp” argument, which basically places the focus on the last cell in the range we are working with. Since we are only interested in the Rows value, the .Rows property is invoked, allowing us to assign the result to an Integer value. One noteworthy aspect of this explanation is the use of “xlUp”, which is a constant and a member of the “Excel.XlDirection” collection.

For now, just keep in mind that there are collections of constants that can be quite useful to know and this is one such example.

On a quick side note, I want to emphasize how important it is to understand the object explorer, so let’s look at an illustration based on the above example. Here is a screenshot of all of the results for “xlUp”:

For-Each Loop

Next, on lines 16-23, we have our For-Each Loop. It’s exactly what you would expect: specify a variable that counts iterations through a loop, define its starting value, define its ending value, provide instructions on what objects you want to work with and what you want to do to them, then close the loop with the counting variable.

Let’s walk through how to use the loop and what is going on for each iteration, because you’ll want to understand these useful objects and methods.

The “Offset” method is very valuable because it is one way of allowing you to select other cells and ranges relative to an existing or previously defined range or cell. In our case, I assign the cell containing the “Sheep” header to a variable called “Header” with a Range data type.

Next, I provide instructions to grab the Value property of the cell that is located with a row number equal to the Counter variable plus one and one column to the right, and set it equal to the Counter plus one plus the value in the cell located one row below the “Header” cell plus the value of the Counter.

Then I change the value of the colorindex property of the cell directly below the “Header” cell to 35, which represents a shade of dark blue (line). Finally, I concatenate the uppercase letter “G” and a randomly generated string of three-digit numbers between 100 and 999 to the value calculated in the first part of the loop (lines 20-21).

Example: The Benefits of Versioning

As soon as you have written code that runs successfully and does what you want it to, then it is often a good idea to save it and start working on a newer version for optimizing. Compare the “_test” version to my revision using the With/End With construct (the version without “_test”). Observe how the following code exemplifies the motto, “Making the Complex Simple.” With fewer lines of code, you have fewer objects and methods to look up and read, which is less work and, in some cases, even simplifies the control flow logic. Here’s the code again:

The four major differences are as follows:

  1. Take the longest repetitive reference of code and put it into a variable. In this case, the cells containing our answer (one to the right of the “Sheep” column) may be stored in a variable, since it is used so frequently.
  2. Use the With/End With construct whenever you have to use more than one method on an object. Since I am changing the color and the value, the difference seems small here. In larger, more complex Sub procedures, there is a considerable difference in readability – and the Macro Recorder uses them, so it’s one more construct that you should get used to using
  3. Combine both steps in the loop that modify the value property. It’s way easier to read and results in fewer lines of code to read. I am now performing the simple calculation and concatenating the string to the end of it. Excel makes this action easy by allowing for implicit conversion between Integer and String (and other) data types when you use the “Variant” data type as I have.
  4. Set the “NewId” value inside the loop. If you set the value after the loop, then your macro will fail because you have not instantiated or defined an instance of “NewId” for use prior to the first time it is called. If you set the value before the loop, then your macro will run, but only provide a new value for the first cell in the new column because the value of the Counter is zero and does not change until the loop begins.

Raise the Limit of Your Productivity and Become the Office Hero with VBA

VBA is a programming language that business professionals, entry-level programmers, and experienced programmers can all use to enhance their career particularly by reducing the amount of time they spend performing repetitive tasks.

Additionally, by learning VBA, you will find yourself looking at all tasks in all MS Office products as potential VBA projects, which could potentially save you and your company a considerable amount of time and money.

When considering the plethora of free resources and simplicity of the language (relative to Python or C#, for example), it should also be a great choice whenever your concern is applying your programming knowledge to assist you in your current job.

Now you can go feel sorry for the casual MS Office users who waste time repeating the same boring tasks!


Footnotes

  1. For the purpose of maintaining concise prose, data analysis and reconciliation between the client’s ad hoc csv report and your worksheet will be referred to as “the process.”
  2. In other words, you do not have to login to another machine during the process in order to understand relationships between points of data. If your system or network administrator has the bandwidth, then feel free to work with him as you update your code to account for scheduled password updates and changing permission levels for all relevant users. Alternatively, you may avoid the issue altogether by writing macros specific to each domain in the process. If either of these options are not available to you, then VBA might not be the optimal solution to this problem.
  3. TXT or CSV files may be converted into Excel workbooks (XLS files)
  4. In case you are wondering why I have included the color coding requirement, think of it as an easy way to learn more objects.
  5. The “FinalRow” formula is something I looked up in another excellent VBA book, “VBA and Macros: Microsoft Excel 2013” by Bill Jelen and Tracy Syrstad on page 82. It is one of the single most important formulas that I have used in VBA because it allows you to dynamically find the size of a table or range. You will appreciate this a lot more later, but just get a feel for it now. Think about how it works by breaking it apart using the “select” method. Actually, I can kill two birds with one stone by helping you use the object explorer while breaking this line apart.