VBA - Use multidimensional array instead of two separate ones?

Question

Lots of stupid questions lately, but I would appreciate some input on this one. I've got a string that comes from an INI-file. It looks like Firstname=FIRSTNAME. This is basically an array with loads of these. I want to split these up, but preserve both of them. So, I managed to put Firstname in it's own array and FIRSTNAME in it's own. But then a colleague of mine said "Why don't you just use a multidimensional array instead?". And this got me thinking, putting Firstname in 0 and FIRSTNAME in 1. But how do I do that?

This is my code right now:

    For iTeller = 0 To UBound(arrIniName)
        If Not arrIniName(iTeller) = "" Then
            arrIniName(iTeller) = Split(arrIniName(iTeller), "=")(0)
        End If
    Next

    For iTeller = 0 To UBound(arrIniValue)
        If Not arrIniValue(iTeller) = "" Then
            arrIniValue(iTeller) = Split(arrIniValue(iTeller), "=")(1)
        End If
    Next

Both arrIniName and arrIniValues consists of the exact same array to begin with. Which looks like this:

arrIniName(0) "Fistname=FIRSTNAME"
arrIniName(1) "Lastname=LASTNAME"
arrIniName(2) "Initials=INITIALS"

So I basically split each one into their own separate arrays the way I do it now. But putting them in a multi dimensional array would probably be better? Because then I'd have just one array to manage and could also pull that array through a For Each loop.

Edit: I ended up doing it like this, where Values is the array

For Each s In Values
    Dim strName, strValue
    s = Split(s, "=")
    strName = s(0)
    strValue = s(1)

    'Run function strName, strValue
Next

To answer the question of what would be the best structure to hold the data, it would help to know what you intend to do with the name/value pairs after you read them in. How will you want to -use- this data? — Steve Jorgensen
– Steve Jorgensen, Commented Mar 30, 2011 at 8:07
I'm going to run both of them through a function that creates a Scripting.Dictionary entry. So basically, I just want to dynamically create variables based on what's in the INI file. Later on, I look for bookmarks in the Word template based on FIRSTNAME and inserts a value into it. I just want it to be dynamic so I only have to update the ini file and insert the bookmark I want, without having to worry about the code behind you see :) Hope that makes sense :p — Kenny Bones
– Kenny Bones, Commented Mar 30, 2011 at 8:11
Chuckle - your comment appeared after I posted my answer. Why can't you create the dictionary right from the start? I mean, why do you need a temporary array to store these values, rather than just putting them straight in the dictionary? — RB.
– RB., Commented Mar 30, 2011 at 8:13
Good point.. I don't really need the arrays for anything. It just got this way because I want to split the original string from the array into two parts. But with your idea, I can now run a For Each loop and split it in there and put each part through the function that creates the dictionary entry. Gonna try it out and see how it works :) — Kenny Bones
– Kenny Bones, Commented Mar 30, 2011 at 8:19
Which works great.. I feel like such an idiot! :) But these "aha"-experiences makes be better at this! :) — Kenny Bones
– Kenny Bones, Commented Mar 30, 2011 at 8:22

RB. · Accepted Answer · 2011-03-30 08:22:56Z

4

The ideal solution sounds like a Dictionary (a data structure which holds Key/Value pairs - exactly what you have in your INI file).

A multi-dimensional array would not be necessary here, as you only have 2 dimensions (key, and value). Arrays are generally more difficult to work with than dictionary's as they are hard to resize, so you need to know how many items you have upfront.

Therefore, I would suggest the following code:

Dim dict As Dictionary
Set dict = new Dictionary
Dim key as String
Dim value as String

For iTeller = 0 To UBound(arrIniValue)
    If Not arrIniValue(iTeller) = "" Then
        key = Split(arrIniValue(iTeller), "=")(0)
        value = Split(arrIniValue(iTeller), "=")(1)
        dict.Add(key, value)
    End If
Next

However, if you want to use a multi-dimensional array, then the following will do it:

' Declare a 2-dimensional array, of dimensions "n by 2".
Dim results(UBound(arrIniValue), 2) As String

For iTeller = 0 To UBound(arrIniValue)
    If Not arrIniValue(iTeller) = "" Then
        key = Split(arrIniValue(iTeller), "=")(0)
        value = Split(arrIniValue(iTeller), "=")(1)
        results(iTeller, 0) = key
        results(iTeller, 1) = value
    End If
Next

edited Mar 30, 2011 at 8:22

answered Mar 30, 2011 at 8:12

RB.

37.4k13 gold badges103 silver badges137 bronze badges

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

6 Comments

Steve Jorgensen Over a year ago

... and if you can live with the limitations of a plain ol' VBA.Collection, you might want to use that instead of Scripting.Dictionary, to reduce the number of external dependencies you have to worry about. VBA.Collection is always available in VBA, regardless of what else is or isn't installed and properly registered.

RB. Over a year ago

Good point - I'm a .NET developer, so I get to have a rich library by default ;-)

Kenny Bones Over a year ago

I'll just set this as the answer and update my original post and show what I ended up doing :)

Kenny Bones Over a year ago

Can I use VBA.Collection the same way as Dictionary?

Joel Goodwin Over a year ago

The VBA.Collection is quite poor. If I recall correctly, it's not indexed like a dictionary and, more seriously, there's no way to check whether something exists in the Collection without tripping an error.

|

Collectives™ on Stack Overflow

VBA - Use multidimensional array instead of two separate ones?

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related