Geek Answers Handbook

Tuple Style Object in VBA

I use VBA in an Access application, and I would like to have an n-tuple object that contains values ​​of different data types. Then I would like to get a collection of these objects.

If I did this in javascript, it would look like this:

var myStructure = {
   name: "blah"
   age: 33
   moreStuff : "test"
};

And then I need the myStructure collection. How can I achieve this in VBA?

+5
source share
4 answers

You can define your own variable type using code, for example:

Public T_Person as Type
    name as string
    dateOfBirth as date
    ....
    email() as string (*)
    ....
End type

You can then declare the type T_person in your code with:

Dim currentPerson as T_Person

currentPerson.name = myName
currentPerson.dateOfBirth = myDate
currentPerson.email(1) = myFirstEmail
....

(*) I don’t remember the details for declaring arrays in such circumstances. When defining a variable, you may need to determine the length of the array. Please check the help.

, , , "". (, name, dateOfBirth ..), ( ). . :

Dim myPerson as Person

set myPerson = New Person

myPerson.name = myName
myPerson.dateOfBirth = myDate

if myPerson.age > 18 then  (*)
    'the guy is an adult'
    myPerson.createAccount
Else
    'the guy is not ...'
Endif

(*) - , , dateOfBirth . , google " VBA", VBA.

, "" (, ), (, "" ) "", VBA. : ( ) ( ). :

Public myPersons as Persons    'at the app level, 1 main collection'

myPersons.add ....              'adding a new person to your collection'

myPersons.count ...             'counting number of persons in myPersons'

, Google " VBA" VBA. , " " VBA.

"" , , "". , . !

PS: , myPersons myPerson Person. , "PersonCollection" "Person", "Individual"

+9

Variant . , .

Const Name as Integer = 0
Const Age as Integer = 1
Const moreStuff as Integer = 2

Dim myStructure as Variant
Redim myStructure(0 to 2, 0 to n)

myStructure(Name, 0) = "Blah"
myStructure(Age, 0) = 33
myStructure(moreStuff, 0) = "test"

. VBA , , , .

, , , .

+4

Collection .

Dim col As New Collection
col.Add("blah", "name")
col.Add(33, "age")
col.Add("test", "moreStuff")

This gives you maximum flexibility. However, this is not very efficient, and the Collection class is not able to get a list of keys.

0
source

You can also view the Scripting.Dictionary object, although I read that it is considered unsafe. It is more dynamic than using type definitions, and unlike Collection, it gives you access to keys.

Here is an example:

Public Function TestDictionary()
    Dim d As New Scripting.Dictionary  'need reference to Microsoft Scripting Runtime
    'Dim d As Object
    'Set d = CreateObject("Scripting.Dictionary")
    Dim k As Variant

    d.Add "Banana", "Yellow"
    d.Add "Apple", "Red"
    d.Add "Grape", "Green"

    For Each k In d.Keys
        Debug.Print k; ": "; d.Item(k)
    Next k
End Function
0
source

More articles:


All Articles