asked on

Convert numeric code with VBA

I need to change a numeric value into a string based on a predefined code. Currently, I convert the numeric value into string. Check and replace the numeric value into corresponding data. Just wonder whether there is any better way to handle this ? Tks

21 -> AX
eg.
1 = X
2 = A
3 = G

Martin Liss

Usage: MsgBox ConvertToString(21)

Function ConvertToString(strValue As String) As String

Const Letters = "XAG"
Dim lngDigit As Long

For lngDigit = 1 To Len(strValue)
    ConvertToString = ConvertToString & Mid$(Letters, Mid$(strValue, lngDigit, 1), 1)
Next
End Function

Open in new window

SOLUTION

Pratik Makwana

membership

This solution is only available to members.

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

Start Free Trial

SOLUTION

ChloesDad

membership

This solution is only available to members.

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

Start Free Trial

AXISHK

ASKER

No sure how to recursively call the function, supposed Pricecode() array has been loaded with 0-9, Tks

MsgBox ConvertToString("512")

Private Function ConvertToString(strValue As String) As String

Dim lngDigit As Long

For lngDigit = 1 To Len(strValue)
ConvertToString = ConvertToString(PriceCode(Mid$(strValue, lngDigit, 1)))
Next

End Function

ASKER CERTIFIED SOLUTION

Martin Liss

membership

This solution is only available to members.

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

Start Free Trial

ChloesDad

Why would you need to recursively call this?

The input is a number and the output is a string.

In a function, the function name is also by default a local variable of the return type, so you don't need the Return x at the end of the function.