Home Search Site Map

VBA Code Examples
User Defined Functions
Implementing VBA

New! Excel User Group Now Open @ www.excelusergroup.org,
VBA = V writing VBA for you behind the scenes. To test this, open a fresh workbook, go to Tools>Macros>Record New Macro... and click OK. Now enter the word Nick into Cell A1 on Sheet 1 and make it Bold. Now open the VBE and look at the created code by double-clicking on 'Module1' in the 'Project Explorer'. It should look similar to the following. (With your name not mine!).

Sub Macro1()
' Macro1 Macro
' Macro recorded 14/11/2004 by Nick Hodge

ActiveCell.FormulaR1C1 = "Nick"
With ActiveCell.Characters(Start:=1, Length:=4).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = FalsWith ActiveCell.Characters(Start:=1, Length:=4).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = FalsWith ActiveCell.Characters(Start:=1, Length:=4).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Selection.Font.Bold = True
End Suben these settings are the 'default' and do not need to be set. It also tracks all your moves, which results in a lot of 'Select'  and 'Activate' statements, which when writing efficient code is seldom necessary, a you can act directly on the object, rather than 'selecting' it and acting on the 'Selection'. The above recorded  macro could be written more efficiently like so.

Sub StreamlinedCode()With...End With' structure, which is effectively saying everything in between should be actions on Range("A1"), you will notice you still use the dot (.) separator between objects and properties.

Within VBA you can also write 'Event code' and UDFs, (User Defined Functions) that act like Excel's built-in functions (SUM, VLOOKUP, etc), these have to be manually written, they cannot be recorded.

VBA is an object-orientated language where you manipulate particular 'objects' using their properties, methods and events. The objects you can manipulate are specific to the 'Application' in which you are writing them, Hence VBA. (This does not mean you cannot control other objects from an application however).

An object is an entity within the program, for example Excel has the following objects (and many. many more!), all of which can be manipulated in code.

Object Description
Application The Excel application itself, contains all other objects below it.
Workbook A single Excel file containing all the worksheets, chart sheets, etc.
Worksheet A single Excel Worksheets containing all the cells, shapes, etc.
Range A cell or group of cells
Cell A single cell
Character A single character in a cell

So, if I wanted to refer to the third character in the cell A1 on Sheet1 in a file called Book1.xls in Excel and make it bold, you could 'drill down' the object model like so. (Note each object or property of an object is separated by periods(.)).

Sub Change3rdChar()
Application.Workbooks("Book1").Worksheets("Sheet1").Range("A1").Characters(3, 1).Font.Bold = True
End Sub

What this does is through the top-level Application object, looks at the 'Book1' workbook in the workbooks collection, (you can have more than one workbook open at a time in Excel), looks at "Sheet1" in the worksheets collection. (You can have more than one worksheet in a workbook), looks at the 'Range' object A1 (a single cell, but we could just have easily set the third character in all cells in the worksheet in one go to bold!), it then looks at a single character object in the characters collection. (You can have more than one character in a cell).  The two digits separated by a comma are the arguments (Start and Length), so we are starting at character 3 and taking 1 character. It then gets the Font object of that character and sets it's property 'Bold' to true! (Quite a route...but it can be simplified!).

Setting all cells just for fun!

Sub SetALLCells()
'Prepare for a short wait as Excel writes Nick 16,777,216 times!
With ActiveSheet.Cells
.Value = "Nick"
'And then make them bold!
.Font.Bold = True
End With
End Sub

Variables can be set up to point to these objects, so we can make the above single line code a bit easier and re-usable like so. (I use the 'Application' object here for clarity.  As it is the top-level object Application.Workbooks... is the same as just Workbooks...)

Sub Change3rdChar()
Dim wb As Workbook
Dim wks As Worksheet
Dim rng As Range

Set wb = Application.ActiveWorkbook
Set wks = wb.ActiveSheet
Set rng = wks.Range("a1")

rng.Characters(3, 1).Font.Bold = True
End Sub

The lines starting with 'Dim are 'Dimensioning' a variable and the part after 'As' is telling Excel what type it should expect. There are many type of variable, Long, Integer, String, Single, Double, Variant, etc and object variables such as these.

Any 'object' variable needs to be 'Set' and this is done in the next few lines. This allocates an object to the variable and from now on, you only need use the variable name to refer to the object. (So in the above example, 'rng' now refers through two other variables to Application.ActiveWorkbook.ActiveSheet.Range("A1"))!

Variable names can be any name you choose except certain reserved names in Excel, for example you can't call a Range object variable Range! They also cannot have spaces or certain 'illegal' characters' (*./\, etc).

Now rather than the huge line we had the shortened version, using the variables is used. Now the 'set-up' of this is probably more long-winded than the single line, but if you continue to refer to the range, for example, as you will find you do, simply having to type a line


Is far less painful than the huge repetitive line that would be necessary via the other route.


Assigning to variables

We have seen above how you assign to an object variable using the 'Set' statement. You can use the same method, (without the need for 'Set') with 'ordinary' numeric, string or variant data types, like so

Sub AssignToVariable()
Dim myString As String
'Assigning Values
myString = ActiveSheet.Name
'Display a message box showing use of the data collected in the variable
MsgBox "The currently active sheet is " & myString, vbOKOnly
End Sub

For a summary of 'Data types' and the memory they consume you should look under Excel help for 'Data Type Summary'. It is good programming practice to always declare variables and always use a 'type'. It may create more syntax errors, (Errors detected by the code 'compiler' as being unable to be run or causing error at run-time), but you can be sure of the data you are receiving at any given time.



Copyright Nick Hodge 2009. All Rights Reserved.