CODEWITHSUNDEEP
vba
Nafi Khatib
Nafi Khatib

Reputation: 17

pass array of values to VBA class

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:

enter image description here

Upvotes: 1

Views: 486

Answers (2)

Brian M Stafford
Brian M Stafford

Reputation: 8868

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

Upvotes: 0

Kin Siang
Kin Siang

Reputation: 2699

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

Upvotes: 1

Related Questions