Excel User defined function - Field matches specific criteria

Question

New to vba - I am essentially try to make this very long if statement into a user defined function. The function would check if the field is the correct format of 2 letters, 2 numbers, an underscore, 1 letter, 2 numbers.

=IF(AND(AND(CODE(MID(A2,1,1))>64,CODE(MID(A2,1,1))<91),AND(CODE(MID(A2,2,1))>64,CODE(MID(A2,2,1))<91),AND(CODE(MID(A2,3,1))>47,CODE(MID(A2,3,1))<57),AND(CODE(MID(A2,4,1))>47,CODE(MID(A2,4,1))<57),CODE(MID(A2,5,1))=95,AND(CODE(MID(A2,6,1))>64,CODE(MID(A2,6,1))<91),AND(CODE(MID(A2,7,1))>47,CODE(MID(A2,7,1))<57),AND(CODE(MID(A2,8,1))>47,CODE(MID(A2,8,1))<57),"Good","Bad Syntax")

Example of A2 would be AA01_A05

Code below - winged it in the end. Any help would be appreciated.

Function bincheck(strValue As String) As Boolean

Dim AislePos As Integer, Rackno As Integer, Udr As Integer, ShelfPos As     Integer, BinNo As Integer
Dim TrueAisle As Boolean, TrueRack As Boolean, TrueUdr As Boolean, TrueShelf As Boolean, TrueBin As Boolean

For AislePos = 1 To 2
    Select Case Asc(Mid(strValue, AislePos, 1))
        Case 65 To 90
            TrueAisle = True
        Case Else
            TrueAisle = False
            Exit For
    End Select
Next
For Rackno = 3 To 4
    Select Case Asc(Mid(strValue, Rackno, 1))
        Case 48 To 56
            TrueRack = True
        Case Else
            TrueRack = False
            Exit For
    End Select
Next
 For Udr = 5 To 5
    Select Case Asc(Mid(strValue, Udr, 1))
        Case Is = 95
            TrueUdr = True
        Case Else
            TrueUdr = False
            Exit For
    End Select
Next
For ShelfPos = 6 To 6
    Select Case Asc(Mid(strValue, ShelfPos, 1))
        Case 65 To 90
            TrueShelf = True
        Case Else
            TrueShelf = False
            Exit For
    End Select
Next
 For BinNo = 7 To 8
    Select Case Asc(Mid(strValue, BinNo, 1))
        Case 48 To 56
            TrueBin = True
        Case Else
            TrueBin = False
            Exit For
    End Select
Next
Select Case bincheck
     Case TrueRack = True And TrueAisle = True And TrueUdr = True And TrueShelf = True And TrueBin = True
bincheck = "Good"
    Case Else
bincheck = "Bad"
End Select
End Function

While it is certainly a great achievement to have written your first own VBA function, I assume that you have a reason for posting this code. Is there a question I am missing or do you encounter an error / bug / problem with your code you are not able to resolve? How may we assist you with your code? — Ralph, Mar 12 '16 at 23:48
Ah sorry, i guess i should have mentioned that in the initial post. I guess im having a hard time (function not working) on how to wrap it up at the end. I think I have the pieces right but not the part that confirms that all the conditions were met. Additionally, is there a more efficient way to do this? — belleater, Mar 12 '16 at 23:58
Have you considered jumping into a Regular Expression (aka RegEx) statement? See [How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops](http://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops/22542835?s=1|0.0000#22542835) — , Mar 13 '16 at 00:14
Always looking for ways to do things better! Thanks for the link! — belleater, Mar 13 '16 at 00:38

score 1 · Answer 1 · 2016-03-13T00:41:40.883

I seems that the last Select Case statement,

Select Case bincheck
     Case TrueRack = True And TrueAisle = True And TrueUdr = True And TrueShelf = True And TrueBin = True
bincheck = "Good"
    Case Else
bincheck = "Bad"
End Select

... would be better as,

bincheck = TrueRack And TrueAisle And TrueUdr And TrueShelf And TrueBin

You do not need to check if a boolean equals true. It is already either true or false.

Checking the length of the string passed into the function might be best as an overall 'wrapping' If condition. If it is not met, then none of the booleans get set past their default declaration value of False (i.e. when they are declared as boolean, they start out life as False).

Function bincheck(strValue As String) As Boolean

    Dim pos As Integer
    Dim TrueAisle As Boolean, TrueRack As Boolean, TrueUdr As Boolean, TrueShelf As Boolean, TrueBin As Boolean

    If Len(strValue) = 8 Then
        For pos = 1 To 2
            Select Case Asc(Mid(strValue, pos , 1))
                Case 65 To 90
                    TrueAisle = True
                Case Else
                    TrueAisle = False
                    Exit For
            End Select
        Next
        For pos = 3 To 4
            Select Case Asc(Mid(strValue, pos , 1))
                Case 48 To 56
                    TrueRack = True
                Case Else
                    TrueRack = False
                    Exit For
            End Select
        Next
         For pos = 5 To 5
            Select Case Asc(Mid(strValue, pos , 1))
                Case Is = 95
                    TrueUdr = True
                Case Else
                    TrueUdr = False
                    Exit For
            End Select
        Next
        For pos = 6 To 6
            Select Case Asc(Mid(strValue, pos , 1))
                Case 65 To 90
                    TrueShelf = True
                Case Else
                    TrueShelf = False
                    Exit For
            End Select
        Next
        For pos = 7 To 8
            Select Case Asc(Mid(strValue, pos , 1))
                Case 48 To 56
                    TrueBin = True
                Case Else
                    TrueBin = False
                    Exit For
            End Select
        Next
    End If

    bincheck = TrueRack And TrueAisle And TrueUdr And TrueShelf And TrueBin

End Function

After a reread, the second suggestion does not seem to be necessary. I was not taking the `Exit For` into account properly. That should solve the problem of overwriting the boolean on the second pass just as well as the modification suggested above. — , Mar 13 '16 at 00:20
Worked! Thank you! One add on question if you don't mind, if the field has less than 8 characters, the function returns a #VALUE error obviously because its looking for a defined amount of characters in each section. Is there a way I put some type of IFERROR functionality so false and errors values are lumped together as FALSE? — belleater, Mar 13 '16 at 00:29
I've changed the second half of my response to reflect your question on length. — , Mar 13 '16 at 00:42

score 1 · Answer 2 · answered Mar 13 '16 at 00:11

If you want your function to return text such as "Good" or "Bad" then you first line must be

Function bincheck(strValue As String) As String

If you want to return a binary value (as your variable name suggests) then you can leave the first line as is

Function bincheck(strValue As String) As Boolean

But the variable bincheck at the end would have to be adjusted to return a binary value such as True or False:

bincheck = True
    Case Else
bincheck = False

But with the function's name being bincheck this might be irritating and I'd suggest an alternative name for the function such as:

Function IsTheStringFormattedCorrectly(strValue As String) As Boolean

Then a return value of True or False might be better to understand.

Thanks for the tips and suggestion- greatly appreciated! – belleater Mar 13 '16 at 00:16 — belleater, Mar 13 '16 at 00:16

chris neilsen · Accepted Answer · 2016-03-13T01:03:05.407

1

The whole thing can be reduced to a Like statement

Function bincheck(strValue As String) As Boolean
     bincheck = strValue LIKE "[A-Z][A-Z]##_[A-Z]##"
End Function

edited Mar 13 '16 at 01:03

answered Mar 13 '16 at 00:53

chris neilsen

52,446
10
84
123

Hah, I feel a bit foolish now as I guess I took the scenic route. Thanks! – belleater Mar 13 '16 at 01:37
@belleater You learned a lot by programming this function so it was certainly not a waste of time. Also, your function has a lot more potential. You checked each part of the string allowing for feed back such as: "The first two characters are supposed to be number. Please fix that. The rest of your string is correct." or "You missed to place a _ in the middle. The rest seems fine.", etc. Of course you would need to refine your code a lot to achieve that. But in the end you'd be able to give better feedback than just "Good" or "Bad". – Ralph Mar 13 '16 at 12:32

Excel User defined function - Field matches specific criteria

3 Answers3