CODEWITHSUNDEEP
arraysvbamultidimensional-array
kiroshiro
kiroshiro

Reputation: 113

VBA looping through all dimensions of an array?

Is it possible to loop through all dimensions of an array? I found a workaround to get the number of dimension of an array. But now I want be able to iterate over this. Without knowing the exact count of dimensions before runtime.

Example: If you want to iterate over a 2- dimensional array. You already know the number of dims. Therefore you will write the code something like that:

var = MyArray(1,2)

or with iteration var:

For n = 1 to Ubound(vArray)
    For n2 1 to Ubound(vArray, 2)
        var = MyArray(n, n2)
    Next n2
Next n

But in my case I don't know the exact number of dimensions before runtime. It could be only one, two or even more. So how I write the command?

For d = 1 to NumberOfDimensions
    For n = 1 to Ubound(vArray, d)
        var = MyArray(???)
    Next n
Next d

My goal is to iterate over all the elements in all dimensions of that array.

Any ideas? Thanks in advance. :)

Upvotes: 1

Views: 1618

Answers (3)

kiroshiro
kiroshiro

Reputation: 113

Simple answer to my own question: Yes it is possible.

Example:

Dim vMyArray({1, 2}, {3, 4}, {5, 6}) As Variant
Dim vArrItem as Variant

For Each vArrItem In vMyArray
    Debug.Print vArrItem
Next vArrItem

This will produce:

1 2 3 4 5 6

In the direct window.

But this was only a piece of my problem. The main goal was to write a "RedimAnyDimension" function for VBA. Now I have a solution found for that. As I mentioned before. I use a workaround to bypass the vba syntax.

You can downlaod a zip archive here: RedimAnyDim.zip ( Hopefully. If it's not working please make a note.)

It creates new vba code at runtime using two template text files. Wich contains the body of the function code. Save this code in a new *.bas file in the active workbook path and then imports the new function into the active project. Executes the code and recieves the result. Finally the new imported module is been deleted.

The project file needs a reference to the "Microsoft Visual Basic for Applications Extensibility 5.3" in order to import and delete the code.

There are still some things to do. As Jaroslav Svestka mentioned before I didn't care about the size of memory or if its overflowing. Although this will only work with non- object array items( Numbers or Strings). And the generated code files could be deleted after usage.

This is maybe like to use a sledgehammer to crack a nut. But I had fun while creating it. :) Now I'm able to redefine the first dimension of any array. Wich is useful if you adding rows to your data arrays but don't want to write all those loops for passing the data to the temporary created array.

I'm sure there are lots of other things which can be tuned up. I would appreciate any ideas.

Upvotes: 0

SeekingWisdom
SeekingWisdom

Reputation: 80

As FunkSoulBrother noted, this can be accomplished using Recursion.

In C-like languages the code would look like this and should be easily converted to VBA:

    void IterateRec(Array arry)
    {   
        int numberOfDimensions = GetNumberOfDimensions();

        for(i = 0; i < numberOfDimensions; i++)
        {
            if(IsArray(arry[i]))
            {
                // Item is an array so we delve one level deeper.
                IterateRec(arry[i]);

            }
            else
            {
                // TODO: Process the current item here.
            }       
        }   
    }

The calling of the function would be in your case:

Sub Main()
    var MyArray = ...
    IterateRec(MyArray)
End Sub

Beware that if the recursion is too deep then you could get StackOverflow exception (or similar error in VBA). In that case you would have to create Stack class and modify the code to use it.

Upvotes: 1

FunkSoulBrother
FunkSoulBrother

Reputation: 2157

for the number of dimensions - you can read about it in Microsoft's support article https://support.microsoft.com/en-us/kb/152288.

Once you have the number of dimensions - you can use a recursive function that will delve one level deeper into the array on each call to itself and process the data (if the data is an array - call the function again, if not - process the current item)

There are things to be aware of : array size limits, checking objects type properly etc.

Good luck!

Upvotes: 1

Related Questions