How can I assign a Variant to a Variant in VBA?

by

In VBA, the only way to assign a Variant to a variable where you don’t know if it is an object or a primitive, is by passing it as a parameter.

If you cannot refactor your code so that the v is passed as a parameter to a Sub, Function or Let Property (despite the Let this also works on objects), you could always declare v in module scope and have a dedicated Sub solely for the purpose of save-assigning that variable:

Private v As Variant

Private Sub SetV(ByVal var As Variant)
    If IsObject(var) Then
        Set v = var
    Else
        v = var
    End If
End Sub

with somewhere else calling SetV SomeMethod().

Not pretty, but it’s the only way without calling SomeMethod() twice or touching its inner workings.

Edit

Ok, I mulled over this and I think I found a better solution that comes closer to what you had in mind:

Public Sub LetSet(ByRef variable As Variant, ByVal value As Variant)
    If IsObject(value) Then
        Set variable = value
    Else
        variable = value
    End If
End Sub

[…] I guess there just is no LetSet v = … statement in VBA

Now there is: LetSet v, SomeMethod()

You don’t have a return value that you need to Let or Set to a variable depending of its type, instead you pass the variable that should hold the return value as first parameter by reference so that the Sub can change its value.

More Related Contents:

  1. What does the $ symbol do in VBA?
  2. Loop through files in a folder using VBA?
  3. Wait for Shell to finish, then format cells – synchronously execute a command
  4. How to error handle 1004 Error with WorksheetFunction.VLookup?
  5. How to compare two entire rows in a sheet
  6. how to detect whether VBA excel found something?
  7. Excel tab sheet names vs. Visual Basic sheet names
  8. Excel VBA “Method ‘Document’ of object ‘IWebBrowser2’ failed”
  9. Determine if cell contains data validation
  10. vba listbox multicolumn add [duplicate]
  11. Fastest way to delete rows which cannot be grabbed with SpecialCells
  12. Combine multiple Excel workbooks into a single workbook
  13. Excel Macro, inserting internationally valid formula during run-time
  14. Sheet protection: UserInterFaceOnly gone
  15. VBScript – How to make program wait until process has finished?
  16. Procedure Too Large
  17. Add a “CC recipient” to Outlook 2010 VBA
  18. Copy filtered data to another sheet using VBA
  19. TextJoin UDF For Excel 2013
  20. Range() vs Cells() vs Range.Cells()
  21. Open an Excel file from SharePoint site
  22. Excel VBA to answer Internet Explorer 11 download prompt, in Windows 10?
  23. mac excel vba loop : from list & then export as pdf
  24. Can Advanced Filter criteria be in the VBA rather than a range?
  25. How to add an embedded image to an HTML message in Outlook 2010
  26. Speed up multiple replacement
  27. Excel Useform: How to hide application but have icon in the taskbar
  28. Reading Web Pages using Excel VBA
  29. Excel VBA: auto click and open file from website
  30. Unable to read data from a CSV using ADO due to the driver thinking I am working with integers/number and showing nulls instead of text

Leave a Comment