Excel VBA Non-Contiguous Array User Defined Function Fix

Question

I have a user defined function as seen below that allows me to combine two non-adjacent columns into a contiguous array.

Function MakeContig(ParamArray av() As Variant) As Variant
    Dim avOut() As Variant
    Dim i       As Long
    Dim j       As Long

    ReDim avOut(1 To av(0).Count, 0 To UBound(av))
    For j = 0 To UBound(av)
        For i = 1 To av(j).Rows.Count
            avOut(i, j) = av(j)(i)
        Next i
    Next j
    MakeContig = avOut
End Function

So, if I type "=makecontig(A1:A5,E1:E5)" into a cell, a single array is formed consisting of the two columns included in the formula. I would like to be able to edit this function so that it ignores an entry of an array full of errors or any type of non-array entry.

For example, if i type "=makecontig(A1:A5,E1:E5, , )" or "=makecontig(A1:A5,Nothing,E1:E5)" or "=makecontig(A1:A5,E1:E5,C1:C5*#N/A)" I would like it to ignore the error or blank parameter and only create a contiguous array for A1:A5 and E1:E5.

I imagine I need to include an If/Then statement somewhere in the UDF but I cannot seem to figure out how to execute it properly.

Any help would be appreciated!

Answer 1

You need to test if the parameter is a range or not:

Function MakeContig(ParamArray av() As Variant) As Variant
    Dim avOut() As Variant
    Dim i       As Long
    Dim j       As Long
    Dim x As Long
    Dim t As Long
    x = -1
    For j = 0 To UBound(av)
        If TypeName(av(j)) = "Range" Then
            x = x + 1
            If av(j).Count > t Then
                t = av(j).Count
            End If
        End If
    Next j
    ReDim avOut(1 To t, 0 To x)
    t = 0
    For j = 0 To UBound(av)
        If TypeName(av(j)) = "Range" Then
        For i = 1 To av(j).Rows.Count
            avOut(i, t) = av(j)(i)
        Next i
        t = t + 1
        End If
    Next j
    MakeContig = avOut
End Function

As you can see:

MakeContig($F$1:$F$8+#N/A,$A$1:$A$26,$C$1:$C$26,$B$1:$B$4*#N/A)

returns only a 2 column wide array: