Steven Soeder

Sharing: Procedure to increment revision letter

Discussion created by Steven Soeder on Oct 17, 2014
Latest reply on Jan 2, 2019 by Pacquelet Yoan

Just wanted to share this little function for upping rev letter.

I created it as part of my attempt to automate much of our ECRN process and thought it might be useful to others.


I find it pretty handy, such as with a RevisionTableAnnotation object you can use it like:

REVTABLE.AddRevision (NextLetter(REVISION))


EDIT:  I updated code to fix a problem with transitioning from AAY to ABA.

EDIT 2:  I posted for peer review on CodeReview and received a much simpler method of doing this, so I've edited the code to this simplified version.

Link: vba - Next revision letter - Code Review Stack Exchange

Option Explicit
'***NOTE: _
    This module requires the following references: _

Public Function NextRevLetter(currentRev As String) As String
    ' Alphabet, skipping I O Q S X Z
    Const alphabet As String = "ABCDEFGHJKLMNPRTUVWY"
    Dim i As Long
    Dim digit As String
    NextRevLetter = currentRev
    For i = Len(NextRevLetter) To 1 Step -1
        digit = Mid$(NextRevLetter, i, 1)
        ' Increment digit: "A"->"B", "B"->"C", ..., "H"->"J", ..., "Y"->"".
        ' Any other garbage maps to "A".
        digit = Mid$(alphabet, InStr(alphabet, digit) + 1, 1)
        If Len(digit) = 0 Then
            ' "Y"->"" case. Reset to "A", then carry.
            Mid$(NextRevLetter, i, 1) = "A"
            Mid$(NextRevLetter, i, 1) = digit
            Exit Function
        End If
    ' Carry.
    NextRevLetter = "A" & NextRevLetter
End Function


Message was edited by: Steve Soeder