调试经验——将VBA子程序转换为自定义函数（Transforming sub procedure to custom function in Excel）

今天发现，可以很容易的将VBA子程序转换为自定义函数，从而可以在worksheet中方便的使用相应的功能。

比如，现有一个实现字符串逆序的子程序。

Sub reverseString()

Dim oStr As String
Dim oArr() As String
Dim rStr As String
Dim rArr() As String

oStr = "乡 ma 故 I 思头低 erofereht 月 kniht 明 I 望头举"
rStr = ""

ReDim oArr(1 To Len(oStr))
ReDim rArr(1 To Len(oStr))

For i = 1 To Len(oStr)
    oArr(i) = Mid(oStr, i, 1)
    rArr(Len(oStr) - i + 1) = oArr(i)
Next

For i = 1 To Len(oStr)
    rStr = rStr + rArr(i)
Next

Debug.Print "转换前的字符串：" & oStr & vbNewLine
Debug.Print "转换后的字符串：" & rStr & vbNewLine

End Sub

经过稍加修改，可以转换为自定义函数：

Function reverseStr(ByVal rng As Range)

Dim oStr As String
Dim oArr() As String
Dim rStr As String
Dim rArr() As String

oStr = rng.Value
rStr = ""

ReDim oArr(1 To Len(oStr))
ReDim rArr(1 To Len(oStr))

For i = 1 To Len(oStr)
    oArr(i) = Mid(oStr, i, 1)
    rArr(Len(oStr) - i + 1) = oArr(i)
Next

For i = 1 To Len(oStr)
    rStr = rStr + rArr(i)
Next

reverseStr = rStr
End Function

然后就可以在工作表中调用这一函数了：