What is Excel VBA?

VBA stands for Visual Basic for Applications. Basically (pun intended) it is a programming language adapted specifically for Microsoft Applications. This includes Outlook, Word, Access, Excel etc. In my opinion, it is easier to learn than some of the more traditional programming languages like Java, or C++ not only because of the structure of the syntax (programming grammar), but also the fact that Excel allows you to record Macros (programming code) and view/edit the code that you produce. This creates a great learning opportunity for those who like to learn by doing, rather than from a book or classroom setting.

This portion of the site is designed to provide examples of VBA code, specifically for Excel. As you read through some of the examples, you should begin to get an idea of how interact with Excel Spreadsheets via VBA code.

You might be asking yourself, “What good is a Excel VBA? I have gotten around Microsoft Excel just fine without it for the past x years.”

Imagine that you grew up in a remote town where you had never seen a car. Everyone walks to the local store, the local bar, or to the local restaurants. There is absolutely nothing wrong with this, everyone is getting along ‘just fine’.

An outsider strolls into the local pub and begins talking to the residents of your town about cars. He preaches how you could get to the store in 5 minutes, rather than 30. Some would shun the idea, convinced that walking is better, or that they would never understand this complicated machine. Those set in their ways begin to leave the pub, but you listen, at least partially intrigued with what he has to say. As you listen, you start to realize that not only can you get places that you typically go faster, you can begin to imagine going places that walking would never allow!

The metaphor at this point should be obvious. Excel VBA is a tool that allows you to go beyond your physical limitations of speed, and manipulate spreadsheets in ways that time would not typically permit.

Excel’s VBA is great for repetitive actions. Just because an action is repetitive, does not mean that it has to be simple, or even quick. Tasks that are done every day once a day in Microsoft Excel, but take hours, could potentially be reduced to seconds! Since I regularly work in Excel, I often find myself spending 5 minutes writing a macro for a task that would take me 30 minutes, even if I never expect to do that task again. At the worst, I have saved nearly half an hour; at the best, you can imagine my surprise when I have to do the task again, but already have a tool to accomplish it in seconds.

For this reason, I recommend you keep either one spreadsheet, or multiple spreadsheets with your Excel VBA code neatly organized. You will be able to grab code from old macros, and adapt it to new situations. As you use a particular piece of code more often, your brain will start to remember what you are looking for, and eventually you will be able to recall and write the code faster than you can find it!

Excel VBA Basics

The first thing you should know about Excel VBA, is how to make sure Macros are enabled. To do this, go to the Excel File Menu and click on Excel Options. Then go to the Trust Center, followed by Trust Center Settings, and finally Macro Settings.

Here you have several choices.

Allowing all macros is fine, so long as you are not downloading spreadsheets others have created (that includes E-mail, online tutorials, even friends [malicious hidden spam]) If you are downloading others’ spreadsheets, you open yourself to the possibility of malicious code being run on your computer.

**Now would be a good time to inform you that you can set an Excel macro to run automatically: for instance upon the saving of a workbook, closing of a workbook, or even upon the opening of a workbook. We will address these later, but I wanted to point out that code can be run, even without your consent, if you do not know the source of the spreadsheet.

If you are concerned for the security of your computer, then go ahead and select Disable all macros with notification. This will allow you to see that there are macros in the sheet, and enable them if you deem them safe.

Upon opening a Macro Enabled Workbook, Microsoft Excel will prompt you:

If you want to enable Excel VBA for the session, select “Options,” and “Enable this content.”

You can leave the macro settings on “Disable with notification”, and switch to “Enable all macros” in the Trust Center Settings when you know you are opening a safe workbook. You may have to close Microsoft Excel for these settings to take effect.

Using a Macro

Now that you know how to record a macro, you need to learn how to access it. There are multiple ways to do this. The first is the developer tab. If you cannot view Excel’s Developer tab, go to the Excel File menu as seen above, and under “Popular”, make sure that the show developer tab check box is checked. This will make the Developer tab visible.

On the left, you will see the Macros button. Clicking this will bring up a list of the macros in all open workbooks (you might notice that the names of macros from other workbooks have do not look as clean as the macros within the active workbook.

This window allows you to run or edit a macro that you have created.

Accessing the Project Manager

Recording a macro, however, is more like riding a bike that driving a car; so if you really want to get some mileage out of Microsoft Excel’s VBA, you need to learn how to change or edit the code. To access the project manager (Excel’s VBA interface [aka IDE]), click “Visual Basic” on the developer tab, or hit Alt + F11.

First of all, you should see the project explorer shown in the left of the picture above. To show the project explorer, or bring it back if you accidentally close it out, it is as simple as clicking “view” then “project explorer,” or hitting Ctrl + R,

Macros are contained within modules. Each module can have more than one macro. To locate your macro, look for your project file in the project explorer. It will appear as “VBAProject(Filename)”

Expand this list. If there is not a subfolder named modules, then you do not have any macros in this project file. To create a new macro from scratch, right click your project file and select “insert module.”

This will bring up a blank space to create your code.
Every macro starts and ends the same way.

Sub Macro_Title()

End Sub

The actions your macro performs are located within these two key lines. The macro title cannot have spaces, cannot be a number, and cannot be a reserved keyword nor share a name with another macro in the same Excel project.

Comments

In the previous image, you may have notice the green text in my “Macro1″. This is a comment. Comments are extremely useful for programmers. They allow a programmer to make notes that make reading the code much easier or help remind what a particular section of code does. They also assist any third party who is reading your code to understand what function a particular piece of code serves.

To create a comment, begin any line with an apostrophe (‘), A.K.A a single quotation. If a particular piece of code is not working, you can comment it out to let the code run without executing the code. Later, when you have fixed or completed that section, you can remove the comments to allow the code to run. Two extremely useful tool are the comment block or uncomment block buttons. To add them to your toolbar, click any down arrow on the toolbar, go to add or remove buttons, then edit, and finally select the comment block and uncomment block tools. This allows you to change several lines of written text, or vice versa.

For now, start playing around with recording macros and editing the code that they create. I will continue to add content.

Alignment VBA

With Selection

.HorizontalAlignment = xlLeft

.VerticalAlignment = xlBottom

.WrapText = False

.Orientation = 45

.IndentLevel = 0

.ShrinkToFit = False

.ReadingOrder = xlContext

.MergeCells = False

End With

Some Options

.HorizontalAlignment = (xlLeft, xlCenter, xlRight)

.VerticalAlignment = (xlTop, xlCenter, xlBottom)

.Orientation = (Any number of Degrees)

.IndentLevel = (Any Integer)

.ReadingOrder = (xlContext, xlLTR, xlRTL)

Font VBA

With Selection.Font

.Name = “Times New Roman”

.Size = 11

.Strikethrough = False

.Superscript = False

.Subscript = False

.OutlineFont = False

.Shadow = False

.Underline = xlUnderlineStyleNone

.ThemeColor = xlThemeColorLight1

.TintAndShade = 0

.ThemeFont = xlThemeFontNone

End With

Borders VBA

With Selection.Borders(xlEdgeLeft)

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlThin

End With

InteriorVBA

With Selection.Interior

.Pattern = xlPatternChecker

.PatternColorIndex = xlAutomatic

.ThemeColor = xlThemeColorDark1

.TintAndShade = -0.149998474074526

.PatternTintAndShade = 0

End With

Interior Gradient

With Selection.Interior

.Pattern = xlPatternLinearGradient

.Gradient.Degree = 90

.Gradient.ColorStops.Clear

End With

With Selection.Interior.Gradient.ColorStops.Add(0)

.ThemeColor = xlThemeColorDark1

.TintAndShade = 0

End With

With Selection.Interior.Gradient.ColorStops.Add(1)

.ThemeColor = xlThemeColorAccent1

.TintAndShade = 0

End With
(xlPatternAutomatic, xlPatternChecker, xlPatternCrissCross, xlPatternDown, xlPatternGray16, xlPatternGray25, xlPatternGray50, xlPatternGray75, xlPatternGray8, xlPatternGrid, xlPatternHorizontal, xlPatternLightDown, xlPatternLightHorizontal, xlPatternLightUp, xlPatternLightVertical, xlPatternNone, xlPatternSemiGray75, xlPatternSolid, xlPatternUp, xlPatternVertical)

Number Formats VBA

Selection.NumberFormat = “0.00%”

Selection.NumberFormat = “$#,##0_);[Red]($#,##0)”