Routine to Create a New Revision

Posted on 2006-05-20
Last Modified: 2010-05-19
Hello Experts,

I'm coding in VB6.

Does anyone know of a function where I can create a new sequential Alpha revision/version after passing a current revision (imagine a document control system where document revisions are roled upon checking in a new doc version.) For example, say the current revision of a document is 'B' and the function would have to return the next revision which would be 'C'. That part is simple enogh. (I expect the ASC/CHR functions would be used as part of this function).

However, the tricky part comes in being able to handle when the current Rev is 'Z'. it needs to return the next revision as 'AA'. Then, subsequent revisions would be 'AB', 'AC', 'AD' ...etc. Then, once again on 'AZ, we need to return 'BA', 'BB', 'BC' etc. There should be no practical limit to the uppermost revision (e.g. a revision may exists as 'AAA' ). This will no doubt call for some type of recursion.

I've assigned a high point value to reflect the urgency of the request, which is high.

Thanks much in advance.
Question by:KentKirimli
    LVL 69

    Expert Comment

    by:Éric Moreau
    Hi KentKirimli,

    I have used this method a couple of years ago for Excel columns:

    ' *****************************************************************
    ' ***
    ' *** Retourne la colonne suivante dans Excel
    ' *** Eric Moreau 1998.09.14
    ' ***
    ' *****************************************************************
    Public Function NextNomColumn(ByVal pstrCol As String) As String
    Dim strFirst As String
    Dim strLast As String

        If Len(pstrCol) > 1 Then strFirst = Left$(pstrCol, 1)
        strLast = Right$(pstrCol, 1)
        If strLast = "Z" Then
            If strFirst = "" Then
                strFirst = "A"
                strFirst = Chr$(Asc(strFirst) + 1)
            End If
            strLast = "A"
            strLast = Chr$(Asc(strLast) + 1)
        End If
        NextNomColumn = strFirst & strLast
    End Function

    LVL 85

    Accepted Solution

    Here is a generic routine that can handle any size revision and is easily configurable with respect to the characters included and the order of those characters:

    Option Explicit

    Private Sub Command1_Click()
        Text1.Text = NextRevision(Text1.Text)
    End Sub

    Private Function NextRevision(Optional ByVal curRevision As String) As String
        Dim i As Integer
        Dim char As String
        Dim chars As String
        Dim index As Integer
        Dim charArray() As String
        ' modify the line below to include the characters you
        ' want in the sequence
        chars = "ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789"
        ' blank revision passed in, return first letter in sequence
        curRevision = Trim(UCase(curRevision))
        If curRevision = "" Then
            NextRevision = Left(chars, 1)
            Exit Function
        End If
        ' make sure current revision has only valid characters in it
        ' otherwise...return first letter in sequence
        For i = 1 To Len(curRevision)
            char = Mid(curRevision, i, 1)
            If InStr(chars, char) = 0 Then
                MsgBox "Returning: " & Left(chars, 1), vbCritical, "Invalid Starting Revision: " & curRevision
                NextRevision = Left(chars, 1)
                Exit Function
            End If
        Next i
        ' build a character array from the current revision
        ReDim charArray(Len(curRevision) - 1)
        For i = 1 To Len(curRevision)
            charArray(i - 1) = Mid(curRevision, i, 1)
        Next i
        ' see if the just the last letter needs to be "incremented"
        char = charArray(UBound(charArray))
        index = InStr(chars, char)
        If index < Len(chars) Then
            charArray(UBound(charArray)) = Mid(chars, index + 1, 1)
            NextRevision = Join(charArray, "")
            Exit Function
        End If
        ' last letter "rolled over"
        ' propagate the "carry over" as far as it needs to go
        charArray(UBound(charArray)) = Left(chars, 1)
        For i = UBound(charArray) - 1 To 0 Step -1
            char = charArray(i)
            index = InStr(chars, char)
            If index < Len(chars) Then
                index = index + 1
                charArray(i) = Mid(chars, index, 1)
                NextRevision = Join(charArray, "")
                Exit Function
                charArray(i) = Left(chars, 1)
            End If
        Next i
        ' "carry over" propagated all the way thru sequence
        ' add first letter of sequence to the beginning of the revision
        NextRevision = Left(chars, 1) & Join(charArray, "")
    End Function

    Author Comment

    Thanks Idle Mind, thats exactly what I was looking for.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Introduction In a recent article ( for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
    Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
    Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

    731 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now