New! Excel User Group Now Open @
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.
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(.)).
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!
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...)
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
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.