Function to create Unique Strings 1

[GHinCali]

I have a need for a routine to generate unique alphanumeric ids using the characters 0 thru 9 and A through Z as the basis, which are stored in a constant,
sIDContents = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ".

The resulting ids would be much like the column headers in Excel (i.e. A, B, C, ..., AA, AB, AC, etc) starting at 1 character length and continuing through X character length, where X is passed in to the routine (i.e if x=3, first id would be A, last id would be 999)

Any ideas?

Thanks in advance,
Gary

 

[SkipVought]

Gary,

Look at the ASCII values of characters A - Z

For instance A is 65

You have 36 "digits" from 0 to Z instead of 2 from 0 to 1 in BASE 2 (BINARY) or 10 from 0 to 9 in BASE 10 (DECIMAL) or 16 from 0 to F in BASE 16 (HEXADECIMAL)

You can use the INT function and MOD function to manipulate the sequence of numbers (BASE 10) used to create the BASE 36 "Number"

As a programmer you should understand these mathematical principles.

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[TonyJollans]

Hi Gary,

Incrementing an ID like this can be done in a couple of ways but I don't quite understand your sequence.

Why is the first ID "A" and the last "999" rather than the first being "1" (or "0"?) and the last "ZZZ" which would make more sense with the string of characters you suggest.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[GHinCali]

Thanks for the thought, Skip. Converting to ascii, creating ids, converting back, etc obviously could be done, but I think there must be a simpler method.

Tony:
I just started with A as the example, since that's the first column in Excel. The actual first id would be 0 as you suggest.

I would appreciate your describing the 'couple of ways' to increment the ID. I am hitting a mental blank wall!

Thanks,
Gary

 

[SkipVought]

What's the business case for this requirement?

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[GHinCali]

Skip;

I am writing a testing program to test another program, which is the 'engine' for a financial analysis package.

Part of the program I am writing adds records to a database (which could be a production database, so I do not want to touch live data) then exercises the 'engines' routines which add, modify and delete records.

The way I thought to do this, is retrieve the actual ids already in use in the db (which I have to do anyway, to test the data retrieval methods), then generate the required number of records I need to test with (which vary in quantity, depending on some internal specifications), add the generated records using the 'engines' various adding methods, modify the same records, then finally delete them.

The function I originally posted the question about will be used to generate the IDs for the records I will add to the db.

Gary

 

[PHV]

To have the 36 figures of your base36 system in an array:
a = Split(StrConv(sIDContents, vbUnicode), Chr(0))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[TonyJollans]

Hi Gary,

I posted something very similar somewhere the other day. Here's one way ..

[blue]Function AlphIncr(Alph As String, MaxLen As Integer) As String

    Dim Pos As Integer, CharVal As Integer
    Dim Chars As String
    Dim Carry As Boolean
    
    Chars = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"
    
    AlphIncr = ""
    Carry = True
    
    For Pos = Len(Alph) To 1 Step -1
        
        CharVal = InStr(Chars, Mid(Alph, Pos, 1))
        If CharVal = 0 Then ' Invalid character in string
            AlphIncr = ""
            Exit Function
        End If
        
        CharVal = CharVal - Carry
        Carry = CharVal > Len(Chars)
        If Carry Then CharVal = 1
        
        AlphIncr = Mid$(Chars, CharVal, 1) & AlphIncr
    
    Next
    
    If Carry Then
        AlphIncr = 1 & AlphIncr
        If Len(AlphIncr) > MaxLen Then ' Result too long
            AlphIncr = ""
            Exit Function
        End If
    End If
    
End Function

Sub TestIt()
    MsgBox AlphIncr("ZZZ", 3)
End Sub[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[GHinCali]

Tony:

That was exactly what I needed! You saved me quite a bit of time and effort! Thank you.

The only question I have concerns the calculation using the boolean variable, Carry. If Carry is True, is Carry = -1 always true? And if Carry is False, is Carry = 0 always true? I thought that a boolean value is 0 for false, anything else is true.

Gary

 

[TonyJollans]

Hi Gary,

Yes, I shouldn't really code like that

If you set a boolean to False it has the value zero.
If you set a boolean to True it has the value -1.

If you check a boolean for False it returns "Yes I am False" if it is zero.
If you check a boolean for True it returns "Yes I am True" for any value other than zero.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[GHinCali]

Tony:

Nice little trick! I'll put it in my own bag of tricks!

Thanks again.

Gary