asked on

How to write UDF for changing A,B,C to 1,2,3?

I want to see this as a UDF to help me learn how to build UDFs.

There are two parts of the UDF.
Check to see if value in the field is A,B,C etc. The field will only have one character.
Upper can be used to convert lowercase to uppercase.
If the Code for the character is 65 to 90, then it is uppercase alphabetic.
If not, then leave the character as is.

Then, convert to a number using the formula:
=CODE(B1)-64
so A = 1, B=2, C=3, etc.

Ryan Chong

do you mean formula such as this?

=IF(AND(CODE(B1)>=65, CODE(B1)<=90), CODE(B1)-64, B1)

Open in new window

29021045.xlsx

ASKER CERTIFIED SOLUTION

Joe Howard

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Alex Campbell

ASKER

Great!! Just what I was looking for it. Now, I try variations and how to break it and how to fix it 8^)

Bill Prew

See if this is helpful. Since you are using this as a learning process, I tried to keep it simple and added comments to explain the logic. I didn't try to use the least code or clever methods, just went for obvious for education. Hope this helps, questions welcome.

' Always good to require variables to be defined with DIM statements,
' will save you some typo errors in building UDFs
Option Explicit


' Small test routine to make sure the UDF performs as desired
Sub TestIt()
    Debug.Print SampleUDF("A")
    Debug.Print SampleUDF("Z")
    Debug.Print SampleUDF("a")
    Debug.Print SampleUDF("z")
    Debug.Print SampleUDF("AB")
    Debug.Print SampleUDF("")
    Debug.Print SampleUDF("1")
    Debug.Print SampleUDF("$")
End Sub


' Actual UDF, specify input parm(s) and return value type
Function SampleUDF(strIn As String) As Long

    ' Local variable definitions
    Dim strLetter As String
    
    ' Always good to edit input parms. Make sure it's only one character,
    ' if not return a value that indicates an error - I used -1 for this example
    If Len(strIn) <> 1 Then
        SampleUDF = -1
        Exit Function
    End If
    
    ' Convert to upper case if needed, save in local variable    
    strLetter = UCase(strIn)
    
    ' Edit to make sure it's between A and Z,
    ' if not return a value that indicates an error
    If strLetter < "A" Or strLetter > "Z" Then
        SampleUDF = -1
        Exit Function
    End If
    
    ' Calculate the numeric value to return, A=1, B=2, C=3, ...
    ' Place into the varianle with the name of the function so the calling
    ' code gets the value the function calculated
    SampleUDF = Asc(strLetter) - Asc("A") + 1

End Function

Open in new window

»bp