Forum Discussion
How to create a alphanumeric sequence on vba
Hi, I need to create a code that are composed by 2 digits and I want to use all the numbers (0-9) and letters (A-Z). Could some help me to create the vba code to do this please? Ex: after th...
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
If you have Microsoft 365 or Office 2021, enter the following formula in a cell. The 1294 cells below it should be empty.
=BASE(SEQUENCE(36*36-1),36,2)
If you have an older version, enter the formula =BASE(ROW(),36,2) in a cell in row 1, then fill down to row 1295.
The problem is that the codes must be like the print below.
I try to use the functions below but it didn't work...
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
prefixo = Left(prevUDI, 5)
nextUDI = prefixo & fBase34(Right(prevUDI, 2) + 1)
End Function
The function genUDI works fine, the problem is the other one...
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- HansVogelaar
I was using the vba code that you suggested but when the code comes to 3J it is jumpping to 3L, 3N to 3Q and 3Y or 3Z to #value. Could you help me please?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
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
LoopIf Len(fBase34) = 1 Then
fBase34 = "0" + fBase34
End IfEnd Function
Function genUDI(ByRef decNum As Long, prefixo As String) As String
'Gera UDI com base em um numero decimalgenUDI = prefixo & fBase34(decNum)
End Function
Function nextUDI(prevUDI As String) As String
'Gera UDI com base no número anteriorDim 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 FunctionI use your vba code and it works fine but when the code is 3J, it jumps to 3L (i think that it is because i don't use the leter I), the 3N jumps to 3Q (I think that it is because i don't use O as well) and 3Z doesn't goes to 40... could you help me please. The vba code that i'm using is:
Sub removeAllDatamatrix()
For Each symbolShape In ActiveSheet.Shapes
If Left(symbolShape.Name, 1) = "5" Then
symbolShape.Delete
End If
Next symbolShapeEnd Sub
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
LoopIf Len(fBase34) = 1 Then
fBase34 = "0" + fBase34
End IfEnd Function
Function ConvertBase34To10(Base34Number As String) As Long
Dim X As Long, Total As Long, Digit As StringFor X = Len(Base34Number) To 1 Step -1
Digit = UCase(Mid(Base34Number, X, 1))
ConvertBase34To10 = ConvertBase34To10 + IIf(IsNumeric(Digit), Digit, Asc(Digit) - 55) * 34 ^ (Len(Base34Number) - X)
NextEnd Function
Function genUDI(ByRef decNum As Long, prefixo As String) As String
'Gera UDI com base em um numero decimalgenUDI = prefixo & fBase34(decNum)
End Function
Function nextUDI(prevUDI As String) As String
'Gera UDI com base no número anteriorDim 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
