Monday, July 29, 2013

When part numbers aren't all numbers...

Context

One of the sites I subscribe to is Excel Ribbon Tips (excelribbon.tips.net), authored by Alan Wyatt.  The information is useful for Excel users and VBA/Excel programmers.  He also provides tips in Word...I'm just not in that mode right now.

In most of his weekly tip sheets there is a call for help from a reader, someone who has a problem they are trying to solve.  The following is the one of the answers I provided.

The Question

One of Alan's subscribers had asked for help in identifying part numbers that have alphabetic characters in them.  

The Answer(s)

One thought, the one I submitted, was to use a function to identify an entry that had one on or more of the 26 letters we have in the English alphabet.  It could be used either as part of a Conditional Formatting statement, in a column to flag the cell next to it, or  called in a VBA subroutine or function.

Function HasAlpha(ByRef PartNo As String)
    Dim Ch_Ctr
    HasAlpha = False
    For Ch_Ctr = 1 To Len(PartNo)
        If InStr(1, "ABCDEFGHIJKLMNOPQRSTUVWXYZ", UCase(Mid(PartNo, Ch_Ctr, 1))) > 0 Then
            HasAlpha = True
            Exit Function
        End If
    Next Ch_Ctr
End Function

An alternative would be to make certain the part number had only allowable characters, meaning it could now be more specific.

Function IsValidPartNumberString(ByRef PartNo As String) as Boolean
    Dim Ch_Ctr
    IsValidPartNumberString= True
    For Ch_Ctr = 1 To Len(PartNo)
        If InStr(1, "1234567890 -/()", UCase(Mid(PartNo, Ch_Ctr, 1))) = 0 Then
            HasAlpha = False
            Exit Function
        End If
    Next Ch_Ctr

End Function

For other ideas you will need to visit Allen's site.