Incrementing Letters: A -> B , Z -> AA , AB -> AC... Best Way? 3

[CubeE101]

What is the BEST way to increment a letter string... ONLY LETTERS, no numbers or special characters...

A increments to B
B -> C
...
Z -> AA
AA -> AB
...
AZ -> BA
...
and so on...

the only way I know how is going through each letter in the string using the ASC(x) to eval each character from right to left, and short circuit the loop if A single letter can be changed...
like this...

myRev = "A"
mvRev = Revise(myRev)

Function Revise(Rev As String)
  Dim I As Integer, M As Integer, Temp As String, Temp2 As String
  Dim A As Integer, Z As Integer
  
  A = Asc("A"): Z = Asc("Z")
  
  Temp = UCase(Rev)
  
  For I = Len(Rev) To 1 Step -1
    M = Asc(Mid(Temp, I, 1))
    If M >= A And M <= Z Then
      If M + 1 > Z Then
        Temp2 = &quot;A&quot;
        If I < Len(Temp) Then Temp2 = Temp2 & Right(Temp2, Len(Temp) - (I))
        If I > 1 Then
          Temp2 = Left(Temp, I - 1) & Temp2
        Else
          Temp = &quot;A&quot; & Temp2
          Exit For
        End If
        Temp = Temp2
      Else
        Temp2 = &quot;&quot;
        If I > 1 Then Temp2 = Left(Temp, I - 1)
        Temp2 = Temp2 & Chr(M + 1)
        If I < Len(Temp) Then Temp2 = Temp2 & Right(Temp, Len(Temp) - I)
        Temp = Temp2
        Exit For
      End If
    Else
      Exit For
    End If
  Next
  Revise = Temp
End Function

Any ideas? Sometimes... the BASIC things in life are the best...

or at least the most fun ;-)
-Josh Stribling

 

[DrJavaJoe]

Try this. It's actually 2 functions all you have to do is call IncrementAlphaString the second function is recursive and is where all the work is done. The 1st function just adds a char to the end if all Z's.


Private Sub Command1_Click()
MsgBox IncrementAlphaString("ZZZZZ&quot
MsgBox IncrementAlphaString("AAAAA&quot
MsgBox IncrementAlphaString("A&quot
MsgBox IncrementAlphaString("Z&quot
End Sub
Private Function IncrementAlphaString(strAlphaString As String) As String
Dim boolAddNewChar As Boolean
Dim strReturn As String
strReturn = IncrementAlphaStringRecursive(strAlphaString, boolAddNewChar)
If boolAddNewChar = True Then
IncrementAlphaString = strReturn & "A"
Else
IncrementAlphaString = strReturn
End If
End Function
Private Function IncrementAlphaStringRecursive(strAlphaString As String, boolAddNewChar As Boolean) As String
Dim strLastChar As String
Dim strCharBeforeTheLast As String
strLastChar = Right(strAlphaString, 1)
If strLastChar = "" Then
boolAddNewChar = True
ElseIf strLastChar <> &quot;Z&quot; Then
IncrementAlphaStringRecursive = Left(strAlphaString, Len(strAlphaString) - 1) & Chr(Asc(strLastChar) + 1)
Else
strLastChar = &quot;A&quot;
strCharBeforeTheLast = IncrementAlphaStringRecursive(Left(strAlphaString, Len(strAlphaString) - 1), boolAddNewChar)
IncrementAlphaStringRecursive = strCharBeforeTheLast & strLastChar
End If
End Function

 

[CajunCenturion]

I've used the following function for this type of string incrementing:

Private Function IncrementText(sText As String) As String

   Dim iOffs As Integer
   Dim iVal  As Integer
   Dim bDone As Boolean
   
   iOffs = Len(sText)
   bDone = False
   Do While (bDone = False)
      iVal = Asc(Mid(sText, iOffs, 1))
      If (iVal = 90) Then
         sText = Left(sText, iOffs - 1) & "A" & Mid(sText, (iOffs + 1))
         If (iOffs = 1) Then
            sText = "A" & sText
            bDone = True
         Else
            iOffs = iOffs - 1
         End If
      Else
         sText = Left(sText, iOffs - 1) & Chr(iVal + 1) & Mid(sText, (iOffs + 1))
         bDone = True
      End If
   Loop
   IncrementText = sText

End Function

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[CubeE101]

Ok... Here is my smaller more optimized version...

the red area Verifies that the string is all letters...
the blue area Increments the letter(s)...

Function Rev(R As String)
Dim Temp As String, I As Integer, M As String, Done As Boolean
R = UCase(R): For I = 1 To Len(R)
If Mid(R, I, 1) < &quot;A&quot; Or Mid(R, I, 1) > &quot;Z&quot; Then Rev = R: Exit Function
Next: For I = Len(R) To 1 Step -1: M = Mid(R, I, 1)
If Done = False Then If M < &quot;Z&quot; Then M = Chr(Asc(M) + 1): Done = True _
Else If I > 1 Then M = &quot;A&quot; Else M = &quot;AA&quot;: Done = True
Temp = M & Temp: Next: Rev = Temp
End Function

And this expands out to this code:

Function Rev(R As String)
Dim Temp As String, I As Integer, M As String, Done As Boolean
R = UCase(R)
For I = 1 To Len(R)
If Mid(R, I, 1) < &quot;A&quot; Or Mid(R, I, 1) > &quot;Z&quot; Then Rev = R: Exit Function
Next
For I = Len(R) To 1 Step -1
M = Mid(R, I, 1)
If Done = False Then If M < &quot;Z&quot; Then M = Chr(Asc(M) + 1): Done = True _
Else If I > 1 Then M = &quot;A&quot; Else M = &quot;AA&quot;: Done = True
Temp = M & Temp
Next
Rev = Temp
End Function

Sometimes... the BASIC things in life are the best...

or at least the most fun ;-)
-Josh Stribling

 

[CubeE101]

lol...
JavaJoe,
It looks like your going for a bad case of CTS or Arthritis if you keep that up...

Just out of curiosity...
whats up with:
strAlphaString
IncrementAlphaString
IncrementAlphaStringRecursive
strLastChar
strCharBeforeTheLast
why not make them:
AStr
IncAStr
IncAStrRecurs
CurChr
LastChr

...Someday your wrist will thank me...

Also for readability...
honostly, Cajun's code is a lot easier to read... less characters...
Your code is all typed out which &quot;SHOULD&quot; be easier to read but ends up being harder on the eyes...
For My code I try to always use only a few letters for variables... such as initials...
Temp is usually my All purpose/temporary variable
I is my index
most people know what X and Y are for... (in most cases)
in the worst case senario... you can always use comments to tell what they stand for...

Dim I as Integer 'Index
Dim Temp as String 'Misc String
Dim LastC as string 'Last Character
Dim X as Long, Y as Long 'X and Y Coords

Also, for prefixes...
if you use strXXXX for a string... It is pointless to say strMyString... That is double naming and eliminating the whole purpose of prefixes...
like strLastString should be strLast...

Thanks for the post... Sometimes... the BASIC things in life are the best...

or at least the most fun ;-)
-Josh Stribling

 

[MichaelRed]

hmmmmmmmmmmmmm,

I was going to contribute hmmmmmmmmmmmmm, with all of the additional commentarym perhaps it is easier to just let the cube roll his own ....


MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[CajunCenturion]

Geez CubeE101 - sorry to offend you with such &quot;hard on the eyes&quot; code with so poorly named variables.

I'll try to keep this in mind next time I participate in one of your questions. Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[strongm]

Don't worry - CubeE101 just has a slightly skewed view of programming style.

 

[DrJavaJoe]

I, M, A, Z, Temp, Temp2 <-Much easier to read as long as you put documentation at each use just to describe what the &^%@% the variable is supposed to represent. When I go back and read my code a year later, I can make some sense of what the variable represents. My 2 cents.

 

[MichaelRed]

&quot;... a slightly skewed view ... &quot;


????????????????????????????????????

SLIGHTLY

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[CubeE101]

DrJavaJoe,
I was just kiding...

(hence the Lol... At the top)


I didn't mean to offend you...
they were just suggestions...
(...I was refering to all the typing)

CajunCenturion
>>Geez CubeE101 - sorry to offend you with such &quot;hard on the eyes&quot; code with so poorly named variables.

What is that all about... I said your code was easy to read...
You Code with Variables Similar to mine...
Next time read the WHOLE thing before you get pissed...

I'll Give You Each a Star for posting and for me offending both of you...

Ya'll take things WAY too seriously...

strongm,
You know I gotta start a little Controvery now and then...
If I didn't have my Skewed Ways... This stuff might get Boring ;-)

-cheers- Sometimes... the BASIC things in life are the best...

or at least the most fun ;-)
-Josh Stribling

 

[CubeE101]

FYI... CTS is Carpal Tunnel Syndrome

the disiese you get in your wrist(s) from typing too much ;-)

It was ALL &quot;suposed&quot; to be a Joke... Lighten up a bit.. Sometimes... the BASIC things in life are the best...

or at least the most fun ;-)
-Josh Stribling

 

[avrcan]

take a look in following:

Sub Test_NextString()
MsgBox NextString(&quot;B&quot
End Sub

Private Function NextString(strX) As String
Dim strC As String
On Error Resume Next
strC = Range(strX & 1).Offset(0, 1).Address(False, False)
If Err = 0 Then
NextString = Left(strC, Len(strC) - 1)
Else
NextString = &quot;Out og Range&quot;
End If
End Function

Andro

 

[MichaelRed]

strC = Range(strX & 1).Offset(0, 1).Address(False, False)


Range undefined function


MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[ffc2003]

This could be thought of as &quot;What's the next number in a base 26 series?&quot; Where a=0, b=1, etc.

Private Function nextB26() As String

    'calculate next base26 value
    
    Dim i As Integer
    Dim finished As Boolean
    Dim nextNum As String
    
    'prefix up to 25 leading zeros onto last used base26 value
    lastBase26 = "aaaaaaaaaaaaaaaaaaaaaaaaa" & lastBase26
    lastBase26 = Right(lastBase26, 26)
    
    ReDim digit(26) As String
    
    For i = 1 To 26
        digit(i) = Mid(lastBase26, 27 - i, 1)
    Next
    
    finished = False
    i = 1
    Do Until finished Or i > 26
        If digit(i) = "z" Then
            digit(i) = "a"
            i = i + 1
        Else
            digit(i) = Chr(Asc(digit(i)) + 1)
            finished = True
        End If
    Loop
    For i = 1 To 26
        nextNum = digit(i) & nextNum
    Next
    
    'remove leading base26 zero values "a"s
    Do Until Left(nextNum, 1) <> &quot;a&quot;
        nextNum = Mid(nextNum, 2)
    Loop
    nextB26 = nextNum
'    Debug.Print nextNum
    
End Function

To use this function, I defined lastbase26 as a string variable in general declarations, and initialized it in the form load event (as =&quot;a&quot. I'm using lower case alphabet. I'm also going way overboard assuming that the longest string will be less than 26 characters - this equates to a number equal to 26^26 (or 6E36).

 

[CubeE101]

I believe avrcan is using Excel to get the next letter...

that is why the funtion is undefined...
Sometimes... the BASIC things in life are the best...

or at least the most fun ;-)
-Josh Stribling

 

[Bob26i]

I would take a more mathematical approach to this problem.

Function ValOfString(s as String) As Long
'No error trapping, I'll leave that to you
Dim i as integer, lAnswer as long

lAnswer = 0 'Sorry, I also program in C, so this is a habit
For i = Len(s) to 1 step -1
lAnswer = 26 * lAnswer + (Asc(Mid$(s, i, 1)) - vbKeyA + 1) 'A = 1, B = 2, ... Z = 26
Next i

ValOfString = lAnswer
End Function

Function StringFromNumber(lNumber as Long) As String

Dim i as Integer, sAnswer as String
Dim lCopy as Long

sAnswer = &quot;&quot;
lCopy = lNumber
Do While lCopy > 0
sAnswer = Chr$(64 + (lCopy Mod 26)) & sAnswer 'Add last character
lCopy = lCopy \ 26 'Integer division &quot;removes&quot; last character
Loop

End Function

Function IncrementString(sOld as String) As String

IncrementString = StringFromNumber(ValOfString(sOld)+1)

End Function

 

[Hypetia]

What about this?
___
Function Increment(S As String) As String
Dim L As Integer, N As Integer
S = UCase$(S) 'to avoid lower case errors
L = Len(S)
ReDim C(0 To L) As Integer
For N = 1 To L
C(N) = Asc(Mid$(S, N))
Next
C(L) = C(L) + 1
For N = L To 1 Step -1
If C(N) > vbKeyZ Then
C(N) = vbKeyA
C(N - 1) = C(N - 1) + 1
Else
Exit For
End If
Next
If C(0) = 1 Then Increment = &quot;A&quot;
For N = 1 To L
Increment = Increment & Chr$(C(N))
Next
End Function

 

[AndyWatt]

In case anyone is even remotely interested I've written a function that adds one to any value in any base between 2 and 36, although it can be extended up to base 256:

Public Function IncVal(Z As String, B As Long, W As Long) As String
' Z = string to add one to
' B = base to use
' W = digit positions
Const S As Long = 1 ' First rec
Const C As String = &quot;0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ\&quot; ' Available digit chars
Dim L As Long ' Loop
Dim M As String * 1 ' Max char
Dim P As Long ' Pointer
Dim F As Boolean ' Flag
Dim O As String ' Output string
ReDim X(S To W + 1) As String * 1 ' Digits

If Len(Z) > W Then
IncVal = &quot;Overflow&quot;
Exit Function
End If

M = Mid(C, B, 1) ' Maximum digit value

' Initialise array
For L = S To W + 1
X(L) = Left(C, 1)
Next L

' Swap string round so lowest digit on left
Z = StrReverse(Z)
' enter into array
For L = 1 To Len(Z)
X(L) = Mid(Z, L, 1)
Next L

' Determine where we are in C
P = InStr(1, C, X(S))
' Set to next character in C
X(S) = Mid(C, P + 1, 1)

' Loop through digit positions
For L = S To W
' Does digit in X(L) exceed the max M
If X(L) > M Then
' Yes - What char is X(L+1)?
P = InStr(1, C, X(L + 1))
' Increment char in X(L+1)
X(L + 1) = Mid(C, P + 1, 1)
' Reset X(L)
X(L) = Left(C, 1)
End If
Next L

' Have we overflowed yet?
If X(W + 1) > Left(C, 1) Then
IncVal = &quot;Overflow&quot;
Exit Function
End If

' format output
F = False
' Set output to empty string
O = vbNullString
' Loop through X in reverse order
For L = W To S Step -1
' If this is the first non-zero digit then set flag
If X(L) > Left(C, 1) And Not F Then F = True
' Add X(L) to output string
If F Then O = O & X(L)
Next L
IncVal = O
End Function

I would like to point out, for the CTS/RSI-aware, that VB reserved names are longer than my variable names. P-)

To make this routine behave like the others and just deal in alpha characters you'd need to change the constant S to 11, or , I suppose, just lop off the first ten characters.

I originally did this with an array of longs - until I realised &quot;What character would represent 1,234,580?&quot;.



Andy
&quot;Logic is invincible because in order to combat logic it is necessary to use logic.&quot; -- Pierre Boutroux
&quot;Why does my program keep showing error messages every time something goes wrong?&quot;