熟悉Excel操作的用户都知道在工作表中列标题有列标(字母形式)和列号(数字形式)两种,如下图所示。
同样在VBA代码中也会用到这两种不同的表示方式。例如应用C6单元格可以使用如下几种方式来引用对象,对于Cells
的第二个参数,即可以使用数字列号,也可以使用列标字母,然而对于Range
来说参数为字符串,也就是使用列标形式。
Cells(6,3)
Cells(6,"C")
Range("C6")
在代码中使用变量的示例。
strCol = "C"
intCol = 3
Cells(6, intCol)
Cells(6, strCol)
Range(strCol & "6")
在VBA代码灵活选择单元格的引用形式,将使得代码简洁高效。某些场景中,可能还是需要实现列标和列号的转换,此时可以使用自定义函数。
列标字母编号由单个字母开始A,B,C,…,Z,之后为AA,AB,…,ZZ,接下来为AAA,AAB,…,XFD,可以看作是26进制编码的表示形式,和其他的进制转换类似,也是采用除以26取余数的方式进行计算,此处需要额外多一步的处理是,对于余数要转换为字母形式。
列号转列标的自定义函数代码如下。
Function Num2Name1(ByVal ColNum As Long) As String
Dim i As Long
Dim intMod As Integer
Num2Name1 = ""
i = ColNum
Do While i > 0
intMod = IIf(i \ 26 = i / 26, 26, i Mod 26)
Num2Name1 = Chr$(intMod + 64) + Num2Name1
i = (i - intMod) / 26
Loop
End Function
【代码解析】
第7行代码判断循环变量i
是否可以被26整除,如果可以,那么intMod
赋值为26,否者赋值为余数。
第8行代码使用Chr
函数将1~26的数字转换为大小字母,并和前面步骤已经得到的结果组合为新的字符串。
第9行代码获得下一次循环需要处理的数字,如果i<=0
,则终止循环。
列标转列号的方法是上述的逆运算,自定义函数代码如下。
Function Name2Num1(ByVal ColName As String) As Long
Dim i As Integer
Dim intLen As Integer
Name2Num1 = 0
intLen = Len(ColName)
If intLen > 0 Then
ColName = UCase$(ColName)
For i = 1 To intLen
Name2Num1 = Name2Num1 + (Asc(Mid(ColName, i, 1)) - 64) * 26 ^ (intLen - i)
Next i
End If
End Function
【代码解析】
第5行代码获取列标字符串的长度。
第7行代码将列标转换为大写字母。
第9行代码中使用Mid
函数逐位读取列标字符,使用Asc
函数转换为ASCII码,减去64实现了将字母变换为1~26的数组序列,根据所在位置不同,乘以26 ^ (intLen - i)
转换为相应的数值进行累加。
使用如下代码可以测试两个自定义函数的结果。
Sub Demo()
Debug.Print Name2Num1("XFD")
Debug.Print Num2Name1(16384)
End Sub
上述两个自定义过程利用进制原理转换实现了列标和列号的转换,但是在VBA中灵活利用对象的属性可以更简洁的实现这个功能。
Function Num2Name(ByVal ColNum As Long) As String
Num2Name = ""
On Error Resume Next
Num2Name = Split(Cells(1, ColNum).Address, "$")(1)
End Function
【代码解析】
第4行代码使用Cells(1, ColNum).Address
获取列号为ColNum
的列中第一个单元格的绝对引用地址(例如:$ABC$1
),Split
函数使用$
作为分隔符将绝对引用地址拆分具有3个元素的数组(下标从零开始),第2个元素为列标。
Function Name2Num(ByVal ColName As String) As Long
Name2Num = 0
On Error Resume Next
Name2Num = Cells(1, ColName).Column
End Function
【代码解析】
第4行代码使用使用单元格的Column
返回列号。
这两个自定义函数避繁就简,核心代码就只有一行,代码非常简洁,有些“高手”可能会质疑这样的实现方式,他们认为调用Excel对象就会增加耗时,但是普通实现方式循环过程次数多,最终的整体效果哪个更好,我个人更欣赏简洁的代码。大家有兴趣的话,可以进行耗时对比测试,并在评论区发布测试结果。