Additional ranges in vba function

Question

I am trying to return the number of columns in a range, sometimes I need one range, but sometimes I need more than one range.

I have included additional ranges to select multiple ranges. If I reference a range in a function prototype that I did not provide in the spreadsheet, I get #Value! error.

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

This is a prototype of a VBA function: -

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 table, I have to enter this in the cell for the function to work.
=GetColoumnCount(BC34:BK34, BC35:BD35, BE35:BF35, BG35:BH35)
this violates the purpose of the optional arguments.

Answer 1

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 

Answer 2

If you use the ParamArray keyword in arguments, you can specify 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 

For 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)