[ACCEPTED]-VBA - Returning array from Property Get-byref

You don't have a let property. Also, the 12 get property is returning the entire array, rather 11 than just the element in question. Change 10 the return type of Property Get from Double() to 9 just plain Double. Add Property Let. Note 8 that it takes two inputs, but only one is 7 passed to it. The last variable (MyValue, in 6 this case) is assumed to get it's value 5 from whatever is after the = sign. Put 4 a break point somewhere early in Test1() and 3 see how the values are affected in the Locals 2 window. Compare the variables created by 1 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 7 unless they are returned through a ByRef parameter. Furthermore, whenever 6 you use = to assign an array to a variable, you've 5 made a new copy of the array, even if you're 4 assigning it to a ByRef argument inside 3 of a procedure, so you're pretty much out 2 of luck trying to make this work.

Some alternative 1 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 14 this using a Collection and a static Property without the need to use a class:

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

The following 10 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 9 into an array or implementing it as an array 8 is straight forward, but collections seem 7 to be more useful to me, with the disadvantage 6 that their elements are not implicitely 5 typed/(compile-time-)type checked.
So this 4 would e.g. turn it into an (read-only) array (with the in-mem-copy-disadvantage 3 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 2 transforming the example from Clayton Ss answer into a static, modifiable module property using some array it 1 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