Share via

Substitute function causes error message:

Anonymous
2016-07-21T13:11:13+00:00

The use of the Substitute function in the below code causes a Compile Error: Sub or function not defined.

Strangely, in a nother module, in another project, it runs OK. The References setup in the two Projects are the same.

Code:

sRestAntwort = Left(Replace(Substitute(sRestAntwort, """", ""), 1, Find("name:", Substitute(sRestAntwort, """", "")) + 4, ""), Find(",id", Replace(Substitute(sRestAntwort, """", ""), 1, Find("name:", Substitute(sRestAntwort, """", "")) + 4, "")) - 1)

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. HansV 462.6K Reputation points MVP Volunteer Moderator
    2016-07-21T17:51:23+00:00

    It looks like you're trying to use worksheet functions in VBA. SUBSTITUTE, for example, is not a VBA function, and while Replace and Find are available in VBA, their syntax is different than that of the worksheet functions REPLACE and FIND.

    If you want to use the worksheet functions in VBA, you can prefix them with Application.WorksheetFunction, e.g. Application.WorksheetFunction.Replace(...) or Application.WorksheetFunction.Substitute(...)

    6 people found this answer helpful.
    0 comments