列のアルファベットを数値へ、数値をアルファベットへ変換するExcelVBA
取得した列番号をRange("○1")のように使いたかったり、アルファベットで取得できちゃったけど数値で計算したいんだよねとかそういうのがたまにあるので、どっちも使える関数を作ってみました。
コード
書いた環境がExcel2003なのでそれ以上の、列が256より大きいverで動くかわからんですが…。そっちでも動いたらいいな！
追記1：2007以降でも動作確認できました！
追記2：コメントのご指摘を受けてコードを少し改変しました。
Sub Sample() Dim va As Variant, c_va As Variant va = "AA" '列のアルファベットor数値 c_va = CNumAlp(va) 'vaを関数へ引渡してc_vaへ変換後の値を格納 MsgBox "変換後の値は" & c_va & "です" End Sub
Function CNumAlp(va As Variant) As Variant '変換する関数
Dim al As String
If IsNumeric(va) = True Then '数値だったら
al = Cells(1, va).Address(RowAbsolute:=False, ColumnAbsolute:=False) '$無しでAddress取得
CNumAlp = Left(al, Len(al) - 1)
Else 'アルファベットだったら
CNumAlp = Range(va & "1").Column '列番号を取得
End If
End Function
解説
1つめが普通のプロシージャ、2つめが変換してくれる関数になってます。
1つめプロシージャは単なる例なので、実用では2つめのCNumAlp関数だけコピっておけば、あとはどこかで
変換後 = CNumAlp(変換前)
のように書けば変換してくれます。数値もアルファベットもカバーできるように、変換前後の変数はVariantで宣言しておいてください。
変数宣言についての入門記事はこちら。
数値だった場合
CNumAlp関数の4行目で数値か判断して、Trueなら行を1に固定して.Addressを取得しています。ちょっと長いのは、行列ともに$（絶対値表記）をつけないよ、という意味です。
前にまとめたことがあります。
行を1と固定しているので、必ずA1,AA1のような形になってくれるはず。Lenで文字数を取得して-1した数だけ左から抜き出せば完成です。
アルファベットだった場合
こちらはもっと簡単。Rangeの形で、.Columnで列番号にしてあげるだけ。
という感じです。あくまで私のやり方なので、もっと良い方法があったら是非教えてください！
ちなみに
プログラム中で、ではなくシート上の表示を 数値←→アルファベット に切り替えるにはこちらの方法が簡単ですー。
関数についてはこんな記事もあります。
ExcelVBAに興味をお持ちの方は、こちらの記事もどうぞ！
- これからExcelのマクロを始めたいという方に！簡単な練習問題作りました。
- 私がExcelVBAでよく使う便利なコード・スニペットまとめ
- プログラム初心者さんへ贈る、エラーが起きたら試してみて欲しいこと
- ExcelVBAのクラスモジュールって何？という人向けの使い方まとめ
書籍を執筆しています。
6件のコメント
こんにちは。
独学ですが会社でExcelVBAを使ったりしています。
僕はアルファベットと数字を分ける時にSplitを使っています。
ちなみに、*youさんの作ったsampleですが、2010でも動きましたよ！
Sub sample() Dim val As Variant, c_val As Variant val = "XFD" '列のアルファベット 'val = 2 ^ 14 '列の数値 c_val = change_val(val) 'valを関数へ引渡してc_valへ変換後の値を格納 MsgBox "変換後の値は" & c_val & "です" End SubFunction change_val(val As Variant) As Variant '変換する関数 If IsNumeric(val) = True Then '数値だったら change_val = Split(Cells(1, val).Address, "$")(1) Else 'アルファベットだったら change_val = Range(val & "1").Column '列番号を取得 End If End FunctionToyoakiさん、コメントありがとうございます。
Split！なるほどそんな方法もあるんですねー！動作確認も、嬉しいです！ありがとうございました(*´∀｀*)
参考にさせていただきました。
Cellの情報ではなく計算で出す方法を考えてみました。
自動判定で文字→数字、数字→文字になり、
Excel2010の上限を超える場合、0を返すようにしております
Function change_val(ByVal g As Variant) As Variant Dim s As Variant Dim i As Integer 'If IsNumeric(g) Then ' If g 3 Then g = StrReverse(g) For i = 1 To Len(g) s = s + CInt(Asc(Mid(g, i, 1)) - 64) * (26 ^ (i - 1)) Next If s > 16384 Then s = 0 End If ' End If 'End If change_val = s End Functiontrashboxさん、追加でいただいたコメントを使って直させていただきました。HTMLがコードを変換してしまって全容がきちんと読めなくて申し訳ないです。アルファベット→数値変換の部分だけは読み取れたので試させていただきましたが、凄いですね熱いですね！！こんなふうにして数値にできるとは…(*ﾟДﾟ*)
Valは関数名として存在するので変数名には使わない方がいいと思います。とりあえずvaとしておきます。
数字からアルファベットへの変換ですが、Cellsは行が省略できます。省略すると自動的に1行目とみなされますが、この場合はいくつでも無関係ですね。
アルファベットから数字への変換にはこんな方法もあります。
Cellsの列番号はアルファベットでも指定できるのです。ただし列をアルファベットで指定する場合は行を省略してもコンマは省略できません。ややこしいですね。
YUNOさん、返信遅くなってしまって申し訳ありません。
Val関数っていうのがあるんですね…！ 他にもまだまだ知らないことがあるのだなと勉強になりました。ありがとうございました！
コメントは承認制ですので、反映までしばらくお待ち下さい。（稀にスパムの誤判定にて届かないこともあるようですので、必要な際はお問い合わせからお願い致します。）
YouTubeでQ&Aコンテンツを企画しています
運営しているYouTubeチャンネルで、ご相談やご質問を募集しています。動画のコメントやお問い合わせページからお気軽にご相談をお寄せください。