VBA-Excle-字母/数字（cells-range）坐标转换

由于Exlce中cells与range两种方式定位单元格时，列号通过数字和字母两种方式，其中数字更有利于脚本使用，因此需要进行转换。

Sub int_str(target As Integer) '数字转字母

Dim temp_str As String
Dim res_str As String
temp_str = ""
E_letter_str = "0,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"

'计算字符
Do While target > 0
    If temp_str = "" Then
        temp_str = Split(E_letter_str, ",")(target Mod (26))
    Else
        temp_str = temp_str & "," & Split(E_letter_str, ",")(target Mod (26))
    End If
    target = (target - target Mod (26)) / 26
Loop

'调换顺序
For Each x In Split(temp_str, ",")
    res_str = x & res_str
Next

Debug.Print "已完成转换：" & target & "→" & res_str

End Sub

 

Sub str_int(target As String) '字母转数字

E_letter_str = "0,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"

'将字母坐标拆分
Dim target_arr() As String
ReDim target_arr(Len(target) - 1)
For i = 1 To Len(target)
    target_arr(Len(target) - i) = Mid(target, i, 1)
Next i

'计算数字
res_int = 0
For i = 0 To (Len(target) - 1)
    For n = 0 To 26
        If Split(E_letter_str, ",")(n) = target_arr(i) Then Exit For
    Next n
    res_int = n * (26 ^ i) + res_int
Next i

Debug.Print "已完成转换：" & target & "→" & res_int

End Sub