Cannot get my Public Function with arguments to work

[mtnstream]

Hello. I'm having issues with a MS Access Public Function that resides in a standalone Module. It is pasted below. When I run the code, it gives me the exact alphabetized list from an array that I was hoping for. However, when I try to add an argument to the Function so I can pass values to it, I get the attached macro dialog. I have no idea why this is happening and would really appreciate some help.

Public Function SplitCleanup(strOrphans As String) As String

Dim intRow As Integer
Dim astrOrphans() As String

On Error GoTo ErrorHandler

SplitCleanup = ""
'strOrphans = ";#Electrophoresis;#Gas Chromatography;#Liquid Chromatography;#Mass Spectrometry;#Services;#Instrument Parts & Supplies;#Columns & Supplies;#Atomic Spectroscopy;#"

If Len(strOrphans) > 0 Then

astrOrphans = Split(strOrphans, ";#")

With WizHook
.Key = 51488399
.SortStringArray astrOrphans
End With

For intRow = 0 To UBound(astrOrphans)
DoCmd.SetWarnings (0)
SplitCleanup = SplitCleanup & Replace(astrOrphans(intRow), "'", "''") & vbCrLf
DoCmd.SetWarnings (1)
Next intRow

MsgBox (SplitCleanup)

End If

Exit Function

ErrorHandler:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description

End Function


Thanks for looking.

Newbie Bill

 

[mtnstream]

FYI....the code I pasted in reflects the code after applying an argument to the function. If you want to see the results as it should work, remove the argument and uncomment the strOrphans list on line 9.

 

[MazeWorX]

In the intermediate window i ran your function as

?splitcleanup(";#Electrophoresis;#Gas Chromatography;#Liquid Chromatography;#Mass Spectrometry;#Services;#Instrument Parts & Supplies;#Columns & Supplies;#Atomic Spectroscopy;#")

with the result:

Atomic Spectroscopy
Columns & Supplies
Electrophoresis
Gas Chromatography
Instrument Parts & Supplies
Liquid Chromatography
Mass Spectrometry
Services

without error leaving 'strOrphans = commented out

btw we cant see the macro error in your attachment

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[MazeWorX]

just had a thought how are you calling your function?

Should be, you could use a string variable to hold the string data

Dim strResult as String
Dim strMyString as String

strMyString = ";#Electrophoresis;#Gas Chromatography;#Liquid Chromatography;#Mass Spectrometry;#Services;#Instrument Parts & Supplies;#Columns & Supplies;#Atomic Spectroscopy;#"

strResult = splitcleanup(strMyString)


HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[mtnstream]

I'm running the code from the standard module it is written in, and it displays the macro creation prompt any time I ask for parameters to be passed in.

http://www.mtnstream.com/macroerror.jpg

When I try and run it in the intermediate window I get "Compile Error: Expected variable or procedure, not module"

I've tried this from two different computers, thinking it must be a configuration issue.

Any thoughts, guys? This is driving me nuts.

 

[mtnstream]

I'm using Access 2010, standard module. The only declarations are Option Explicit and Option Compare Database.

 

[PHV]

Compile Error: Expected variable or procedure, not module
So, change the name of the module as it can't be the same as a proceure.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mtnstream]

I found the solution here:

http://support.microsoft.com/kb/312851

I had the name of the module the same as my function. Bad form.

 

[MazeWorX]

PHV beat you to it by 5 mins

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>