Home » Word Press » Why The Set Command Is So Important In Learning VBA For Excel

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

VBA instructions and homes will also be sophisticated, particularly when you are beginning out creating Excel programs. Fortunately, the VBA language is reasonably intuitive and you’ll be able to practice some not unusual sense to get your code running correctly.

Using the set command permits VBA to come up with get admission to to the homes of decisions and levels, making coding that a lot more straightforward.

How To Use The Set Command

One state of affairs may well be to get admission to a column from an information desk:


Names Sales
John 98
Maria 122
Henri 120
Mary 102
Peter 85
Jacques 130
Mary 100

If you employ the present area command to choose the primary column and print to the

fast window, the code may appear to be this:

 

Range("a1").CurrentRegion.Select
For Each c In Selection.Rows
Debug.Print c.Columns(1)
Next

The most effective downside the use of this system is that VBA does not come up with any lend a hand. For instance you’ll be able to get a rely of the choice of rows with this command:


noRows=variety.rows.rely

That turns out high-quality, however you want to grasp the row rely command within the first position. VBA can lend a hand with this should you do issues somewhat in a different way and outline the choice as a variety first.


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

With the choice outlined, VBA provides some lend a hand in having access to the homes of the

vary. To get admission to the homes on this case you’ll be able to kind mySel. and you might be presented the selection of over 20 other homes.

To get the choice of rows, you could make a selection the rows choice, after which the rely belongings. In this fashion, you’ll be able to use not unusual sense and experimentation to get the end result you want.


noRows=mySel.rows.rely

Using The Set Command To Code A Worksheet Function

We’ll use the set command on this instance to learn how many cells include the title “Mary”.

Without the set command we would write one thing like this:


myStr="Mary"
countStr = Application.WorksheetFunction.DependIf(vary("a1:a8"), myStr)

We too can practice the set command to worksheet purposes and this makes writing the process that a lot more straightforward.


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

The ensuing code is modest and simple to learn, particularly if you want to make use of the serve as once more later within the process.


countStr= func.DependIf(mySel, "Mary")

It’s helpful to notice that the use of the set command could make your code run somewhat slower. But in eventualities the place you might be no longer positive about the proper coding, it could make your procedures more straightforward to write down and is a profitable trade-off.

Summary

Using the set command is a good suggestion when writing any code, particularly in case you are not sure as to the proper approach of having access to the homes of a spread. One of the goals when the use of VBA is to keep up a correspondence your concepts in a transparent and simple way and the set command is helping you do this.

Check Also

Tabe   Premium Tabs (Navigation)

tabe – Premium Tabs (Navigation)

tabe – Premium Tabs (Navigation). tabe is a straightforward Tabs Plugin. Based on jQuery and …