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.

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

rng.Value="Nick"

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.

Top

Copyright Nick Hodge 2008. All Rights Reserved.