How to make a function that takes a variable and changes it blank if it fails test?

[Vita]

Hi, I am trying to make a function to detect if the user enters non-numeric characters (dashes are okay) but I want it to take a variable and check the value of the variable and if it has non-numeric characters change the variable to blank.
Upon typing this I realized it may be easier to just have it set the textbox control to an empty string but I am curious if I am able to make the variable work.

Here is my code:

Public Function NonNumericDetector(inString As String) As String
    Dim chrString As String
    Dim strPos As Integer


    For strPos = 1 To Len(inString)
        chrString = Mid(inString, strPos, 1)
        If (IsNumeric(chrString) Or (chrString = "-") = False) Then
            MsgBox ("Please enter a workstep.")
            inString = inString & " = ''"
        End If
    Next
    NonNumericDetector = CStr(inString)
End Function

Currently It just repeatedy pops up with the messagebox and doesn't set the value
Thanks for any help!



Edit: Note this is my first time making a custom function and I was basing it off a similar one I saw online. It also seems to expect an end of function in the "IsNumeric(chrString)" it specifically highlights the chrString.

[Micron]

inString = ""

What you have will concatenate a space, equals sign and a zls (zero length string)
EDIT
10 prompts for a 10 character word before the user can do anything about it makes no sense though.
Nor do you need to convert a string into a string: Cstr(inString)
This seems easier to read to me:

If Not IsNumeric(chrString) Or Not chrString = "-" Then

but I question whether it should be AND vs OR because - will result in False in the first half of the test.

[Vita]

inString = ""

What you have will concatenate a space, equals sign and a zls (zero length string)
EDIT
10 prompts for a 10 character word before the user can do anything about it makes no sense though.
Nor do you need to convert a string into a string: Cstr(inString)
This seems easier to read to me:

If Not IsNumeric(chrString) Or Not chrString = "-" Then

but I question whether it should be AND vs OR because - will result in False in the first half of the test.

Well a valid input should look like "178259-02-521" so it needs to allow dashes. Would the format function work as a check here?

I didn't even think about checking if its blank outside the function. I was just thinking if the function could change the variable then I could just call the function and have it check a variable.

Here is the changed code:

Public Function NonNumericDetector(inString As String) As String
    Dim chrString As String
    Dim strPos As Integer




    For strPos = 1 To Len(inString)
        chrString = Mid(inString, strPos, 1)
        If Not IsNumeric(chrString) And (chrString = "-") Then
            inString = ""
        End If
    Next
    NonNumericDetector = inString
End Function

And the changed usage

strStatus = Me.WorkticketScanTxt.ValueIf
 (NonNumericDetector(strStatus)) = "" Then
    strStatus = ""
    MsgBox ("Please enter a valid workticket number")
End If

[davegri]

This function will detect a bad string. I'm not sure what you want returned for a bad (or good) string.
Note that the function will pass a string with two or more '-' as OK.

Public Function NonNumericDetector(inString As String) As String
    Dim chrString As String
    Dim strPos As Integer, fail As Boolean
    fail = False
    For strPos = 1 To Len(inString)
        chrString = Mid(inString, strPos, 1)
        Select Case Asc(chrString)
            Case 48 To 57, 45
            Case Else
                fail = True
        End Select
    Next
    If fail Then
        MsgBox ("Please enter a valid workstep.")
    End If
    NonNumericDetector = inString
End Function

[Micron]

IMO, not enough sample data and sample results to definitively answer. Given the latest example, even simple Val might work - but not if you can have A1234-567-890. Yes, a function can alter a variable that is passed to it and return a modified value, but not if passed byVal.

[Vita]

This function will detect a bad string. I'm not sure what you want returned for a bad (or good) string.
Note that the function will pass a string with two or more '-' as OK.

Public Function NonNumericDetector(inString As String) As String
    Dim chrString As String
    Dim strPos As Integer, fail As Boolean
    fail = False
    For strPos = 1 To Len(inString)
        chrString = Mid(inString, strPos, 1)
        Select Case Asc(chrString)
            Case 48 To 57, 45
            Case Else
                fail = True
        End Select
    Next
    If fail Then
        MsgBox ("Please enter a valid workstep.")
    End If
    NonNumericDetector = inString
End Function

Interesting. I am not sure what the following lines do so I will have to read up on how they work and see if it will work.

Select Case Asc(chrString)
     Case 48 To 57, 45
     Case Else
          fail = True
     End Select

IMO, not enough sample data and sample results to definitively answer. Given the latest example, even simple Val might work - but not if you can have A1234-567-890. Yes, a function can alter a variable that is passed to it and return a modified value, but not if passed byVal.

A valid workticket number will always be 6-2-3 numbers. So 285468-25-251, 321548-56-546, 284658-01-007. Not sure what other data/information I could give so please feel free to ask.
Edit: Note these ticket numbers are dummies and do not follow any pattern.

[Micron]

you left off the second Not, so I don't see that working. Based on your naming convention (pretty much same as mine) would not preface an integer variable with str. With the sample you posted, this seems to work, but it's a limited test.

Public Function NonNumericDetector(inString As String) As Boolean
    Dim chrString As String
    Dim intPos As Integer

    For intPos = 1 To Len(inString)
        chrString = Mid(inString, intPos, 1)
        If Not IsNumeric(chrString) And Not (chrString = "-") Then
            NonNumericDetector = True
            Exit Function
        End If
    Next
    NonNumericDetector = False
End Function

Also, I imagine the call would be like
If my value is not numeric then
do this
Else
do that
End If

which suggests the test is boolean but that's probably not important. What is important is the second Not.

EDIT

A valid workticket number will always be 6-2-3 numbers.

That clears it up.

[davegri]

Mod to my post #4 to verify 2 dashes:

Public Function NonNumericDetector(inString As String) As String
    Dim chrString As String
    Dim strPos As Integer, fail As Boolean, nDash As Long
    fail = False
    For strPos = 1 To Len(inString)
        chrString = Mid(inString, strPos, 1)
        If Asc(chrString) = 45 Then nDash = nDash + 1
        Select Case Asc(chrString)
            Case 48 To 57, 45
            Case Else
                fail = True
        End Select
    Next
    If nDash <> 2 Or fail Then
        MsgBox ("Please enter a valid workstep.")
    End If
    NonNumericDetector = inString
End Function

The case statement checks to see that the ascii values of the characters are 48 to 57 which represents digits 0 thru 9, or 45 for a dash. New code checks for existence of 2 and only 2 dashes

[Vita]

Mod to my post #4 to verify 2 dashes:

Public Function NonNumericDetector(inString As String) As String
    Dim chrString As String
    Dim strPos As Integer, fail As Boolean, nDash As Long
    fail = False
    For strPos = 1 To Len(inString)
        chrString = Mid(inString, strPos, 1)
        If Asc(chrString) = 45 Then nDash = nDash + 1
        Select Case Asc(chrString)
            Case 48 To 57, 45
            Case Else
                fail = True
        End Select
    Next
    If nDash <> 2 Or fail Then
        MsgBox ("Please enter a valid workstep.")
    End If
    NonNumericDetector = inString
End Function

The case statement checks to see that the ascii values of the characters are 48 to 57 which represents digits 0 thru 9, or 45 for a dash. New code checks for existence of 2 and only 2 dashes

Not that I think it will ever be a problem but this does allow for someone to enter "--" and by pass that. Would changing the Or to And fix that?

Also I did some reading and I do think I see the value in using "Select Case" in certain areas but I was wondering if I could have someone with more experience explain "Select Case" vs "if Else"
From my understanding you cant use "To" in and If statement which makes it quicker and easier to use a Select statement for a range of expressions.
The other use case I could imagine was if you for some reason only need it to pick the first case out of many. Which I imagine you could also use a For statement and an array to cycle through and confirm the first matching case right?
Either way I would love some clarification from someone who knows how to use it.

Thank you if you have the time to teach me about it and this was all said with more of an inquisitive tone.

Edit: I adjusted the code to include an if statement that checks for "--". Code below. Please let me know if there is a simpler way so I can think that way next time
and apologies for the long time before posts. Work got busy and I haven't had a minute to work on this.

Public Function NonNumericDetector(inString As String) As String
    Dim chrString As String
    Dim strPos As Integer, fail As Boolean, nDash As Long
    fail = False
    For strPos = 1 To Len(inString)
        chrString = Mid(inString, strPos, 1)
        If Asc(chrString) = 45 Then nDash = nDash + 1
        Select Case Asc(chrString)
            Case 48 To 57, 45
            Case Else
                fail = True
        End Select
    Next
    If (inString = "--") Then
        fail = True
    End If
    If nDash <> 2 Or fail Then
        MsgBox ("Please enter a valid workstep.")
    End If
    NonNumericDetector = fail
End Function

[davegri]

Some changes, with comments in code. Additional explanation of Select Case widely available via google.
changed check for double dash to InStr function.
changed return type of the function code to boolean

Public Function NonNumericDetector(inString As String) As Boolean
    Dim chrString As String
    Dim strPos As Integer, fail As Boolean, nDash As Long
    fail = False
    For strPos = 1 To Len(inString)
        chrString = Mid(inString, strPos, 1)            'examine inString character by character
        If Asc(chrString) = 45 Then nDash = nDash + 1   'count dashes
        Select Case Asc(chrString)
            Case 48 To 57, 45           'nums 0 thru9 or dash, do nothing, all is OK
            Case Else                   'anything other than nums 0 thru 9 or dash is bad input
                fail = True
        End Select
    Next strPos
    
    If InStr(inString, "--") > 0 Then   'check if string contains "--"
        fail = True
    End If
    
    If nDash <> 2 Then                  'count of dashes computed above
        fail = True
    End If
    
    If fail = True Then
        MsgBox ("Please enter a valid workstep.")
    End If
    
    NonNumericDetector = fail
End Function

[accesstos]

A valid workticket number will always be 6-2-3 numbers. So 285468-25-251, 321548-56-546, 284658-01-007.

So, why not

Function IsTicketNum(strIn As String) As Boolean
    IsTicketNum = strIn Like "######-##-###"
End Function

???

Did I missed something?

[Vita]

So, why not

Function IsTicketNum(strIn As String) As Boolean
    IsTicketNum = strIn Like "######-##-###"
End Function

???

Did I missed something?

Would that work?! I would definitely do that. I was thinking about having a backup method inside the function to for if someone types out a workticket. As our current system (which has limited accounts that are really expensive which is why I'm making this) has a way where we could type in one manually and it will still recognize it.

I was thinking of using len() to check if it is equal to 11 numbers (or 12 because the current system requires you to put a 0 in front of it if you manually type it) and if it is then it would add two dashes and pass it through the function again.
I was just thinking where to put it in the current function but if this formatting works then it would probably make it a lot easier.

I may try this but I am also gonna try and have a check for length after checking to see if its numbers.

[accesstos]

Would that work?!

Of course! You can check it!

But, this version I think is better:

Function IsTicketNum(strIn As String) As Boolean
    strIn = Replace(strIn, "-", "")
    strIn = Format(strIn, "######-##-###")
    IsTicketNum = (strIn Like "######-##-###")
End Function

Put it in a standard code module and try in the immediate window. Below are some tests that I've done:

?IsTicketNum("0123123-12-123")
True
?IsTicketNum("012-312-312-123")
True
?IsTicketNum("12312-312-123")
True
?IsTicketNum("12312-312123")
True
?IsTicketNum("0123123-12-123")
True
?IsTicketNum("000012312312123")
True
?IsTicketNum("1000012312312123")
False
?IsTicketNum("A12312312123")
False
?IsTicketNum("123")
False
?IsTicketNum("123-ABC")
False
?IsTicketNum("ABCEFGHIJKL")
False

Also, you can check it in a subroutine:

Sub TestTicket()
    Dim strIn As String

    strIn = InputBox("Input your ticket number", "Ticket check")
    If IsTicketNum(strIn) Then
        'Now strIn is in ######-##-### format.
        MsgBox "'" & strIn & "' is a valid ticket num!", vbInformation, "Ticket test"
    Else
        MsgBox "'" & strIn & "' is not a valid ticket num!", vbExclamation, "Ticket test"
    End If
End Sub

Cheers,
John