Difference in type between using and not using Set keyword

by

This is why. When you say this:

Dim startCell, iCell As Range

you think you’ve done this:

Dim startCell As Range, iCell As Range

but what you’ve really done is this:

Dim startCell 'As Variant, by default
Dim iCell As Range

This is a classic VBA mistake. Most VBA programmers have made it, and that’s why most VBA programmers fall back on declaring only one variable per Dim statement (i.e. one per line). Otherwise it’s way too easy to make that mistake, and difficult to spot it afterwards.

So with Dim startCell you’ve implicitly declared your variable as Variant type (equivalent to Dim startCell As Variant).

When you then say this:

Set startCell = Cells(iCell.Row + 1, iCell.Column)

the Variant acquires the type of the thing on the right hand side of the reference assignment (Range). However, when you say this:

startCell = Cells(iCell.Row + 1, iCell.Column)

without the Set keyword, you’re not assigning a reference, but a value to the variable startCell, which now acquires the type of the value on the right hand side. What is that type? Well, the default property of a Range object is Value, so you’re going to get the type of Cells(iCell.Row + 1, iCell.Column).Value. If that cell contains a string, then you’ll get a string.

More Related Contents:

  1. Loop through files in a folder using VBA?
  2. Wait for Shell to finish, then format cells – synchronously execute a command
  3. How to compare two entire rows in a sheet
  4. how to detect whether VBA excel found something?
  5. Excel tab sheet names vs. Visual Basic sheet names
  6. Excel VBA “Method ‘Document’ of object ‘IWebBrowser2’ failed”
  7. Determine if cell contains data validation
  8. vba listbox multicolumn add [duplicate]
  9. Combine multiple Excel workbooks into a single workbook
  10. Excel Macro, inserting internationally valid formula during run-time
  11. Getting the headings from a Word document
  12. VBA – Convert string to UNICODE
  13. How to break long string to multiple lines
  14. How to loop through all controls in a form, including controls in a subform – Access 2007
  15. How to copy a chart from Excel to PowerPoint?
  16. vba paste not working
  17. FileDialog doesn’t work
  18. How to refer to Excel objects in Access VBA?
  19. mac excel vba loop : from list & then export as pdf
  20. Can Advanced Filter criteria be in the VBA rather than a range?
  21. How to add an embedded image to an HTML message in Outlook 2010
  22. Speed up multiple replacement
  23. Excel Useform: How to hide application but have icon in the taskbar
  24. Reading Web Pages using Excel VBA
  25. Excel VBA: auto click and open file from website
  26. How can I automatically populate the VBA Editor with line numbers?
  27. Calling regasm without administrative rights for COM interop in Excel VBA
  28. Is there a way for DIR(path) in VBA to handle strings longer than 260?
  29. How do I activate a specific workbook and a specific sheet?
  30. Copy an entire worksheet to a new worksheet in Excel 2010

Leave a Comment