Why The Set Command Is So Important In Learning VBA For Excel

Posted on Mar 2 2017 - 9:45pm by Techy Hints

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.



Source by Andy L Gibson

About the Author

Geeky Magazine provides you with the daily latest technical news, reviews, startups, and every new in the internet world.