【入門編】 初心者向け EXCEL VBA (7/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講座の第七回目。

では、本題に入ろう。

現在のシートを加工して新シートを作成する【後半】

初心者向けEXCEL VBA講座の第7回目であるが、第6回が前編なのでまだお読みで無い方は前編をお読み頂くのが良いだろう。

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

当記事で使うVBAプログラム（拡張子 .xlsm）は上の第六回記事の中からダウンロード出来る。 

 

第6回を終わった時点で、以下の準備が完了しているものとする。

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

 

図1.　Sheet1に貼ったおかき会社の一覧

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

今まで使って来た関数の問題点

今までに何度も登場したこの関数では、幾つもの問題がある。

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列の（株）を株式会社に置換してA列に書き込む関数（今までに出て来た）

 

処理対象の行が4行目から19行目までに限定されている。

For r = 4 To 19
   val_r = ActiveSheet.Cells(r, 2).Value

また、Cells(r, 2) の 2 が定数であるが、これは Cells(行, 列) の列が 2 つまり、

A列は 1

B列は 2

C列は 3

 ・・・

となるので　B列限定の処理だ。

この辺りをもう少し汎用性のあるものにすれば、各種の応用に利用出来る。

 

 早速、即席で作ってみた。

 

シート名・処理範囲を指定して文字列置換し、結果を新シートに書き出すVBA

以下に示す2つのVBAコードをそれぞれ Module1 と Module2 に保管する。

なお、2つの Module に分けずに一つの Module1 に全部書き込んでも問題は無い。

分けた理由は、一つにまとめるとコードが長くなるので、単に2つに分けただけである。

その辺りは、好き好きなので皆さんの好きにして頂きたい。

Shee1シートの長方形領域データを処理して新シートに書き出す関数

まず、こちらの関数が処理対象のシート名と、処理範囲の行と列を指定して行と列の二重ループでその領域のデータを取り出す。

Option Explicit

Sub Test_ForLoop_Conv3()

    '【説明】
    '
    ' sheetNameOld　このシート名のシートのデータを処理する
    ' sheetNameNew  処理結果をこのシートに書く
    ' rSta　処理する開始行
    ' rEnd　処理する終了行
    ' cSta　処理する開始列
    ' cEnd　処理する終了列
    ' この行ｘ列の範囲の文字列データに対して以下の処理を行い、別シートに書く
    '
    '【処理の内容】
    '
    '　（株）を　株式会社　に置換する
    '
    '　各種のカッコ株に対応版
    '
    '（株）　　全角（　　株　　全角　）
    '(株)　　　半角 (    株　　半角  )
    '㈱        一文字で　カッコ株のUnicode文字
    '（株)     全角（　　株　　半角  )
    '(株）     半角 (　　株　　全角  ）
    '
    'などどれでも"株式会社"に置き換える。
   
    Const rSta As Long = 4
    Const rEnd As Long = 19
    Const cSta As Long = 2
    Const cEnd As Long = 5
    Const sheetNameOld As String = "Sheet1"
    Const sheetNameNew As String = "Sheet1New"
    
    AddNewSheet_IfNotExist (sheetNameNew) 'sheetNameNewが無い場合に限り追加

    Dim r As Long
    Dim c As Long
    Dim val_r As Variant

    For r = rSta To rEnd
    For c = cSta To cEnd

      ' val_r = ActiveSheet.Cells(r, c).Value
        val_r = Sheets(sheetNameOld).Cells(r, c).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
        
        Sheets(sheetNameNew).Cells(r, c) = val_r
    
    Next c
    Next r

End Sub

コード1.　Module1の内容 

上の関数の処理内容自体は、今までに登場したように、（株）を株式会社に置き換えると言う単純なものだ。

ただし出力先が、新シートの同じセルの位置に書き出している。

 

なお、おかき会社のデータでは（株）があるのはB列だけであるが、まあ、セルデータ処理の一般的な状況を想定して

指定した行範囲（開始行 rSta、終了行 rEnd）

指定した列範囲（開始列 cSta、終了列 cEnd）

の長方形領域に対して、文字列置換を実行している。

結果は、別シートの同じセル位置に書き出す。

 

新規シートの作成関数、シートの存在判定関数

こちらのSub関数とFunction関数は、前回の講座で作成したものだ。

最初の Sub() は名前を変えて、かつ、引数でシート名を指定出来るように変えた。

また Subの名前も変更したが、まあ、そのあたりは好き好きなのでどんな名前でも良いだろう。

Option Explicit

'Sub Macro指定した名前のシートが無い場合に限り追加するマクロ() '旧関数名
Sub AddNewSheet_IfNotExist(sheetNameNew As String)
 
   '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
 

 コード3.　Module2の内容 

 

上のコードで後半部分の Function ExistSheet() も、前回の講座で説明したやつだ。

引数で指定したシート名を持つシートが現在アクティブなブックに有るのか無いのか調べる関数。色んな場面で利用出来ると思う。

 

さて、実行してみる。

Sub Test_ForLoop_Conv3() を実行してみる

関数 Sub Test_ForLoop_Conv3() を実行するために、その関数内にマウスカーソルを置いて、実行ボタン▶をクリックする。

そうすると、順調に行けば、処理が一瞬で行われて、新しいシート

Sheet1New

が作成される。

図2.　Sheet1Newが作成されてそこに処理結果が表示される

 

無事にこの図2に示すSheet1Newが生成されて、上図のようになれば成功だ。

B列の会社名は、文字列置換が実行されて（株）が株式会社に成っている。

このデータには無いが、もしC列、D列、E列にも（株）が有ればそれも株式会社に置き換わる。

今回の例題を各自で応用して新しい手法をマスターする

今回の例題で覚えた手法を使うと、いろんな場面で応用できる。

• 何らかのデータを処理して、新しいシートを作成してそこに出力する。
• ただし、新しいシートは毎回作成する必要は無いので、無い場合に限り作成する。

今回行ったこういう処理はEXCEL VBAで非常に良く行う処理なので、これを覚えれば色んな場面で役立つだろう。

 

もし必要なら、各自で以下の処理を追加してみると、さらに上級テクニックをマスター出来る。

Sheet1Newが既にある場合は、現状ではそのシートをアクティブにしているだけである。

でも場合によっては、Sheet1Newの中身を全部クリアしたい場合もある。

あるいは、既にSheet1Newがある場合には削除してから再作成 しても良い。

その方がスッキリするし。

こういう処理を行う場合には、何をするか？

それは、もう皆さんにはお分かりだと思うが、マクロの記録だ。

使うのはもちろん、マクロの記録

マクロの記録の意味や使い方は前回の例題や第2回目の例題にも登場したので、まだやった事が無い人はその記事を読んで頂くのが良い。

手順としては、

• マクロの記録を有効にする。
• Sheet1Newを全選択する。
• 削除キーを押してデータを全削除する。
• マクロの記録を終了する。
• VBAの編集画面に戻ると Module2、Module3 などの番号が付いたモジュールが自動生成されている。
• その中に今記録された Sub Macro2() のようなのが生成されている。

それを見れば、特定のシートの全データを削除するコマンドが分る。

その部分をコピーペーストして、自分のVBAプログラムに組み込めば良い。

 

同様に、シートを削除する処理もマクロの記録で取得する事が可能だ。

要するに、エクセルのマクロの記録はVBAプログラミングに必須であり、とっても役に立つ。

まとめ

全10回シリーズの初心者向け EXCEL VBA講座の第7回目が無事に終わった。

本記事はその第7回目であるが、第6回の前半部分に次ぐ後半部分となっている。

この2つの回をマスター出来れば、

何らかのシートの、指定した行列範囲（二次元の長方形領域）に対して、

行ｘ列の二重ループ処理でセルのデータを順番に読み取って、

そのデータに対して、文字列置換、数値データなら集計、平均など実施し、

新規作成した別シートにデータを書き出す。

こういう一連の処理を使いこなせるようになる。

もちろん、ここまで読んで頂いた皆さんはそのテクニックを完璧に習得出来ただろう。

 

この次の講座では、文字列処理に関して、EXCEL上級者でもあまり使いこなせる人がいない高等テクニックを説明する予定だ。

文字列の処理はEXCELのデータ加工でも最も頻繁に登場すると思うので、その高等テクニックを覚えると、もう貴方は文字列の処理に関しては一気に上級者レベルになれるだろう。

乞うご期待と言うやつやな。

まあ、希望者が多い場合には、すぐに執筆に取り掛かりますが…

無いか。

エクセルの本を読む

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

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

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

なんでやねん！

こちらの本は、EXCEL関連本で本日の時点でアマゾンランキング第一位だ。

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

あかんがな。

全てのMicrosoft Excel の 売れ筋ランキングをみる