Ask Your Question
0

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

asked 2019-10-20 07:39:06 +0100

lonk gravatar image

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 flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
1

answered 2019-10-20 08:25:36 +0100

updated 2019-10-20 09:39:39 +0100

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
edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-10-20 07:39:06 +0100

Seen: 47 times

Last updated: Oct 20 '19