excel - Creating VB array that is public, within class module

Question

Quick summary - Im working with some legacy code where I need to add a class with string arrays and tripped up by the inability to declare a Public string array.

More:

I have VB code with an excel sheet that has 200 lines of data on different individuals. Each individual's data will be remixed by the code and shunted into a Word template to produce a report on the individual. I need to add another piece of code as below:

I have created a class type MotivationBuckP and I want to create four objects of that class that contain string arrays of variable length. (there may be a way for me to code it with fixed length if this is easier)

The arrays begin empty and will be filled depending on the particular individual's data. I'm using arrays because although there is a fixed amount of string content (18 titles and 18 longer bits of text), each individual will have them distributed differently across the four objects (think of each individual as a 18-gallon barrel poured into four buckets)

I understand that in the Class Module I need to declare all the variables as Public, eg:

Public MotivID As Integer
Public MotivQuant As Integer
Public strMotivatorTitle() As String
Public strMotivatorDescriptor() As String

But in response to the presence of the last 2 variables, running gives me the error:

Compile error: 
Constants, fixed-level strings, arrays, user-defined types and Declare statements are not allowed as Public members of object modules

Due to the constraints of existing code I need to create and use the strMotivatorTitle and strMotivatorDescriptor variables in multiple modules. But I understand I can't do this unless it is declared publically (and, I presume, in the class module). So this is where I hit a wall.

Any help much appreciated. I haven't used VB heavily since my PhD so I'm probably being tripped up by something obvious...

Answer 1

A work around is to declare them as Variant and ReDim as string array when you initialiose them

Public strMotivatorTitle As Variant 
Public strMotivatorDescriptor As Variant

Initialise as array, for example

Private Sub Class_Initialize()
    ReDim strMotivatorTitle(0 To 9)

    strMotivatorTitle(0) = "Foo"
    strMotivatorTitle(1) = "Bar"
    ' etc
End Sub

Another way, declare your string arrays as Private and use `Property Get' to access them

Private pMotivatorTitle() As String
Private pMotivatorDescriptor() As String

Property Get strMotivatorTitle() As String()
    strMotivatorTitle = pMotivatorTitle
End Property

Property Get strMotivatorDescriptor() As String()
    strMotivatorDescriptor= pMotivatorDescriptor 
End Property