VBA 字母和数字互换，Cells和Range转换

今夕乃何夕

已于 2022-06-22 22:00:41 修改

阅读量5.1k

点赞数 1

文章标签： vba excel

于 2021-05-20 16:54:23 首次发布

本文链接：https://blog.youkuaiyun.com/weixin_58444518/article/details/117082547

版权

这里给两个写好的函数

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