Excelの行列を文字から数値へ (26進数変換 vba)

いわゆる26進数変換です

VBAでコード書いていると列や行の位置を指定する事がありますが、固定文字列でコーディングすると変更があった際に面倒なので

基本的には数字で定義しておいて、変化に強くしておきます。 

 実装、テスト済なのでそのまま貼ってお使いください。

テスト実装も記載済です

Option Explicit

Public Function ToBase26(ByVal self As Long) As String
    If self <= 0 Then
        ToBase26 = ""
        Exit Function
    End If
    
    Dim n As Long
    n = IIf((self Mod 26 = 0), 26, self Mod 26)
    
    If self = n Then
        ToBase26 = Chr((n + 64))
    Else
        ToBase26 = ToBase26((self - n) / 26) & Chr(n + 64)
    End If
    
End Function

Public Function FromBase26(ByVal self As String) As Long

    Dim charLength As Integer
    Dim i As Integer
    Dim steps As Integer
    
    If self = "" Then
        FromBase26 = 0
        Exit Function
    End If
    Dim result As Long
    
    Dim chars As Byte
    charLength = Len(self)

    For i = charLength - 1 To 0 Step -1
        Dim current As Integer
        chars = asc(Mid(self, i + 1, 1))
        current = CInt(chars) - 64      'ASC To 26
        If current < 1 Or current > 26 Then
            FromBase26 = 0
            Exit Function
        End If
        If (steps = 0) Then
            result = result + current
        Else
            result = result + (current * (26 ^ steps))
        End If
        steps = steps + 1
    Next

    FromBase26 = result
End Function

Public Function ToBase26Test()

    Dim i As Integer
    Dim tmp As String
    Dim ar As String
    For i = 1 To 2000
        tmp = ToBase26(i)
        ar = ar + tmp & vbTab
    Next
    
    Debug.Print ar

End Function

Public Function FromBase26Test()

    Dim tmp As Integer
    Dim i As Integer
    
    'ToBase26のテストがOKであれば本テスト実施可能
    
    For i = 1 To 2000
        tmp = FromBase26(ToBase26(i))
        If (tmp <> i) Then
            Debug.Print (ToBase26(i))
            Stop
        End If
    Next

    ' Stopしなければ全部OK


End Function