VBA - Returning array from Property Get

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 modulePrivate v() As DoublePublic Property Get Vec(index As Long) As Double    Vec = v(index)End PropertyPublic Property Let Vec(index As Long, MyValue As Double)    v(index) = MyValueEnd PropertyPrivate Sub Class_Initialize()    ReDim v(0 To 3)End Sub' end class module'Begin moduleSub 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.6End 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/1915920Static 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 = cEnd 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/1915920Static 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 = aEnd 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/1915920Private v() As DoubleStatic 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 PropertyPublic Property Let Vec(index As Long, MyValue As Double)    v(index) = MyValueEnd Property