Arrays of Strings in Excel VBA

Question

I am working with XML data files, and these have a lot of tables of data. There are many table-types, and each different table-type has a fixed number of fields.

I would like a generic table class. This class would have a string containing the name of its table type, like "condition" or "encounter". I would like to have this class have a pointer to a Public array of strings that has been initialized to hold all of the expected fieldnames of this table type.

For example, I will have:

The public variables:

Public cond_fnames() As String
Public enc_fnames() As String

be initialized as:

cond_fnames = Split("id,strName,strDesc,aFieldNames,aModifiers", ",")
enc_fnames = Split("id,strName,strDesc,strImg,nTreasureID", ",")

(the above examples are truncated, and are actually much longer)

I don't want each table-class-object to have to have the whole array of strings as I expect to have to create a lot of table-class-objects.

How can I have a class object hold a pointer to the public array of strings?

(the reason I want this array of strings is that I am using this as an index mechanism for storing the associated table value. XML table fields have a fieldname and a value. So in the above "conditions" table, the value for the fieldname "strDesc" would be stored in the 3rd collection position. Perhaps there is a better way to do this indexing. I had thought of using enums, but don't know how to have the enumerated values be private to each class (different XML tables can have the same value field-names, at different positions within the table) nor do I want each table-class-object to have to have storage for the long list of field-names.)

Answer 1

I ended up using a collection to hold all of the string's, rather than an array. That way I can have the TableType class hold the collection of table fieldnames.

To answer the question in the comment, the 'table' nature of the data is due to the XML file data format that I am dealing with. It consists of many many 'tables' in the format:

       <table name="conditions">
        <column name="id">13</column>
        <column name="strName">Heat exhaustion</column>
        <column name="strDesc">&lt;us&gt; is suffering from heat exhaustion.</column>
        <column name="aFieldNames">fWaterConsumptionRate,fSleepQuality,m_fMoraleHidden,m_fDefense,MinSafeTemp,MaxSafeTemp,fPassiveRewarmPerHour</column>
        <column name="aModifiers">0.5,-0.75,-0.1,-0.02,15,15,-1</column>
  .
  .
       </table>

That is a "conditions" table entry in the XML file. There are about 25 different table-types, each with their own fixed set of fieldnames, and many of the fieldnames are common between the table-types.

Each "conditions" table entry will all have the same fieldnames, but they could be in any order. I am populating Userforms that contain textboxes for each of these table fields for each of these table-types, so I have a LOT of textboxes and I need to populate them in a known, sequential fashion, as I do not want to have to know the 'name' or handle to each text box.

To do this, I just have collections. The collection of fieldnames (otherwise known as the 'column name' from the XML file table entry) is used to find the index into the collection of fieldvalues, and this index is also used to write the fieldvalue into its corresponding collection of textboxes.

Anyways, since classes can't have public arrays, I ended up using a collection. Thanks for your time in trying to understand the question.