How To Automate Tasks With Microsoft Excel VBA
Microsoft Office uses VBA as it’s macro or programming language. If you would like to learn to automate tasks with Excel VBA check out the video below and read this article.
This video and article will 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 will cover the minimum that we can get away with, including some concepts that you first need to understand.…
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 will cover "For" and "Do" loops, notice also you are 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 excuting 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
Continuing from the code above, lets look at some sample code. Make sure to read the comments in the code as they are 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