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

[lonk]

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.

[mikekaganski]

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)
    GetColumnIndex = oRange.RangeAddress.StartColumn
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