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;
Range("a1").CurrentRegion.Select
Command for each c In Selection.Rows
Debug.Print c.Columns(1)
Next
You can get a count of the total number of rows with this command norows:
noRows=selection.rows.coun
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.
noRows=mySel.rows.count
>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.
myStr="Marry"
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
myStr="Marry"
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.
Conclusion
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.