3

I'm having a bit of a problem working with arrays in VBA, where the same would be trivial in (almost) any other language:

Public Function getArray() As MyType()
    'do a lot of work which returns an array of an unknown length'
    'this is O(N^2) or something equally intensive so I only want to call this once'
End Function

Public Sub doSomething()
    Dim myArray() As MyType
    Set myArray = getArray() 'FAILS with "Cannot assign to array error"'
End Sub

I think it might be that I need to define the length of the array in advance, or ReDim a dynamic array. But I don't know the length of the returned array ahead of time, and I'd like to avoid calling the function twice:

Public Sub doSomething()
    Dim myArray(0 To UBound(getArray()) As MyType  'not entirely sure if this would work, but it involves calling getArray twice which I'd like to avoid
    Set myArray = getArray()
End Sub

In C# or Java the equivalent would be:

public MyType[] getArray(){
    //do some work and return an array of an unknown length
}

public void doSomething(){
    MyType[] myArray;
    myArray = getArray();  //one line and I don't need to define the length of array beforehand
}
Improve this question

4 Answers 4

Reset to default
2

When assigning arrays of custom objects in vba you need to pass them around as variants I've included a full working sample.

Class Module named MyType:

Public Once As Integer
Public Twice As Integer
Public Thrice As Integer

Code in standard module:

Public Function getArray() As MyType()
Dim i As Integer, arr() As MyType
    'do a lot of work which returns an array of an unknown length'
    'this is O(N^2) or something equally intensive so I only want to call this once'
    For i = 0 To Int(Rnd() * 6) + 1
        ReDim Preserve arr(i)
        Set arr(i) = New MyType
        arr(i).Once = i
        arr(i).Twice = i * 2
        arr(i).Thrice = i * 3
    Next i
    getArray = arr
    MsgBox "Long process complete"
End Function

Public Sub doSomething()
Static myArray() As MyType
Dim i As Integer
    If UBound(myArray) = -1 Then
        myArray = getArray()
    End If
    For i = LBound(myArray) To UBound(myArray)
        Debug.Print myArray(i).Once & vbTab & _
                    myArray(i).Twice & vbTab & _
                    myArray(i).Thrice
    Next i
End Sub

Public Sub Test()
Dim i As Integer
    For i = 1 To 3
        Debug.Print "Run Number " & i & vbCrLf & String(10, "-")
        doSomething
        Debug.Print
    Next i
End Sub

The first time you run doSomething an array of random length will be generated and you will see a message box that says "Long process complete". Subsequent calls to doSomething will re-use the array created the first time.

If you copy this code and just run the Test sub it will call doSomething three times. You will see the message box once and the output of doSomething in the immediate window three times.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

2

Well, you could pass the array as a reference to the function like this:

Public Sub MyFunc(ByRef arr() As MyType)
  ...
End Sub


Dim myArr() as MyType
MyFunc myArr

Inside the function you can ReDim your array as wanted.

Improve this answer

Comments

1

It is indeed possible to return an array from a function in VBA. According to MSDN:

[Y]ou can also call a procedure that returns an array and assign that to another array. [ . . . ] Note that to return an array from a procedure, you simply assign the array to the name of the procedure.

So you just need to modify your existing code by removing Set from the assignment statement:

Public Function getArray() As MyType()
    'do a lot of work which returns an array of an unknown length'
    'this is O(N^2) or something equally intensive so I only want to call this once'
End Function

Public Sub doSomething()
    Dim myArray() As MyType
    myArray = getArray
End Sub
Improve this answer

5 Comments

@mwolfe: I haven't personally tried it because I don't have access to Office right now, but it's exactly the same as that shown on MSDN (see the link included in my answer). I have little reason to assume that it won't work.
Mea culpa. I went back and rechecked my own testing. It appears that what you've got will indeed work. I've also updated my own answer accordingly.
@mwolfe02, I wrote up a quick answer that looked very much like this, tested it, and then found this was already here. So I upvoted it as a correct solution and didn't bother to post mine. But yes it works.
@jtolle: I've since come to the same conclusion (see my comment immediately above yours). I would delete my original comment if not for the fact that then all the follow-ups would no longer make sense.
@mwolfe: You were initially correct that arrays can be passed as a Variant instead of a specific type, but as you've since figured out, that's unrelated to the fact that you've created an object of a custom class. Thanks for testing the code and verifying that it does indeed work.
1

I think you just need to get rid of the set in Set myArray = getArray()

This behaves properly:

Option Explicit

Public Function getArray() As Integer()
    Dim test(1 To 5) As Integer
    test(1) = 2
    test(2) = 4
    getArray = test
End Function

Public Sub doSomething()
    Dim myArray() As Integer
    myArray = getArray()
    Debug.Print (myArray(2))
End Sub
Improve this answer

7 Comments

This gives the "Object variable or With block variable not set" error, so I think the Set is required
This could be when your array is empty.
It works for me, without the set that is. What is myType? I have just used integers.
myType is a class I've defined in a class module.
And that's why this solution won't work for this particular case. Working with custom objects complicates things in VBA.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.