VBA - Returning array from Property Get

If arrays are returned by reference, why doesn't the following work:

'Class1 class module
Privat开发者_JS百科e v() As Double
Public Property Get Vec() As Double()
    Vec = v()
End Property
Private Sub Class_Initialize()
    ReDim v(0 To 3)
End Sub
' end class module

Sub Test1()
    Dim c As Class1
    Set c = New Class1
    Debug.Print c.Vec()(1) ' prints 0 as expected
    c.Vec()(1) = 5.6
    Debug.Print c.Vec()(1) ' still prints 0
End Sub

You don't have a let property. Also, the get property is returning the entire array, rather than just the element in question. Change the return type of Property Get from Double() to just plain Double. Add Property Let. Note that it takes two inputs, but only one is passed to it. The last variable (MyValue, in this case) is assumed to get it's value from whatever is after the = sign. Put a break point somewhere early in Test1() and see how the values are affected in the Locals window. Compare the variables created by the original code versus my code:

'Class1 class module
Private v() As Double
Public Property Get Vec(index As Long) As Double
    Vec = v(index)
End Property
Public Property Let Vec(index As Long, MyValue As Double)
    v(index) = MyValue
End Property
Private Sub Class_Initialize()
    ReDim v(0 To 3)
End Sub
' end class module

'Begin module
Sub Test1()
    Dim c As Class1
    Set c = New Class1
    Debug.Print c.Vec(1) ' prints 0 as expected
    c.Vec(1) = 5.6
    Debug.Print c.Vec(1) ' prints 5.6
End Sub
'End module  

In VBA, arrays are never returned by reference unless they are returned through a ByRef parameter. Furthermore, whenever you use = to assign an array to a variable, you've made a new copy of the array, even if you're assigning it to a ByRef argument inside of a procedure, so you're pretty much out of luck trying to make this work.

Some alternative are...

• Use a VBA.Collection instead of an array.
• Make your own class that encapsulates an array and exposes procedures for indirectly accessing and manipulating the internal array.

I want to suggest another nice way to do this using a Collection and a static Property without the need to use a class:

imagine you want to have the xlCVError enum as an array (or collection), e.g. to loop through it on errors and handle it based on the actual error.

The following is initialized once on access:

'from https://stackoverflow.com/a/56646199/1915920
Static Property Get XlCVErrorColl() As Collection
    Dim c As Collection  'will be already initalized after 1st access
                         'because of "Static Property" above!
    Set XlCVErrorColl = c
    If Not c Is Nothing Then Exit Property

   'initialize once:

    Set c = New Collection
    c.Add XlCVError.xlErrDiv0
    c.Add XlCVError.xlErrNA
    c.Add XlCVError.xlErrName
    c.Add XlCVError.xlErrNull
    c.Add XlCVError.xlErrNum
    c.Add XlCVError.xlErrRef
    c.Add XlCVError.xlErrValue
    Set XlCVErrorColl = c
End Property

Turning this into an array or implementing it as an array is straight forward, but collections seem to be more useful to me, with the disadvantage that their elements are not implicitely typed/(compile-time-)type checked.
So this would e.g. turn it into an (read-only) array (with the in-mem-copy-disadvantage mentioned in other answers/comments):

'from https://stackoverflow.com/a/56646199/1915920
Static Property Get XlCVErrorArr() As XlCVError()
   Dim a() As XlCVError
   XlCVErrorArr = a
   If UBound( a ) > 0 Then Exit Property

   'initialize once:

   Dim c As Collection:  Set c = XlCVErrorColl
   ReDim a(c.Count)
   Dim i As Integer:  For i = 1 To c.Count 
       a(i) = c(i)
   Next i
   XlCVErrorArr = a
End Function

So transforming the example from Clayton Ss answer into a static, modifiable module property using some array it would be:

'module (no class required)
'from https://stackoverflow.com/a/56646199/1915920

Private v() As Double

Static Property Get Vec(index As Long) As Double
    If UBound(v) < 3 Then  'initialize once:
      ReDim v(0 To 3)  'one could initialize it with anyting after here too
    end if

    Vec = v(index)
End Property

Public Property Let Vec(index As Long, MyValue As Double)
    v(index) = MyValue
End Property