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.

Polyphony

It might help to show us the code you've been trying. – Cajolery

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

Kirst

You're welcome!! Next time, please do show what you've tried. Even though it may not work or is causing errors, it is good form to show your efforts when asking for help :) – Kirst

Answer 2

By default, arrays are passed by reference. I've seen in a few places that passing "ByVal" throws an exception. However, I have been able to use ByVal without issue in O365.

Tridactyl

Yes it seems to say ByVal won't work here: learn.microsoft.com/en-us/office/vba/language/reference/… But I just tried creating an array: Dim strArray(0 To 3) As String . I ran it through a function ByVal, and it worked fine: the function took it in, and changed values within the scope of the function, and the original was unchanged. – Specter