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?

Andrew Cooper · Accepted Answer · 2010-09-28 06:19:37Z

11

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.

edited Sep 28, 2010 at 6:19

answered Sep 28, 2010 at 5:39

Andrew Cooper

32.7k5 gold badges86 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gediminas Over a year ago

For me Optional R As Variant worked. And in the function If IsMissing(R) Then Set R= Range("whatever") (Excel 2016).

user180100 · Accepted Answer · 2010-09-28 05:53:47Z

0

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

answered Sep 28, 2010 at 5:53

user180100

Collectives™ on Stack Overflow

Excel VBA - Using Ranges as Optional parameters for functions?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related