「バカを殴るVBA」 part1 シート差分高速比較ツールコード詳細解説

本記事は「VBA-Tool part1 ①シート差分高速比較ツール」の記事で紹介している学習用VBA-Toolの詳細解説になります。

「バカを殴るVBA」関連記事の地図

Toolの内部のコードの解説ですが、なるべく「読みモノ」として完結させたいため図解を用いて「なんとなくの雰囲気が分かる」だけにとどめます。

また、ざっくりとつかみやすくするために情報を色で分けてみます
ー「画面」（オレンジ）
ーー「関数」（青）
ーーー「説明」（緑）
ーーーー「学習ポイント」（紫）

「ファイル選択」ボタン　->　【２】
「シート名全選択」 ->　【３】
「処理開始」->　【１】
と分けて説明します。

【１】「処理開始」ボタン

この処理開始ボタンを押すとメイン処理が始まります。
ボタンは「cb_run」という名前で、それを押下する（clickする）と
cb_run_Click()というコードが呼ばれ、その中の命令が上から下に向かって流れるイメージです。

「処理開始」ボタン（cb_run）
画像取得の関係で２分割しました。
処理①～⑦までを順番に実施して、所望の処理結果を出力する、というのが超ざっくりとした説明です。
そして①～⑦は関数化（処理をまとめてある）してあります。
以下少し細かく見てゆきます

機能①~③（cb_run_click_①~③）
①②は事前準備です。

①はmainシートの上にユーザーによって入力された値を取得します。
（ファイルパス／シート名／対象範囲）
取得したデータは変数という入れ物に格納されます。

②は取得したデータのチェックを行います。
ユーザーが入力したデータが必ずしも正しいとは限りません。
正しいデータが入力されているかをまとめてチェックする部分です。
上の①で取得したデータを変数を使って②に取り込みチェックします。

③④⑤が本処理になります。

③ではファイルＡから必要なデータを取得します。
シート上の範囲のデータを２次元配列に取り込みます。
（一行で大量のデータを一瞬で取得できます。この③の中の機能が本記事のキモです）

④はファイルＢから必要なデータを取得し二次元配列にします。
処理内容的には③と同じです。

③④⑤⑥のイメージ図
⑤はデータ比較の機能。
③で取得したファイルＡの二次元配列データと
④で取得したファイルＢの二次元配列データを比較します。
比較結果を格納する二次元配列もここで作成します。
この比較結果の二次元配列が出力結果となります

⑥データ出力
⑥はデータ出力です。
出力結果のテンプレートをコピーしてそこに比較結果の２次元配列データを転記することで結果出力となります。
差分有の個所には”1”が入力＋赤塗されコメントに差分内容が記載されます。

⑦テキストファイル出力
⑦はテキストファイル出力です。
⑤のデータ比較時には比較結果のほかに、連想配列というデータも作成していて、そのデータをテキストで出力しています。
出力したテキストファイルには差分個所のデータ位置＋差分内容が一覧で出力されています。（同じ結果だが出力方法を変えて出すことで使いやすくしている）

①～⑦詳細

以降①～⑦の詳細を見てゆきます
【１】←ボタン
　①　←　機能　
　という区別をしてゆきます）

【１】ー①　データを取得

　

メインシートからファイルAとファイルBのパスを取得します。
パスが空白なら明らかに指定間違いなので警告＆止めます。

また、自分自身（もファイルなので）を指定するのも間違いなので警告＆処理を停止します。分解した最後の要素にファイル名のみが格納されているのでそれを取り出します。

そして、Split()という関数をつかって文字列を分解します。
区切り文字は”¥”で、分解された要素はtmpPartsというバリアント型変数に格納されます。
（vbaでExcelファイルのOpenをするときはフルパスが必要、Closeをするときにはファイ名のみが必要になります。のでここで取得している）

次にシート名が入力されているかをチェック。
最後に「対象範囲指定」が入力されているかをチェックします。

【１】ー②　データチェック（対象範囲指定データの形式が正しいか？）

対象範囲指定データの正しいデータの想定は「A1:X100」の形式です。
この形式に則っているかを確認します。

まず英字の表記ゆれの問題があります。
そのために内部で「半角に変換」→「小文字に変換」することで、
表記を揃えます。

次にSplit()という文字列分割関数で”:”の文字で分割します。
正しい表記であればtmpParts(分解された要素が入っている配列)の最大値は　１となるはずです。

要素0番目　→　tmpParts(0) →　”A1”が入っている。　
　要素1番目　→　tmpParts(1) →　”X100”が入っている。

となっているはずで、
　要素0番目をStartRange　（範囲の開始位置）
　要素1番目をEndRange　（範囲の終了位置）
に入れて処理を終了します。

【１】ー③＆④　ファイルA＆ファイルBのデータを取得

【１】ー③＆④
ファイルA、Bを開いて指定範囲のデータを取得します。
「 dim2Arry = .range(StartRange& ":" & EndRange).value 」というのが本記事のキモの部分です。
極々簡単に言うと、「シート上の範囲を指定し、＝つけて変数に代入する」だけで、その範囲のデータが一瞬で取得できます。（下が例）

少し専門的な言葉を使って言うと、
「シート上の選択範囲をバリアント型変数で受けると2次元配列が作成される」となります。これ大量データでもほぼ一瞬で取れます。
（1000行×256列＝25万6千個のセルとかでも秒でかえってきます）

中級者以上のテクニックかと思いますが、世にはびこる「クソ遅いVBAツール」の多さをみると全然ここまで行けてないことが多いんだなとおもいます。（Toolかけてタバコ休憩いくヤツとかよく見たし）

初学者にいきなり「これを素で組め！」は無理かと思いますが、こんな事できるんだ！って事見せておくのって有効じゃないかな？と思います。
当人のスキルアップ＋現場の効率化の両面において。

【１】ー⑤　データ比較（FileA⇔FileB）＋出力用連想配列作成

FileAとFileBの2次元配列を比較して結果の2次元配列をつくります。

③④⑤⑥のイメージ図
先に出した絵ですが、FileAのデータとFileBのデータのサイズは同じです。
一次元目、二次元目も同じサイズです。（縦×横のサイズが同じという事です）

なのでFor～NextというLoop処理を入れ子（2重ループ）にして、同じインデックスでLoopして比較させる事で全データの比較ができます。
比較結果もFileAデータ／FileBデータと同じサイズの2次元配列をつくり、結果を書き込んでゆけば結果マトメのデータとなります。

Ａ,Ｂの同位置のセルが空白だったら
　➡「-」を入力

Ａ,Ｂの同位置のセルに文字列が入っており、その値が同じなら
　➡「0」を入力

Ａ,Ｂの同位置のセルに文字列が入っており、その値が異なっていたら
　➡「1」を入力

マトメに書き込むデータを「３種類でよい」と判断しました。
（異値の場所が分かればよいので）

dicObj_CompResInfoは連想配列というものですが、説明が複雑になるので
今回は説明は省略します。
（結果シートの赤塗／セルにコメント追記／テキストでの出力で使います）

【１】ー⑥　データ出力

比較結果をシートに出力します。

最初にExcel自体の表示更新＋警告出力を無効化します。

出力シートのテンプレートを隠し持っていて（非表示にしてある）、
それを一瞬だけ表示　→　コピー　→　シートに名前を付ける　→　テンプレをまた隠す（非表示にする）という処理をして出力シートを準備します。

次に処理結果の2次元配列を２重ループでシートに書き出します。
その際に連想配列（DicCompResInfo）の情報を使って「コメント追記＋セル赤塗」を行います。

最後にExcel自体の表示更新＋警告出力を有効化（元にもどす）します。

image.png

【１】ー⑦　差分情報をテキストファイルで出力

差分のあった位置と差分内容をテキストファイルでまとめて出力します。

連想配列のKeyを全部掃き出し→（KeyList）※これがセル位置
連想配列のItemを全部掃き出し→（ItemList）これが差分データ

と書き出しデータを準備した後、ファイル名を設定し、
テキストファイルを出力します。
（For～Nextの間の部分がテキスト出力の典型的な書き方です）

【２】「ファイル選択」ボタン

このボタンを押すと、ファイル選択のウインドウが開きます。
ファイルを選択するとファイルパス入力個所に入力されます。

cb_FilePath1_Click()
ファイル選択ボタンを押下するとここに処理が飛びます。
画面上のボタンはcb_FilePath1という名前で、それを押す（Clickする）とここの処理が呼ばれます。
ここでの処理はさらに別の関数を呼ぶだけです。
Call →　関数を呼び出す命令
inputFileSelect(CELL_FILEPARH1)　→ 関数名

inputFileSelect()
よばれた関数の中を見てみます。
非常にざっくりいうと、
・FileDialogというファイル選択の機能を作る。
・ファイル選択のウインドウを表示させる。
・ファイルが選択されたらメインシートのファイルパス記載セルにフルパスを代入する
・ファイル選択がキャンセルされたらメッセージを出す。
ことをしています。

ファイル選択ダイアログの機能はファイル選択するときにしか使わないので、正直言って「必要な時にネットから拾ってきてコピペする」で良いと思います。

【３】「シート名全取得」ボタン

「シート名全取得」というボタンを押すと「cd_get_FIle1SheetsName_Click()」のコードに飛びます。

この機能は「ユーザーに気づかれないようにExcelファイルを開いてその中のシート名を全部取得し、その情報をボタンの左にあるドロップダウンリストに登録する」というものです。

本処理を行うのにシート名の指定まで必要になりますが、それをイチイチ記憶して記載するなんてタルいでしょ？ツールとしてもイケてないと思います。ので、どうしたら楽か？を考えたところ、「ドロップダウンリストで選択できたら楽かも？」という動機で機能を実装しました。

処理の順番としては
ファイルパスを取得→チェック→Excelの画面更新を止める→ファイルを開く→持ってるシートの数分ループしてドロップダウンリストに登録→ファイル閉じる→Excelの画面更新設定を元にもどす
という流れです。本処理とは関係なくドロップダウンリスト作成だけの処理

以上