VBA instructions and properties will be difficult, particularly once you’re beginning out growing Excel purposes. Luckily, the VBA language is kind of intuitive and you may apply some frequent sense to get your code working correctly.
Utilizing the set command permits VBA to present you entry to the properties of alternatives and ranges, making coding that a lot simpler.
How To Use The Set Command
One state of affairs is likely to be to entry a column from an information desk:
Names Gross sales
John 98
Maria 122
Henri 120
Mary 102
Peter 85
Jacques 130
Mary 100
If you happen to use the present area command to pick out the primary column and print to the
fast window, the code would possibly appear to be this:
Vary("a1").CurrentRegion.Choose
For Every c In Choice.Rows
Debug.Print c.Columns(1)
Subsequent
The one downside utilizing this technique is that VBA would not provide you with any assist. For instance you may get a rely of the variety of rows with this command:
noRows=choice.rows.rely
That appears effective, however it’s essential to know the row rely command within the first place. VBA may also help with this in case you do issues barely in a different way and outline the choice as a spread first.
Dim mySel As Vary
Set mySel = Vary("a1").CurrentRegion
With the choice outlined, VBA provides some assist in accessing the properties of the
vary. To entry the properties on this case you possibly can sort mySel. and you might be provided the selection of over 20 totally different properties.
To get the variety of rows, you would choose the rows choice, after which the rely property. On this means, you should use frequent sense and experimentation to get the consequence you want.
noRows=mySel.rows.rely
Utilizing The Set Command To Code A Worksheet Perform
We’ll use the set command on this instance to learn the way many cells comprise the title “Mary”.
With out the set command we would write one thing like this:
myStr="Mary"
countStr = Utility.WorksheetFunction.CountIf(vary("a1:a8"), myStr)
We will additionally apply the set command to worksheet features and this makes writing the process that a lot simpler.
dim mySel as vary
set mySel = Vary("a1").CurrentRegion
Dim func As WorksheetFunction
Set func = Utility.WorksheetFunction
myStr="Mary"
The ensuing code is straightforward and simple to learn, particularly if it’s essential to use the operate once more later within the process.
countStr= func.CountIf(mySel, "Mary")
It is helpful to notice that utilizing the set command could make your code run barely slower. However in conditions the place you are unsure in regards to the appropriate coding, it will possibly make your procedures simpler to write down and is a worthwhile trade-off.
Abstract
Utilizing the set command is a good suggestion when writing any code, particularly in case you’re uncertain as to the right means of accessing the properties of a range. One of many targets when utilizing VBA is to speak your concepts in a transparent and simple method and the set command helps you try this.