古庄 潤(ふるしょう じゅん)

本業はエンジニア。ICに様々な機械をつなぎ,電流やら電圧を測定する。もちろん,これらの測定器もVBAでコントロールし,取り込んだデータもマクロで処理する。人呼んで、マクロの鬼軍曹!。

診断(1)
セルの文字列を指定文字で分解する

「先生,今日は何の研究ですか?」
「おぉ,ゲール君,見ろ!バチルス菌と白せん菌の戦いじゃ。壮絶なドラマじゃぞ」
「また,ゲームですか…。次の方,どーぞー」

今月の相談
「セルに取り込んだデータを分解して横のセルに代入したいのですが,どうしたらいいでしょうか?」

「どれどれ? ん~,なるほど。名前と住所などを分離したいわけじゃな(図1)。取り込む前に分けていれば…。まぁ,よい。取り込んでしまったものを処理する方法を考えよう。」
「今回の処方箋(リスト1)は,ループによる文字の分解と,キャラクタ・コード(ここではASCIIコード)による条件分岐じゃな。このマクロは,分解する文字列から順番に1文字を取り出し,そのASCIIコードを調べる。ここで条件分岐をして,その1文字がデータの区切りを示す文字でない場合は,文字列型変数に追加し,区切り文字だった場合は,文字を累積した文字列型変数の値をセルに代入する。代入するセルの位置は,変数jを使う。この変数は,文字列の代入が終ったところで,1だけインクリメントする。これにより,分解して取り出した文字列は,次々と右側のセルに代入されることになる。」

図1●分解したい文字列が記述されているワークシート
図1●分解したい文字列が記述されているワークシート
[画像のクリックで拡大表示]

Sub MOIJIRETU_BUNKAI_1()
  Dim R As Range
  Dim i As Integer
  Dim j As Integer
  Dim mySTR As String
  Dim myStringLen As Integer
  Dim myCHARA As String

  If Selection.Columns.Count > 1 Then
    MsgBox "選択範囲が正しくありません", vbCritical
    Exit Sub
  End If

  mySTR = ""
  For Each R In Selection
    myStringLen = Len(R.Text)
    j = 1
    For i = 1 To myStringLen
      myCHARA = Mid(R.Text, i, 1)
      Select Case Asc(myCHARA)
        Case 44
          R.Offset(0, j).Value = mySTR
          mySTR = ""
          j = j + 1
        Case Else
          mySTR = mySTR & myCHARA
      End Select
    Next i
    R.Offset(0, j).Value = mySTR
    mySTR = ""
  Next R
End Sub
リスト1●文字列を指定した文字で分解するMOIJIRETU_BUNKAI_1プロシジャ

 では,リスト1を詳しく見ていきましょう。

 最初のIfステートメントでは,複数の列(セルの横の並び)が選択されている場合は,メッセージを表示して処理を中止するようにしています。このマクロは,分解の対象となる文字列が格納されているセルの右側のセルに,分解した文字列を順番に代入します。したがって,代入先のセルが選択範囲に含まれている場合に処理を実行すると不測の事態を招く可能性があります。選択範囲は,文字列が代入されている行(セルの縦の並び)にしてください。

 次のFor Each...Nextステートメントは,選択されている範囲(セルの集団)から,順次一つのセルを取り出して,変数Rに代入するループです。このループの中で,Rは,CELLオブジェクトとして扱うことができます。

 変数R(CELLオブジェクト)のTextプロパティの値の文字数を,Len関数でカウントして変数myStringLenに代入します。CELLオブジェクトのTextプロパティは,CELLの値が数値でも,文字列でも,テキストとして取り出します。

 変数myStringLenは,文字列から文字を取り出して処理するForループで,カウンタ(ループの回数を制限する数値)として使用します。

 Forループに入る前に,変数jに1を代入します。この変数の値は,分解した文字列を代入するセルの列番号に利用します。

 続くFor...Nextは,分解の対象となる文字列の文字数ぶんループを繰り返します。ループのカウンタ変数iの値は,Mid関数(文字列から指定された文字数ぶんの文字列を返す)の中で取り出す文字の位置に利用します。

 Mid関数で任意の1文字を取り出し,変数myCHARに代入します。次の条件分岐(Selectステートメント)で,取り出した文字が,区切り文字(ここではカンマ)の場合と,そうでない場合に処理を分けます。その際,Asc関数で文字のASCIIコードを取得しています。ASCIIコード「44」は,カンマを示します。

 区切り文字だった場合は,変数mySTRの値をセルに代入した後,変数mySTRの値をクリアし,文字列を代入するセルの列番号となるjの値をインクリメントします。カンマでない場合は,変数mySTRの最後に取り出した文字を結合します。

 文字列の最後には区切り文字はない(という設定な)ので,Forループが終了したら,最後の文字列をセルに代入し,変数をクリアします。

 このマクロは,分岐する条件の数値を変更することにで,制御コードを含むあらゆるキャラクタ・コードを区切りとして文字列を分解することができます。

「では,ゲール君,マクロを使って文字列を分解してみたまえ」
「はい,先生。おや? 全く何も起きませんよ」
「そんなバナナ!?」
「本当です」
「うむ~,ASCIIコードの44番はカンマ(,)。マクロは間違っていないようじゃが」
「先生,これは半角のカンマとは,少し形が違うようですが」
「そうか! では,マクロでASCIIコードを調べてみよう。分解する文字列のセルを選択して,私が作ったVIEW_ASCIIプロシジャ(リスト2)を実行したまえ」

Sub VIEW_ASCII()
  Dim i As Integer
  Dim mySTR As String
  Dim myMSG As String

  mySTR = ActiveCell.Value
  For i = 1 To Len(mySTR)
    myMSG = myMSG & Asc(Mid(mySTR, i, 1)) & " "
  Next i

  myMSG = Mid(myMSG, 1, Len(myMSG) - 1)

  MsgBox myMSG
End Sub
リスト2●文字列のASCIIコードをメッセージボックスに表示するVIEW_ASCIIプロシジャ

 リスト2では,まずセルの値を文字列型変数mySTRに代入します。そして,mySTRから1文字を取り出し,そのASCIIコードを半角スペースでつないで変数myMSGに結合します。最後に,myMSGをメッセージボックスに表示します(図2)。

図2●VIEW_ASCIIプロシジャを実行したところ。文字列のすべてのASCIIコードをメッセージボックスに表示している
図2●VIEW_ASCIIプロシジャを実行したところ。文字列のすべてのASCIIコードをメッセージボックスに表示している
[画像のクリックで拡大表示]

「先生,出ました」
「うむ。5番目と9番目と14番目に164と表示されておるな。ゲール君,この文字列にあるカンマのASCIIコードは164じゃ」
「え~っと,ASCIIコードの164は…。あった! 半角かな文字のカンマです」
「そうか,それで原因はわかった。この元データも,おそらくは何かのプログラムで作ったか,テキスト・ファイルにエクスポートしたものだろう。そのプログラムで,区切り文字の指定を間違ったということだな。リスト1の分岐条件を164に変更して,マクロを実行じゃ(図3)」
「先生,出ました。カンマを区切り文字として,データが分解されています」
「そうか!やっぱり,わしは天才じゃ」
「でも先生,TextToColumnsメソッドを使ったほうが簡単ではないですか?」
「ドキッ! ゲール君も成長したのぅ。その通り,Excel/VBAにはTextToColumnsメソッドが実装されておる。それを使えば,もっと簡単に書ける」

図3●正しく分解された文字列
図3●正しく分解された文字列
[画像のクリックで拡大表示]

 TextToColumnsメソッドは,セルに入力されているデータ(一つのセル内で,カンマ,スペース,タブなどで区切られたデータ)を,複数の列に区切ります。リスト3は,そのTextToColumnsを使ったマクロです。複数に改行していますが,実際には一つのステートメントとそのオプションです。オプションはたくさんあるので,そのすべてを説明するのは省きます(ヘルプを参照してください)。ここではポイントだけを紹介します。

Sub MOIJIRETU_BUNKAI_2()
  Selection.TextToColumns _
    Destination:=Cells(Selection.Row, Selection.Column + 1), _
    DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, _
    ConsecutiveDelimiter:=True, _
    Tab:=False, _
    Semicolon:=False, _
    Comma:=False, _
    Space:=False, _
    Other:=True, OtherChar:=Chr(164), _
    FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), _
    TrailingMinusNumbers:=True
End Sub
リスト3●TextToColumnsメソッドを使ったMOIJIRETU_BUNKAI_2プロシジャ

 TabからSpaceまでのオプションは,デフォルトで用意されている区切り文字です。タブ,セミコロン,カンマ,スペースを区切り文字として分解する場合は,これらのオプションをTrueにします。これ以外の文字を区切りとして使いたい場合は,OtherオプションをTrueにし,OtherCharオプションに区切り文字のコードを指定してマクロを実行します。

「先生,やっぱり,こっちのほうがスマートですねぇ。最初からこれを使えばよかったのに」
「ゲール君,何事も基礎じゃよ。TextToColumnsメソッドの中身は,リスト1のようなもんなんじゃ。TextToColumnsメソッドはそれとしてしか使い道が無いが,リスト1は,いかようにも応用できる。基礎を知っていれば怖いものなしと言うことなんじゃよ」
「本当にぃ?」
「当たり前じゃ,ゴホッ,ゴホッ」
「ところで先生,ASCIIコードとか制御コードとかは,何ですか」
「えっ! それも知らずにわしの話を聞いておったのか。まぁよい,それは次回たっぷりと解説してやろう」