When one should use Set [e.g for SpecialCells return value]?

by 
Range a = Selection.SpecialCells(xlCellTypeConstants, 23)

This is not valid VBA, regardless of data type. You don’t declare variable type in front of variable name, as you would do in C#, and you don’t initialize variable at the point of declaration, as you would do in VB.NET.

You can do:

Dim a As Range
Set a = Selection.SpecialCells(xlCellTypeConstants, 23)

This will save a reference to the range into a.

You can also do:

Dim a As Variant
a = Selection.SpecialCells(xlCellTypeConstants, 23)

This will save in a a 2D array of values of cells in the range.

Returns a Range object that represents all the cells that match the specified type and value.

But it actually returns a byRef object and that is why I have to use Set.

There are no byval objects in VBA. All objects are byref, and when you want to copy a reference to an object, you always use Set. The reason why you need Set is default properties. Each object can have a default property that is requested when only object name is provided. This creates ambiguity, so you need to say Set when you need to manipulate the object reference itselt, and omit Set when you want the default property of an object. The default property of Range is Value.

More Related Contents:

  1. VBA – How to grab the cell below if a cell has text?
  2. Getting ScriptControl to work with Excel 2010 x64
  3. Pop up the Excel Statusbar?
  4. Get list of Excel files in a folder using VBA [duplicate]
  5. Should I turn .CutCopyMode back on before exiting my sub procedure?
  6. Determine if cell contains data validation
  7. Sheet protection: UserInterFaceOnly gone
  8. VBScript – How to make program wait until process has finished?
  9. How to highlight a cell using the hex color value within the cell?
  10. Removing special characters VBA Excel
  11. Open an Excel file from SharePoint site
  12. VBA recognizing workbook by partial name
  13. Excel VBA wildcard search
  14. How do I loop an excel 2010 table by using his name & column reference?
  15. Excel VBA Project has generated multiple Workbook objects
  16. Copy an entire worksheet to a new worksheet in Excel 2010
  17. Loop through files in a folder using VBA?
  18. filter out multiple criteria using excel vba
  19. Run same excel macro on multiple excel files
  20. how to detect whether VBA excel found something?
  21. VBA : save a file with UTF-8 without BOM
  22. Is there a faster CountIF
  23. Excel VBA How to detect if something was pasted in a Worksheet
  24. Getting error Procedure too large in VBA Macros (Excel)
  25. How to do late binding in VBA?
  26. How to break long string to multiple lines
  27. Can Advanced Filter criteria be in the VBA rather than a range?
  28. In Excel VBA, what is the way to check if the web page is fully loaded?
  29. Excel Useform: How to hide application but have icon in the taskbar
  30. Reading Web Pages using Excel VBA

Leave a Comment