How To Automate Tasks With Microsoft Excel VBA

Microsoft Office uses VBA as it’s macro or programming language. If you’d like to learn to automate tasks with Excel VBA check out the video below and read this article.

We’ll give you an 80/20 overview of how to get coding vba macros for excel like an expert, enjoy…

Lets get you coding fast, I’ll cover the minimum that we can get away with, including some concepts that you first need to understand.…

Programming Terms

Module – This is the "page" that you type your code into, like a word processor document if you will.

Subroutine – This is a sub grouping of code within the module organized by task.

So for example, you may have 4 lines of code that set the background colour of a given range to red.

Lets look at the code below, the line "setRangeBackgroundToRed" on line 06 represents a call to a subroutine between lines 19 and 26.

This code will set the background of a range of Microsoft Excel cells to red.

02  ' Four lines of code are now contained within this easy to read  subroutine
03
04  Sub doStuff()
05    ' Other Code
06    setRangeBackgroundToRed
07    ' Other code here
08  End Sub  

19  Sub setRangeBackgroundToRed()  ' Function name (this is a comment btw)
20    dim rngSquare as Range
21    Set rngSquare = thisworkbook.worksheets(1).range(“A1:D4”)
22    rngSquare.resize( ,1).value = “First Column”
23    rngSquare.resize( ,1).font.color=vbRed
24    rngSquare.Resize( ,1).offset(,1).value = “Second Column”
25    Set rngSquare = nothing
26  End Sub

Function – this is pretty much the same as subroutine, with the only difference being that it will return a value.

The following is an example of using a custom function with it’s code below.

03  dim szCustLastName as String
04  szCustLastName = getCustomerLastName(45)
05
06  Function getCustomerLastName(byval iID as Integer) as String
07    ' multiple lines of code to go to database
08    ' and look up id here.
09    getCustomerLastName = szLastName
10  End Function

Variable – Think of these as buckets into which you store the results of your calculations, or the spreadsheet cells you use to store values that you intend to use in other formula.

In VBA variables have data-types such as string (text), integer, double and various application specific variable types such as range or worksheet if using Excel.

In the code listings displayed on this page rngSquare and szCustLastName are examples of the creation and use of variables.

Loops – Loops are essential for doing similar tasks again and again.

Here we’ll cover "For" and "Do" loops, notice also you’re getting examples of using variables and in this example the excel vba Range Object. First lets look at the for loop.

This says repeat the loop a specified number of times starting at the initial number contained in "iCounter" in this instance and continue until we reach the number contained in the variable iRows.

This is one of the more common loops used by vba programmers.

01  Dim iCounter as Integer, iRows as Integer
02  Dim rngSquare as Range
03
04  iRows = rngSquare.rows
05  For iCounter = 1 to iRows
06    ' This line will output something like...
07    ' I'm Looping row 2 of 4 rows!
08     MsgBox "I'm Looping row " & iCounter & " of " & iRows & " rows!"
09  Next

The next most used used loop is the "Do" loop.

This is a little more dangerous than the previous loop in so far as you could create an "infinite loop" if you are not careful (or unlucky 😉 ).

If you do accidentally create an infinite loop in vba, then this can only be stopped by pressing "ctrl-break" or in more extremes cases you may have to shut down you application from windows thereby risk losing unsaved work.

03  Dim bTestCondition as Boolean
04  Do
05     ' Execute various lines of code
06     ' Keep on looping
07  Loop while bTestCondition = false

The above loop continues executing while the "while" condition is true, You can get the same behaviour by omitting the while bTestCondition = false
part and instead inserting an "if" statement and when it evaluates to true use exit do to leave the loop.

Object Orientation – This to a certain extent is just a way of thinking, grasshopper!

At it’s most simple, just think of objects as code bundled into THINGS that do stuff.

Your other code then just asks these “things” to do stuff. This is done by calling functions (methods) and variables (properties) that are attached to these things.

There is a lot more to it than that, but if you can think in this way it makes you coding a lot simpler.

For example lets look at a spreadsheet range.. say A1 to D4, we can turn this into an object using VBA and then it becomes easier to manipulate.

Case in point follows…

01  Sub setRangeBackgroundToRed()	' Function name (this is a comment btw)
02    Dim rngSquare as Range
03    Set rngSquare = thisworkbook.worksheets(1).range(“A1:D4”)
04    rngSquare.resize( , 1).value = “First Column”
05    rngSquare.resize(  1).font.color=vbRed
06    rngSquare.Resize( , 1).offset(, 1).value = “Second Column”
07    Set rngSquare = nothing
08  End Sub

So in the above example we created an Object called “rngSquare” from the range address A1 to D4 and then manipulated it to change it’s color and write labels into column 1 and column 2.

Notice the dot notation, bye the way, take for example the line

06    rngSquare.Resize( , 1).offset( , 1).value = “Second Column”

What’s going on here? You could ask.

Well you use dot notation to communicate with objects.

That is to say, as mentioned once already, that if you wish to call a function, or read or set a property (variable), then you use Object.VariableName (property name) or Object.MethodName (function Name)

So in the above case, the instruction is…

Take rngSquare (the previously defined rectangular 2 column range)

Then resize it to one column,then offset that one column by moving it one column to the right, now set the value of the cells within this new range location to read “Second Column”.

All elegently expressed on one line.

Excel Specific Objects

Given that the best way to get things done in Microsoft Office and Excel for the purposes of this tutorial is to "Think In Objects" then lets look at this simplified map of the Excel object model.

The Application object sits on top.

This represents Excel in the object tree.

Under that you have the workbooks collection which as it’s name implies is an object that manages 1 to n open workbooks (workbook being an Excel file which contains 1 to n sheets).

Similarlly the workbook has a worksheets collection that manages all worksheets in the workbook

Workbooks – The workbooks collection manages all open workbooks in the current Excel Session. Read the comments (green) in the code below for more insight.

03  Dim wbk as Workbook ' Define object of "type" Workbook
04
05  ' From the workbooks collection use the open method 
06  ' and pass in the path to the file string.
07  ' then assign the opened file to the wbk variable.
08  Set wbk = Workbooks.Open("c:\test\myWorkbook.xls")

Workbook – This represents an Excel file that contains 1 to many "Worksheet" or "Sheet" (different names for the same thing) objects. The code above demonstrates a workbook object being opened.

Worksheets – This is the collection object that manages all worksheets or spreadsheets within your Excel File. You use this to query how many sheets exist in the file, to add new sheets and to get a code "reference"to individual sheets.

Worksheet – These are you individual "spreadsheets".

Range – The Excel object most frequently used to manipulate data on your spreadsheet

Conclusion

Continuing from the code above, lets look at some sample code. Make sure to read the comments in the code as they’re part of this tutorial. Try this out and if you have any questions – leave a comment below.

02  Dim wbk as Workbook
03  Dim wks as Worksheet
04  Dim iWksCount as Integer, iCounter as Integer
05  Dim rngData as Range
06
07  Set wbk = Workbooks.Open("c:\test\myWorkbook.xls")
08  ' Get number of sheets in workbook
09  iWksCount = wbk.Worksheets.count
10  ' We could use a different type of for loop here (for each next),
11  ' but I want to illustrate other concepts.
12  For iCounter = 1 to iWksCount ' Loop over each worksheet
13  ' Assign worksheet reference to worksheet object
14    set wks = wbk.Worksheets(iCounter) 
15    ' You can query each object and navigate the object tree...
16    MsgBox "Sheet Name is " & wks.Name
17    MsgBox "Workbook Name is " & wks.parent.Name
18    ' You can also make changes to the object
19    wks.name = "Data Sheet"	
20    MsgBox "This worksheet has " & wks.Rows.Count & " rows!"
21    Set rngData = wks.range("b5:e20")
22  Next