Excel-将列字母转换为数字等价的vbs [英] Excel - convert column letter to number equivalent vbs

问题描述

我正在编写一个vbs脚本以从Excel电子表格中提取一些数据.当前使用的功能:

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

objSheet.Cells(rowNum, colNum).Value

要获取单元格值，这使我可以对列号进行数学运算，例如加3可在三列之间移动.但是在某些情况下，我想指定要通过字母获取的列:

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

因此，我需要编写一个vbs函数将列字母转换为数字E =>5.需要能够处理超过26列宽的电子表格.

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.

我在Internet和SO上看到了很多功能，它们却相反，但是用这种方式进行转换却没什么用.

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.

谢谢

推荐答案

此代码将在没有Excel的情况下运行:

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

或者，在Excel中，您可以简单地使用以下代码:

Alternatively, in Excel, you can simply use this:

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

这篇关于Excel-将列字母转换为数字等价的vbs的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！