Excel列号转换函数

以下是两个使用VBA的自定义函数，用户转换Excel的列号（2003及其以下版本）

Function ColumnConv(SpecStr As String) As String
    Dim I As Integer, I1 As Integer, I2 As Integer
    If IsNumeric(SpecStr) Then
        '1转A，27转AA
        I = Val(SpecStr)
        If I > 26 Then I1 = Int(I / 26)
        I2 = I Mod 26
        If I2 = 0 Then I2 = 26: I1 = I1 - 1
        ColumnConv = IIf(I1 > 0, Chr(Asc("A") - 1 + I1), "") & Chr((Asc("A") - 1 + I2))
    Else
        'A转1，AA转27
        I = Len(SpecStr)
        If I <> 1 And I <> 2 Then Exit Function
        SpecStr = UCase(SpecStr)
        If I = 1 Then
            I1 = Asc(Mid(SpecStr, 1, 1)) - Asc("A") + 1
        ElseIf I = 2 Then
            I1 = (Asc(Mid(SpecStr, 1, 1)) - Asc("A") + 1) * 26
            I2 = Asc(Mid(SpecStr, 2, 1)) - Asc("A") + 1
        End If
        ColumnConv = I2 + I1
    End If
End Function

下面的自定义函数要简洁多了。

Function ColumnConv1(SpecStr As String) As String
    If IsNumeric(SpecStr) Then
        ColumnConv1 = Left(Cells(1, CInt(SpecStr)).Address(0, 0), Len(Cells(1, CInt(SpecStr)).Address(0, 0)) - 1)
    Else
        ColumnConv1 = Range(SpecStr & 1).Column
    End If
End Function

 '附：取当前活动单元格的行号、列标
Dim sRow As String, sCol As String
sRow = Split(ActiveCell.Address, "$")(2)
sCol = Split(ActiveCell.Address, "$")(1)
'若活动单元格是D5,则ActiveCell.Address返回"$D$5",Split()函数将活动单元格地址"$D$5"，
'依据"$"作为分隔符，分割成一个一维数组，也就是会得到{"","D","5"}，
'而这个数组里有三个元素，分别是(0)、(1)和(2)，
'(1)就是取数组里的第二个元素(列标)，
'(2)就是取数组里的第三个元素(行标)。