VBA中如何将函数赋值给变量调用？避免重复编写替换函数

1. 自定义包装函数（兼容所有Office版本） #

直接写个短名的包装函数，把常用的Replace/Substitute逻辑包进去，之后用短名调用即可：

包装VBA内置Replace #

Function R(src As String, find As String, repl As String, _
           Optional start As Long = 1, Optional count As Long = -1, _
           Optional compare As VbCompareMethod = vbBinaryCompare) As String
    R = Replace(src, find, repl, start, count, compare)
End Function

调用示例：

Dim myStr As String
myStr = "abc123abc456"
' 链式替换无需重复写Replace
myStr = R(R(myStr, "abc", "XYZ"), "123", "789")

包装WorksheetFunction.Substitute #

Function S(src As String, find As String, repl As String, _
           Optional instance_num As Long = 0) As String
    If instance_num = 0 Then
        S = WorksheetFunction.Substitute(src, find, repl)
    Else
        S = WorksheetFunction.Substitute(src, find, repl, instance_num)
    End If
End Function

调用示例：

myStr = S(S(myStr, "abc", "XYZ"), "456", "000")

2. 用VBA Lambda简化（仅Office 365/2021+支持） #

如果是新版Office，可直接用Lambda创建匿名函数赋值给变量：

Dim R As Variant
Set R = Lambda$(src, find, repl, Optional start = 1, Optional count = -1, Optional compare = vbBinaryCompare) _
    Replace(src, find, repl, start, count, compare)

' 调用方式和包装函数一致
myStr = R(R(myStr, "abc", "XYZ"), "123", "789")

为什么直接赋值函数名行不通？ #

VBA里的内置函数（比如Replace）不是对象，无法直接赋值给变量；WorksheetFunction.Substitute是对象的成员方法，同样不能直接把方法本身赋值给普通变量。必须通过包装或Lambda这种间接方式实现简化调用的需求。

内容的提问来源于stack exchange，提问作者Sirius_Black