Excel VBA - Using Ranges as Optional parameters for functions?

Question

I'd like to write a VBA function that has a Range as an optional parameter. For instance something like:

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 a #VALUE! error. I also tried wrapping the For loop inside an If (R) Then...End If statement.

What would be the way to deal with an optional Range, where if the range exists, then it is iterated through via a For Each loop?

Answer 1

Try this

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've tested this okay in Excel 2007. You need to put it into a Module, not the Worksheet or Workbook code sections. The function can then be called from VBA with or without a Range object, or as a worksheet 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