VBA is hosted in Excel, but it's completely distinct: VBA doesn't know anything about Excel's worksheet functions. At least, not natively.
So, Substitute isn't defined anywhere - same for VLookup, and many other worksheet functions. Some VBA functions have the same name (or a similar one) as Excel worksheet functions, and behave differently.
The VBA standard library does have a Strings module that exposes a number of specialized String functions (several of which also exist as worksheet functions, e.g. =LEFT vs Left and Left$).
As Tom correctly specifies, the VBA standard library function that's equivalent to Excel's Substitute function, is Replace - a function that's defined in the VBA.Strings module, which you can explore by pressing F2 to bring up the VBE's Object Browser:
Since these functions are all globally-scoped, you don't have to fully qualify them; that's why & how you can legally write Debug.Print Replace(string1, string2). But know that you could always type VBA. and get IntelliSense to list what functions are available - VBA.Strings.Replace works, as does VBA.Replace, or just Replace:
Public Function IPFix(ByVal IP As String) As String
IPFix = VBA.Strings.Replace(VBA.Strings.Replace(VBA.Strings.Replace("@." & Trim(IP), ".0", "."), ".0", "."), "@.", "")
End Function
Now that's wordy. The benefit though, is that now if your VBA project defines a Replace function, places that invoke VBA.Strings.Replace or VBA.Replace, will still be invoking that function instead of your VBAProject.Module1.Replace custom function - because an identifier reference in VBA is always resolved by the compiler, starting with the smallest accessible scope: so a custom function can exist in a custom module and "shadow" or "hide" a VBA standard library function (or any other function defined in any other referenced type library).
Lasty, note that for the times you do need to invoke an actual worksheet function, you can do it from the Excel object model - here using a With block to reduce the verbiage:
Public Function IPFix(ByVal IP As String) As String
With Application.WorksheetFunction
IPFix = .Substitute(.Substitute(.Substitute("@." & Trim(IP), ".0", "."), ".0", "."), "@.", "")
End With
End Function
Worksheet functions invoked like this will work exactly like their actual-worksheet counterparts; you can use Index, Match, VLookup, and a lot of other worksheet functions that way, with an early-bound standard VBA member call that will raise a standard VBA runtime error given invalid arguments.
You can also invoke them late-bound, directly against the Application object - in which case invalid arguments won't raise an error, but return one - just like they do in actual worksheets (e.g. #VALUE!) - if you want to return that worksheet error value, your function needs to return a Variant (implicit like you have, or explicit as follows), because an Error is a VBA data type that can't be coerced into a String value, so the return assignment would throw a type mismatch error:
Public Function IPFix(ByVal IP As String) As Variant
With Application
IPFix = .Substitute(.Substitute(.Substitute("@." & Trim(IP), ".0", "."), ".0", "."), "@.", "")
End With
End Function
Note that because these calls are late-bound, they are only resolved at run-time, which means you must be very careful with typos; you also don't get any IntelliSense or "parameter quick-info" to help as you type it.
