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

この記事は約14分で読めます。
スポンサーリンク

現在のところ、以下の7つの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 処理する終了列
    ' この行x列の範囲の文字列データに対して以下の処理を行い、別シートに書く
    '
    '【処理の内容】
    '
    ' (株)を 株式会社 に置換する
    '
    ' 各種のカッコ株に対応版
    '
    '(株)  全角(  株  全角 )
    '(株)   半角 (    株  半角  )
    '㈱        一文字で カッコ株の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つの回をマスター出来れば、

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

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

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

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

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

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

 

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

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

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

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

無いか。

エクセルの本を読む

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

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

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

なんでやねん!

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

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

あかんがな。

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

 

スポンサーリンク
ワテ推薦のプログラミングスクールで学ぶ
コメントを読む

この記事には読者の方からコメントが 6件あります。
興味ある人はこのページ下部にあるコメントを参照下さい。

Excel VBA
スポンサーリンク
warekoをフォローする
スポンサーリンク
われこ われこ

コメント

  1. しがない事務員 より:

    VBA初心者です。といってもほとんどマクロの記録ばかり利用しています。
    第1回~7回まで拝見いたしました!!今まで見た本やサイトよりも分かりやすいです。

    アドバイスをいただきたく、コメントしました。
    毎日名前が変わるファイルからデータを貼り付けしたいのですが、その場合はどうすれば良いのでしょうか。色々ネット検索してみましたが解決できずに困っています。

    ①売上(20171101).xlsm ※次の日にはカッコ内の日付が変わる
    ②売上進捗報告.xlsm

    ①のデータを②のシートに貼り付けて加工していますが、
    毎日日付が変わるため、その都度モジュールの日付部分のみを手作業で更新しています^^;

  2. wareko より:

    しがない事務員様
    この度は、小生のEXCEL VBA記事にコメント頂きましてありがとうございます。
    さて、お問い合わせの件で確認させて頂きたいのですが、

    >①のデータを②のシートに貼り付けて加工していますが、
    >毎日日付が変わるため、その都度モジュールの日付部分のみを手作業で更新しています^^;

    との事ですが、
    ①のデータを②のシートに貼り付ける操作は、手作業ではなくて、
    「②売上進捗報告.xlsm」の中の日付部分を手作業で書き換えれば、あとはその日付の「①売上(20171101).xlsm」のファイルを読み取って貼り付ける処理は既にVBAで実現出来ていると言う事ですか?

    • しがない事務員 より:

      ご返信ありがとうございます。
      分かりずらい質問の書き方で失礼いたしました。

      ・①のデータを②に貼り付ける作業は、マクロ記録したものを実行しております。
       内容としては、フィルタをかけて、不要な項目を削除してから貼り付けるよう記録しています。

      ・②のマクロを実行する前に以下の作業を行っております。
       「マクロ編集 ⇒ Windows(“売上(20171102).xlsm”).Activate ⇒日付のみ更新して閉じる⇒マクロ実行」

      この日付を手動で更新する作業を自動化したいです。。。
      お手すきの際に構いませんので、ご教示いただけますと大変嬉しいです。
      何卒よろしくお願いいたします。

  3. wareko より:

    しがない事務員様
    詳しい説明ありがとうございました。
    さて、時間があったので即席でサンプルVBAプログラムを作ってみました。
    その製作過程をブログの記事してみました。
    https://www.wareko.jp/blog/post-26009
    お試し頂きまして、もし期待していた動きとは違うとか、あるいは、何か分からない点などありましたら、ご遠慮なくご指摘下さい。

  4. 匿名 より:

    新入社員にVBAの初歩を教えるのにこの入門編が一番適しているので教材にさせてもらってます。第7回目まで楽しく読ませていただきました。第8回目のOpenを早期に希望します。

  5. wareko より:

    匿名様
    この度は小生のサイトにコメントありがとうございました。

    このVBA入門シリーズは、このブログサイトを立ち上げた数年前に執筆したものが多く、ブログ記事作成も初めての経験だったので、分かりにくい文章、回りくどい表現、洗練されていないソースコードなど、多数の改善すべき点があると思っています。

    方針としては、VBAを習う実践的な手法を意識して執筆しましたので、実際に匿名様からお褒めの言葉を頂きまして嬉しいです。

    今後も、EXCELアドイン関連の記事を充実させて行きたいと思っています。