Pardon me as am a newbie in VBA.
Sometimes I use
Dim r as Range
r = Range("A1")
Other times I use
Set r = Range("A1")
What is the difference? And when should I use what?
There's no reason to use set
unless referring to an object reference. It's good practice to only use it in that context. For all other simple data types, just use an assignment operator. It's a good idea to dim
(dimension) ALL variables however:
Examples of simple data types would be integer
, long
, boolean
, string
. These are just data types and do not have their own methods and properties.
Dim i as Integer
i = 5
Dim myWord as String
myWord = "Whatever I want"
An example of an object
would be a Range
, a Worksheet
, or a Workbook
. These have their own methods and properties.
Dim myRange as Range
Set myRange = Sheet1.Range("A1")
If you try to use the last line without Set
, VB will throw an error. Now that you have an object
declared you can access its properties and methods.
myString = myRange.Value
Can I please know which tutorial or book did you refer to understand this?
This answer doesn't really explain the "why"
VBA is very clever, it doesn't require you to tell it what the hell you're doing like a lot of languages. This however adds time. If you are using a whole host of different dimensions on all sorts of different varients then it adds up the time. If you tell VBA what to expect when it sees a variable then it doesn't have to work it out. Also if you tell VBA that a variable is an integer not a string, then it won't take up as much RAM. Although the latter point is probably not as valid in common small VBA projects it's still good coding practise.
is it okay to use
Set
withoutDim
ing the variable first?