Create as many levels of a loop as there are dimensions of a string

[Jubinell]

*Edit* sorry the title should have been:
Create as many levels of a loop as there are dimensions of an array

Sorry for the confusion!

---

Hi,

This is somewhat difficult to explain, but please look at the following UDF:

Public Function ConcSep_Array(InArray() As Variant, Optional Sep As String) As String
Dim OutStr As String
Dim i As Integer

For i = LBound(InArray, 1) To UBound(InArray, 1)
    If InArray(i) <> "" Then OutStr = OutStr & InArray(i) & Sep
Next i

ConcSep_Array = Left(OutStr, Len(OutStr) - Len(Sep))
End Function

This UDF takes a one-dimensional Excel array then stacks its elements one after another separated by the variable Sep in a continuous string of text ConcSep_Array. The result is then returned to Excel as text.

Now this works great with a one-dimensional InArray(). If InArray() were 2-dimensional, I would need to have 2 loops, like this:

For i = LBound(InArray, 1) To UBound(InArray, 1)
    For j = LBound(InArray, 2) To UBound(InArray, 2)
        If InArray(i, j) <> "" Then OutStr = OutStr & InArray(i, j) & Sep
    Next j
Next i

The question is...I don't know how many dimensions InArray() may have. Hence, I'd like to have as many loop levels as there are dimensions of InArray().

Can this be done?

 

[Andrew Poulsom]

You can use Chip Pearson's NumberOfArrayDimensions function here:

http://www.cpearson.com/Excel/VBAArrays.htm#NumberOfDimensions

 

[Jubinell]

Thank you Andrew for the speedy reply. Supposed I got the number of dimensions of the input array. Now how do I create as many loop levels as there are dimensions?