[Excel VBA] 列番号と列文字を相互変換する

ざっくり説明

列番号を渡すと列文字にしてくれて、
列文字を渡すと列番号してくれます。

ex.
ConvertColValueMutually ("AB")　→　28
ConvertColValueMutually (3776)　→　"EOF"

コード

列番号と列文字を相互変換する

Function ConvertColValueMutually(ByVal colValue As String) As Variant
'================================
'用途　　：列番号と列文字の相互変換
'--------------------------------
'第一引数：列番号もしくは列文字 (String型)
'戻り値　：引数が列番号の場合→列文字 (Variant型)
'　　　　　引数が列文字の場合→列番号 (Variant型)
'================================
    Const MAXIMUM_COL_NUMBER As Long = 16384
    Const MAXIMUM_COL_LETTER As String = "XFD"

    If colValue = "" Then GoTo ErrorLabel

    Dim colNumber As Long
    Dim colLetter As String

    If IsNumeric(colValue) Then
        If Len(colValue) > Len(CStr(MAXIMUM_COL_NUMBER)) Then GoTo ErrorLabel
        If colValue <> Int(colValue) Then GoTo ErrorLabel
        colNumber = colValue

        If colNumber <= 0 Then GoTo ErrorLabel
        If colNumber > MAXIMUM_COL_NUMBER Then GoTo ErrorLabel

        Do While colNumber > 0
            colNumber = colNumber - 1
            colLetter = Chr((colNumber Mod 26) + 65) & colLetter
            colNumber = Int(colNumber / 26)
        Loop

        ConvertColValueMutually = colLetter
    Else
        colLetter = UCase(colValue)
        If colLetter = vbNullString Then GoTo ErrorLabel
        If Len(colLetter) > Len(MAXIMUM_COL_LETTER) Then GoTo ErrorLabel
        If Len(colLetter) = Len(MAXIMUM_COL_LETTER) And _
            colLetter > MAXIMUM_COL_LETTER Then GoTo ErrorLabel
    
        Dim i As Long
        For i = 1 To Len(colLetter)
            Dim targetCharacter As String
            Dim asciiCode As Long
            targetCharacter = Left(Right(colLetter, i), 1)
            asciiCode = Asc(targetCharacter)
            If asciiCode < 65 Or asciiCode > 90 Then GoTo ErrorLabel
            colNumber = colNumber + (asciiCode - 64) * 26 ^ (i - 1)
        Next
    
        ConvertColValueMutually = colNumber
    End If

    Exit Function
ErrorLabel:

    ConvertColValueMutually = "ERROR"
End Function

使用方法

第一引数に列番号か列文字を渡す

仕様等

• 引数で文字を入力するときは小文字でも大丈夫です
• エラーで弾かれたものはErrorLabelに飛ばされるので適宜処理を記入して下さい
  (とりあえず文字列"ERROR"を返します)

注意点

以下はエラーになります。(以下、第一引数を引数と言う)

• 引数で空白("")を渡す
• 引数(数字)が小数
• 引数(数字)が0以下
• 引数(数字)が最大列の数字(現状16384)より大きい
• 引数(文字)がアルファベットではない
• 引数(文字)が最大列の文字列(現状"XFD")より大きい

引数で#REF!とか#DIV/0!とか渡すと止まることに気付いてるけど対処はしてないです

その他

セルに入力させた列文字を取得・変換して
列番号としてマクロ内で使うという流れがメインかなって勝手に思ってます

---

ConvertColValueMutuallyの中にConvertColNumberToColLetterとConvertColLetterToColNumberという2つの関数を含ませて分割しようかとも思ったけれど、色々考えて止めました。分割版も一応別記事で書こうかね。

入力された列文字が最大列の文字列("XFD")を超えていないかを判定するときに
変換後の列番号が最大列の数字を超えていないかで判断してるのにモヤっとしている。
分割版のときはなんかアルゴリズム変えたい。

2024/8/21追記
分割版のマクロ書きました！以下へどうぞ

---

分割版を書いた時に一部コード変更しました。
一応、修正前は以下の通り。

(前略)
    Else
        If Len(colValue) > Len(MAXIMUM_COL_LETTER) Then GoTo ErrorLabel
        colLetter = colValue

        Dim i As Long
        For i = 1 To Len(colLetter)
            Dim targetCharacter As String
            Dim asciiCode As Long

            targetCharacter = Left(Right(colLetter, i), 1)
            asciiCode = Asc(UCase(targetCharacter))
            If asciiCode <= 65 And asciiCode >= 90 Then GoTo ErrorLabel
            colNumber = colNumber + (asciiCode - 64) * 26 ^ (i - 1)
        Next

        If colNumber > MAXIMUM_COL_NUMBER Then GoTo ErrorLabel

        ConvertColValueMutually = colNumber
    End If
(後略)