Copy an array reference in VBA

by

Yes, you can, if both variables are of type Variant.

Here’s why: The Variant type is itself a wrapper. The actual bit content of a Variant is 16 bytes. The first byte indicates the actual data type currently stored. The value corresponds exactly the VbVarType enum. I.e if the Variant is currently holding a Long value, the first byte will be 0x03, the value of vbLong. The second byte contains some bit flags. For exampe, if the variant contains an array, the bit at 0x20 in this byte will be set.

The use of the remaining 14 bytes depends on the data type being stored. For any array type, it contains the address of the array.

That means if you directly overwrite the value of one variant using RtlMoveMemory you have in effect overwritten the reference to an array. This does in fact work!

There’s one caveat: When an array variable goes out of scope, the VB runtime will reclaim the memory that the actual array elements contained. When you have manually duplicated an array reference via the Variant CopyMemory technique I’ve just described, the result is that the runtime will try to reclaim that same memory twice when both variants go out of scope, and the program will crash. To avoid this, you need to manually “erase” all but one of the references by overwriting the variant again, such as with 0s, before the variables go out of scope.

Example 1: This works, but will crash once both variables go out of scope (when the sub exits)

Private Declare PtrSafe Sub CopyMemory Lib "kernel32" _
    Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As Long)

Sub CopyArrayRef_Bad()
    Dim v1 As Variant, v2 As Variant
    v1 = Array(1, 2, 3)
    CopyMemory v2, v1, 16

    ' Proof:
    v2(1) = "Hello"
    Debug.Print Join(v1, ", ")

    ' ... and now the program will crash
End Sub

Example 2: With careful cleanup, you can get away with it!

Private Declare PtrSafe Sub CopyMemory Lib "kernel32" _
    Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As Long)

Private Declare PtrSafe Sub FillMemory Lib "kernel32" _
    Alias "RtlFillMemory" (Destination As Any, ByVal Length As Long, ByVal Fill As Byte)

Sub CopyArrayRef_Good()
    Dim v1 As Variant, v2 As Variant
    v1 = Array(1, 2, 3)
    CopyMemory v2, v1, 16

    ' Proof:
    v2(1) = "Hello"
    Debug.Print Join(v1, ", ")

    ' Clean up:
    FillMemory v2, 16, 0

    ' All good!
End Sub

More Related Contents:

  1. Partial Arrays by reference
  2. Does the bitness of the OS ever matter, or is it just the application I need to worry about?
  3. How to copy cells that contain certain text to another worksheet [closed]
  4. What are the differences between a pointer variable and a reference variable in C++?
  5. Loop Through All Subfolders Using VBA [duplicate]
  6. Combine rows / concatenate rows
  7. What is the difference between Dim, Global, Public, and Private as Modular Field Access Modifiers?
  8. VBA Runtime Error 1004 “Application-defined or Object-defined error” when Selecting Range
  9. How do I base64 encode a string efficiently using Excel VBA?
  10. Assign event handlers to controls on user form created dynamically in VBA
  11. How to move each emails from inbox to a sub-folder [duplicate]
  12. How to “flatten” or “collapse” a 2D Excel table into 1D?
  13. error: invalid initialization of non-const reference of type ‘int&’ from an rvalue of type ‘int’
  14. Automatically generating handling of issues
  15. What’s the difference between Dim As New vs Dim / Set
  16. VBA: Run time error ’91’?
  17. VBA – How the colon `:` works in VBA code with condition
  18. How to create a separate CSV file from VBA?
  19. VBA: How to change the value of another cell via a function?
  20. How can I generate GUIDs in Excel?
  21. Range.Find not making a difference between January and November (February and December) in VBA Excel
  22. Excel Select vs Activate
  23. Fast compare method of 2 columns [closed]
  24. How can an Excel Add-In respond to events in any worksheet?
  25. Run-time error ’91’ (Object variable or With block variable not set)
  26. Calling regasm without administrative rights for COM interop in Excel VBA
  27. Why is a c++ reference considered safer than a pointer?
  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. Pass by pointer & Pass by reference [duplicate]

Leave a Comment