VBA列数字与字母互换

<内容来至Excelhome>


vba列数字与字母互换:


方案一:速度:快


Function NumToStr(ByVal Num As Long) As String   '数字转字母
    Dim M As Long
    If Num < 1 Then Exit Function
    Do
        M = Num Mod 26
        If M = 0 Then M = 26
        NumToStr = Chr(64 + M) & NumToStr
        Num = (Num - M) / 26
    Loop Until Num <= 0
End Function




Function StrToNum(ByVal Str As String) As Long '字母转数字
    Dim s As String, S1 As String
    If Str = "" Then Exit Function
    Str = UCase(Str)
    s = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
    For i = 1 To Len(Str)
        S1 = Mid(Str, i, 1)
        If InStr(s, S1) = 0 Then Crazy_Num = -1: Exit Function
        StrToNum = StrToNum + InStr(s, S1) * 26 ^ (Len(Str) - i)
    Next
End Function
方案二:迭代 速度:略快 


Function colinf(t) '列标签字母⇔列序号数值 相互转换
    If t = "" Then colinf = "": Exit Function '如果引用単元格=空白 即返回空白退出
    If IsNumeric(t) Then '如果引用単元格=数值(列序号)，計算返回列标签大写英文字母
        If t < 1 Then colinf = "": Exit Function '如果数是0或負数返回空白退出
        Do
            colinf = Chr((t - 1) Mod 26 + 65) & colinf '以26除数反複Mod求余得到1-26字母
            t = Int((t - 1) / 26) '求余后再用26除后Int取整……
        Loop Until t <= 0 '反複迭代计算直至数t已不能除26求余止
    Else '如果引用単元格=文本字符，計算返回序号数値
        t = UCase(t) '首先文本転換大写字母
        For i = 1 To Len(t) '遍各字符
            colinf = colinf + (Asc(Mid(t, i, 1)) - 64) * 26 ^ (Len(t) - i) '按26冪乗計算累計数
        Next
    End If
End Function




方案三:递归 速度:略快 


Function getColStr$(n) '列序号数字→列标签字母
    Dim s$, t as long
    t = n
    Do
        s = Chr((t - 1) Mod 26 + 65) & s
        t = Int((t - 1) / 26)
    Loop Until t <= 0
    getColStr = s
End Function


方案四:


Function getC_Str(n) '列序号数字→列标签字母


    getC_Str = Split(Cells(1, n).Address, "$")(1)


End Function


------------------以下为测试3种方案全码-----------------
Sub test()
    dqT = Timer
    M = 1000: n = 10000
    Dim strTmp


    For i = 1 To M
        For j = 1 To n
            strTmp = getC_Str(n)
        Next j
    Next i


    dqT1 = Timer
    For i = 1 To M
        For j = 1 To n
            strTmp = getColStr(j)
        Next j
    Next i
    dqT2 = Timer


    For i = 1 To M
        For j = 1 To n
            strTmp = NumToStr(n)
        Next j
    Next i


    MsgBox " getC_Str  用时：" & Format(Timer - dqT1, "0.0000") & vbLf & "getColStr 用时：" & Format(dqT2 - dqT1, "0.0000") & vbLf & "NumToStr用时：" & Format(Timer - dqT2, "0.0000")
End Sub


Function getColStr$(n) '列序号数字→列标签字母
     Dim s$, t as long
    t = n
    Do
        s = Chr((t - 1) Mod 26 + 65) & s
        t = Int((t - 1) / 26)
    Loop Until t <= 0
    getColStr = s
End Function


Function NumToStr(ByVal Num As Long) As String   '数字转字母
    Dim M As Long
    If Num < 1 Then Exit Function
    Do
        M = Num Mod 26
        If M = 0 Then M = 26
        NumToStr = Chr(64 + M) & NumToStr
        Num = (Num - M) / 26
    Loop Until Num <= 0
End Function


Function getC_Str(n) '列序号数字→列标签字母


    getC_Str = Split(Cells(1, n).Address, "$")(1)


End Function