Excel VBA - 使用范围作为函数的可选参数？

发布于 2024-09-25 12:06:38 字数 345 浏览 0 评论 0原文

我想编写一个 VBA 函数，其中包含 Range 作为可选参数。例如：

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

我尝试了上面的函数，但我得到了#VALUE！错误。我还尝试将 For 循环包装在 If (R) Then...End If 语句内。

处理可选范围的方法是什么，如果范围存在，则通过 For Each 循环对其进行迭代？

原文

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

前事休说 2024-10-02 12:06:38

试试这个，

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

我已经在 Excel 2007 中测试过了。您需要将其放入模块中，而不是工作表或工作簿代码部分中。然后可以使用或不使用 Range 对象从 VBA 调用该函数，或者将其作为工作表函数调用。

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.

回复收藏 0 原文

︶￣淡然 2024-10-02 12:06:38

解决方法：

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

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

回复收藏 0 原文

~没有更多了~