Excel VBA - Using ranges as additional parameters for functions?

Question

I would like to write a VBA function that has a range as an optional parameter. For example, for example:

Public Function testfunc(S As String, Optional R As Range) As String testfunc = S For Each cell In R testfunc = testfunc + cell Next cell End Function 

I tried the function above, but I get #CURRENCY! error. I also tried wrapping the For loop inside an If (R) Then ... End If statement.

What will be the way to work with the additional range, where if the range exists, then it repeats through the For Each loop?

Answer 1

try it

 Public Function testfunc(S As String, Optional R As Range = Nothing) As String testfunc = S if not R is nothing then For Each cell In R testfunc = testfunc & cell Next cell end if End Function 

I tested it well in Excel 2007. You need to put it in the "Module" sections, not the "Worksheet" or "Book Code". You can then call the function from VBA with or without a Range object, or as a sheet function.

Answer 2

Workaround:

 Public Function testfunc(S As String, Optional R As String = vbNullString) As String testfunc = S If R <> vbNullString Then For Each cell In Range(R) ' & for concatenation not + ' testfunc = testfunc & cell Next cell End If End Function Sub test() MsgBox testfunc("", "A1:A5"), vbOKOnly, "Results" MsgBox testfunc(""), vbOKOnly, "Results" End Sub