Column/Range: macro for converting from numeric to alphanumeric format

[Archangelos]

It is very convenient to handle columns in numeric format than in alpabetical format, i.e. it's to write cells(1,2) instead of cells("B1").

However, the bloody ranges work work only in alphanumeric format. I wrote some code for converting either columns or a range from numeric format to alphanumeric.

Function ColumnNumToAlpha(Arithmos As Integer) As String 'Use these to convert a column from numeric to alphanumeric


Select Case Arithmos
       Case Is < 1
            ColumnNumToAlpha = "Column number ranges from 1 to 16384"
       Case Is > 16384
            ColumnNumToAlpha = "Column number ranges from 1 to 16384"
       Case Else


            If Arithmos > 702 Then
               ColumnNumToAlpha = AlphanumericTria(Arithmos)
            Else
               If Arithmos > 26 Then
                  ColumnNumToAlpha = AlphanumericDyo(Arithmos)
               Else
                  ColumnNumToAlpha = AlphanumericEna(Arithmos)
               End If
            End If
End Select


End Function






Function RangeNumToAlpha(LeftCol As Integer, UpRow As Integer, RightCol As Integer, LowRow As Integer) As String 'Use these to convert a range from numeric to alphanumeric


Dim UpRowFlag As Integer
Dim LowRowFlag As Integer
Dim LeftColFlag As Integer
Dim RightColFlag As Integer


        If UpRow < 1 Then
           UpRowFlag = 1
        Else
           If UpRow > 65536 Then
              UpRowFlag = 1
           Else
              UpRowFlag = 0
           End If
        End If


        If LowRow < 1 Then
           LowRowFlag = 1
        Else
           If LowRow > 65536 Then
              LowRowFlag = 1
           Else
              LowRowFlag = 0
           End If
        End If


        If LeftCol < 1 Then
           LeftColFlag = 1
        Else
           If LeftCol > 65536 Then
              LeftColFlag = 1
           Else
              LeftColFlag = 0
           End If
        End If
        
        If RightCol < 1 Then
           RightColFlag = 1
        Else
           If RightCol > 65536 Then
              RightColFlag = 1
           Else
              RightColFlag = 0
           End If
        End If




       If UpRowFlag + LowRowFlag + LeftColFlag + RightColFlag > 0 Then
          RangeNumToAlpha = "Column number ranges from 1 to 16384, Row number ranges from 1 to 65536"
       Else
          RangeNumToAlpha = ColumnNumToAlpha(LeftCol) & UpRow & ":" & ColumnNumToAlpha(RightCol) & LowRow
       End If
       
End Function










Function AlphanumericEna(StiliArithmos As Integer) As String 'Auxilliary function
         AlphanumericEna = Gramma(StiliArithmos - 1)
End Function


Function AlphanumericDyo(StiliArithmos As Integer) As String 'Auxilliary function
         AlphanumericDyo = Gramma(((StiliArithmos - 27) \ 26)) & Gramma((StiliArithmos - 27) Mod 26)
End Function




Function AlphanumericTria(StiliArithmos As Integer) As String 'Auxilliary function
Dim Psifio0 As Integer
Dim Psifio1 As Integer
Dim Psifio2 As Integer




         Psifio2 = (StiliArithmos - 703) \ 676
         Psifio1 = ((StiliArithmos - 703) - (Psifio2 * 676)) \ 26
         Psifio0 = (StiliArithmos - 703) - (Psifio2 * 676) - (Psifio1 * 26)
         AlphanumericTria = Gramma(Psifio2) & Gramma(Psifio1) & Gramma(Psifio0)
End Function












Function Gramma(Noumero As Integer) As String 'Auxilliary function


Select Case Noumero
       Case Is = 0
            Gramma = "A"
       Case Is = 1
            Gramma = "B"
       Case Is = 2
            Gramma = "C"
       Case Is = 3
            Gramma = "D"
       Case Is = 4
            Gramma = "E"
       Case Is = 5
            Gramma = "F"
       Case Is = 6
            Gramma = "G"
       Case Is = 7
            Gramma = "H"
       Case Is = 8
            Gramma = "I"
       Case Is = 9
            Gramma = "J"
       Case Is = 10
            Gramma = "K"
       Case Is = 11
            Gramma = "L"
       Case Is = 12
            Gramma = "M"
       Case Is = 13
            Gramma = "N"
       Case Is = 14
            Gramma = "O"
       Case Is = 15
            Gramma = "P"
       Case Is = 16
            Gramma = "Q"
       Case Is = 17
            Gramma = "R"
       Case Is = 18
            Gramma = "S"
       Case Is = 19
            Gramma = "T"
       Case Is = 20
            Gramma = "U"
       Case Is = 21
            Gramma = "V"
       Case Is = 22
            Gramma = "W"
       Case Is = 23
            Gramma = "X"
       Case Is = 24
            Gramma = "Y"
       Case Is = 25
            Gramma = "Z"
       Case Else
            'Nothing else to be done


End Select




End Function

How do you like it?

 

[Archangelos]

THe funny thing is that I had googled it in the past but I had n't found anything. I had to write my own code and then discover a few useful thing.

 

[JackDanIce]

I think reading about attributes and properties of cell objects will help you learn more or generally reading this board and replies and trying to understand any posted code.