What is the difference between dim and set in vba

by

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

More Related Contents:

  1. SumIf using Excel VBA
  2. How can I call a macro which contains a loop but only run one iteration of that loop?
  3. Test or check if sheet exists
  4. Copy from one workbook and paste into another
  5. How to copy Outlook mail message into excel using VBA or Macros
  6. For Loop not fully cycling in excel VBA [duplicate]
  7. Does the VBA “And” operator evaluate the second argument when the first is false?
  8. Split cell values into multiple rows and keep other data
  9. Save each sheet in a workbook to separate CSV files
  10. Is VBA an OOP language, and does it support polymorphism?
  11. Export each sheet to a separate csv file
  12. Detect if range is empty
  13. Checking if a worksheet-based checkbox is checked
  14. Faster way to remove ‘extra’ spaces (more than 1) from a large range of cells using VBA for Excel
  15. Uncheck all checkboxes across entire workbook via CommandButton
  16. VBA workbooks.Close without being prompted to if the user wants to save?
  17. Extract Data from Word Document to an Excel SpreadSheet
  18. VBA equivalent to Excel’s mod function
  19. Excel macro – paste only non empty cells from one sheet to another
  20. Pointers needed for speeding up nested loop macro in VBA
  21. ‘1004’: “The sort reference is not valid.”
  22. Select entire column in table using Excel VBA
  23. Create hyperlink to another sheet
  24. Reducing WithEvent declarations and subs with VBA and ActiveX
  25. Convert Early Binding VBA to Late Binding VBA : Excel to Outlook Contacts
  26. Delete all data rows from an Excel table (apart from the first)
  27. Excel Hide/Show all tabs on Ribbon except custom tab
  28. Macros Not Showing Up in Macro Table
  29. ReDim Preserve “Subscript Out of Range”
  30. Excel VBA Run-time Error ‘32809’ – Trying to Understand it

Leave a Comment