pass array of values to VBA class

Question

I created account class in VBA (ClsAccount), I want to assign Amount property which take an arry of values (for example a revenue ClsAccount can have amount 100 , 200, 300 each one for different year)

    Private AccAmount() As Variant

Property Let amount(amt() As Variant)
For Each i In amt
AccAmount(i) = amt(i)
Loop
End Property

Property Get amount() As Variant
amount() = AccAmount()
End Property

Private Sub Class_Initialize()

End Sub

Sub test()


Dim revenue As New ClsAccount
Dim arr(1) As Variant

arr(0) = 100
arr(1) = 200

revenue(0) = arr(0)
revenue(1) = arr(1)

MsgBox revenue(0)
MsgBox revenue(1)

End Sub

I am getting this error:

`amount() = AccAmount()` must be `amount = AccAmount()` because you return the value. — Paul Ogilvie, May 23 '21 at 14:19

score 1 · Answer 1 · answered May 23 '21 at 14:16

1

Modify your code as following and it will work, you may compare the different, seem like we cannot use array as parameter directly based on documentation, hope it is helping you :)

'Class module name : ClsAccount
Dim amt() As Double
Public Property Get amount(i As Long) As Double

amount = amt(i)

End Property

Public Property Let amount(i As Long, value As Double)
amt(i) = value
End Property

Private Sub Class_Initialize()
    ReDim amt(0 To 1)
End Sub

Sub test()

Dim revenue As New ClsAccount
Set revenue = New ClsAccount

revenue.amount(1) = 100
revenue.amount(0) = 200

MsgBox revenue.amount(1)
MsgBox revenue.amount(0)

End Sub

answered May 23 '21 at 14:16

Kin Siang

2,644
2
4
8

thank you very much it worked!, one follow up question, if I want to pass the array size as variable inPrivate Sub Class_Initialize() ReDim amt(0 To 1) End Subhow can I do that? – Nafi Khatib May 23 '21 at 16:03
I think the main purpose of Class_Initialize is trying to set a array property to a double variable. For class module function, I rarely use it, so if you are interest you can check online resource, check this https://stackoverflow.com/questions/5613564/vba-returning-array-from-property-get . As mentioned in the answer, collection itself is useful already, i use it sometime also. – Kin Siang May 24 '21 at 00:08
Actually your second answer address you for array parameter, you can give it a shot also – Kin Siang May 24 '21 at 05:55

score 0 · Accepted Answer · answered May 23 '21 at 19:43

The error message is because your Property is defined incorrectly. The Property Get is returning a Variant but the Property Let is taking a Variant array. These are not the same and thus the error message. Define everything in the Class as a Variant and the code becomes:

Option Explicit

Private AccAmount As Variant

Property Let amount(amt As Variant)
   AccAmount = amt
End Property

Property Get amount() As Variant
   amount = AccAmount
End Property

Then on the UI side:

Option Explicit

Sub test()
   Dim Revenue As New ClsAccount
   
   'you can use an array
   Dim arr(1) As Double
   arr(0) = 100
   arr(1) = 200
   Revenue.amount = arr
   
   MsgBox Revenue.amount(0)
   MsgBox Revenue.amount(1)
   
   'or you can use the array function
   Revenue.amount = Array(300, 400)
   
   MsgBox Revenue.amount(0)
   MsgBox Revenue.amount(1)
End Sub

pass array of values to VBA class

2 Answers2