1 Reply Latest reply on Jan 2, 2019 5:55 AM by Pacquelet Yoan

    Sharing: Procedure to increment revision letter

    Steve Soeder

      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