这里的列名称指的是A、B、C等样式的列,列号指列的编号,如1、2、3等等。
转换之后,A列对应的列号为1,反之,列号为1的列转换之后为A。函数如下:
1、通过列名称转换成对应的列号。
Function GetColumnNum(ByVal ColumnName As String) As Integer
Dim Result As Integer, First As Integer, Last As Integer Result = 1
If Trim(ColumnName) <> "" Then
If Len(ColumnName) = 1 Then
Result = Asc(UCase(ColumnName)) - 64
ElseIf Len(ColumnName) = 2 Then
If UCase(ColumnName) > "IV" Then
ColumnName = "IV"
First = Asc(UCase(Left(ColumnName, 1))) - 64
Last = Asc(UCase(Right(ColumnName, 1))) - 64
Result = First * 26 + Last
End If
End If
GetColumnNum = Result End Function
测试:
Sub TestGetColumnNum()
Dim ColumnNum As Integer
ColumnNum = GetColumnNum("AA")
MsgBox ColumnNum, vbInformation, "测试"
End Sub
2、通过列号转换成对应的列名称。
Function GetColumnName(ByVal ColumnNum As Integer) As String
Dim First As Integer, Last As Integer
Dim Result As String
If ColumnNum > 256 Then ColumnNum = 256
First = Int(ColumnNum / 27)
Last = ColumnNum - (First * 26)
If First > 0 Then
Result = Chr(First + 64)
End If If Last > 0 Then
Result = Result & Chr(Last + 64)
End If
GetColumnName = Result
End Function
测试:
Sub TestGetColumnName()
Dim ColumnName As String
ColumnName = GetColumnName(27)
MsgBox ColumnName, vbInformation, "测试"
End Sub
3、说明
在以上两个函数中,如果输入的参数大于Excel的最大列号"IV"(256),则返回的值为最大的列数。