VBA Variable substitution

[zaphod3000]

I have a module which does some housekeeping on a number of tables.

At the moment, my code looks like this (this is just part of it)



For Counter = 1 To NoOfShipFlds
addFieldDescription ShipTable, ShipTableFlds(Counter, 1), ShipTableFlds(Counter, 2)
Next

For Counter = 1 To NoOfHBLsFlds
addFieldDescription HBLsTable, HBLsTableFlds(Counter, 1), HBLsTableFlds(Counter, 2)
Next

Whilst this works, it not particuarly elgant, so I was wondering if something like this was possible

Dim MyArray(2,2)
MyArray(1,1)="ShipTable"
MyArray(1,2)="ShipTableFlds"
MyArray(2,1)="HBLsTable"
MyArray(2,2)="HBLsTableFlds"

For oCounter = 1 to 2
for iCounter = 1 to 2
addFieldDescription MyArray(oCounter,iCounter), ShipTableFlds(oCounter, iCounter), ShipTableFlds(oCounter,iCounter)
Next
next

Where MyArray(x,y) would return a reference to a variable not a value.

In my foxPro days this used to be called Macro Substitution. Is there an equivalent in VBA?

 

[Remou]

You do not show the code for the AddFieldDescription procedure, so this is a little vague. Why not use existing arrays? You can add a description to tables you wish to change to limit the tables updated, or you can change all tables. If you wish to change all tables, you will need to exclude system tables, where the name starts with "MSys". For example:

Dim tdf As DAO.TableDef

'If the table does not have a Description
'Property, an error 3270 is generated
On Error Resume Next

For Each tdf In CurrentDb.TableDefs
    If tdf.Properties("Description") = "Hello" Then
        For Each fld In tdf.Fields
            'Just guessing
            addFieldDescription tdf.Name, fld.Name
        Next
    End If
Next

However, if I have missed your point, I see no reason why you should not use an array as illustrated. You may wish to note that VBA arays start at 0, so 2 (above) gives 0,1,2.

 

[danvlas]

Not sure if you can reference a variable name by getting it from a table/array/string...

However, you can access any object by using its name:

rst.Fields(MyArray(oCounter,iCounter))
Forms(MyArray(oCounter,iCounter))
Me.Controls(MyArray(oCounter,iCounter))

Generally:

Collection("NameOfObject")

If your array contains integers, it will still work, but with each object's index within the collection.


HTH



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html