列名変換
久々にExcelでマクロ組んでいて列名を数字に変換しないといけない部分が出てきた。
そういえば、ずいぶん前のはてブにExcelの列名変換をお題にプログラミングコンテストをして〜というエントリが上がってて、自分でもやってみようとVBAで作ってみた記憶があった。が、マイドキュメントあさってみたがそれっぽいファイルが残っていない。
お題があったblogの別エントリで回答がのっていたはずだし、ブコメや関連リンクでさまざまな回答寄せられていたことは覚えてる。たぶんブックマークからたどればそれらを見つけることは可能。だが、何となく悔しいので、あえて元エントリも回答も見ずに作り直してみた。
Option Explicit Sub test() Dim s As String Dim i, c For i = 1 To 1000 s = ColStr(i) c = StrCol(s) Sheets("Sheet2").Cells(i, 1).Value = i Sheets("Sheet2").Cells(i, 2).Value = s Sheets("Sheet2").Cells(i, 3).Value = c Next i End Sub ' 文字列から列番号を返す Function StrCol(cs As String) Dim up, l, i, r, c up = StrConv(cs, vbUpperCase) l = Len(cs) r = 0 If l > 0 Then For i = 0 To (l - 1) c = Asc(Mid(up, l - i, 1)) - &H40 r = r + ((26 ^ i) * c) Next i End If StrCol = r End Function ' 列番号から文字列を返す(A〜ZZZ) Function ColStr(c) As String Dim C1, C2, C3, cc If c > (676 + 26) Then C1 = Int(Int(c / 26) / 26) C2 = Int(Int(c / 26) Mod 26) C3 = c Mod 26 If C2 = 0 Then C1 = C1 - 1: C2 = 26 If C3 = 0 Then C2 = C2 - 1: C3 = 26 ColStr = Chr(C1 + &H40) & Chr(C2 + &H40) & Chr(C3 + &H40) ElseIf c > 26 Then C1 = Int(c / 26) C2 = c Mod 26 If C2 = 0 Then C1 = C1 - 1: C2 = 26 ColStr = Chr(C1 + &H40) & Chr(C2 + &H40) ElseIf c > 0 Then ColStr = Chr(c + &H40) End If End Function
VBAエディタを開いてからStrColを正しく動くようにするまでに45分。変換のロジックはわかっていたものの、どういう関数を使ってどう評価させればいいのかという部分でだいぶ時間を食ったなあという感想。
検算用に使っているColStrはこれまた必要に迫られてずいぶん前に作った関数(d:id:Nikki_A:20100728)。本当は再帰など駆使して作るべきものなんだろうけど、現在のExcelの仕様では3ケタあれば十分なのでこのままでいっかーと放置してある。
と。
ここまで元エントリ読まずに書きあげ、それから自分のブクマの過去ログあさって発掘してきた。
http://blog.jnito.com/entry/20111102/1320253815 <元記事
http://b.hatena.ne.jp/entry/blog.jnito.com/entry/20111102/1320253815 <ブクマ
ああ……問題2の存在を忘れていた。ColStrをきちんと作り直すか。また忘れたころに。