Optional ranges in vba function

Question

I am trying to return the columns count on a range, sometimes I need one range, but, sometimes I need more than one range.

I have put in optional ranges so I can choose multiple ranges. If I reference a range in the function prototype that I have not supplied in the spreadsheet I get the #Value! error.

I need a way to check if the optional ranges are null, void empty etc. so I don't have to reference the range.

This is the VBA Function Prototype:-

Function GetColoumnCount(ARange1 As Range, Optional ARange2 As Range, Optional ARange3 As Range, Optional ARange4 As Range) As Integer

    Dim Result As Integer
    Result = 0

    Result = ARange1.Columns.Count ' This works
    Result = ARange1.Columns.Count + ARange2.Columns.Count ' This doesn't work

    GetColoumnCount = Result    
End Function

In my spreadsheet I have to enter this in a cell for the function to work.
=GetColoumnCount(BC34:BK34, BC35:BD35, BE35:BF35, BG35:BH35)
this defeats the purpose of having optional arguments.

Answer 1

If you use the ParamArray keyword in the arguments you can supply a variable number of arguments.

Public Function GetColumnCount(ParamArray Ranges() As Variant) As Long

    Dim lReturn As Long
    Dim i As Long
    Dim rResult As Range
    Dim rArea As Range

    'Loop through the Ranges array supplied by ParamArray
    For i = LBound(Ranges) To UBound(Ranges)
        'Only work with those array members that are ranges
        If TypeName(Ranges(i)) = "Range" Then
            'Use Union to combine all the ranges
            If rResult Is Nothing Then
                Set rResult = Ranges(i)
            Else
                Set rResult = Application.Union(rResult, Ranges(i))
            End If
        End If
    Next i

    'Loop through the Areas and add up the columns
    If Not rResult Is Nothing Then
        For Each rArea In rResult.Areas
            lReturn = lReturn + rArea.Columns.Count
        Next rArea
    End If

    GetColumnCount = lReturn

End Function

To use:

=getcolumncount(E2:K18) = 7
=getcolumncount(D4:L14,N4:P14) =12 (9+3)
=getcolumncount(C7:F15,H7:L15,K7:N15) =11 (omits double counting overlap)

Answer 2

Try it like this

Function GetColoumnCount(ARange1 As Range, Optional ARange2 As Range, Optional ARange3 As Range, Optional ARange4 As Range) As Long  
    Dim Result As Long
    Result = 0

    Result = ARange1.Columns.Count ' This works
    If Not ARange2 Is Nothing Then
        Result = Result + ARange2.Columns.Count
    End If

    GetColoumnCount = Result    
End Function