Forum Discussion
How to create a alphanumeric sequence on vba
- Dec 27, 2022
New version:
Function nextUDI(prevUDI As String) As String 'Gera UDI com base no número anterior Dim prefixo As String Dim n As Long Dim prevVal As Long Const strAlphabet = "0123456789ABCDEFGHJKLMNPQRSTUVWXYZ" n = Len(prevUDI) prevVal = 34 * (InStr(strAlphabet, Mid(prevUDI, n - 1, 1)) - 1) + InStr(strAlphabet, Right(prevUDI, 1)) - 1 prefixo = Left(prevUDI, n - 2) nextUDI = prefixo & fBase34(prevVal + 1) End Function
Function nextUDI(prevUDI As String) As String
'Gera UDI com base no número anterior
Dim prefixo As String
Dim prevVal As Long
prevVal = Application.Decimal(Right(prevUDI, 2), 34)
prefixo = Left(prevUDI, Len(prevUDI) - 2)
nextUDI = prefixo & fBase34(prevVal + 1)
End Function
I was using the vba code that you suggested and it was going great but the code is jumping from the 3J to 3L (I think that it is becasue I don't use "I"), from 3N to 3Q (I think that it is because I don't use "O") and from 3Y and 3Z to #value... could you help me please?
The vba code that I'm using is:
Function fBase34(ByRef lngNumToConvert As Long) As String
'Converte base 10 para 34 (base 36 sem I e O)
Dim strAlphabet As String
strAlphabet = "0123456789ABCDEFGHJKLMNPQRSTUVWXYZ"
If lngNumToConvert = 0 Then
Base34Encode = "0"
Exit Function
End If
fBase36Encode = vbNullString
Do While lngNumToConvert <> 0
fBase34 = Mid(strAlphabet, lngNumToConvert Mod 34 + 1, 1) & fBase34
lngNumToConvert = lngNumToConvert \ 34
Loop
If Len(fBase34) = 1 Then
fBase34 = "0" + fBase34
End If
End Function
Function genUDI(ByRef decNum As Long, prefixo As String) As String
'Gera UDI com base em um numero decimal
genUDI = prefixo & fBase34(decNum)
End Function
Function nextUDI(prevUDI As String) As String
'Gera UDI com base no número anterior
Dim prefixo As String
Dim prevVal As Long
prevVal = Application.Decimal(Right(prevUDI, 2), 34)
prefixo = Left(prevUDI, Len(prevUDI) - 2)
nextUDI = prefixo & fBase34(prevVal + 1)
End Function