VBA – Ubound of Multidimensional Array or How to Get What We Need?

Transforming Range to Array in VBA is actually a one liner – we take the range and we assign it to a variant:

The “strange” part is that the array is converted to 2-dimensional array after this:

and working with these is not always fun. Although, if it was a single row or a single column range, it would have been ok. But, let’s say that our range looks like this:

and we want to get the words “ACADEMY” from row 2 and “ICOSE” from column “B” into 2 new arrays, that will look like this: Array("A","C","A","D","E","M","Y")  and Array("I","C","O","S","E"). Is it doable? It is. We can even make the task harder, saying that we need the last column and the last row of our range, that is converted now to a 2d array. This is how to get what we need, as working with 2d arrays is a bit tricky:

Thus, knowing the magic of Ubound(myArray,2) to count the cells in a given row, we may produce two functions, that will transform for us a whole row or column of 2d array to a new 1d array, that we may use further. These are the functions:

Yup, they work even better than imagined – as the 2d array is usually 1-based (as it is from a range), the newly produced array from the function is with 0-th element being Empty (Leer in German is Empty):

This is a bit useful, if you are going to do some manipulations to this array and later write it down to an Excel range. The whole code is here, I have added also PrintArray function, that displays the new arrays:

Tsar’s Palace in Sofia is to the left of my shoe.

Enjoy and make a PR, if you have better ideas!

Tagged with: , , , , , ,