# BASIC|Calc: How to convert column name to column index

I need to convert the column name to index, for example, column index of A of cell A5 is 0, column index of ABH of cell ABH17 is 735. Help me please.

edit retag close merge delete

Sort by » oldest newest most voted

Use one of Misc module functions:

GlobalScope.BasicLibraries.LoadLibrary("Tools")
index = GetColumnIndex(oSheet, "ABH17")


... which internally does this (you may see in Basic Object Catalog):

Function GetColumnIndex(oSheet as Object, ColName as String)
Dim oRange as Object
oRange = oSheet.GetCellRangeByName(ColName)
End Function


You may use this to convert string like "ABH" to 735:

Function GetColIndex(colName As String)
Dim l As Long, i As Long, n As Long, ch As Long
l = Len(colName)
If (l = 0) Or (l > 3) Then Err = 14
For i = 1 To l
ch = Asc(UCase(Mid(colName,i,1)))
' must be between Asc("A") and Asc("Z") '
If (ch < 65) Or (ch > 90) Then Err = 14
' n * (Asc("Z")-Asc("A")+1) + ch-Asc("A")+1 '
n = n * 26 + ch - 64
Next i
' Check if result is in range - but remember that the upper limit will increase soon '
If (n > 1024) Then Err = 14
GetColIndex = n - 1
End Function

more