What is happening here....

[zmbd]

OK, I've used this little function off and on for quite awhile... works slick until recently.

Normally this function returns only the alphanumerics...
Takes a string like "AS456^(SK)-4"
and returns "AS4564"

Recently it started doing this:
Takes a string like "AS456^(SK)-4"
and returns "^()-"

I fixed that by taking the passed string as a value (the original code had nothing there)

Then a few days ago, what caused me to think about the details of this code is that one report needed to preserve the dash and underscore in some of the strings... so I altered the pattern expression....

It dawned on me... why does that work?
The pattern is inside the the IF...THEN structure... I'm using late binding so the Object RegEx should always be "Nothing;" thus the pattern and the object should never be read... and yet, this works!?

I've stepped thru the code a 100 times and the lines inside the IF...THEN structure never executes.

If you take the pattern as it is in the code below:
"AS123@#$_345-Az"
will return
"AS123_345-Az"

If you alter the code to my normal pattern of

.Pattern = "[^a-zA-Z0-9]"

Then:
"AS123@#$_345-Az"
will return
"AS123345Az"

and yet the line is never executed.

(I'm using late binding here; so no library references to the regular expressions so that I don't have to deal with seting the reference on all of the lab-PCs)

Function RemChrs(ByVal s As String) As String
    Static RegEx As Object
 
    If RegEx Is Nothing Then
        Set RegEx = CreateObject("VBScript.RegExp")
        With RegEx
            .Global = True
            .Pattern = "[^a-zA-Z0-9_\-]"
        End With
    End If
    RemChrs = RegEx.Replace(s, "")
End Function

I use this function alot to strip illegal charactors out of data entry on forms and never thought about it.

Dazed and confussed (@_@)

[jimatqsi]

I'm not following you. You said " I'm using late binding so the Object RegEx should always be "Nothing;" thus the pattern and the object should never be read." What does that mean, "should never be read"?

The If test in line 4 is satisfied because Regex is Nothing; Lines 5-9 get executed, logically.I'm not sure why the debugger wouldn't step through those lines but there are rare times when the debugger actually interferes with the code.

I'm not sure why you're mystified about why it normally works. I'm not sure why it does not work when you step through the code with the debugger. And I don't know at all why it just recently started not working for you.

Sorry, I'm not much help.

Jim

[Rabbit]

Since you declared it as a static object, the if block should run the very first time the function is called and never again as long as the code is still in memory.

Close and reopen the database. Set the breakpoint. Call the function. It should run the if block. Run it again, it should no longer run the if block.

[NeoPa]

I'm a little confused as to what the question is but, like Rabbit, I'll assume it's "Why do I never see the code between the If ... and End If lines execute?".

In that case, my response is exactly that of Rabbit's. I strongly suspect you'll see it execute just the once - exactly as he suggests.

[zmbd]

There you go Rabbit.
Thank you NeoPa -

Of Course it's the "static" setting, don't even need to run the code.

Just having one of those obtuse, forst for the trees days. (^_^)

I just stepped away from everything PC related for a day or two... knew it was something simple and why I was having to reset the project to get the pattern to change.

Working on the formating and reporting for too long and my eyes crossed.

[NeoPa]

Good for you Z :-) We all have times like that I can assure you.

[zmbd]

:blush: Embarrassed enough to kill the thread… then again… maybe it’s a good thing for others to see that experts/moderators are human too (^_^)

Besides, that little function is just too slick to keep to myself. I found it on the web looking to validate alphanumerics in a field. Now I use it to simply strip the fields of illegal charactors and don't worry about user input. (^_^)

[NeoPa]

Interesting indeed :-) I don't use regular expressions from within VBA but I use them in TextPad and they are SO powerful.

Creating scripts from a list of data is so much fun. Even VBA code sometimes.