VB.NET - String Incrementer (For Loops With Excel)

If you need a function that will increment a string, in particular to put in a loop that goes through columns in excel, here is what you are looking for.


I’m using it to increment a string (my purpose is to move through the columns in excel a -> b ; b -> c ; and so on.  But then what is also does, it will go from z -> aa ; so it can be used to loop through columns that go past the column z.

''' <summary>

    ''' jefz - function is to be used to increment a string character.  When the string reaches Z it will start

    ''' counting again from AA. Primary purpose is to be used as a counter for referencing excel cells going across

    ''' the worksheet.

    '''

    ''' Ex:  A   ->  B

    '''      M   ->  N

    '''      Z   ->  AA

    '''      AM  ->  AN

    '''      AZ  ->  BA

    '''      ZZ  ->  AAA

    ''' </summary>

    ''' <param name="strString">letter to start incrementing (must be in lowercase)</param>

    ''' <returns></returns>

    ''' <remarks></remarks>

    Private Function IncrementString(ByRef strString As String) As String

        Dim intLenString As Integer

        Dim strChar As String

        Dim intI As Integer

        intLenString = Len(strString)

        ' Start at far right

        For intI = intLenString To 0 Step -1

            ' If we reach the far left then add an A and exit

            If intI = 0 Then

                strString = "a" & strString

                Exit For

            End If

            ' Consider next character

            strChar = Mid(strString, intI, 1)

            If strChar = "z" Then

                ' If we find Z then increment this to A

                ' and increment the character after this (in next loop iteration)

                strString = Microsoft.VisualBasic.Left$(strString, intI - 1) & "a" & Mid(strString, intI + 1, intLenString)

            Else

                ' Increment this non-Z and exit

                strString = Microsoft.VisualBasic.Left$(strString, intI - 1) & Chr(Asc(strChar) + 1) & Mid(strString, intI + 1, intLenString)

                Exit For

            End If

        Next intI

        IncrementString = strString

    End Function