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
– Paul Ogilvie, Commented May 23, 2021 at 14:19

Kin Siang · Accepted Answer · 2021-05-23 14:16:33Z

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, 2021 at 14:16

Kin Siang

2,6992 gold badges6 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Nafi Khatib Over a year ago

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?

Kin Siang Over a year ago

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 stackoverflow.com/questions/5613564/… . As mentioned in the answer, collection itself is useful already, i use it sometime also.

Kin Siang Over a year ago

Actually your second answer address you for array parameter, you can give it a shot also

Brian M Stafford · Accepted Answer · 2021-05-23 19:43:25Z

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

Collectives™ on Stack Overflow

pass array of values to VBA class

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related