While VBA is not the most powerful language out there, it is definitely worth learning but before I tell you why you should learn at least the basics, let’s address few misconceptions.

“VBA is Outdated“

This is partially true, but that doesn’t automatically make VBA obsolete. Microsoft hasn’t issued a statement that they will stop supporting VBA. They won’t introduce any more functionalities to enhance it.

VBA is still incredibly useful for those working in Finance, HR, Analytics and it can be for you too.

Sure, Python is easier to work with, but VBA works incredibly well with Office Applications since it’s directly integrated into Office Applications.

“Office? VBA Only Works with Excel Right?”

Oh no no no.

VBA is used in Word, PowerPoint, MS Access and even Outlook. This comes as a shock to many since they are busy chasing after the hottest item in the market.

Everyone wants to learn Python, without looking at what is already accepted as a standard in the market. If you look at how things play out in the real world, it’s harder to score web development projects in Python than it is with Java or Dot Net. You have a better chance of getting ahead learning Java that learning Python.

Then again, it depends on what exactly it is that you want to work with as a Software Developer.

This article is tailored for Automation Developers, which is why you won’t be seeing any discussion on web development, or data science.

VBA is the first language we will look at, followed by VBScript, and finally Python. I’ve seen few posts on automating Excel using JavaScript, but I have yet to explore it.

VBA isn’t going anywhere anytime soon so it wouldn’t hurt to learn it – at least learn enough to develop robust automations that can be easily integrated into RPA.

RPA and Visual Basic

Like I’ve mentioned in previous articles, it isn’t practical to have a click and drag action for every conceivable operation, which is why RPA platforms have provided us with an SDK which we can use to create our own click and drag actions.

In addition to that, we can also develop scripts and easily integrate them with RPA. Don’t get me wrong, RPA can easily take care of 90-95% of the automation. It’s only that 5-10% we have to watch out for.

I’d recommend starting with VBA since most RPA automations involve Excel Automation to varying degrees.

Initial Setup

To kickstart your VBA journey, create and open a new Excel Workbook.

I usually open excel using the Run Command like so:

The Visual Basic Editor comes preinstalled with Excel, but to access it we have to either enable Developer’s Tab or use Alt+F11.

To activate the Developer’s Tab, right-click the Ribbon and press Customize the Ribbon.

Next check the box for Developer and press Ok.

Now you should see the Developer’s Tab appear on the Ribbon and we can enter the Visual Basic Editor.

Security Concerns with Macros

VBA was designed during the 90s when Microsoft didn’t take the threat posed by viruses seriously. The Internet only made things worse, which is why they introduced a feature that disabled macros by default.

Why disable macros? Because you can create viruses with macros.

But even so, why disable macros entirely?

Macro viruses are a pain to deal with as they work by copying itself into other macro enabled documents (Excel, Word etc.). This is especially troublesome since it isn’t dependent on the operating system and can go on an infection spree.

Also, Macros can work with shell commands which means it can run programs and delete files it isn’t supposed to. This is why clients can sometimes be hesitant to work with macro enabled documents.

Don’t get it twisted, VBA is not evil for the exact same reason a knife is not evil just because it can put someone into an early grave.

We can prevent malicious codes from running by keeping macros disabled from the Trust Center, by downloading macro-enabled documents from trusted sources and by deleting macro-enabled documents once it’s no longer in use.

Two Types of Code Blocks

VBA code is written into Sheets and Modules. Its always best to write VBA in modules since Sheets can get renamed, moved or deleted and this can have unintended consequences.

Let’s create a module by right-clicking in Projects and pressing Insert > Module.

Now that we have our module ready, lets look at the types of VBA code we can write.

Subroutines and Functions

Subroutines will be the main focus of most Automation Developers since RPA can pass arguments into and trigger them. Subroutines are only capable of performing a sequence of operations without returning anything which is why they are used specifically to manipulate data in Excel, whether it be to create charts, graphs, pivot tables or just basic data cleaning.

Although Subroutines can’t return data, it can behave like functions and is sometimes preferred over Functions if you are looking to optimize your VBA code. This is done by passing the value(s) in as a Reference, but that is an advanced topic and you won’t be needing it, at least not yet.

Functions on the other hand are used to compute values and return results based on those computations. I bet you are familiar with IF, SUM, COUNT, AVERAGE, VLOOKUP etc. In addition to these, we can create functions for specific calculations, or in conjunction with Subroutines.

Lets Create a Simple VBA Program

I’ll walk you through a very simple exercise.

Here is the dataset we will be working with:

There is a Sheet titled FoodSales which will be the focus of our automation. We will develop a macro that highlights those cells in the UnitPrice column which exceed 2.

This will be a rather straightforward process, so you won’t have any trouble following along.

First, we will access the Visual Basic Editor and create a Module, after which we will provide a suitable name for our Subroutine.

Sub colorCells()

End Sub

If your variables are not declared, the compiler will term them as variants and it will consume more memory which can affect performance. This behavior only takes place when variable declaration, or Option Explicit as its called, is disabled.

I usually keep variable declarations enabled, and what that means is the compiler will validate whether the variables you are using in your VBA are declared or not, and if there are it will throw an error when you execute the program.

Once we have our block ready, its time to declare our variables. Some are obvious, while some aren’t.

Before we color cells, we need to know where those cells are located, or more specifically, which sheet they belong to. This implies that we have to declare a worksheet object.

Variables in VBA are declared by adding a Dim, followed by the variable name, and the type it will assume.

The Worksheet is not a simple data type, it is in fact an object. To set values to an Object, you have to use the Set keyword.

Sub colorCells()
    Dim Sh As Worksheet
    Set Sh = ThisWorkbook.Worksheets("FoodSales") 
End Sub

After we locate the sheet we have to think of a way to color the required cells. Its not like we can look at all the cells in one go and color the ones that fit the criteria…sure we can “see” which cells require highlighting but programs aren’t blessed with that sort of intelligence.

Programs like to think iteratively, or at least most of them do.

Simply put, we can instruct VBA to loop through each row and check if the cell in that particular column fits the criteria and if so, color it.

There are many ways to loop through data in VBA, and we will go for the simplest one for now:

For i = 2 To ?
Next i

Since we have Option Explicit enabled, we will have to declare i as Long. We used to declare numerical values as Integer before, but the latest version of VBA converts Integers to Long.

Why?

I don’t have the faintest clue. You can read it from the official sources here.

Don’t Mind Me, Just Looping Through

Before we start iterating, we have to set boundaries. The program must iterate as long as data is present otherwise it will take more time than necessary. So how do we go about achieving that?

Sure, I will explain how it’s done, but I ask these questions to get you in the habit of visualizing the process. You aren’t going to burn any calories by watching someone else exercise!

Sub colorCells()
    Dim Sh As Worksheet
    Set Sh = ThisWorkbook.Worksheets("FoodSales")
    
    Dim i As Long
    For i = 2 To ?
    
    Next i
    
End Sub

The loop will go on iterating as long as i is not equal to the limit.

You’ve probably guessed by now, the limit has to be equal to the total number of rows. Again, there any many ways to compute this, but let me show you the best way to do so.

An Excel Range can either be a Cell, or a group of cells. We can represent them using the Range or Cells method, and I’ll show you both.

Range("A1").Select 'Selects Cell A1
Range("A1:D5").Select 'Selects all cells within the boundary of A1 and D5
Cells(1,1).Select 'Selects Cell A1; used mostly in loops

And this is how we can find the last row.

Dim rowCount As Long
'Range method
rowCount = Sh.Range("A" & Sh.Rows.Count).End(xlUp).Row
'Cells method
rowCount = Sh.Cells(Sh.Rows.Count,1).End(xlUp).Row

Lets break it down.

End(xlUp) is VBA for Ctrl+Up Arrow.

This bit of code goes all the way to the last cell in the worksheet, and shifts back up to the last row item in that column. This is useful, because we can ignore gaps in between the dataset and capture the actual row count.

If you are not sure what it does, try performing this manually in Excel.

And finally, Row returns the Row Number of the current cell its at.

This is what the code should look like:

Sub colorCells()
    Dim Sh As Worksheet
    Set Sh = ThisWorkbook.Worksheets("FoodSales")
    
    Dim rowCount As Long
    rowCount = Sh.Range("A" & Sh.Rows.Count).End(xlUp).Row

    Dim i As Long
    For i = 2 To rowCount

    Next i
    
End Sub

Love May Be Unconditional, but This Ain’t About Love

Now that we have our loop ready, we can proceed to check whether the cell in the seventh column(UnitPrice) is greater than 2.

This is achieved using the If Method like so:

If [Condition] Then [Action]
Or
If [Condition] Then
    [Actions]
End If

The If Method comes in two forms. The first is used when only a single operation is performed, while the last form is used when multiple action are being performed.

Since we will only perform a single operation, we can use the first form but I will be using the second form as it improves readability.

If Sh.Cells(i, 7).Value > 2 Then
    
End If

To color cells, we can directly project changes onto the range since color is an attribute that belongs to the range object.

If Sh.Cells(i, 7).Value > 2 Then
    Sh.Cells(i, 7).Interior.Color = vbRed
End If

And with that, you have created your first Subroutine!

Sub colorCells()
    Dim Sh As Worksheet
    Set Sh = ThisWorkbook.Worksheets("FoodSales")
    
    Dim rowCount As Long
    rowCount = Sh.Range("A" & Sh.Rows.Count).End(xlUp).Row

    Dim i As Long
    For i = 2 To rowCount
        If Sh.Cells(i, 7).Value > 2 Then
            Sh.Cells(i, 7).Interior.Color = vbRed
        End If
    Next i
    
End Sub

Once we have our macro ready, next we have to save the file as a Macro-Enabled Workbook.

Triggering Macros from Automation 360

Lets head back to our Control Room and trigger our newly created Macro from there.

For this, you have to drag in Three Excel Actions:

• Excel Advanced: Open
• Excel Advanced: Run Macro
• Excel Advanced: Close

After running the process, this is the output we will receive:

In Conclusion

If you ever get stuck anywhere, you can just Google it, but you will be perpetually stuck if you don’t study the basics. Google can retrieve the pieces for you, and you have to figure out how they fit together.

Next week, I will walk you through the exact same example, but this time we will modify it in such a way that makes it convenient for us.

Here’s a little hint as to what the modification is going to be – remember, we had to save our file as a Macro-Enabled Workbook.

Are all Excel Workbooks macro-enabled by default?

Is it possible to programmatically convert a Workbook to Macro Enabled Workbook? Is that the right approach?

How about injecting macros into a plain Excel Workbook?