列の英字と数字を変換する
VBAで列（横）座標を指定する際に、行（縦）と同じように連番でほしい場合があります。Cells関数を使う場合などですね。
しかし列座標は英字になっており、AからZ、Zの次はAAと独自ルールで設定されています。このことから、列BGは数字でいうと何番？というような場合にすぐにわかりません。
そのような場合に英字列から数字、数字列から英字を取得する方法を紹介します。
列の英字から数字を取得するコード（A列→1列目）
列位置を数字で取得するには、対象列のRangeオブジェクトのColumnプロパティを参照することで可能です。
例えばB列であれば、以下のように取得できます。
|
1 |
s = Range("B:B").Column |
列の数字から英字を取得するコード（1列目→A列）
列位置を英字で取得するには、対象列のRangeオブジェクトのAddressプロパティの列の英字部分を参照することで可能です。
Addressプロパティは「$A$1」のように$で区切られて表現されます。これをSplit関数で$で区切ると、配列の１番目に列の英字、２番目に行の数字が返されます。
それを利用して、例えば2列目であれば、以下のように取得できます。
|
1 |
s = Split(Cells(, 2).Address, "$")(1) |
お遊びコード。列の英字から数字を「がんばって」取得するコード
以下はおあそびコードです。実際に使うのは上のを使ってください。
上記のRangeオブジェクトのプロパティを使う方法ではなく、文字コード表を使って頑張って取得する方法です。
CnvColAlphaToInt関数で列座標の数字を取得します。この関数はExcel2007以降の16384列に対応しています。英字を数字に変換する個所はGetColInt関数で切り出しています。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
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 |
お遊びコード。列の数字から英字を「がんばって」取得するコード
上とは逆に数字から英字に戻すマクロです。
こちらもExcel2007に対応済みで、数字を英字に変換する個所はGetColAlpha関数に切り出しています。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
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 |