Compile Error: Sub or Function not defined

[cstordalen]

Greetings -

A developer in a central office at the university I work at developed a
one-file Excel solution to automatically cleanup submitted data from
around our university for incorporation into a central system. This
file is built in Excel on Windows. I am not a macros/VBA guy and so am
lost as to how to hopefully resolve the issue we are having with the
file.

Launching the file results in a message stating that Macros are
required and asking if they should be turned on. When selecting this
option a window appears with the message "Compile Error: Sub or
Function not defined".

The code in the VBA editor that appears after selecting 'OK' from the
message is:

Function doubleQuotes(strInput)
' also trims extra spaces from start and end
' also escapes special characters like "_" and "&"
Dim strResults As String
strResults = Replace(Trim(strInput), "'", "''")
strResults = Replace(strResults, "_", "\_")
strResults = Replace(strResults, "&", "\&")
doubleQuotes = strResults
End Function

I read in another thread that the Visual Basic in Office 2004 has not
kept pace with the updates to the Windows version. Could this be the
cause - the Mac version doesn't recognize this/these commands?

It sounds as though we will not get help in making this file
Mac-friendly from the Windows developer who put it together so any help
from this group would be most appreciated.

Regards -

Christopher

 

[JE McGimpsey]

Launching the file results in a message stating that Macros are
required and asking if they should be turned on. When selecting this
option a window appears with the message "Compile Error: Sub or
Function not defined".

The code in the VBA editor that appears after selecting 'OK' from the
message is:

Function doubleQuotes(strInput)
' also trims extra spaces from start and end
' also escapes special characters like "_" and "&"
Dim strResults As String
strResults = Replace(Trim(strInput), "'", "''")
strResults = Replace(strResults, "_", "\_")
strResults = Replace(strResults, "&", "\&")
doubleQuotes = strResults
End Function

I read in another thread that the Visual Basic in Office 2004 has not
kept pace with the updates to the Windows version. Could this be the
cause - the Mac version doesn't recognize this/these commands?

That's exactly it. Replace() was introduced in VBA6.

Fortunately if you replace each instance of

Replace(

with

Application.Substitute(

the code will work fine in MacXL (and WinXL97).

For my applications, I normally write my own Replace() code and
conditionally compile it:

#If Mac Then
Public Function Replace(ByRef Text As String, _
ByRef sOld As String, ByRef sNew As String, _
Optional ByVal Start As Long = 1, _
Optional ByVal Count As Long = 2147483647, _
Optional ByVal Compare As Integer = vbBinaryCompare _
) As String
'lots of code here
End Function
#End If

That way, WinXL200x will ignore my Replace() function and use the
built-in one, and Macs will use mine.