这里给两个写好的函数
1.数字转为字母,输入数字即可转化为列号(这里最大到ZZ,注意传入要大写),比如:NumToLetter(28)="AB"
Function NumToLetter(num As Long) As String
Dim alphabet()
'a-z字母表
alphabet() = Array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z")
'判断列,字母拆分
If num <= 26 Then
NumToLetter = alphabet(num - 1)
ElseIf num <= 729 Then
NumToLetter = alphabet((num \ 26) - 1) & alphabet((num Mod 26) - 1)
End If
End Function
2.字母转为数字,输入列号即可转化为数字,比如:LetterToNum(AB)=28
Function LetterToNum(Letter As String) As Long
Dim alphabet As Variant
Dim i, num, num1, num2, num3 As Integer
Dim Letter1, Letter2, Letter3 As String
'a-z字母表
alphabet = Array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z")
If Len(Letter) = 1 Then
For i = 0 To UBound(alphabet)
If Letter = alphabet(i) Then
num = i + 1
End If
LetterToNum = num
Next
ElseIf Len(Letter) = 2 Then
Letter1 = Left(Letter, 1)
Letter2 = Right(Letter, 1)
For i = 0 To UBound(alphabet)
If Letter1 = alphabet(i) Then
num1 = i + 1
End If
Next
For i = 0 To UBound(alphabet)
If Letter2 = alphabet(i) Then
num2 = i + 1
End If
Next
num = num1 * 26 + num2
LetterToNum = num
ElseIf Len(Letter) = 3 Then
Letter1 = Left(Letter, 1)
Letter2 = Mid(Letter, 2, 1)
Letter3 = Right(Letter, 1)
Letter1 = Left(Letter, 1)
Letter2 = Right(Letter, 1)
For i = 0 To UBound(alphabet)
If Letter1 = alphabet(i) Then
num1 = i + 1
End If
Next
For i = 0 To UBound(alphabet)
If Letter2 = alphabet(i) Then
num2 = i + 1
End If
Next
For i = 0 To UBound(alphabet)
If Letter3 = alphabet(i) Then
num3 = i + 1
End If
Next
num = num1 * 26 * 26 + num2 * 26 + num3
LetterToNum = num
Else
MsgBox "输入不正确"
End If
End Function