こんにちは！ 健史です。
VBAを習得し活用を始めた方で
・要求されたこと実現したいことはできているけど、もっと良い方法はあるのかなぁ？
・できれば実データのExcelシートにマクロを残したくない、マクロを保存したくない
・マクロの中に処理対象データの所在を直接書きたくない
など思われることありませんか。
これまでの経験を踏まえた私の設計の考え方・設計思想を一例として紹介します。
なかには当然！、常識！ということもあります。
「これは良いかも」と思われたものは取り入れて、更に経験を積み重ね、ブラッシュアップしてご自身の設計思想を作って頂ければと思います。
また、紹介する内容は現時点での考え方であり、更に良い方法があれば修正していきます。
基本、データファイルにマクロを作成しない
イメージ
状況によってはマクロファイルの中に出力ファイルを作成したり、参照ファイルや更新ファイルを予めコピーしておくこともあります。
状況というのは、
・出力ファイルや更新ファイルを第三者に提供することがなく別ファイルに作成する必要がない
・参照ファイルの内容が半永久的に変わることがない
などです。
一方で、VBAは簡単にプログラミングして効果を得られる言語ですから、基本に拘わり過ぎず臨機応変に作成することが必要と考えています。
なお、当該サイトで紹介しているものほとんどがデータもマクロも同じファイルに作成しています。
この方法を考え付く前の記事であること、また実行しやすいよう、説明しやすいように作成しています。
ご了承願います。
入出力ファイルやパラメータはマクロのシート１で設定
上記は以下の記事の内容です。
データファイルにマクロを作成せず、マクロ自身のExcelファイルも1つのファイルとしてその他の入出力ファイルを同時に処理する場合の方法です。
各ファイルを「Workbook」として定義して使用します。
'定義
Dim excel0, excel1, excel2 As Workbook
'マクロ自身をexcel0にセット・・・項目定義後の処理の最初、他の入出力ファイルをオープンする前に記述
Set excel0 = ActiveWorkbook
'入力ファイルや参照ファイルをオープンと当時にexcel1にセットする
'strPath1には[C:\temp01\file01.xlsx]などのファイル名がセットされている
Set excel1 = Workbooks.Open(strPath1)
'新規にファイルを開くと同時にexcel2へセットする場合
Set excel2 = Workbooks.Add
'新規に開いたファイルの保存
'strPath2には[C:\temp01\file02.xlsx]などのファイル名がセットされている
excel2.SaveAs strPath2
excel0.Save
'参照は、先頭にワークブック名を付けるだけ
If excel0.Sheets(1).Cells(1,2) = "" Then
何度実行しても初めて実行するのと同じようにする
集計結果やファイルから読み込む領域は初期クリアする
例えば、シート上に集計結果やテキストデータ・フォルダのディレクトリ情報などを読み込んで格納する処理の場合です。
1回目は15件、2回目は10件のデータを処理するとき、格納するシートをクリアしないで処理する場合を考えます。
その場合、2回目の処理では10件だけになるはずが、1回目の11～15件目のデータも残ってしまいます。
一方、プログラムを作成して一発で正常に動作することは稀で、複数回実行することが殆どです。
また「１回しか実行しない」想定でも後日「再度実行する」こともあります。
その場合、想定しないデータが残ってしまい、最終的に正常な結果を得られことがあります。
ですから、１回しか実行しない場合でも、複数回実行することを想定し、何度実行しても初めて実行するのと同じ結果になるよう初期クリア処理を必ず実行します。
'データファイル上にマクロを作成して実行する場合
Sheets(2).Cells.Clear
Sheets(2).Cells(1, 1) = "フォルダ名"
Sheets(2).Cells(1, 2) = "ファイル名"
'複数のExcelファイルを処理する場合(ワークブック名が先頭に付いている)
excel0.Sheets(2).Cells.Clear
excel0.Sheets(2).Cells(1, 1) = "フォルダ名"
excel0.Sheets(2).Cells(1, 2) = "ファイル名"
シート全てをクリアし、見出しもセットし直します。
初期クリアが困難な場合
処理結果を格納するシート上のセル位置が固定で、しかも複数のセルに散らばっている場合です。
例では2部門ですが、更に多くの部門があり部門別に商品区分の個数がまちまちの場合のこともあります。
そうしたセルの配置では、クリア処理そのものが複雑になり、集計する商品区分や部門が増減した場合は、クリア処理も修正する必要があります。
その場合は、
・更新するファイルを参照ファイルとして読み込み
・参照ファイルとは別のフォルダに、もしくは、ファイル名を変更
・別ファイルとして保存
します。
もしくは事前にバックアップ・退避しておき、実行の都度、手動でコピーして使用します。
中間的なデータはマクロファイルなどのシートに格納する
処理で中間的に使用するデータがあります。
フォルダ内の全てのファイルを対象に処理する場合(私は)、ファイル一覧をシート２[ファイル一覧]に格納し、格納したシート２から再度対象ファイルを順次読み込み処理しています。
'ファイル名情報をシート２へ格納
excel0.Sheets(2).Cells.Clear
excel0.Sheets(2).Cells(1, 1) = "フォルダ名"
excel0.Sheets(2).Cells(1, 2) = "ファイル名"
ix02 = 1
If strFile1 = "" Then 'ファルダ内のファイルを全て処理する場合
strFilename = Dir(strPath1 & cnsDir, vbNormal)
Do While strFilename <> ""
ix02 = ix02 + 1
excel0.Sheets(2).Cells(ix02, 1) = strPath1 & "\" & strFilename
excel0.Sheets(2).Cells(ix02, 2) = strFilename
strFilename = Dir()
Loop
Else '特定ファイルのみを処理する場合
ix02 = ix02 + 1
excel0.Sheets(2).Cells(ix02, 1) = strPath1 & "\" & strFile1
excel0.Sheets(2).Cells(ix02, 2) = strFile1
End If
'シート２のファイルを読み込み処理し上書き保存
ix02_max = ix02
For ix02 = 2 To ix02_max
Set excel1 = Workbooks.Open(excel0.Sheets(2).Cells(ix02, 1))
For sht = Sheets.Count To 1 Step -1
excel1.Sheets(sht).Activate
excel1.Sheets(sht).Range("A1").Select
If numZoom <> 0 Then
ActiveWindow.Zoom = numZoom
End If
Next
excel1.Save
Next
これは、処理対象ファイルが誤っていないかの確認ができるため、またチェックシートなどとしても再利用可能なために採用しています。
実際にプログラミングするときも「この処理までを実行し正常動作、処理対象ファイルを確認」しています。
上流工程が間違っていたのでは、下流工程の処理が正確でも正しい結果は得られませんから。
コンロトールブレイクはループ型
ここからは、コーティング規約的な内容とも思いますが、説明します。
ここでは詳細を説明致しませんが、以下の記事でコントロールブレイクの処理「IF文型」と「ループ型」を説明しています。
VBAでは、ファイルの読み込み処理やセルの処理順序をプログラムで制御できることから、ループ型で作成します。
If文型でのメリットを感じません。
If文型を使うのは、「カーソルFOR LOOP文」で作成したPL/SQLなどファイルの読み込みを制御できないロジックを検証するためだけに使用することになるでしょう。
もし「If文型で作成したプログラムが正常に動作せず看てほしい！」となった場合は、目的が「If文型でのロジックを習得」という場合を除いて、作成者にループ型に作り変えてもらうか、できないなら私が作り変えてしまうでしょう。
処理をスキップする上から下へのGoTo文は使用する
GoTo文の使用に否定的な方もおられますが、プログラムの見やすさの観点から使っています。
GoTo文使わないと、Ifの入れ子が多くなり見づらくなることもあります。
対象外のデータの処理をスキップする場合には、積極的に使います。
以下の記事では、上記の部分をGoToを使用せず[Else]に代えるとElse処理が長くなり、コーディングも見やすくするために列をずらしますが見づらくなります。
GoTo文を使用したプログラム：Excel-VBA 実例 テキストの文字列を検索し値を抽出
最後に
過去に作成したプログラムでも再利用する場合には作り変えることもあり、紹介した考え方で作成しています。
参考にして頂ければと思います。
コメント