列の英字を数字で取得する　数字を英字で取得する

VBAで列（横）座標を指定する際に、行（縦）と同じように連番でほしい場合があります。

Cells関数を使う場合などですね。

しかし列座標は英字になっており、AからZ、Zの次はAAと独自ルールで設定されています。

このことから、列BGは数字でいうと何番？というような場合にすぐにわかりません。

そのような場合に以下のCnvColAlphaToInt関数で列座標の数字を取得します。

この関数はExcel2007以降の16384列に対応しています。

英字を数字に変換する個所はGetColInt関数で切り出しています。

Function CnvColAlphaToInt(ByVal a_sCol As String) As Long
    Dim iRet
    Dim iPower                                      '// 乗数
    Dim iLen                                        '// 引数文字列長
    Dim sChar                       As String       '// １文字
    
    iRet = 0
    iLen = Len(a_sCol)
    iPower = 0
    
    '// 文字列の右側から順に計算
    Do
        If (iPower >= iLen) Then
            Exit Do
        End If
        
        sChar = Mid(a_sCol, iLen - iPower, 1)
        
        iRet = iRet + GetColInt(sChar) * (26 ^ iPower)
        
        iPower = iPower + 1
    Loop
    
    CnvColAlphaToInt = iRet
End Function

Function GetColInt(a_sCol As String) As Long
    Dim iRet
    
    iRet = Asc(a_sCol) - 64
    
    GetColInt = iRet
End Function

Function CnvColAlphaToInt(ByVal a_sCol As String) As Long

Dim iRet

Dim iPower '// 乗数

Dim iLen '// 引数文字列長

Dim sChar As String '// １文字

iRet = 0

iLen = Len(a_sCol)

iPower = 0

'// 文字列の右側から順に計算

If (iPower >= iLen) Then

Exit Do

End If

sChar = Mid(a_sCol, iLen - iPower, 1)

iRet = iRet + GetColInt(sChar) * (26 ^ iPower)

iPower = iPower + 1

Loop

CnvColAlphaToInt = iRet

End Function

Function GetColInt(a_sCol As String) As Long

Dim iRet

iRet = Asc(a_sCol) - 64

GetColInt = iRet

End Function

また、逆に数字から英字に戻すマクロです。

こちらもExcel2007に対応済みで、数字を英字に変換する個所はGetColAlpha関数に切り出しています。

Function CnvColumnIntToAlpha(ByVal a_lCol As Long) As String
    Dim sRet
    Dim sTemp
    
    If (a_lCol <= 26) Then
        sRet = GetColAlpha(a_lCol)
    ElseIf (a_lCol < 702) Then
        '// 左文字
        sTemp = GetColAlpha(Int(a_lCol / 26))
        sRet = sTemp
        '// 右文字
        sTemp = GetColAlpha(a_lCol Mod 26)
        sRet = sRet & sTemp
    ElseIf (a_lCol = 702) Then
        sRet = "ZZ"
    ElseIf (a_lCol <= 16384) Then
        '// 一番左文字
        sTemp = GetColAlpha(Int(a_lCol / 26 / 26))
        sRet = sTemp
        '// 真ん中文字
        sTemp = GetColAlpha(Int(a_lCol / 26) Mod 26)
        sRet = sRet & sTemp
        '// 右文字
        sTemp = GetColAlpha(a_lCol Mod 26)
        sRet = sRet & sTemp
    Else
        Call MsgBox("最大列を超えてるよ", vbOKOnly)
    End If
    
    CnvColumnIntToAlpha = sRet

End Function

Function GetColAlpha(a_lCol As Long) As String
    Dim sRet
    
    sRet = Chr$(a_lCol + 64)
    
    GetColAlpha = sRet
End Function

Function CnvColumnIntToAlpha(ByVal a_lCol As Long) As String

Dim sRet

Dim sTemp

If (a_lCol <= 26) Then

sRet = GetColAlpha(a_lCol)

ElseIf (a_lCol < 702) Then

'// 左文字

sTemp = GetColAlpha(Int(a_lCol / 26))

sRet = sTemp

'// 右文字

sTemp = GetColAlpha(a_lCol Mod 26)

sRet = sRet & sTemp

ElseIf (a_lCol = 702) Then

sRet = "ZZ"

ElseIf (a_lCol <= 16384) Then

'// 一番左文字

sTemp = GetColAlpha(Int(a_lCol / 26 / 26))

sRet = sTemp

'// 真ん中文字

sTemp = GetColAlpha(Int(a_lCol / 26) Mod 26)

sRet = sRet & sTemp

'// 右文字

sTemp = GetColAlpha(a_lCol Mod 26)

sRet = sRet & sTemp

Else

Call MsgBox("最大列を超えてるよ", vbOKOnly)

End If

CnvColumnIntToAlpha = sRet

End Function

Function GetColAlpha(a_lCol As Long) As String

Dim sRet

sRet = Chr$(a_lCol + 64)

GetColAlpha = sRet

End Function

是非、活用してください。

関連記事