VBA commands and properties can be complicated, especially when you’re starting out developing Excel applications. Fortunately, the VBA language is quite intuitive and you can apply some common sense to get your code working properly.
Using the set command enables VBA to give you access to the properties of selections and ranges, making coding that much easier.
How To Use The Set Command
One scenario might be to access a column from a data table:
Names Sales
John 98
Maria 122
Henri 120
Mary 102
Peter 85
Jacques 130
Mary 100
If you use the current region command to select the first column and print to the
immediate window, the code might look like this:
Range("a1").CurrentRegion.Select
For Each c In Selection.Rows
Debug.Print c.Columns(1)
Next
The only problem using this method is that VBA doesn’t give you any help. For example you can get a count of the number of rows with this command:
noRows=selection.rows.count
That seems fine, but you need to know the row count command in the first place. VBA can help with this if you do things slightly differently and define the selection as a range first.
Dim mySel As Range
Set mySel = Range("a1").CurrentRegion
With the selection defined, VBA offers some help in accessing the properties of the
range. To access the properties in this case you can type mySel. and you are offered the choice of over 20 different properties.
To get the number of rows, you would select the rows option, and then the count property. In this way, you can use common sense and experimentation to get the result you need.
noRows=mySel.rows.count
Using The Set Command To Code A Worksheet Function
We’ll use the set command in this example to find out how many cells contain the name “Mary”.
Without the set command we’d write something like this:
myStr="Mary"
countStr = Application.WorksheetFunction.CountIf(range("a1:a8"), myStr)
We can also apply the set command to worksheet functions and this makes writing the procedure that much easier.
dim mySel as range
set mySel = Range("a1").CurrentRegion
Dim func As WorksheetFunction
Set func = Application.WorksheetFunction
myStr="Mary"
The resulting code is simple and easy to read, especially if you need to use the function again later in the procedure.
countStr= func.CountIf(mySel, "Mary")
It’s useful to note that using the set command can make your code run slightly slower. But in situations where you’re not sure about the correct coding, it can make your procedures easier to write and is a worthwhile trade-off.
Summary
Using the set command is a good idea when writing any code, especially if you’re unsure as to the correct way of accessing the properties of a selection. One of the objectives when using VBA is to communicate your ideas in a clear and easy manner and the set command helps you do that.