Random Number between 00000001-9999999 that doesn't repeat past numbers?

[WSBirch]

Good morning,
We have a function coming up where we will need to generate a barcode based on a 7-digit value. We need each value to be unique. It doesn't particularly have to be random, but if we use a value, we can't use it again. Office 365 for Windows. Some VBA is totally fine.

Is there any way to display a value between 0000001-9999999, only one value at a time, then use the 3 of 9 font to convert it to a barcode, then print that barcode to a particular network printer, then the value changes and never uses the older values ever again? I'm thinking it makes the most sense to instead of produce a random value, to just make sequential. Then obviously never use a value less than itself because it would likely have been used.

For example, you have 0000001, click the "print" button, it sends the 0000001 as a 3 of 9 barcode to print from the printer, then the value becomes 0000002 AND saves the file - so that the next time you hit print, it prints that value instead. Also, now that the file is saved, anyone else that opens the file in the future won't be able to go back in and use old numbers. What's the best way to go about this kind of thing?

Thank you!

 

[BSALV]

i don't know the "3 of 9 barcode" but suppose you start at "000..01" and you print 5 codes, you remember that you stopped at 5. Is that enough or do you really want those 9,999,999 barcodes random & unique generated ?
A worksheet has +1,000,000 rows, so that 'll be 10 entire rows in the worst case and without personal mistakes

 

[BSALV]

i went for random (was more exciting) and with a table named "TBL_History"

Map1
	A	B	C	D	E	F	G
1	date	numbers			wanted numbers	10
2	8/03/2022 17:40	5139643 7439328 0381668 0838878 9018433 9500791 3866628 8248453 1546968 9052156
3
Blad5

Sub Random()
     With Range("TBL_History").ListObject                       'table with all your "historical" barcodes
          Select Case .ListRows.Count                           'make an array "sp0" with all your individual used barcodes
               Case 0: sp0 = Split("")
               Case 1: sp0 = Split(.ListColumns("Numbers").DataBodyRange.Cells(1, 1).Value, vbLf)
               Case Else: sp0 = Split(Join(Application.Transpose(.ListColumns("Numbers").DataBodyRange.Value), vbLf), vbLf)
          End Select

          a = WorksheetFunction.RandArray(100000, , 1, 9999999, 1)   'random 100,000 integer numbers between 1 and 9,999,999
          s = "": sp1 = Split(s, vbLf)                          'aux. string
          For i = 1 To UBound(a)                                'loop through all those random numbers
               barcode = Format(a(i, 1), "0000000")
               j1 = UBound(Filter(sp0, barcode, 1, vbTextCompare))     'already used in the history
               j2 = UBound(Filter(sp1, barcode, 1, vbTextCompare))     'already used in this session
               If j1 + j2 = -2 Then                             'no !!
                    s = s & vbLf & barcode                      'add to this session
               End If
               sp1 = Split(s, vbLf)                             'split barcodes in this session
               If UBound(sp1) = Range("F1").Value Then Exit For     'enough barcodes found in this session
          Next

          sp1 = Split(Mid(s, 2), vbLf)                          'delete leading vblf
          If UBound(sp1) + 1 = Range("F1").Value Then           'if found enough barcodes
               .ListRows.Add.Range.Range("A1").Resize(, 2).Value = Array(Now, s)     'add to table
          Else
               MsgBox "no luck"                                 'not enough
          End If
     End With
End Sub

 

[WSBirch]

i don't know the "3 of 9 barcode" but suppose you start at "000..01" and you print 5 codes, you remember that you stopped at 5. Is that enough or do you really want those 9,999,999 barcodes random & unique generated ?
A worksheet has +1,000,000 rows, so that 'll be 10 entire rows in the worst case and without personal mistakes

The 3 of 9 font is just a font that is in Office 365

If you have a value and surround it in asterisks then change that cell's font to the "3 of 9 barcode" it'll convert the cell into a barcode like the below image

The VBA for random numbers does sound more exciting! I'll have to play with that and see how it works for us, thank you so much! I'm going to mark it solved for now, but any other suggestions - even less exciting, are more than welcome. Thank you!

 

[BSALV]

if you change the format of the columns D:M into 3 of 9, then you have your barcodes immediately

barcodes.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	10		date	barcode1	barcode2	barcode3	barcode4	barcode5	barcode6	barcode7	barcode8	barcode9	barcode10
2			8/03/2022 19:26	*0945936*	*6562222*	*5602390*	*3473889*	*8088123*	*7419993*	*0389608*	*7147011*	*1073425*	*2330350*
3			8/03/2022 19:28	*7379004*	*6904071*	*7212447*	*0475260*	*4412843*	*5725771*	*5404017*	*8882385*	*7514337*	*5101967*
4			8/03/2022 19:28	*3729201*	*8482402*	*5629776*	*7941187*	*9058219*	*9617557*	*2164399*	*7019730*	*7791200*	*5115163*
5
Blad1

Sub Random()
     With Range("TBL_History").ListObject                       'table with all your "historical" barcodes
          Select Case .ListRows.Count                           'make an array "sp0" with all your individual used barcodes
               Case 0: sp0 = Split("")
                 Case Else: sp0 = Split(Replace(WorksheetFunction.TextJoin(vbLf, 1, .DataBodyRange.Offset(, 1).Resize(.ListColumns.Count - 1)), "*", ""), vbLf)
          End Select

          a = WorksheetFunction.RandArray(100000, , 1, 9999999, 1)   'random 100,000 integer numbers between 1 and 9,999,999
          s = "": sp1 = Split(s, vbLf)                          'aux. string
          For i = 1 To UBound(a)                                'loop through all those random numbers
               barcode = Format(a(i, 1), "0000000")
               j1 = UBound(Filter(sp0, barcode, 1, vbTextCompare))     'already used in the history
               j2 = UBound(Filter(sp1, barcode, 1, vbTextCompare))     'already used in this session
               If j1 + j2 = -2 Then                             'no !!
                    s = s & vbLf & barcode                      'add to this session
               End If
               sp1 = Split(s, vbLf)                             'split barcodes in this session
               If UBound(sp1) >= Range("A1").Value Then Exit For     'enough barcodes found in this session
          Next

          sp1 = Split(Mid(s, 2), vbLf)                          'delete leading vblf
          sp2 = Split("*" & Replace(Mid(s, 2), vbLf, "*" & vbLf & "*") & "*", vbLf)
          If UBound(sp1) + 1 >= Range("A1").Value Then           'if found enough barcodes
               With .ListRows.Add.Range.Range("A1")
                .Value = Now     'add timestamp to table
               .Offset(, 1).Resize(, UBound(sp2) + 1).Value = sp2  'add to table
          End With
          Else
               MsgBox "no luck"                                 'not enough
          End If
     End With
End Sub