|
以下是兩個使用VBA的自定義函數(shù)，用戶轉(zhuǎn)換Excel的列號（2003及其以下版本）
Function ColumnConv(SpecStr As String) As String
Dim I As Integer, I1 As Integer, I2 As Integer
If IsNumeric(SpecStr) Then
'1轉(zhuǎn)A，27轉(zhuǎn)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轉(zhuǎn)1，AA轉(zhuǎn)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
下面的自定義函數(shù)要簡潔多了。
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
'附：取當(dāng)前活動單元格的行號、列標(biāo)
Dim sRow As String, sCol As String
sRow = Split(ActiveCell.Address, "$")(2)
sCol = Split(ActiveCell.Address, "$")(1)
'若活動單元格是D5,則ActiveCell.Address返回"$D$5",Split()函數(shù)將活動單元格地址"$D$5"，
'依據(jù)"$"作為分隔符，分割成一個一維數(shù)組，也就是會得到{"","D","5"}，
'而這個數(shù)組里有三個元素，分別是(0)、(1)和(2)，
'(1)就是取數(shù)組里的第二個元素(列標(biāo))，
'(2)就是取數(shù)組里的第三個元素(行標(biāo))。
|
