Search code examples
excelvbavb6

What is the difference between dim and set in vba

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?

Solution

  • 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