VBA Pass Array By Reference and Modify Contents

Question

VBA question. I'm using it to make Solidworks macros, but that's not important.

Can someone explain the syntax to pass an array (1-Dimensional of type Double, with length of three) to a subroutine or function so I can modify the contents. I know that they have to be passed by reference. I'm talking exact syntax, because everything I try I get type mismatch error on the subroutine/function call.

All I'm looking for is the correct Dim statement for the array of Doubles, initialize statement to make all array values zero, then the subroutine call statement and subroutine header that I would need. Please be specific if I need to use variant type or dynamic array, even when I already know the type and size of the array. Use a function or a sub, I don't care which.

My code works fine as of now, but I'm tired of avoiding function and subroutine calls when I'm using arrays. I've looked at countless documentation and similar questions on here, but I just cannot figure it out. Thanks a lot.

Answer 1

This is fairly trivial, using the ByRef keyword in the function signature will pass your array by reference rather than by value. This means that manipulations to that array will be preserved and bubble up to the calling scope. This is probably an oversimplification, but think of it this way:

• ByRef: you're passing a reference to the thing (array, Object, etc.) and any transformations to that thing will be apparent anywhere that thing exists.
• ByVal: you're passing the value representation of the thing. Essentially, you're passing a "copy" of it. You can manipulate this object, but it is not the same object from the calling scope, it's just a copy. So when the enclosing scope ends, you're still left with only the original thing.

Initialize your array as a numeric type and that will give you default 0 values.

Example as follows:

Option Explicit
Sub foo()
    Dim myArray(1 To 3) As Double  'The array is initialized with zero-values 

    Call bar(myArray)

    MsgBox myArray(3)

End Sub

Sub bar(ByRef dblArray() As Double)

    dblArray(1) = 123
    dblArray(2) = 345
    dblArray(3) = 33456

End Sub