Excel VBA - 使用范围作为函数的可选参数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3809944/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA - Using Ranges as Optional parameters for functions?
提问by Tristan
I'd like to write a VBA function that has a Range as an optional parameter. For instance something like:
我想编写一个以 Range 作为可选参数的 VBA 函数。例如类似的东西：
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.
我尝试了上面的函数，但我得到了一个 #VALUE！错误。我还尝试将 For 循环包装在 If (R) Then...End If 语句中。
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?
处理可选范围的方法是什么，如果范围存在，则通过 For Each 循环进行迭代？
回答by Andrew Cooper
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.
我已经在 Excel 2007 中对此进行了测试。您需要将其放入模块中，而不是工作表或工作簿代码部分。然后可以从带有或不带有 Range 对象的 VBA 或作为工作表函数调用该函数。
回答by Tristan
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
