Unable to assign the returned data from a function to an array. "Compile error: Can't assign to array"

Here's a minimum version that produces the error. I try to assign the returned data from the function TestFunction to the array TestArray.

Option Explicit
Sub Test()
    Dim TestArray(1 To 3) As Long
    TestArray = TestFunction()
End Sub


Function TestFunction() As Long()

    Dim Value1 As Long
    Dim Value2 As Long
    Dim Value3 As Long
    
    '-----------------
    '
    ' Some code that calculates the three values
    '
    '-----------------
    
    
    'Assign the three values to the returned array
    TestFunction(1) = Value1
    TestFunction(2) = Value2
    TestFunction(3) = Value3
    
End Function

I get the error "Compile error: Can't assign to array" at the line TestArray = TestFunction()

Anyone have an idea why?

Solution

You have two issues here:

The first (that is causing the compiler error "Can't assign to array") is that TestArray (in the calling Test sub) is a static array - an array that has already a fixed size and the memory is already assigned to it. In VBA, you can't assign a different array to to a static array.

So changing the definition to a dynamic array will do the trick:

Sub Test()
    Dim TestArray() As Long
    ' Also possible: 
    ' Dim TestArray As Variant
    ' But not:
    ' Dim TestArray() As Variant

    TestArray = TestFunction()
End Sub

Once this is fixed, you will face another compiler error inside your function, when you try to assign the first value. It reads "Function call on left-hand side of assignment must return an variant or object". This comes from the fact that for the VBA compiler, TestFunction(1) looks like a (recursive) function call to TestFunction, not as an assignment to an array.

The fix is to use a variable to create the array and assign it as function value at the end:

Function TestFunction() As Long()
    (...)
    Dim result(1 To 3) As Long
    'Assign the three values to the returned array
     result(1) = Value1
     result(2) = Value2
     result(3) = Value3
  
     TestFunction = result
End Function

Note that it's probably better to use the result array already in the calculation part (instead of the 3 Value-variables)