Custom Function not working 1

[MacroAlan]

In my form, there is a textbox where someone can type a string of numbers separated by commas. I want to test whether the last character of the string is a comma or space and warn him to fix:

Public Function CheckFieldSyntax(N As String) As String
[COLOR=red]'If last character is a comma or space, warn user[/color]
    Select Case N
        Case InStrRev(N, ",", -1) > 5
            MsgBox "Your selection may not end in a comma", vbCritical
            
        Case Right(N, 2) = "," & Chr(32)
            MsgBox "Check your selection - the ending looks incomplete", vbCritical
        Case InStrRev(N, Chr(32), -1) > 0
            MsgBox "The end of your selection is a space, please check", vbInformation
        Case Else
        Debug.Print N & " ~~~ " & Right(N, 1)
    End Select
            
End Function

It does not blow up, it just does not work!


Alan

 

[HarleyQuinn]

It's to do with the way you've laid out your SELECT CASE.

Here's a simple example for you situation:

Public Function CheckFieldSyntax(N As String) As String
'If last character is a comma or space, warn user
    Select Case [red]Right(N, 1)[/red]
        Case [red]Chr(32), ","[/red]
            MsgBox "Your selection may not end in a comma or a space", vbCritical
        Case Else
        Debug.Print N & " ~~~ " & Right(N, 1)
    End Select
            
End Function

Also I'm not sure that InStrRev is behaving the way you would expect, as while starting at the end of the string it returns the character position based on numbers starting at the beginning of the string.

Also:

InStrRev(N, Chr(32), -1) > 0

would evaluate as true if there is a space anywhere in the string and I don't think (from reading you messages to the user) that's what you wanted.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[MacroAlan]

We convinced the manager to go in a different direction that made this a moot point.


Alan