Site icon @Poremsky.com

Create sequential numbers or random character keywords

I receive many requests for help in adding numbers and codes to Outlook subject lines. Some users want to sequential numbers, such as you'd use with invoices, others want to use random numbers or random characters for tracking purposes.

These code samples are basic VB and will work in many situations, in any Office application, or in VB (if anyone still uses VB outside of Office applications).

My examples display the number or random characters in a message box, but you can use it anywhere you'd use a string or variable.

Sequential numbering

With sequential numbering you have two options: save the number in the registry or save the number in a text file. Saving the number in a text file allows you to use it on multiple machines, or shared with other users. Storing it in the registry means you don't have a text file laying around.

This code sample adds the value to the registry, at HKEY_CURRENT_USER\Software\VB and VBA Program Settings\Outlook\Invoices (You can change the sAppName, sSection, and sKey names in the code, if desired.)

 Sub AddInvoiceNumber()

 Dim sAppName As String
 Dim sSection As String
 Dim sKey As String
 Dim lRegValue As Long
 Dim lFormValue As Long
 Dim iDefault As Integer
 sAppName = "Outlook"
 sSection = "Invoices"
 sKey = "Current Invoice Number"

 ' The default starting number.
     iDefault = 101 ' adjust as needed

 ' Get stored registry value, if any.
     lRegValue = GetSetting(sAppName, sSection, sKey, iDefault)

 ' If the result is 0, set to default value.
     If lRegValue = 0 Then lRegValue = iDefault

 ' Increment and update invoice number.
     SaveSetting sAppName, sSection, sKey, lRegValue + 1

' do whatever with the random number
 MsgBox  CStr(lRegValue)

 End Sub

Random numbering

This example generates a random number between 1 and 10,000. Change the High and Low numbers to restrict the random number to a specific number range.

 Sub UseRandomNumber()

   intHigh = 10000
   intLow = 1

 Randomize
   intNumber = Int((intHigh - intLow + 1) * Rnd + intLow)

' do whatever with the random number
 MsgBox intNumber 
 End Sub

Random alphanumeric characters

This code sample generates a 10 character alphanumeric code.

To create longer (or shorter) codes, change the value in GetRandom(10).

This function generates codes using upper and lowercase letters and numbers. Non-alphanumeric characters are replaced with random uppercase letters or numbers. (See the ASCII table for the character codes.)

Remove the two If iRand > lines to include those characters.


Sub UseRandomCharacters()
' change 10 to another number for longer key
' do whatever with the random number
 MsgBox GetRandom(10)
 End Sub

 Function GetRandom(Count)
 Randomize
 
 For i = 1 To Count
    iRand = Int((122 - 48 + 1) * Rnd + 48)
   'remove non-alphanumeric characters, replace with uppercase or numbers
     If iRand > 90 And iRand < 97 Then iRand = Int((90 - 65 + 1) * Rnd + 65)
     If iRand > 57 And iRand < 65 Then iRand = Int((57 - 48 + 1) * Rnd + 48)
    GetRandom = GetRandom &amp; Chr(iRand)
 Next
 End Function

More Information

Create a serial number (VBOffice.net)
Macro to Increment Invoice Number to New Form Document (MSKB) This macro works in all versions of Office. It's the basis for the AddInvoiceNumber code sample above.

Exit mobile version