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...

In a class, you might define desired array to be used outside of the class as Property, or as returned value of a method. Outside the class, you make a new MyClass, and use them easily as class member. — jacouh
– jacouh, Commented Nov 9, 2013 at 21:08

chris neilsen · Accepted Answer · 2013-11-09 22:00:12Z

7

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

edited Nov 9, 2013 at 22:00

answered Nov 9, 2013 at 20:58

chris neilsen

53.3k10 gold badges91 silver badges127 bronze badges

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

1 Comment

RomnieEE Over a year ago

I have found that the work around does not work. myclass.mypublicvariantholdingarray(i) = 3.1415--simply does not take. You can fool the compiler. But VBA sniffs you out and stops you.

Collectives™ on Stack Overflow

Creating VB array that is public, within class module

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related