How to use the Set Command in VBA?

In this Article, I present to you Set command of VBA. Lets learn How to write and how set Command is useful for Excel application development. With the help of Set command we can access the data from the excel database. It may be helpful for Excel application developer.

In this article we will learn about using the set command to enable VBA and access the properties of selection, range and write a code. VBA commands and properties of its commands helps us to develop or improve the Excel applications.

How to use the Set Command

A database had given below-

Name Sale

Raju 95

Mohan 110

Hari 100

Marry 120

Santosh 152

Phagu 450

Ranju 120

First we need to selection of the first column region command and print the window. The code of this process given below;

Command for each c In Selection.Rows
Debug.Print c.Columns(1)

You can get a count of the total number of rows with this command norows:


In the above code row count command in the first place. Now we use VBA to help with this. Define the selection as a range first:

Dim mySel As Range
Set mySel = Range("a1").CurrentRegion

VBA provide some help to access the properties of the range. In this stage we can use mysel command to access the properties.



How to using the Set Command to code?

We shall use the set command to find out how many cells contain have to the name "Marry".
If we write prodedure wthout the set command than we had write something like given this code.

countStr = Application.WorksheetFunction.CountIf(range("a1:a8"), myStr)

We can also apply the set command to worksheet functions

dim mySel as range
set mySel = Range("a1").CurrentRegion
Dim func As WorksheetFunction
Set func = Application.WorksheetFunction

If we need more search of record the use the procedure again to other records.

countStr= func.CountIf(mySel, "Marry")

This is a useful article that help to using the set command. But we need to take care in writing code.

Using the set command is a good practice when writing any code, if you are VBA application than the procedure code help you to clearing Set Command.


  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name: