CODEWITHSUNDEEP
excelvba
eberg
eberg

Reputation: 23

VBA generate a code

there. I made this code that replaces a character for two number (e.g. 0 = 10; 1 = 11; 2 = 12; ...) and everything works fine except for the first element (the zero element). So, if I put "010a4" string on cell A1 and use my formula "=GENERATECODE(A1)", my expected return value is "1011102014" but I've got a "110111102014" string. So, only zero value occur this error and I can't figured out why. Any thoughts?

My code:

    Function GENERATECODE(Code As String)
    Dim A As String
    Dim B As String
    Dim i As Integer
    Const AccChars = "0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ"
    Const RegChars = "1011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071"
    For i = 1 To Len(AccChars)
        A = Mid(AccChars, i, 1)
        B = Mid(RegChars, 2 * i - 1, 2)
        Code = Replace(Code, A, B)
    Next
    GENERATECODE = Code
End Function

Upvotes: 2

Views: 150

Answers (2)

John Coleman
John Coleman

Reputation: 52008

In addition to being incorrect, your current code is inefficient since it involves scanning over the code string multiple times instead of just once. Simply scan over the string once, gathering the substitutions into an array which is joined at the end:

Function GENERATECODE(Code As String) As String
    Dim codes As Variant
    Dim i As Long, n As Long
    Dim c As String
    n = Len(Code)
    ReDim codes(1 To n)
    For i = 1 To n
        c = Mid(Code, i, 1)
        Select Case c
            Case "0" To "9":
                codes(i) = "1" & c
            Case "a" To "z":
                codes(i) = Asc(c) - 77
            Case "A" To "Z":
                codes(i) = Asc(c) - 19
            Case Else:
                codes(i) = "??"
        End Select
    Next i
    GENERATECODE = Join(codes, "")
End Function

Example:

?generatecode("010a4")
1011102014

The point of the two offsets is that you want "a" to map to 20 and "A" to map to 46. Note Asc("a") - 77 = 97 - 77 and Asc("A") - 19 = 65-19 = 46.

Upvotes: 1

Vincent G
Vincent G

Reputation: 3188

Your problem is that your code first change each 0 to 10 then each 1 to 11. So each 0 give you 10 then 110.

If you want to keep the same kind of algorithm (which might not be a good choice), you need to change AccChars and RegChars so that a character is never replaced by a string that can give a character found later on the AccChars String. In your case just replace Const AccChars = "012 ... per Const AccChars = "102 ... and Const RegChars = "101112 ... per Const RegChars = "111012 ...

But it might be better to change your algorithm altogether. I would first suggest to not use in place editing of the string, but rather to use 2 Strings.

Upvotes: 2

Related Questions