Initializing Object with Arrays VBA

Question

I'm trying to create a class with arrays in it, and I'm having issues creating the class for it...

CLASS:

Private pST(0 To 2) As String

Public Property Get ST() As String
   ST() = pST()
End Property
Public Property Let ST(value() As String)  '<---- ERROR HERE
   pST() = value()
End Property

CODE RUN:

Sub test()

   Dim foo As cPurchaseOrder
   Set foo = New cPurchaseOrder

   foo.ST(0) = "test"

   Debug.Print foo.ST(0)

End Sub

THE ERROR:

Compile error:

Definitions of property procedures for the same property are inconsistent, or property procedure has an optional parameter, a ParamArray, or an invalid Set final parameter.

THE QUESTION:

How can I properly initialize a class with arrays as variables?

EDIT: in relation to Mat's Mug response

CLASS CHANGED:

Private pST As Variant

Public Property Get STContent(ByVal index As Long) As String
    STContent = pST(index)
End Property

Public Property Let STContent(ByVal index As Long, ByVal value As String)
    pST(index) = value
End Property

Private Sub Class_Initialize()
   ReDim pST(0 To 2)
End Sub

CODE RUN TO TEST:

Sub test()

   Dim foo As cPurchaseOrder
   Set foo = New cPurchaseOrder

   foo.STContent(0) = "test" '<--- Type mismatch here

   Debug.Print foo.STContent(0)

End Sub

You need to initialize the array in the Class_Initialize handler, e.g. ReDim pST(0 To 20), before you start assigning to it. If the backing field was a compile-time array, the error would have been "index out of bounds" — Mathieu Guindon
– Mathieu Guindon, Commented Jan 9, 2018 at 17:36
@Mat'sMug I've added the Class_Initialize and it works fine when I just assign a value to foo.STContent(0). However when I try to output the value, I'm not getting Variable not defined on Content = pST(index) in the Public Property Get STContent` — Maldred
– Maldred, Commented Jan 9, 2018 at 17:49
Sorry if I'm being a nascence, I'm still quite new to Class objects and learning :( — Maldred
– Maldred, Commented Jan 9, 2018 at 17:50
Put Option Explicit at the top of your module, and avoid copy-pasting code from random strangers on the Internet ;-) the assignment would be STContent = pST(index). — Mathieu Guindon
– Mathieu Guindon, Commented Jan 9, 2018 at 17:50

Mathieu Guindon · Accepted Answer · 2018-01-09 16:52:37Z

6

Your getter would need to return a String() array for the types to be consistent:

Public Property Get ST() As String()

However I wouldn't recommend exposing an array like this. First because assigning typed arrays is rather painful, second because the setter (Property Let) is actually cheating here:

Public Property Let ST([ByRef] value() As String)

Unless you specify ByVal explicitly, a parameter is always passed ByRef in VBA... except there's this quirk about Property Let - the RHS/value parameter is always passed ByVal at run-time.

And arrays can only ever be passed ByRef.

Therefore, a property that gets (or assigns, actually) a whole array doesn't make much sense.

A better way would be to encapsulate the array (I'd make it a Variant though), and expose its contents (not the array itself) through an indexed property:

Private internal As Variant 'String array

'...

Public Property Get Content(ByVal index As Long) As String
    Content = internal(index)
End Property

Public Property Let Content(ByVal index As Long, ByVal value As String)
    internal(index) = value
End Property

edited Jan 9, 2018 at 16:52

answered Jan 9, 2018 at 16:47

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

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

6 Comments

Maldred Over a year ago

I really like they way you approach this and the explanation is superb! I ran into another issue with this method however. I ran the code foo.Content(0) = "test" and I'm receiving the error "Type mismatch"

Maldred Over a year ago

Is there anything wrong with "cheating" the setter in your explanation here?

Mathieu Guindon Over a year ago

Not sure about that type mismatch issue, could be related to your backing field, did you make it a variant? Is it initialized? As for "cheating", IMO exposing an array setter is just asking for problems.. not to mention it defeats the purpose of a class/object, which is to encapsulate data.

Maldred Over a year ago

I've edited my question to relate to your answer and my new question in comments above

Mathieu Guindon Over a year ago

@Maldred unless you didn't show it, you're not initializing the array, it's just a Variant/Empty until you do that.

|

Rory · Accepted Answer · 2018-01-09 16:45:27Z

You have a lot of issues there.

First, your Property Get needs to return a String array. Second, your array needs to be dynamic, or you need to rewrite the whole thing so that you pass an index value to it, otherwise there is no way to indicate which value you are passing to the array. So, for example, using a dynamic array:

Private pST() As String

Public Property Get ST() As String()
   ST = pST
End Property
Public Property Let ST(value() As String)
   pST() = value()
End Property

and the calling code:

Sub test()

   Dim foo As cPurchaseOrder
   Set foo = New cPurchaseOrder
   Dim asData() As String
   ReDim asData(0)
   asData(0) = "test"

   foo.ST = asData

   Debug.Print foo.ST()(0)

End Sub

Unfortunately, I couldn't be sure form the original what the intent was.

Vityata · Accepted Answer · 2018-01-09 16:44:56Z

It is getting late here but give it a try. In the module:

Option Explicit

Sub Test()

   Dim foo As cPurchaseOrder
   Set foo = New cPurchaseOrder
   foo.AddValueToSt "test", 1
   Debug.Print foo.ST(1)

End Sub

In the Class:

Option Explicit

Private pST

Public Property Get ST() As Variant
   ST = pST
End Property

Public Property Let ST(value As Variant)
   pST = value
End Property

Public Function AddValueToSt(value As Variant, position As Long)
    pST(position) = value
End Function

Private Sub Class_Initialize()
    ReDim pST(2)
End Sub

This is my way to use the Factory Method Pattern. When I say "my way", for me this pattern is translated to "Whenever some OOP requires more than 5 minutes of thinking simply add a function."

Collectives™ on Stack Overflow

Initializing Object with Arrays VBA

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related