Question

VBA程序出现sub或者function未定义

Sub test()
Dim I, j, K As Long
Dim a, b As String
Dim useSheet As Excel.Worksheet
Set useSheet = ThisWorkbook.Worksheets("Sheet1")

a = useSheet.Cells(1, 1).Value
b = Trim(Right(Substitute(a, "_", Rept(" ", Len(a))), Len(a)))

End Sub

会提示sub或者function未定义

但是如果我把b=Trim(Right(Substitute(a, "_", Rept(" ", Len(a))), Len(a)))删除就可以正常运行

为什么？

Answer 1

SUBSTITUTE和rept是工作表函数，需要用WORKSHEETFUNCTION来调用。

Sub test()
 Dim I, j, K As Long
 Dim a, b As String
 Dim useSheet As Excel.Worksheet
 Set useSheet = ThisWorkbook.Worksheets("Sheet1")

a = useSheet.Cells(1, 1).Value
b = Trim(Right(Application.WorksheetFunction.Substitute(a, "_", Application.WorksheetFunction.Rept(" ", Len(a))), Len(a)))

End Sub

Answer 2

Substitute错了，应该修改为replace

Answer 3

Substitute 换成 Application.Substitute 看看可以不