VBA generate a code

Question

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

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. — Vincent G
– Vincent G, Commented Apr 16, 2018 at 13:49
As Vincent pointed out, you're replacing char by char, thus after replacing 0 by 10, you replace again on same input. A solution would be to start from an empty string, and build it in the loop, using & — Jean Rostan
– Jean Rostan, Commented Apr 16, 2018 at 13:52

Vincent G · Accepted Answer · 2018-04-16 14:12:57Z

2

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.

answered Apr 16, 2018 at 14:12

Vincent G

3,1981 gold badge16 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

eberg Over a year ago

Wow... Thank you very much!!! Worked fine!!! I will explore another solution that will be more efficient (since my database is pretty huge), as you and others suggested. Again, thank you very much!!!

John Coleman · Accepted Answer · 2018-04-17 11:03:04Z

1

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.

edited Apr 17, 2018 at 11:03

answered Apr 16, 2018 at 14:22

John Coleman

52.1k7 gold badges59 silver badges127 bronze badges

1 Comment

eberg Over a year ago

Worked fine and, apparently, it is more efficient than what I had done. Thank you for your elegant solution!!

Collectives™ on Stack Overflow

VBA generate a code

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related