Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

VBA - Returning array from Property Get

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

'Class1 class module
Private 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
like image 413
ThomasMcLeod Avatar asked Apr 10 '11 17:04

ThomasMcLeod


People also ask

How do you return an array from a value in VBA?

A function in a normal module (but not a Class module) can return an array by putting () after the data type. Note that what is returned is actually a copy of the array inside the function, not a reference. So if the function returns the contents of a Static array its data can't be changed by the calling procedure.

How do I create a dynamic array in VBA?

Create a Dynamic Array in VBAFirst, declare an array with its name. After that, the elements count left the parentheses empty. Now, use the ReDim statement. In the end, specify the count of elements you want to add to the array.

How do I pass an array to a function in Excel VBA?

If you declare an array variable, then set it using Array() then pass the variable into your function, VBA will be happy.

What is ParamArray in VBA?

A parameter array can be used to pass an array of arguments to a procedure. You don't have to know the number of elements in the array when you define the procedure. You use the ParamArray keyword to denote a parameter array.


1 Answers

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  
like image 177
Clayton S Avatar answered Sep 23 '22 13:09

Clayton S