• Home
 · 
• Miscellaneous
 · 
• This code helps you convert a numeric column index for MS-Excel to it's equivalent string and vice-

This code helps you convert a numeric column index for MS-Excel to it's equivalent string and vice-

This code helps you convert a numeric column index for MS-Excel to it's equivalent string and vice-versa. This is often required when using

Rate This code helps you convert a numeric column index for MS-Excel to it's equivalent string and vice-

• 1
• 2
• 3
• 4
• 5

(1(1 Vote))

'Web-site: http://www.prashantsharma.com
'E-Mail: [email protected]

'This function returns the string equivalent of a column index
Public Function GetCol(ByVal idx As Integer) As String
    Dim j As Integer, s As String
    If idx > 0 And idx <= 256 Then 'Check limits
        idx = idx - 1 'Shift it to 0's scale
        j = idx Mod 26
        s = Chr$(65 + j)
        idx = Int(idx - j) / 26
        If idx > 0 Then s = Chr$(64 + idx) & s
        GetCol = s
    End If
End Function

'This function returns the integer equivalent of a column index in string
Public Function GetIdx(ByVal col As String) As Integer
    Dim j As Integer, i As Integer, c As String
    col = UCase$(Trim$(col))
    j = Len(col)
    If j > 0 And j <= 2 Then 'Check limits
        c = Right$(col, 1)
        i = Asc(c) - 64
        If j = 2 Then
            c = Left$(col, 1)
            i = i + (Asc(c) - 64) * 26
        End If
        GetIdx = i
    End If
End Function