【入門編】初心者向け EXCEL VBA (6/10) – 現在のシートを加工して新シートを作成する【前半】

現在のところ、以下の７つのEXCEL VBA講座がある。

第一回目	【入門編】初心者向け EXCEL VBA (1/10) – 今年こそ使えるようになりたい人
第二回目	【入門編】初心者向け EXCEL VBA (2/10) –実際にVBAを開発する手順はこれだ
第三回目	【入門編】初心者向け EXCEL VBA (3/10) – EXCEL VBAのFor Nextループをマスターする
第四回目	【入門編】初心者向け EXCEL VBA (4/10) – EXCEL VBAのFor Nextループで文字列を置換する
第五回目	【入門編】初心者向け EXCEL VBA (5/10) – VBAユーザーフォームは男のロマン【これで完璧】
第六回目	【入門編】初心者向け EXCEL VBA (6/10) – 現在のシートを加工して新シートを作成する【前半】
第七回目	【入門編】初心者向け EXCEL VBA (7/10) – 現在のシートを加工して新シートを作成する【後半】

それと、読者の皆さんの疑問に答えるシリーズも開始した。

	タイトル	質問者さん(敬称略)
第一回目	【ワレコEXCEL講座】エクセルファイル①をエクセルファイル②に読み込む	しがない事務員

当記事はそのEXCEL VBA講座の第六回目。

今回の例題では、以下のテクニックを習得しよう。

現在のシート（Sheet1）に何らかのデータがある（名簿、住所録、財務表、など）
そのデータを加工して新シートSheet1Newを作成し加工済データを保存する
データの加工ではループ処理で各行の各列を順番に処理する手法を学ぶ（長方形領域）

だ。

この説明だけでは、今一つ実感が湧かないかもしれない。

具体的に何を習得するかと言うと、過去の例題で行った文字列置換処理に対して上に挙げたテクニックを取り入れて、より汎用性のあるテクニックを習得するのだ。

では、本題に入ろう。

サンプルVBAダウンロード

本ページで使用したEXCELファイルをダウンロードしたい人はここからどうぞ。

【入門編】初心者向け EXCEL VBA (6/10) – 現在のシートを加工して新シートを作成する【前半】用のxlsmファイル

過去の例題でやった事
1. 過去の例題を復習
  1. 加工前のデータ：　B列の社名に（株）がある
  2. 加工後のデータ：　社名の（株）を株式会社に置き換えてA列に書いた結果
2. 過去の例題の問題点
さっそく今回の例題に取り掛かる
指定した名前のシートが無い場合に限り追加するVBA
1. 指定した名前のシートが無い場合に限り追加するVBAコード解説
  1. Function ExistSheet() はシートの存在判定関数
  2. 指定した名前のシートが無い場合に限りシートを追加する関数
まとめ
1. エクセルの本を読む

過去の例題でやった事

過去の例題ではおかき会社の社名があるB列のデータを加工した。

過去の例題を復習

加工前のデータ：　B列の社名に（株）がある

図1.　加工前のデータ

上図のデータの中にある（株）を株式会社に置き換えてA列に書いたのが下図だ。

加工後のデータ：　社名の（株）を株式会社に置き換えてA列に書いた結果

図2.　加工後のデータ（前回の例題）

単純な処理ではあるが、これで文字列置換の基本を学ぶ事が出来たと思う。

過去の例題の問題点

しかし幾つかの問題がある。

A列にデータを書いたけれど一般にはA列には別のデータがある場合もあるのでそこには書きたくない。
文字列置換処理ではFOR NEXT文を使ってB列の4行目から19行名までを処理したが、B列だけでなくC列、D列、E列も処理したい場合もある。つまり長方形の領域のデータを扱いたい。
文字列置換ではカッコの文字が全角や半角の場合があるので、こんな文字列　（株）、(株)、（株)、(株）を置換関数内にズラズラと並べたが、ちょっとダサい。もう少しスマートなテクニックは無いの？
（株）だけでなく（有）も有限会社に置き換えたい。

などである。

さっそく今回の例題に取り掛かる

今回の例題では、これらの問題点を改善して、より汎用性のあるVBAプログラミングのテクニックを学ぼう。

使用するデータ

おかき好きのワテが調査した新潟のおかき製造元のデータだ。

新潟県米菓工業協同組合		〒940-0048	新潟県長岡市台町1丁目8番4号
さくら製菓（株）	http://www.sakura-do.jp	〒957-0058	新発田市西園町1丁目11番22号
（株）栗山米菓	http://www.kuriyama-beika.co.jp	〒950-3134	新潟市北区新崎2661番地
（株）三幸	http://www.sanko-seika.co.jp	〒950-3134	新潟市北区新崎1丁目13番34号
亀田製菓（株）	http://www.kamedaseika.co.jp	〒950-0198	新潟市江南区亀田工業団地3丁目1の1
吉沢製菓（有）	http://www.gosennet.com/~yoshizawaseika/	〒956-0852	新潟市秋葉区柄目木101番地
（株）末広製菓	http://suehiroseika.co.jp	〒953-0131	新潟市西蒲区西長島779番地
浪花屋製菓（株）	http://www.naniwayaseika.co.jp	〒940-1104	長岡市摂田屋町2680番地
越後製菓（株）	http://www.echigoseika.co.jp	〒940-0056	長岡市呉服町1丁目4番地5
加藤製菓（株）	http://www9.ocn.ne.jp/~kato.k.k/	〒940-1106	長岡市宮内8丁目8番18号
岩塚製菓（株）	http://www.iwatsukaseika.co.jp	〒949-5492	長岡市浦9750番地
阿部幸製菓（株）	http://www.abeko.co.jp	〒947-0026	小千谷市上ノ山4丁目8番16号
竹内製菓（株）	http://www.takeuchiseika.com	〒947-0003	小千谷市大字ひ生乙1672番地
（有）山文	http://www.echigo-yamabun.com	〒949-7403	魚沼市根小屋1441-1
（株）ブルボン	http://www.bourbon.co.jp	〒945-0011	柏崎市松波4丁目2番14号
（株）新野屋	http://www1.ganba716.net/modules/aranoya	〒945-0055	柏崎市駅前1丁目5番14号
（株）みながわ製菓	http://www.minagawa-seika.co.jp/	〒943-0882	上越市中田原111番地

まず、この表データをマウスで選択して、上の図1.　加工前のデータのようにSheet1のB3の位置に貼り付ける。

その結果、Sheet1が図１.のようになっていれば準備完了だ。

開発タブをクリックしてVisual Basicの画面を出す

もし開発タブが見つからない人は以下の記事を参考にして開発タブを表示する。

【入門編】初心者向け EXCEL VBA (1/10) – 今年こそ使えるようになりたい人

開発タブのVisual BasicアイコンをクリックしてVBAエディタを開く（下図）

図3.　VBAで標準モジュールを追加する方法

開いたVBAエディタで標準モジュールを追加する（上図）。

そうすると、下図のように標準モジュール Module1 と言うのが追加される。

図4.　VBAに標準モジュールを追加した直後の状態

この標準モジュールはVBAプログラミングを行う場合には、自作の関数などはこの中に書いておくと良い。

今追加したModule1の白いウインドウに以下の関数をコピーして貼り付ける。

このコードは前回の例題で登場したものと全く同じである。

Option Explicit

Sub Test_ForLoop_Conv2()

    '【機能】
    '
    '　（株）を　株式会社　に置換する
    '
    '　各種のカッコ株に対応版
    '
    '（株）　　全角（　　株　　全角　）
    '(株)　　　半角 (    株　　半角  )
    '㈱        一文字で　カッコ株のUnicode文字
    '（株)     全角（　　株　　半角  )
    '(株）     半角 (　　株　　全角  ）
    '
    'などどれでも"株式会社"に置き換える。
    

    Dim r As Long

    Dim val_r As Variant

    For r = 4 To 19

        val_r = ActiveSheet.Cells(r, 2).Value
      
        val_r = Replace(val_r, "（株）", "株式会社")
        val_r = Replace(val_r, "(株)", "株式会社")
        val_r = Replace(val_r, "㈱", "株式会社")
        val_r = Replace(val_r, "（株)", "株式会社")
        val_r = Replace(val_r, "(株）", "株式会社")

        Debug.Print r, val_r
        
        Cells(r, 1) = val_r

    Next r

End Sub

コード1.　B列の4行目から19行目までの（株）文字を株式会社に置換するコード

無事にコードを貼り付けると以下のようになる。

復習の為に、試しに実行してみよう。

茶色の●の所をクリックしてブレークポイント（茶色●）を表示しておく。

次に、関数内のどこでも良いのでクリックしてマウスカーソルを置いておく。

図5.　Module1にVBAコードを貼り付けた状態

上部のツールバーにある緑色の三角ボタン ▶ （実行ボタン）をクリックする。

ブレークポイントで停止するのでもう一回三角ボタン ▶ （実行ボタン）をクリックする。

まあ不要ならブレークポイントは置かなくても良い。

そうすると、繰り返しになるが上の図2.　加工後のデータ（前回の例題）が表示される（下図）。

図6.　加工後のデータ（前回の例題）図2と同じ

さて、ここまでは前回までの復習である。

必要ならこの辺りで、今まで作成したエクセルファイルを適当な名前を付けて保存しておく。

VBAプログラムを含んでいるので、ファイル拡張子は、

.xlsm

となる。

例えば、

Book1.xlsm

など。

いよいよ、新しいテクニックを習得する。

新シートの作成テクニックを習得する

加工結果をA列に書くのでは無くて、新シートを作成してそこに書き込みたい。

こういう場合には、ある程度VBAを使いこなせる人でも、

「え~っと、VBAで新しいシートを作成するコマンドは何だっけ？」

「EXCEL　新シート　追加　VBA　で検索してみるか。」

と言うのでも良いのだが、もっと良い方法がある。

それも過去の例題で登場したマクロの記録だ。

エクセルのマクロは何でも知っている！

開発タブのマクロの記録をクリックし実行する

図7.　マクロの記録を実行する

マクロの記録をクリックするとこんな画面が出る（下図）

図8.　マクロの記録ダイアログが出たらそのまま OK ボタンをクリック

このダイアログウインドウの意味が良く分からなくても良いので、兎に角 OK ボタンをクリックする。

クリックすると、勝手にウインドウが消えるが何も起こらない。

でも、実はマクロの記録と言う機能がエクセルの裏で動いている。

新しいシートを追加する

図9.　新しいシートを追加した

シートを追加したらマクロのマクロの記録終了をクリックする（下図）。

図10.　マクロの記録（記録終了ボタンをクリック）

マクロの記録を終了しても何も起こらない。

記録したマクロを確認する

図11.　マクロの記録結果はModuleになる

マクロの記録を終了したら、標準モジュールの項目を見てみる。

Module2が自動生成されてそれをクリックしてみると、Sub Macro2() と言うのが見つかるだろう。

Sub Macro2()
'
' Macro2 Macro
'
'
   Sheets.Add After:=ActiveSheet
End Sub

コード2.　「新しいシートの追加」コマンド（マクロの記録で自動生成された）

まあ要するにこのコマンドを実行すると、現在のActiveなSheetの後ろ（After）に追加出来ると言う訳だ。

じゃあ、追加されたシートの名前は自動的に Sheet2 となったが、もし自分で名前を付けたい場合はどうするのか？

それもマクロに聞いてみる。

再びマクロの記録を実行する。

画面キャプチャーは省略するが、結果だけを書くと以下の様になるはずだ。

「Sheet2」を「追加された新シート」にした場合だ。

Sub Macro3()
'
' Macro3 Macro
'
'
 Sheets("Sheet2").Select
 Sheets("Sheet2").Name = "追加された新シート"
End Sub

コード3.　シートの名前を変えるコマンド（マクロの記録で自動生成された）

なお、マクロの記録を実行中にあなたが行った操作は全て記録されている。

もしどこかのセルをクリックしたり、シートを切り替えたりしただけでも、全部記録されている。

例えば上のコード3. では

 Sheets("Sheet2").Select

と言う行があるが、これは見ての通り、マウスでシート2を選択した操作である。

このコマンド自体はシート名を変更する処理には無関係だが、兎に角マクロは全部の操作を記録してくれる便利なやつだ。

そうすると、もうお分かりだと思うが、現在アクティブなシートの後ろに新シートを追加して、その名前を変更したい場合には、Macro2やMacro3を応用して、以下の関数を実行すると可能である。

Sub Macro新シート追加して名前を変えるマクロ()
   Sheets.Add After:=ActiveSheet
   Sheets("Sheet2").Name = "追加された新シート"
End Sub

コード4.　新シート追加し、シートの名前を変えるコマンド

ただし、これだと幾つか問題がある。

追加されたシート名は “Sheet2” を仮定しているので、もし “Sheet3″ が追加されてもその名前は変化せずに、”Sheet2” の名前が “追加された新シート” になってしまう。　
既に “追加された新シート” と言う名前のシートが有る場合にはエラーする。

などである。

指定した名前のシートが無い場合に限り追加するVBA

では、改良版を作成してみた。

sheetNameNew = "追加された新シート"

で指定したシートが無い場合に限りシートを追加してその名前に変更する。

もしこのシートが既に存在する場合には、そのシートをアクティブにする。

その改良版の関数がこれだ。即席で作ってみた。

Option Explicit

Sub Macro指定した名前のシートが無い場合に限り追加するマクロ()
 
   Dim sheetNameNew As String
   sheetNameNew = "追加された新シート"
 
   If ExistSheet(sheetNameNew) Then
      Sheets(sheetNameNew).Select ' こっちでも
     'Sheets(sheetNameNew).Activate ' こっちでも良い
   Else
      Sheets.Add After:=ActiveSheet
      ActiveSheet.Name = sheetNameNew
   End If
 
End Sub

Function ExistSheet(sheetName As String) As Boolean
   Debug.Print "Function ExistSheet in"
   Debug.Print "  sheetName ->" & sheetName & "<- が存在するのかどうか調べる"
 
   Dim i As Long
   Dim sheetName_i As String
   Dim iStr As String
 
   For i = 1 To ActiveWorkbook.Sheets.Count
      sheetName_i = ActiveWorkbook.Sheets(i).Name
      iStr = CStr(i)
      Debug.Print "  sheetName_i[" & iStr & "]->" & sheetName_i & "<-"
      If sheetName_i = sheetName Then
         ExistSheet = True
         Debug.Print "  sheetName ->" & sheetName & "<- は既にある。"
         Debug.Print "Function ExistSheet out"
         Exit Function
      End If
   Next i
   ExistSheet = False
   Debug.Print "  sheetName ->" & sheetName & "<- は存在しない。"
   Debug.Print "Function ExistSheet out"
End Function

コード5.　指定したシート名が無い場合に限り新シートを追加するVBA関数

おおっと、いきなり数十行のプログラムになったので戸惑う人もいるかもしれないが、一つずつ見て行けば理解出来る。

VBAの編集画面は以下の通り。

図12.　指定した名前のシートが無い場合に限り追加するVBA

指定した名前のシートが無い場合に限り追加するVBAコード解説

上のVBAコードは一つの Sub() と一つの Function() から成っている。

Sub Macro指定した名前のシートが無い場合に限り追加するマクロ()

Function ExistSheet(sheetName As String) As Boolean

SubとFunctionは同じようなものだが、違いはFunctionは実行が終わると何らかの値を返す。

Function ExistSheet() はシートの存在判定関数

この例では、引数 sheetName で与えたシート名を持つシートが存在すれば True（真）、無ければ False（偽）を返す関数だ。

要するに特定の名前を持つシートが有るのか無いのか調べる関数。

処理はコードを見れば大体分ると思うが、現在のアクティブブックに対して、全シートをFOR NEXTで順番にループして、その名前を調べる。引数で与えたシート名に一致するかどうかをIF文で判定している。

デバッグで分かり易くするために Debug.Print文を多用しているが、不要なら削除しても動作する。

Debug.Printの出力結果はイミディエイトウィンドウに表示されるので

ツールバー

　表示

　　イミディエイトウインドウ

で表示出来る。

なお、アクティブブックと言うのは、もし複数のEXCELファイルを開いている場合には、Windowsの画面で現在アクティブなEXCELが開いているブックだ。

指定した名前のシートが無い場合に限りシートを追加する関数

シートを追加する関数が、

Sub Macro指定した名前のシートが無い場合に限り追加するマクロ()

この Sub だ。

先ほど説明した ExistSheet() を利用してシートの存在を判定している。

もしシートが無い場合に限り新しいシートを追加している（下のコード）。

      Sheets.Add After:=ActiveSheet
      ActiveSheet.Name = sheetNameNew

コード6.　新規シートを追加した直後にシートを変更する

なお、この2行目が味噌である。

最初に登場したマクロでは、新規に追加された特定のシート名 “Sheet2” の名前を変更していた（下のコード）

 Sheets("Sheet2").Name = "追加された新シート"

でも、新規に追加されたシート名が毎回 “Sheet2” とは限らない。

ではどうするのかと言うと、新規に追加されたシートはアクティブになるので、上のコード6. のように ActiveSheet.Name を変更すると、上手い具合に行く。

ここまでで今回の例題の半分くらいの説明が終わった。

あとまだ半分くらいある。

多少疲れている人もいると思うが、この後半部分が重要だ。

このテクニックを覚えると、エクセルのデータを扱う為の一般的な手法をマスター出来る。

まとめ

さて、この時点で、以下の準備が完了しているものとする。

おかきの会社名の一覧表をシートに貼った。
（株）を株式会社に変更する単純な処理は成功した
新しいシートを追加する関数を理解出来た

これらのテクニックを使って、新しいシートに（株）を株式会社に変更した結果を書き込むプログラムを作成してみよう。

と思ったのだが、ちょっと長くなりそうなのでその部分は次の記事にする事にした。

続く

エクセルの本を読む

入門者のExcel VBA―初めての人にベストな学び方 (ブルーバックス)

講談社

この本は講談社の有名なブルーバックスシリーズだ。2012年4月刊で、現在も改訂されて出版が続いている人気の本だ。アマゾンのレビューでも高評価だ。

小型の本なので通勤通学電車の中で読めるので、ビジネスマン必読の一冊だ。

ちなみに、ワテは読んでいない。

なんでやねん！

たった1日で即戦力になるExcelの教科書

技術評論社