Excel - convert column letter to number equivalent vbs

Question

I'm writing a vbs script to extract some data from an excel spreadsheet. Currently using the function:

objSheet.Cells(rowNum, colNum).Value

To get cell values, this allows me to do maths on the column number, e.g. add three to move across three columns. But in some instances I want to specify which Columns to get by letter:

objSheet.Cells(4, E).Value

I therefore need to write a vbs function to convert column letter to numbers E => 5. Needs to be able to handle a spreadsheet more than 26 cols wide.

I've seen lots of functions on the internet and SO for doing the opposite but not found much for doing the conversion this way.

Thanks

What about the column function http://www.techonthenet.com/excel/formulas/column.php ? Couldn't you pass vb equivalent of column(E:E) into your function? — luk3thomas, Mar 26 '13 at 12:44
This is in vbscript using an Excel.Application object where objSheet is defined as: objExcel.ActiveWorkbook.Worksheets(1) — Jona, Mar 26 '13 at 12:46

score 4 · Accepted Answer · answered Mar 26 '13 at 12:50

4

This code will run without Excel:

Function ColNum(strCol As String) As Integer
    Dim i As Integer, col As Integer

    For i = Len(strCol) To 1 Step -1
        col = col + (Asc(Mid(strCol, i, 1)) - 64) * (26 ^ (i - 1))
    Next
    ColNum = col
End Function

Alternatively, in Excel, you can simply use this:

Function ColNum(strCol As String) As Integer
    ColNum = Range(strCol & "1").Column
End Function

answered Mar 26 '13 at 12:50

Peter Albert

16,917
5
64
88

Thanks Peter, I've edited your answer to include a version that runs in vbs (basically stripping out the typing) for future googlers and so that the question is definitely answered. – Jona Mar 26 '13 at 13:04
Seems like the edit was rejected. :-( do you have to leave out the return type of a function in vbscript or does the code run as is? – Peter Albert Mar 26 '13 at 15:47

score 0 · Answer 2 · answered Jan 26 '15 at 16:54

without vba:

To convert a column number to a letter you enter this formula:

=LEFT(ADDRESS(1,3,4)) (returns the column letter for the cell C1=C)

But this is still not satisfactory because if you insert a column before the column C the number 3 won't change to 4, so here is a fix:

=LEFT(ADDRESS(1,COLUMN(C1),4))

Hope this helps.

This is especially helpful when you need to create strings that specify a cell range for example.

Excel - convert column letter to number equivalent vbs

2 Answers2

Linked