What is the difference between dim and set in vba

Question

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

User · Answer

Dim simply declares the value and the type   Set assigns a value to the variable

User · Answer

According to VBA help on SET statement it sets a reference to an object so if you change a property the actual object will also changes   Dim newObj as Object Set var1 Object1 same type as Object  Set var2 Object1 same type as Object  Set var3 Object1 same type as Object  Set var4 Object1 same type as Object  Var1 property1 NewPropertyValue   the other Vars properties also changes so   Var1 property1 Var2 property1 Var3 property1 Var4 property1 Object1 Property1 NewpropertyValue    actualy all vars are the same

User · Answer

Dim is short for Dimension and is used in VBA and VB6 to declare local variables   Set on the other hand  has nothing to do with variable declarations  The Set keyword is used to assign an object variable to a new object   Hope that clarifies the difference for you

User · Answer

Dim declares the variable    Dim r As Range  Set sets the variable to an object reference    Set r   Range  A1        However  I don t think this is what you re really asking       Sometimes I use       Dim r as Range     r   Range  A1      This will never work  Without Set you will receive runtime error  91 Object variable or With block variable not set  This is because you must use Set to assign a variables value to an object reference  Then the code above will work    I think the code below illustrates what you re really asking about  Let s suppose we don t declare a type and let r be a Variant type instead   Public Sub test       Dim r     debug print TypeName r       Set r   Range  A1       debug print TypeName r       r   Range  A1       debug print TypeName r  End Sub   So  let s break down what happens here    r is declared as a Variant   Dim r    TypeName r  returns  Empty   which is the value for an uninitialized variant  r is set to the Range containing cell  A1   Set r   Range  A1     TypeName r  returns  Range   r is set to the value of the default property of Range  A1     r   Range  A1     TypeName r  returns  String     In this case  the default property of a Range is  Value  so the following two lines of code are equivalent   r   Range  A1   r   Range  A1   Value   For more about default object properties  please see Chip Pearson s  Default Member of a Class      As for your Set example      Other times I use  Set r   Range  A1      This wouldn t work without first declaring that r is a Range or Variant object    using the Dim statement - unless you don t have Option Explicit enabled  which you should  Always  Otherwise  you re using identifiers that you haven t declared and they are all implicitly declared as  Variants

User · Answer

Dim  you are defining a variable  here  r is a variable of type Range   Set  you are setting the property  here  set the value of r to Range  A1   - this is not a type  but a value     You have to use set with objects  if r were a simple type  e g  int  string   then you would just write   Dim r As Integer r 5

User · Answer

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

User · Answer

If a variable is defined as an object e g  Dim myfldr As Folder  it is assigned a value by using the keyword   Set

[excel] What is the difference between dim and set in vba

Examples related to excel

Examples related to vba

Examples related to vb6