列のアルファベットを数値へ、数値をアルファベットへ変換するExcelVBA

列のアルファベットを数値へ、数値をアルファベットへ変換する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で列番号にしてあげるだけ。

という感じです。あくまで私のやり方なので、もっと良い方法があったら是非教えてください!

ちなみに

プログラム中で、ではなくシート上の表示を 数値←→アルファベット に切り替えるにはこちらの方法が簡単ですー。

関数についてはこんな記事もあります。

公開日:2013/02/25

6件のコメント

  1. Toyoaki より:

    こんにちは。

    独学ですが会社で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 Sub
    
    Function 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 Function
    
    • *you より:

      Toyoakiさん、コメントありがとうございます。
      Split!なるほどそんな方法もあるんですねー!動作確認も、嬉しいです!ありがとうございました(*´∀`*)

  2. trashbox より:

    参考にさせていただきました。
    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 Function
    
    • *you より:

      trashboxさん、追加でいただいたコメントを使って直させていただきました。HTMLがコードを変換してしまって全容がきちんと読めなくて申し訳ないです。アルファベット→数値変換の部分だけは読み取れたので試させていただきましたが、凄いですね熱いですね!!こんなふうにして数値にできるとは…(*゚Д゚*)

  3. YUNO より:

    Valは関数名として存在するので変数名には使わない方がいいと思います。とりあえずvaとしておきます。

    数字からアルファベットへの変換ですが、Cellsは行が省略できます。省略すると自動的に1行目とみなされますが、この場合はいくつでも無関係ですね。

    change_val = Split(Cells(va).Address, "$")(1)
    

    アルファベットから数字への変換にはこんな方法もあります。
    Cellsの列番号はアルファベットでも指定できるのです。ただし列をアルファベットで指定する場合は行を省略してもコンマは省略できません。ややこしいですね。

    change_val = Cells(, va).Column
    
    • *you より:

      YUNOさん、返信遅くなってしまって申し訳ありません。

      Val関数っていうのがあるんですね…! 他にもまだまだ知らないことがあるのだなと勉強になりました。ありがとうございました!


trashbox へ返信する コメントをキャンセル

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください

コメントは承認制ですので、反映までしばらくお待ち下さい。(稀にスパムの誤判定にて届かないこともあるようですので、必要な際はお問い合わせからお願い致します。)