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.
previous post