2020/12/20

アンケートの回収と集計方法

• １．背景
• ２．システムの概要
• 　２－１．アンケート画面作成段階
• 　２－２．アンケート送付・回答段階
• 　２－３．アンケート集計段階
• ３．プログラムの流れ
• 　３ー１．アンケート送信まで
• 　３ー２．アンケート集計時
• ４．アンケート記入用ワークシート（ActiveXコントロール）の作成
• 　４－１．コントロールの配置
• 　４－２．コントロールの設定
• ５．アンケート記入用ワークシート（フォームコントロール）の作成
• 　５－１．コントロールの配置
• 　５－２．コントロールの設定
• ６．アンケート記入と集計のための共通定数・配列（Module１）
• 　６－１．アンケート結果保存先の設定
• 　６－２．コントロールと集計結果書込み位置の指定（ActiveXコントロール）
• 　６－３．コントロールと集計結果書込み位置の指定（フォームコントロール）
• ７．アンケート送付プログラム（Module２）
• 　７ー１．アンケートの回答
• 　７ー２．アンケートのチェック
• 　７ー３．固有情報の取得
• 　　７ー３ー１．ＩＰアドレス①
• 　　７ー３ー２．ＩＰアドレス②
• 　　７ー３ー３．MACアドレス①
• 　　７ー３ー４．MACアドレス②
• 　　７ー３ー５．ユーザー名①
• 　　７ー３ー６．ユーザー名②
• 　　７ー３ー７．コンピューター名①
• 　　７ー３ー８．コンピューター名②
• 　　７ー３ー９．コンピューター名③
• ８．アンケート集計プログラム（Module３）
• 　８ー１．アンケート集計・出力
• 　８ー２．回収されたファイルの一覧作成
• ９．その他のプログラム（Sheet1のシートモジュール）
• １０．最後に

１．背景

アンケート形式で意見を聞いたりデータを集めたりすることは、良くあります。昔は紙のアンケート用紙でしたが、最近はWeb式が多く集計も一瞬で出来ます。一方使い慣れたExcelを使ってアンケートをする場面もまだまだあるようで、メールで送られてきたExcelファイルに記入後、返信をしてもらう様な形が多いかと思います。
しかし集計する方は大変です。返信されたファイルを「名前を付けて保存」し、ファイルを１つ１つ開いてディスプレイを見ながら手作業で集計したり、コピペで値を集約しているかと思います。更にファイルを印刷して、紙を見ながら集計する場合だってあると思います。

これではExcelで実施するメリットが生かし切れていません。今回はアンケートに答えたらそのファイルをサーバーに集約し、集計もマクロで一発のシステムを紹介します。
尚この手法は、アンケートだけでなく他の業務にも応用できるのではないかとも思います。

２．システム概要

全体の流れは図２－１のようになります。


図２－１

まず管理者がアンケートファイルを作成し、メール等で各人に送付①します。
アンケートファイルを受取った人は、Excelで開きアンケートに記入②します。
記入し終わったら、記入済みのExcelファイルを別名をつけてサーバー上に保存③します。この時の保存ファイル名は、重複しないファイル名にしています。
回答済みのアンケートが回収されたら、集計アンケートファイル内のマクロを起動することで「サーバー内の回答済みファイルを１つずつ呼出して集計④」をします。

２－１．アンケート画面作成段階

まずExcelのワークシート上にアンケート文面を作るのは、今までと同じです。
ワークシート上には、図２－２のようなコントロール（「オプションボタン」や「チェックボックス」など）を配置することができます。これらはアンケートを選択式にする時には、良く使われる手法です。

図２－２

コントロールには図２－２のように「フォームコントロール」と「ActiveXコントロール」の２種類がありますが、外観的にはどちらもほぼ一緒です。しかし制御方法は大きく異なるため、どちらが使い易いかを大雑把に場面分けしてみると、以下のようになります。
　・フォームコントロール：コントロールをワークシート上のセルの値・数式と連動させるような場合
　・ActiveXコントロール：VBAでコントロールを制御するような場合

作る人の慣れの問題もあるので、今回のサンプルファイルでは「フォームコントロール」と「ActiveXコントロール」の両方でアンケート画面を作成してみました。図２－３が「ActiveXコントロール」で作成した画面（サンプルファイルのSheet1）、図２－４が「フォームコントロール」で作成した画面（サンプルファイルのSheet2）です。

図２－３


図２－４

どちらのコントロールで作成してもOKですし、両方のコントロールを混ぜて作成しても（たぶん）大丈夫です。
サンプルファイルにはQ1～Q5の５問があり、Q1・Q2はオプションボタンを使った「１つだけ選択」する設問、Q3はチェックボックスを使った「複数選択が可能」な設問。
Q4は「任意の数値」が入力可能なテキストボックス（図２－４ではセルそのもの）、Q5は「任意の文字列」が入力可能なテキストボックス（図２－４ではセルそのもの）です。

２－２．アンケート送付・回答段階

アンケートが完了したら、アンケートファイルを添付ファイルにして、メール等で各人に送付します。
メールを受け取った各人はアンケートファイルをExcelで開き、アンケートを記入したのち、送信ボタン（図２－３・図２－４の『全て記入しおわったらクリックして下さい』のボタン）をクリックします。

「送信ボタン」をクリックすると記入済みのアンケートファイルはサーバーに保存されます。ここで言うサーバーは、各人が自由にファイル保存可能なファイルサーバー等を指します。
しかし、メール送信時のファイル名は「アンケート.xlsm」などと誰もが同じファイル名ですので、このままのファイル名でサーバー先に保存しようとすると、二番目の人から先は「同じファイル名があります」と怒られる事になります。
自動で「アンケート(2).xlsm」などとファイル名変更をしてくれるサーバーであれば良いのですが、通常はそうは行きません。
「同じ名前で上書き」されても困りますので、アンケートを回答する人同士で重複しないようなユニークなファイル名を付ける必要があります。

また、ユニークなファイル名の付け方として「保存先に既に保存されているファイル名を調べ、それ以外のファイル名にする」方法も考えられますが、「同じ人が何度も回答できてしまう」デメリットもあります。「同一人物がアンケートを再回答した時は新しいアンケートの方を有効」するのが正しいと思いますので、ファイル名はどうしても「各人を特定できるもの」が必要になります。

例えば社内のＰＣであれば「ＩＰアドレス」「ユーザー名」「コンピュータ名」「MACアドレス」等で管理しているでしょうから、そのどれか、または複数を組み合わせることで、「重複せず」且つ「何度も回答するのを防止できる」ような各人個有のファイル名となり得ると思います。

「DHCP（IPアドレス自動割り振り）」などを使用している会社もあると思いますので、固有名（２つ以上の組合せもＯＫ）をどれにするかは状況により異なると思いますが、その個有名をファイル名にしてサーバーに保存します。

「個有名が分かるファイル名をつける」と言う事は、無記名でアンケートを回答しても「誰が何を回答したか」が分かってしまうことを意味します。社内で「誰が」が分かる（＝個人が特定できる）ということは、回答したアンケートは「個人情報」となります。 アンケートを個人情報として、あなたが取得するためには「利用目的を明確に通知する」ことが必要になりますので、少なくともアンケート画面上に「アンケートは〇〇の目的のみで使用しますので御了承下さい」と書く必要があります。 また正直者であれば「このアンケートは（無記名アンケートだけど）個人が特定できてしまいます」と書くことになりますので、今回のシステムは本来「記名式のアンケート」で使用するのが正しい使い方だと思います。ちなみに「記名式アンケート」も、もちろん個人情報です。 と言って、アンケートに記入してくれた氏名を元にファイル名にして保存するようなシステムにすると、偽名を使われたり、他人の氏名を使われたりした場合にシステムとしてどう対応をするか、という問題も発生してしまいます。 このように考えると、PCから取得できる情報を使って回収する方法は有用と思えます。 一方、ファイル名にIPアドレスを使う場合なら「管理者以外がIPアドレスから個人名を特定することは難しい」のですが、不可能ではありません。ですので、もしユーザー側にアンケートの保存先がバレてしまえば「他人のファイルを盗み見ることが可能」になってしまいます。 これを防ぐためには「他の人に見られないように、サーバーに回答ファイルが保存されたらすぐに別の場所にファイルを移動」する等の検討も必要と思います。 また、今回くらいの知識がある人ならば「社内の個人を特定するプログラム」くらいは、すぐに作れてしまいます。しかし、作るシステムが今回のようなアンケートでなくても、「これは個人情報ではないだろうか」と常に自問しながら作る事が必要だと思います。

２－３．アンケート集計段階

サーバーに集まった回答済ファイルを１つずつ呼び出して集計した結果は、図２－５のようになります。

図２－５

事前に、「１つのコントロール（例えばオプションボタン）」と「その結果を書き込むセル位置」のセットを作っておきます。今回のサンプルでは「Q1 ｘ 4セット　＋　Q2 ｘ 4セット　＋　Q3 ｘ 4セット　＋　Q4 ｘ 1セット　＋　Q5 ｘ 1セット」の計１４セットになります。
そして回答済ファイルを開き、「コントロールが選択されているか（TrueやOnになっているか）」を調べ、選択されているならば「結果を書き込むセル位置」に１を足していきます。それをサンプルファイルで言えば１４セット分繰り返します。
１つのファイルが完了したら、ファイルを閉じ、次のファイルを開いて１４セットを繰り返していきます。

すべての回答ファイルからのデータ吸上げが完了すると、図２－５のようにアンケート結果が集計できるというものです。
但し、今回は合計値を出しているだけなので「目の悪い人は、睡眠時間が少ない」みたいな傾向を掴むような解析はできません。
しかし、例えば図２－６のように「１ファイルの結果を横一行に並べる」ようにすることも、プログラムを少し修正するだけで可能となるので、そこから解析につなげることも出来ると思います。

図２－６

３．プログラムの流れ

３ー１．アンケート送信まで

管理者がアンケートファイルをメール等で配布したのち、回答者はExcelファイルを開きアンケートに答えていきます。記入が終わり、一番上の送信ボタンをクリックすると、マクロが起動します。

図３－１

まず正しく回答が記入されているかをチェック③し、間違いがあった場合には「どこが違っているかをコメント表示④し、マクロ終了」します。例えばオプションボタンが１つも選ばれていない、とか必須項目のTextBoxに値が入っていない等です。
その際、チェック対象の各コントロールは「コントロール位置配列データ②」から取得します。これは集計の時に使用するデータと同一のものを使用しています。
正しく入力されている場合は、回答済みファイルを保存するファイル名を作成⑤します。ファイル名は「アンケートを回答する人同士で重複しない」ように、IPアドレス・MACアドレス等⑥から作ります。
ファイル名を作成したら、そのファイル名が保存先①に存在するか否かをチェック⑦します。これは「同一人物が再度回答」したことを想定し、「再回答の時には最新のファイルのみを残す」ために保存先①にある同一名ファイルを削除します。
その上で、今回回答したファイルを保存⑧します。

３ー２．アンケート集計時

保存先①にアンケートの回答ファイルが集まりましたら、集計マクロを起動します。

図３－２

まずは保存先①内のファイルをリスト化③します。これより後は、このリスト③を元に保存先①から１つずつファイルを呼び出して処理していきます。
集計をする前に、集計シート上の古いデータ（複数回集計をすることをイメージしています）を消去④します。
今回の集計は「１つファイルを開きコントロール項目がチェックされていれば、集計シート上の既存の集計値に１を足す。１つのファイルの集計が終了したらファイルを閉じ、次のファイルを開く」という手順を繰り返す方法です。
ですので古い集計結果が残っていると、その値に対して足し算をしていきますので、正しい集計結果が得られなくなってしまいます。

集計シートのデータ消去④が完了したら、リスト③を使って保存先①からファイルを１つずつ開いて⑤いきます。
アンケートシート上には複数のコントロールがあるので「コントロールの位置配列データ②」を呼び出し、そのデータの順序でコントロールの状態を調べ⑥て行きます。例えばチェックボックスがON（チェックが入っている）の場合は数値１、OFF（チェックが入っていない）の場合は数値０とし、集計シート上の既に集計した値に加算⑦をしていきます。
そして、加算をした結果を集計シート上に上書き⑧します。この⑥～⑧の工程を「コントロールの位置配列データ②」に従って全てのコントロールに対して処理を行います。
全てのコントロールへの処理が完了したらファイルを閉じ⑨、次のファイルを開きます⑤。
リスト③のファイルの処理が全て終わったら、集計終了です。

４．アンケート記入用ワークシート（ActiveXコントロール）の作成

ActiveXコントロールを使ったアンケート記入ワークシートは、サンプルファイルではSheet１になります。

４－１．コントロールの配置

「開発」タブの「コントロール」グループ内の「挿入」ボタンをクリックする表示されるコントロール一覧の中から、図４－１のように必要なコントロールをクリックし、ワークシート上に並べていきます。下段がActiveXコントロールになります。

図４－１

４－２．コントロールの設定

配置が完了したら、次に１つ１つのコントロールの設定を行います。
図４－２の左側のように、コントロールを選択した状態でマウス右ボタンをクリックし、メニューの中から「プロパティ」を選択します。

図４－２

すると、図４－２の右側のように「プロパティ一覧」が表示されます。
まずプロパティ内の「オブジェクト名」は、コントロールを作った順に「OptionButton１」「OptionButton２」・・・という番号が自動的に振られます。もし試行錯誤しながら作ると、オブジェクト名の順番がバラバラになってしまいますので、配置が完了した時点で「オブジェクト名を分かり易い様に変更」すると、後の処理が楽になります。
次に「コントロールの表示文字」は、プロパティ一覧内の「Caption値」を変更する形で行います。

コントロールの内「オプションボタン」は、「いくつかの選択肢から１つだけを選ぶ」コントロールです。ですので今回サンプルのようにQ1にもQ2にもオプションボタンを配置した場合には、「Q1とQ2の全８つの内から１つだけを選ぶ」形になってしまいます。
これを解決するため「同じ設問のオプションボタンには、同じGroupName 値をつける」ことで、「同じGroupName内のオプションボタンは１つしか選択できない」状態を作ることが出来ます。
標準ではGroupNameプロパティには「配置した先のワークシート名」が入っていますが、設定値はString型なので「１」「２」のような数字でもＯＫです。

尚、チェックボックス・テキストボックスもプロパティ設定が可能ですが、オブジェクト名を揃える以外の設定は今回不要です。

ワークシートの一番上に配置したコマンドボタンは「クリックするとアンケートを送付」する役目ですので、シートモジュールにClickイベントプロシージャ（図９－１）を作成し、そこから「questionSendプロシージャ（図７－１）」を呼び出します。

５．アンケート記入用ワークシート（フォームコントロール）の作成

フォームコントロールを使ったアンケート記入ワークシートは、サンプルファイルではSheet２になります。

５－１．コントロールの配置

「開発」タブの「コントロール」グループ内の「挿入」ボタンをクリックする表示されるコントロール一覧の中から、図５－１のように必要なコントロールをクリックし、ワークシート上に並べていきます。フォームコントロールは上段になります。

図５－１

５－２．コントロールの設定

まず、配置が完了した時点でコントロールの名前（ActiveXコントロールのオブジェクト名に相当）を揃えます。コントロールの名前は、フォームコントロールを作った順番に番号が振られてしまいますので、後の処理が楽にするため名前を揃えるのです。
名前の変更は、まずコントロールを選択します。しかし、普通にマウスの左クリックでコントロールを掴もうとしても、コントロールがOn-Offするだけですので、マウスの右ボタンでコントロールをクリックします。
コントロールが選択できると、図５－２のように「名前欄に名前が表示」されますので、そこにカーソルを当てて編集をします。編集が完了したら、リターンキーを押して確定します。


図５－２

表示文字を変更するには上記と同様にマウスの右クリックでコントロールを選択し、図５－３のように文字列の場所にカーソルを当てて編集をします。編集完了したら、コントロール以外のどこかのセルをクリックし編集を確定させます。

図５－３

またActiveXコントロールの時と同じく、フォームコントロールのオプションボタンもグループ化を行う必要があります。しかしActiveXのようなGroupNameプロパティはフォームコントロールにはありません。
ですので、図５－４のように「フォームコントロールのグループボックス」で、一組となるオプションボタン全体を囲むように配置します。

図５－４

グループボックスで囲む際はギリギリを狙わず、余裕をもって囲んだ方がうまくいきますし、可能なら逆の順番（グループボックスを配置してから、その中に収まるように、オプションボタンを配置する）の方が失敗は少なそうです。
またグループボックス配置後、組ごとのオプションボタンが正常に動作するか必ずチェックした方が良いと思います。
なお、図５－４ではオプションボタンを全て選択している絵になっていますが、説明用ですので選択する必要はありません。

グループボックスでオプションボタンを囲んでおけば、確かに正常には動くのですが「枠が目障り」「見栄えは悪い」のも確かです。グループボックスの文字（「グループ１」など）は消すことは出来ますが、キチンとオプションボタンを囲んでいないといけないので、整然とグループボックスの枠を配置するのは大変です。
そこで「グループボックスを非表示」にする方法を今回使います。

まず、「ホーム」タブの「編集」グループ内の「検索と選択」ボタンをクリックし、その中から一番下の「オブジェクトの選択と 表示」を選択します（図５－５）。

図５－５

すると図５－６のように、ワークシートの右側に「フォームコントロールの一覧」が表示されます。

図５－６

その中に「Group Box 1（左上の名前ボックスで名前を変更すると、その名前で表示される）」などとなっているのがグループボックスです。リストのどれかを選択（選択すると背景が緑色に変わる）すると、そのコントロールが選択状態になるので「どのコントロールが何という名前なのか」が分かると思います。

その１つ１つのリストの右に並んでいる「」印がコントロールの表示・非表示を操作するボタンで、マークをクリックし「」印にすることで非表示になります。これでグループボックスの枠が無いが、その機能（Q1とQ2とでオプションボタンが分けられている）を保持した違和感の無いアンケート画面になります。

図５－７

最後に、一番上のコマンドボタンに図５－８のように「アンケート送信」のマクロを登録します。登録するマクロはModule2の「questionSend」プロシージャになります。

図５－８

６．アンケート記入と集計のための共通定数・配列（Module１）

Module１には「アンケートの回答時の項目チェック」と「回答済みアンケートの集計時」の両方で使用する定数・配列をPublicで宣言しています。

６－１．アンケート結果保存先の設定

まず、アンケートの回答結果を保存する先のフォルダーパスを図６－１のように定数として宣言します。なおこの保存先は集計する際のデータ先と同じとしています。
なお前述した様に、セキュリティ向上を理由に「サーバーに保存されたアンケート結果を別の場所に移動する」のであれば、保存先・集計先は別アドレスに設定する必要があります。

1. '==========　⇩(1)　アンケート結果の保存先（シートレベル定数）　====================
2. Public Const SERVER As String = "¥¥SERVER¥USER¥Excel¥answer¥"　　 '最後に「¥」を付けて指定

図６－１

最後に「¥」を付けている理由ですが、例えばFileSystemObjectを使ってフォルダー名を取得する時には「フォルダー名の最後には「¥」が付きません。しかし、フォルダー名とファイル名を結合する時には間に「¥」が必ず必要になります。ですので、今回は最後に「¥」を付けてフォルダー名を設定しています。
もし、都合で「¥」を付けられない時には、ファイル保存やファイル呼出しのコードに「& "¥" &」でフォルダ名とファイル名をつなぐように改造して下さい。

６－２．コントロールと集計結果書込み位置の指定（ActiveXコントロール）

図６－４、図６－６は、「選択コントロール、集計場所、集計方法」を配列に格納することで、冗長な名前の付いたコントロールオブジェクトへのアクセスを単純化することを狙っています。また、基本的にはアンケートを記入するシートと集計するシートは同じですので、「集計するExcelブック」側と「回答済みのExcelブック」側を切り替えられるように、引数でそれぞれのWorkbookオブジェクトを渡しています。

まず、アンケートを集計する段階でのコントロールと集計場所の関係を図６－２に示しました。
図６－２のq(1)～q(12)である「オプションボタンやチェックボックスのコントロール」は「On - Off」のどちらかですから、「On ＝１」「Off ＝ ０」として計算・表示することにしました。ですのでコントロールのすぐ下のセルを集計場所（緑色点線枠）にしてあります。そのため、設問の間（例えば、Ｑ１とＱ２の間）に空行を１行ずつ入れてあります。
またq(1)～q(12)は「On - Off」で計算しますので、「集計方法＝onoff」としています。

図６－２

一方、q(13)は「On - Off」ではありません、数値が入ります。ですのでコントロールの名前と集計場所はq(1)～q(12)と同じですが、集計方法は「num」としました。この「onoff」「num」の違いは、図６－５で説明します。

また、図６－３のq(14)は、任意の文字列です。これは計算が出来ませんので、ユーザーが入力した通りに羅列していくしかありません。但し羅列する方向は「下方向」と「右方向」があります。「上方向」「左方向」も考えられますが、すぐに端にぶつかってしまうためアンケート量の少ない場合のみにか使えないため、今回は外しました。

図６－３

以上の様な考え方で「各ActiveXコントロールに対する集計場所と集計方法を配列化」しているのが、makeArray1関数（図６－４）になります。引数としては、WB1・WB2の２つのワークブックを受け取ります。

3. '==========　⇩(2)　ActiveXコントロールの位置配列　====================
4. Public Function makeArray1(WB1 As Workbook, WB2 As Workbook) As Variant
5. 　Dim q(1 To 14) As Variant
7. 　q(1) = Array(WB1.Sheets("Sheet1").OptionButton1, WB2.Sheets("Sheet1").Range("c4"), "onoff")
8. 　q(2) = Array(WB1.Sheets("Sheet1").OptionButton2, WB2.Sheets("Sheet1").Range("d4"), "onoff")
9. 　q(3) = Array(WB1.Sheets("Sheet1").OptionButton3, WB2.Sheets("Sheet1").Range("e4"), "onoff")
10. 　q(4) = Array(WB1.Sheets("Sheet1").OptionButton4, WB2.Sheets("Sheet1").Range("f4"), "onoff")
12. 　q(5) = Array(WB1.Sheets("Sheet1").OptionButton5, WB2.Sheets("Sheet1").Range("c6"), "onoff")
13. 　q(6) = Array(WB1.Sheets("Sheet1").OptionButton6, WB2.Sheets("Sheet1").Range("d6"), "onoff")
14. 　q(7) = Array(WB1.Sheets("Sheet1").OptionButton7, WB2.Sheets("Sheet1").Range("e6"), "onoff")
15. 　q(8) = Array(WB1.Sheets("Sheet1").OptionButton8, WB2.Sheets("Sheet1").Range("f6"), "onoff")
17. 　q(9) = Array(WB1.Sheets("Sheet1").CheckBox1, WB2.Sheets("Sheet1").Range("c8"), "onoff")
18. 　q(10) = Array(WB1.Sheets("Sheet1").CheckBox2, WB2.Sheets("Sheet1").Range("d8"), "onoff")
19. 　q(11) = Array(WB1.Sheets("Sheet1").CheckBox3, WB2.Sheets("Sheet1").Range("e8"), "onoff")
20. 　q(12) = Array(WB1.Sheets("Sheet1").CheckBox4, WB2.Sheets("Sheet1").Range("f8"), "onoff")
22. 　q(13) = Array(WB1.Sheets("Sheet1").TextBox1, WB2.Sheets("Sheet1").Range("c10"), "num")
23. 　q(14) = Array(WB1.Sheets("Sheet1").TextBox2, WB2.Sheets("Sheet1").Range("c12"), "down")
25. 　makeArray1 = q
26. End Function

図６－４

７～１０行目がアンケートのＱ１、１２～１５行目がＱ２、１７～２０行目がＱ３、２２行目がＱ４、２３行目がＱ５になります。
７～２３行目の各行では、以下の３つを「Array関数」で配列化しています。
　・「選択コントロール」：引数WB1を使った「WB1ワークブック側のコントロール」
　・「集計場所」：引数のWB2を使った「WB2ワークブック側の集計セル位置」
　・「集計方法」：各ワークシートの値をどの様に集計するか（「onoff」「num」「down」「right」のどれか）
その配列を代入している先は「q()」という配列の各要素で、２５行目で配列q全体をmakeArray関数の戻り値にしています。

代表として７行目の式で詳細説明します。
まず１番目の値は、「WB1.Sheets("Sheet1").OptionButton1」です。これは「Sheet1に貼り付けたActiveXコントロールの、Ｑ１の１番目のオプションボタンのオブジェクト名」を表しています。
２番目の値は、「WB2.Sheets("Sheet1").Range("c4")」です。これは「『Ｑ１の１番目のオプションボタン』の集計結果を書き込むセル位置」を指しており、場所的には「『Ｑ１の１番目のオプションボタン』のすぐ下のセル」にしています。
３番目の集計方法は、図６－５の通り、今回「onoff」「num」「down」「right」を準備しています。
７行目では「onoff」を指定していますので、オプションボタンのOn-Offを調べ、On＝１、Off＝０の値を「集計結果を書き込むセル位置」に既に記入済みの値に加えていくことを表しています。

記号	対象となるコントロール	扱うデータ	集計方法
onoff	OptionButton、CheckBox	ｺﾝﾄﾛｰﾙのOn,Off	ｺﾝﾄﾛｰﾙのValue値をOn=1,Off＝0に変換した後、既集計結果に加える
num	TextBox、セル範囲	数値	入力値を既集計結果に加える
down		文字	入力値をセルに書き込み、順次書込み位置を下方向に移動する
right		文字	入力値をセルに書き込み、順次書込み位置を右方向に移動する

図６－５

なお配列qは２次元配列のように見えますが、１次元配列を1次元配列にネストしているものです。
ですので各値を呼び出すには、例えばq(1,1)という形ではなく「q(1)(1)」という形で呼び出すことになります。

６－３．コントロールと集計結果書込み位置の指定（フォームコントロール）

フォームコントロールの場合はActiveXコントロールと異なり「オブジェクト名」の確認が難しいため、「名前」を使ってコントロールを特定しています。そのフォームコントロールに対する集計場所と集計方法についてはmakeArray1と同じであり、３項目の配列化をmakeArray2関数（図６－６）で行っています。
引数としては、makeArray1と同様にWB1・WB2の２つのワークブックを受け取ります。

27. '==========　⇩(3)　フォームコントロールの位置配列　====================
28. Public Function makeArray2(WB1 As Workbook, WB2 As Workbook) As Variant
29. 　Dim q(1 To 14) As Variant
31. 　q(1) = Array(WB1.Sheets("Sheet2").OptionButtons("オプション 1"), WB2.Sheets("Sheet2").Range("c4"), "onoff")
32. 　q(2) = Array(WB1.Sheets("Sheet2").OptionButtons("オプション 2"), WB2.Sheets("Sheet2").Range("d4"), "onoff")
33. 　q(3) = Array(WB1.Sheets("Sheet2").OptionButtons("オプション 3"), WB2.Sheets("Sheet2").Range("e4"), "onoff")
34. 　q(4) = Array(WB1.Sheets("Sheet2").OptionButtons("オプション 4"), WB2.Sheets("Sheet2").Range("f4"), "onoff")
36. 　q(5) = Array(WB1.Sheets("Sheet2").OptionButtons("オプション 5"), WB2.Sheets("Sheet2").Range("c6"), "onoff")
37. 　q(6) = Array(WB1.Sheets("Sheet2").OptionButtons("オプション 6"), WB2.Sheets("Sheet2").Range("d6"), "onoff")
38. 　q(7) = Array(WB1.Sheets("Sheet2").OptionButtons("オプション 7"), WB2.Sheets("Sheet2").Range("e6"), "onoff")
39. 　q(8) = Array(WB1.Sheets("Sheet2").OptionButtons("オプション 8"), WB2.Sheets("Sheet2").Range("f6"), "onoff")
41. 　q(9) = Array(WB1.Sheets("Sheet2").CheckBoxes("チェック 1"), WB2.Sheets("Sheet2").Range("c8"), "onoff")
42. 　q(10) = Array(WB1.Sheets("Sheet2").CheckBoxes("チェック 2"), WB2.Sheets("Sheet2").Range("d8"), "onoff")
43. 　q(11) = Array(WB1.Sheets("Sheet2").CheckBoxes("チェック 3"), WB2.Sheets("Sheet2").Range("e8"), "onoff")
44. 　q(12) = Array(WB1.Sheets("Sheet2").CheckBoxes("チェック 4"), WB2.Sheets("Sheet2").Range("f8"), "onoff")
46. 　q(13) = Array(WB1.Sheets("Sheet2").Range("c9"), WB2.Sheets("Sheet2").Range("c10"), "num")
47. 　q(14) = Array(WB1.Sheets("Sheet2").Range("c11"), WB2.Sheets("Sheet2").Range("c12"), "down")
49. 　makeArray2 = q
50. End Function

図６－６

代表として３１行目で説明します。まず１番目の値は「WB1.Sheets("Sheet2").OptionButtons("オプション 1")」です。この中の「オプション 1」がＱ１の１番目のオプションボタンの名前になります。

フォームコントロールの指定の方法としては、上記の「OptionButtons("オプション 1")」のような表し方以外には、「OptionButtons(1)」のような表し方もできるのです。しかし、コントロールの名前と番号は一致せず、どうも図６－７のように「オブジェクトの表示」で表示された順の「下からの順序」で番号が付けられているようです。（未確定情報）

図６－７

図６－７の右欄では「コントロールを新たに作成すると一番上に追加」されますので、「コントロールの番号は、作った順番」となりますが、簡単に順番の入替えが可能ですので「コントロールの名前（ワークシートの左上の名前欄）」で特定した方が確実と思われます。

その他については、図６－４と同じです。

７．アンケート送付プログラム（Module２）

７ー１．アンケートの回答

アンケート画面の一番上のCommandButtonをクリックした時に呼び出されるのが図７－１です。
Sheet2はフォームコントロールのCommandButtonですので直接「マクロの登録」をしていますが、Sheet1はActiveXコントロールのためSheet1のシートモジュールに「CommandButton1_Click」イベントプロシージャ（図９－１）を設け、そこから図７－１を呼び出しています。

51. '==========　⇩(4)　アンケートの回答　====================
52. Public Sub questionSend()
53. 　Dim FN As String　　　　　 '保存用ファイル名
54. 　Dim Fso As Object　　　　　 'FileSystemObjectオブジェクト
56. 　If questCheck = False Then Exit Sub
58. 　FN = IPaddress1 & ".xlsm"
60. 　If Not Dir(SERVER & FN) = "" Then
61. 　　Set Fso = CreateObject("Scripting.FileSystemObject")
62. 　　Fso.deletefile (SERVER & FN)
63. 　　Set Fso = Nothing
64. 　End If
66. 　ActiveWorkbook.SaveAs filename:=SERVER & FN
68. 　If Workbooks.Count = 1 Then Application.Quit
69. 　ThisWorkbook.Close
70. End Sub

図７－１

５６行目では「questCheck（図７－４）」関数プロシージャを呼出し、False（アンケートの回答にミスがある）の場合はExit Subで「保存を中止」します。なお「どこにミスがあるのか」については「questCheck」側でコメントを出しています。

５８行目では「保存するファイル名」を作っています。
ファイル名は、「保存時に同一ファイルにならないように」する事と「同一人物がアンケートを再回答した時は新しいアンケートを有効とする」ために、アンケート回答者固有の値にする必要があります。
アンケートを回答する人を「パソコンから得られる値」を使って特定するには、図７－２のように色々あります。どれも完璧ではありませんが、社内でPCやユーザーをちゃんと管理しているのであれば、どれかの値（または組合せ）で特定できるのではないかと思います。

値	重複の可能性	考慮すべき点
IPｱﾄﾞﾚｽ	同一ｻﾌﾞﾈｯﾄ内なら基本的に無し	DHCPの場合､変わる可能性有り 無線LAN､有線LAN両方に接続した場合､複数ｱﾄﾞﾚｽが存在
MACｱﾄﾞﾚｽ	基本的に無し	無線LAN、有線LANそれぞれにｱﾄﾞﾚｽ有り
ｺﾝﾋﾟｭｰﾀｰ名	可能性あり	管理項目にしているならﾕﾆｰｸ値として使用可
ﾕｰｻﾞｰ名	可能性少ない	1ﾕｰｻﾞｰが複数PCにﾛｸﾞｲﾝしている可能性有り 共用PCでは個人の特定不能
PCのO/Sﾊﾞｰｼﾞｮﾝ	可能性大	特定手段としては弱い
O/SのﾌﾟﾛﾀﾞｸﾄID	基本的に無し	法人用PCにはIDが存在しない?
PCの型式	可能性大	特定手段としては弱い

図７－２

サンプルファイルではファイル名にIPアドレス（IPaddress1）を使っていますが、下記で紹介する「コンピューター名」「ユーザー名」なども含めて、社内の環境に合わせたもので検討して下さい。

６０行目では、５８行目で作成したファイル名が「保存先に存在するか否か」をDir関数を使って調べています。もし存在した（＝同じユーザーが既に回答済みだった）場合には、これから保存するファイルで上書きする必要があります。
６１行目で「FileSystemObjectオブジェクト」を生成し、６２行目でdeletefileメソッドで古いファイルを削除しています。
（サーバー等の保存先が、ファイル削除が可能な状態になっていることが必要です。）

そのあとで６６行目の「名前を付けて保存」で、新しいファイル名で保存します。
６０～６４行目のような「事前に同一ファイルは削除しておく」方法以外に、図７－３のようにDisplayAlertsを使いアラートを出ないようにして「強制的に上書き」してしまう方法もあります。

65. 　Application.DisplayAlerts = False
66. 　　ActiveWorkbook.SaveAs filename:=SERVER & FN
67. 　Application.DisplayAlerts = True

図７－３

古いファイルを気持ち良く削除してしまうのでしたら図７－３の方法でも良いと思いますが、例えば安全のために「古いファイルは名前（例えば日時をファイル名にする）を変えて別なところに一時保管」などが必要でしたら、FileSystemObjectを使ってファイル名変更等の処理となります。

６８～６９行目ではファイルを閉じているのですが、「Excelで開いているのは自分（アンケート）だけ」の場合に６９行目の「ThisWorkbook.Close」を実行してしまうと「ファイルを開いていないExcel」だけが残ってしまうことになります。
ですので、もし「開いているのが自分だけ（＝１個だけ）」の場合には「Application.Quit」で、Excelごと閉じてしまった方が自然です。
２つ以上開いている（＝アンケート以外にもExcelで開いている）時には、６９行目の「ThisWorkbook.Close」を実行し「アンケートだけを閉じて、他のファイルは残す」ことにしています。

７ー２．アンケートのチェック

図７－１の５６行目から呼び出される「アンケート内容のチェック」が図７－４です。アンケートが送付できる（＝別名保存ができる）状態であればTrueを、回答方法などが正しくない時はFalseを戻します。

71. '==========　⇩(5)　アンケートのチェック　====================
72. Private Function questCheck() As Boolean
73. 　Dim ansAddress As Variant　　　 'アンケートのコントロール等の配列
74. 　Dim i As Long　　　　　　　　　 'カウンタ変数（設問当たりのコントロール数）
75. 　Dim cnt As Long　　　　　　　　 'カウンタ変数（設問当たりのONの数
77. 　ansAddress = makeArray1(ThisWorkbook, ThisWorkbook)
79. 　cnt = 0
80. 　For i = 1 To 4
81. 　　cnt = cnt + Abs(ansAddress(i)(0).Value) Mod 2
82. 　Next i
83. 　If Not cnt = 1 Then
84. 　　MsgBox "Q1が選択されていません"
85. 　　Exit Function
86. 　End If
88. 　cnt = 0
89. 　For i = 5 To 8
90. 　　cnt = cnt + Abs(ansAddress(i)(0).Value) Mod 2
91. 　Next i
92. 　If Not cnt = 1 Then
93. 　　MsgBox "Q2が選択されていません"
94. 　　Exit Function
95. 　End If
97. 　cnt = 0
98. 　For i = 9 To 12
99. 　　cnt = cnt + Abs(ansAddress(i)(0).Value) Mod 2
100. 　Next i
101. 　If cnt < 1 Then
102. 　　MsgBox "Q3が選択されていません"
103. 　　Exit Function
104. 　End If
106. 　i = 13
107. 　If Not IsNumeric(ansAddress(i)(0).Value) Or ansAddress(i)(0).Value = "" Then
108. 　　MsgBox "Q4が記入されていないか" & vbNewLine & "数値ではありません"
109. 　　Exit Function
110. 　End If
112. 　i = 14
113. 　If ansAddress(i)(0).Value = "" Then
114. 　　MsgBox "Q５が記入されていません"
115. 　　Exit Function
116. 　End If
117. 　
118. 　questCheck = True
119. End Function

図７－４

まず７７行目で、アンケート画面にあるコントロール類の配列を取得します。ここではActiveXコントロールのmakeArray1（図６－４）を呼び出していますが、実際にはフォームコントロールを使用したり、混合で作成したりすると思いますので、先にそのコントロール配列用プロシージャを作成して下さい。

コントロール配列用のmakeArrayプロシージャには引数を２つ渡しますが、ここでは両方とも「ThisWorkbook」を渡しています。
というのも、呼び出す配列の中で使用するのは「送信ボタンを押したブック上のアンケートのコントロールそのもの」だけですので、第一引数にThisWorkbookを渡しさえすれば良いのです。しかし「第二引数は空」というわけにはいかないので、同じThisWorkbookを渡しているだけです。

７９行目以下は、各設問ごとに内容をチェックしています。
７９～８６行目がＱ１、８８～９５行目がＱ２、９７～１０４行目がＱ３、１０６～１１０行目がＱ４、１１２～１１６行目がＱ５をチェックしています。
なおチェックの前提として「コントロールとしてActiveXコントロールが来てもフォームコントロールが来ても成立」するようにします。

まず、Ｑ１・Ｑ２・Ｑ３はコードがほとんど同じなので、代表してＱ１の７９～８６行目で説明します。
この３問は、オプションボタンとチェックボックスのOn-Offの状態を調べています。そこで、今回使われているオプションボタンとチェックボックスのValue値がOn-Offでどの様な値をとるかについて図７－５に示します。

コントロール種	コントロール	On	Off
AxtiveXコントロール	OptionButton	-1	0
	CheckBox	-1	0
フォームコントロール	OptionButton	1	-4146
	CheckBox	1	-4146

図７－５

各設問ごとに「何個のコントロールがOnになっているか」を数えることで、正しく回答できているかを判断することにしましたので、「Onであれば１」「Offであれば０」になるように計算式を考えます。
図７－５のOn-OffをOn = 1、Off = 0に変換する方法には、たぶん何種類かの手法があるのではないかと思いますが、今回は以下のように考えました。
　①各値を絶対値（±をなくす）で考える。
　②Onは奇数、Offは偶数である。（２で割った時にOnは余りが出る）

この考え方を式にしたのが「Abs(ansAddress(i)(0).Value) Mod 2」で、Onの場合だと「１」Offの場合だと「０」になります。
そこで８０行目のFor～Nextでコントロールを移動しながら、On-Offを調べた結果を変数cntに足していく事で、For～Nextを抜けた時には「設問内に何個Onのコントロールが存在するか」が判る事になります。
８３行目ではそのOnの数を調べ、１でなければ８４行目でコメントを出し、８５行目で「アンケート内容チェック関数questCheck」を抜け出します。この時関数はBoolean型を戻しますが、まだ戻り値に何も入れていないので既定値の「False（回答方法が正しくない）」を戻すことになります。

設問１（Ｑ１）のチェックが完了すれば、次の設問（Ｑ２）である８８～９５行目に処理が進むことになります。
次の処理に進む際には、Onの数を入れていた変数cntは初期化しておく必要がありますので、８８行目で変数cntに０を代入しています。
なお、設問３（Ｑ３）のOnの数の判断は１０１行目でしていますが、この判断式（If cnt < 1 Then）では「１つ以上入れてください」という意味になります。チェックボックスは「１つもチェックしていなくてもＯＫ」が通常とも思えますので、設問内容によって判断基準を検討して下さい。

設問４（１０６～１１０行目）は、ActiveXコントロールであればTextBox、それ以外（セルそのものはフォームコントロールでは無いので）であれば通常のセル値の処理が必要となります。そこで入力値に対して得られるValue値を図７－６に整理しました。

ｺﾝﾄﾛｰﾙ種	ｺﾝﾄﾛｰﾙ	①空欄	数値を入力		｢’｣付きで数値を入力		⑥ 数値以外を入力
			②半角	③全角	④半角	⑤全角
AxtiveXｺﾝﾄﾛｰﾙ	TextBox	" " (VarType=8)	数値	数値	文字列	文字列	文字列
ﾌｫｰﾑｺﾝﾄﾛｰﾙ	（セル）	" " (VarType=0)	数値	数値	数値	数値	文字列

図７－６

この設問４は「兄弟の人数」を訊いていますので、入力する値は「ゼロ以上の数値」である必要があります。ですので入力した値が「数値では無い」または「何も入力されていない」時に、注意のコメントを出すことになります。
ですので図７－６で言えば「①⑥以外は数値として認めたい」のですが、ActiveXの④⑤を数値にする簡単な手段が見つかりませんでした。
（もちろん手が無い訳ではありません。ActiveXに分岐した後に先頭の「’（アポストロフィ）」を削除すれば良いのですが、１行くらいで完結できる数式が見つからなかった、という意味です。）

そこで「ActiveXの④⑤は『文字列』でエラーと判断する」ということにして、If文は以下の式にしました（図７－４の１０７行目）。
「If Not IsNumeric(ansAddress(i)(0).Value) Or ansAddress(i)(0).Value = "" Then」
Orより前半の式は「数値か否か」を調べていますので、図７－６の⑥とActiveXの④⑤を指しています。Orより後半の式は図７－６の①を指します。
ちなみに１０６行目の「i = 13」は、１０７行目に埋め込んでしまっても良いのですが、「何番目のコントロールを処理しているか」をできるだけ表に出して見易くするのと、番号が変わったとしても「i = 〇〇」だけを変更した方が修正ミスが少ないだろうと判断したからです。

設問５（１１２～１１６行目）は、設問４と同じくActiveXコントロールであればTextBox、その他であれば通常のセル値の処理になります。
設問内容は「好きな歌手」ですので、数値の歌手（例えば１２３という歌手）はまず居ないでしょうが、とりあえず「無記入だけコメントを出す」ことにしました。状況に応じて数式を組み立てて下さい。

ここまでは、各設問の回答内容をチェックし、問題が有ればコメントを出したあとquestCheck関数を抜け出しています。関数の戻り値には何も設定しないまま抜け出しますので、既定値のFalseが関数の戻り値になります。
全ての設問のチェックが合格すると、１１８行目に到達します。そこでquestCheck関数の戻り値にTrueを設定したあと、関数を抜け出しますので「全てのチェックに合格した時のみ戻り値がTrue」となります。

７ー３．固有情報の取得

図７－１の５８行目では「保存するファイル名」を組み立てていますが、他の回答者と保存ファイル名が一緒にならないようにユニークな名前を付けています。その名前には図７－２のように色々考えられますが、今回は「ＩＰアドレス」「ＭＡＣアドレス」「ユーザー名」「コンピューター名」の取得方法について、以下で説明します。

７ー３ー１．ＩＰアドレス①

WMI（Windows Management Instrumentation）でＩＰアドレスを取得するものが図７－７です。

120. '==========　⇩(6)　WMIでIPアドレスを取得　====================
121. Private Function IPaddress1() As String
122. 　Dim NAConf As Object　　　 'ネットワークアダプタ情報のオブジェクト
123. 　Dim IPEna As Object　　　　 '各ネットワークアダプタ情報
124. 　Dim strIP As Variant　　　　 'IPアドレス（IPv4、IPv6）
126. 　Set NAConf = GetObject("winmgmts:¥¥.¥root¥cimv2"). _
127. 　　　ExecQuery("SELECT * FROM Win32_NetworkAdapterConfiguration Where (IPEnabled = TRUE)")
129. 　For Each IPEna In NAConf
130. 　　For Each strIP In IPEna.IPaddress
131. 　　　If UBound(Split(strIP, "."), 1) = 3 Then
132. 　　　　IPaddress1 = strIP
133. 　　　　Exit For
134. 　　　End If
135. 　　Next strIP
136. 　Next IPEna
138. 　Set NAConf = Nothing
139. End Function

図７－７

１２６行目は、Windowsを管理するWMIオブジェクトを取得します。「winmgmts」はWMIオブジェクトのライブラリを意味します。また、オブジェクトの場所「¥¥」は「.（現在のWindows PC）」のレジストリの名前空間 「¥root¥cimv2」になります。
１２７行目では、そのWMIオブジェクトの「Win32_NetworkAdapterConfiguration」クラスの中から、有効（IPEnabled = TRUE）なネットワークアダプターの情報を取得し、変数NAConfに代入します。
取得したネットワークアダプタ情報は、左辺の変数NACconfに代入しています。

１２９行目では、変数NACconfの中からFor Each を使って、各ネットワークアダプタ情報（変数IPEna）を取り出します。「各ネットワークアダプタ」とは「接続されているネットワークアダプタ」ですので、たとえば有線LANと無線LANの両方が接続されている状態の時には、図７－８の右側のように２つのネットワークアダプタ情報が得られます。

図７－８

１３０行目では、そのネットワークアダプタ情報の中のIPaddressを１つずつ取り出し、変数strIPにセットしています。
IPアドレスには、IPv4とIPv6の２種類があります。現状ではIPv4で管理しているところが多いと思いますが、IPv6も表示されるようなPC設定になっていれば、図７－９のように「IPv4アドレス」と「IPv6アドレス」が配列として取得されます。
図７－９

なお、図７－１０のように「IPv6を不使用」に設定にしているPCの場合は、IPv4のみの配列となります。

図７－１０

１３１行目ではセットされた変数strIPが「IPv4かIPv6か」を調べています。
まず、IPv4・IPv6の表記の違いは以下の通りです。

図７－１１

今回１３１行目で使用してる「UBound(Split(strIP, "."), 1) = 3」は、IPv4表記の中に「.（ピリオド）」が３つ存在することを利用したIF文になっています。
もしIPv6を拾うのであれば、１３１行目のIF文をNotで否定文にするか、またはIPv6のみ「：（コロン）」が有ることから「InStr(strIP,":")>0」とすれば良いと思います。

しかし「IPv4アドレス」「IPv6アドレス」どちらを取得するにしても、図７－８に示したように「複数のアダプタが接続状態」の時には、思った通りの値が取得できない可能性はあります。
と言うのは、アダプタの順番は（私のPCの場合は）有線LANの方が先に来るようですが、For～Eachを使っているため「最初に取得するアダプタが有線LAN」である保証は無いからです。
ちなみに今回のコードでは、最終的に「最後のアダプタのIPアドレス」が戻されることになりますが、もし「最初のアダプタのIPアドレス」を戻したければ、１３５行目と１３６行目の間に「Exit For」を挿入すれば良いことになります。

１３２行目では、１３１行目で選んだIPアドレスを関数プロシージャの戻り値にしています。

７ー３ー２．ＩＰアドレス②

ＩＰアドレスを取得する別の方法として「コマンドプロンプト（Dos窓）にコマンド入力する事で表示される情報」を使う方法が図７－１２です。

140. '==========　⇩(7)　WSHshellオブジェクトでIPアドレスを取得　====================
141. Private Function IPaddress2() As String
142. 　Dim wsh As Object　　　　　　 'WSH(Windows Script Host)shellオブジェクト
143. 　Dim cmd As String　　　　　　 '実行コマンド
144. 　Dim result As Object　　　　　 'コマンド実行結果（事前バインディングだとWshExec型）
145. 　Dim rowArray As Variant　　　 '実行結果を各行ごとに配列にしたもの
146. 　Dim i As Long　　　　　　　　　 'カウンタ変数（実行結果の行位置）
147. 　Dim addressPos As Long　　　 '行の中でのIPアドレスの文字位置
149. 　Set wsh = CreateObject("WScript.Shell")
150. 　cmd = "ipconfig"
152. 　Set result = wsh.exec("%ComSpec% /c " & cmd)
154. 　Do While result.Status = 0
155. 　　DoEvents
156. 　Loop
158. 　rowArray = Split(result.StdOut.ReadAll, vbCrLf)
159. 　
160. 　For i = 0 To UBound(rowArray, 1)
161. 　　If InStr(rowArray(i), "IPv4") > 0 Then Exit For
162. 　Next i
163. 　addressPos = InStr(rowArray(i), ":")
165. 　IPaddress2 = Mid(rowArray(i), addressPos + 2)
167. 　Set result = Nothing
168. 　Set wsh = Nothing
169. End Function

図７－１２

１４９行目は、WSH(Windows Script Host)のWshShellクラスのインスタンスを生成し、変数wshに代入しています。
１５０行目は、実行するコマンドです。今回は「ipconfig」を使用します。手動でコマンドプロンプト上で「ipconfig」を実行すると、図７－１３のような結果が出力されます。
図７－１３の左側がネットワークアダプタが１つの場合、右側が２つ（この場合は、有線LANと無線LAN）の場合です。
（図７－１３は、必要行のみを抜き出して表示し、IPv4・IPv6の値は架空のものに修正してあります。）

図７－１３

１５２行目では設定したコマンドを実行し、その結果を変数resultに代入します。
実行にはWSHのExecメソッドを使用し、その引数（カッコ内）に「"%ComSpec% /c " & cmd」を指定しています。これは図７－１４のように３つの内容を示しています。

図７－１４

先頭の「%ComSpec%」の、「%」で囲まれた「ComSpec」は、環境変数の１つです。
環境変数とは「Windows等のO/Sが提供するデータ共有機能」で、特定の値やPC内の場所（フォルダー等）、また特定ファイルのフルパス名などが登録されています。
例えば今回使っている環境変数「ComSpec」には、「cmd.exe というファイルのフルパス」が登録されています。
もちろん「フルパスをつけたcmd.exe を直接指定」することも可能ですが、このcmd.exeファイルが置かれている場所が、どのPCでも同じかというとそうでもありません。たとえば私のPCであれば「C:¥Windows¥system32¥cmd.exe」という場所にcmd.exeがあるのですが、あなたのPCでは違う場所に置いてある可能性があります。

置いてあるファイルがPCで異なる場合、そのファイルを使うプログラムを作ろうとした時、非常にやり難いことになります。
たとえば「Windowsのスタートメニュー上には必ず『コマンドプロンプト』のアイコンが置いてあり、それをクリックすればどのPCでもコマンドプロンプトが起動する」ことが分かっていれば、プログラムを考えるのが楽になります。
これと同じように「環境変数『ComSpec』を操作すれば、どのPCでもコマンドプロンプトが起動する」ように、環境変数に「C:¥Windows¥system32¥cmd.exe」のようにファイルの場所を登録しておくのです。

この環境変数に対して、登録してある値を呼び出す場合は「環境変数を『%』で囲む」ことになっているため、今回の「%ComSpec%」は「cmd.exeのフルパス」が呼び出されることになります。cmd.exeは「実行ファイル」ですので、「コマンドプロンプトが実行される」ことになります。

２番目の「/c」は、コマンドプロンプト実行ファイル（cmd.exe）のオプションで、「コマンドを実行した後、コマンドプロンプトを終了する」ことを指定しています。
最後の「ipconfig」が「コマンドプロンプト上で実行するコマンド」になります。

ですので「"%ComSpec% /c " & cmd」は、「コマンドプロンプトを開き（実行し）、ipconfigを実行し、実行し終わったらコマンドプロンプトを閉じる」という動作になり、得られた結果（図７－１３）は変数resultに代入されます。

今回、コマンドの実行結果を受け取る変数resultは、１４４行目でObject型として宣言しました。これはWSHを実行時バインディングしているためです。 もし事前バインディング（「Windows Script Host Object Model」を参照設定）をする時には、実行結果を受取る型は「WshExec型」となりますので、変数resultも「WshExec型」して宣言をします。

１５４～１５６行目のDo～Loopは、１５２行目の実行が完了するのを待っています。
今回の変数resultのStatusプロパティは「プロセスが起動中（コマンド実行途中）の間は０を示し、プロセスが終了すると１」になりますので、起動中の間（While result.Status = 0）は次のコードに移行するのを待つことになります。

ただし今回のコマンドプロンプトでの実行は、Windowsを使わない「consoleアプリ」ですので、Do～Loopを回しながら待たなくても、「StdOut.Readall」の部分で「完了するのを待ってくれる」ようです。
ですので「１５４～１５６行目のDo～Loop」は無くても今回は正常に動作します。

実行した結果は「変数result」に格納されていますが、それを文字列にするには「画面に表示されたものを文字列にする」指示が必要です。１５８行目の「result.StdOut.ReadAll」がそれに相当し、「標準出力（＝画面に表示）を全て読み込む」という意味になります。
その文字列は図７－１３のような複数行の文字列ですので、まずSplit関数を使い「改行（vbCrLf）」で区切った配列にします（１５８行目）。
この処理で配列rowArrayの各要素内に１行ずつの文字列が入っていることになります。

１６０～１６２行目のFor～Nextで配列rowArrayから１要素ずつ取り出し、１６１行目で各行の文字列の中に「IPv4」が存在するか否かを調べます。InStr関数ですので、存在しなかったら０を返し、存在したら先頭からの位置（０以外の正数）を返してくれます。
ですのでもし存在したら「Exit For」で、１６０～１６２行目のFor～Nextを抜け出します。

IPv4の文字列が見つかりFor～Nextを抜け出した時点でのカウント変数ｉの値は、抜け出した後もそのまま保持されています。
そのｉの値を使った「rowArray(i)」は「IPv4の文字列がある行」であり、例えば以下のような文字列になります。
　「IPv4 アドレス . . . . . . . . . . . .: 192.168.0.3」

この文字列から右端のIPアドレス値「192.168.0.3」を取り出したいので、その直前にある「：（コロン）」に着目し、その位置をInStr関数で取得して変数addressPosに代入します（１６３行目）。
「：（コロン）」と「192.168.0.3」の間には、スペースが１つ入っていますので、「Mid(rowArray(i), addressPos + 2)」とすることでIPアドレス「192.168.0.3」が文字列として取り出せ、それを関数プロシージャの戻り値に代入します（１６５行目）。

７ー３ー３．MACアドレス①

WMI（Windows Management Instrumentation）でＭＡＣアドレスを取得するものが図７－１５です。

170. '==========　⇩(8)　WMIでMACアドレスを取得　====================
171. Private Function MACaddress1() As String
172. 　Dim NAConf As Object　　　 'ネットワークアダプタ情報のオブジェクト
173. 　Dim IPEna As Object　　　　 '各ネットワークアダプタ情報
175. 　Set NAConf = GetObject("winmgmts:¥¥.¥root¥cimv2"). _
176. 　　　ExecQuery("SELECT * FROM Win32_NetworkAdapterConfiguration Where (IPEnabled = TRUE)")
178. 　For Each IPEna In NAConf
179. 　　MACaddress1 = IPEna.MacAddress
180. 　Next IPEna
182. 　Set NAConf = Nothing
183. End Function

図７－１５

各ネットワークアダプタ情報を取得するまで（～１７８行目）は、図７－７と同じです。
１７９行目では図７－１６のように、ネットワークアダプタ情報の中からMACAddressプロパティを使ってMACアドレスを取り出し、関数プロシージャの戻り値にしています。

図７－１６

なお、図７－８のように、接続されているネットワークアダプタが複数ある場合は、１７８行目からも複数のネットワークアダプタ情報が得られます。ですので図７－１５のコードだと、「最後のネットワークアダプタのMACアドレス」が関数プロシージャの戻り値になりますので、注意が必要です。

７ー３ー４．MACアドレス②

WSH（Windows Script Host）でＭＡＣアドレスを取得するものが図７－１７です。

184. '==========　⇩(9)　WSHでMACACアドレスを取得　====================
185. Private Function MACaddress2()
186. 　Dim wsh As Object　　　　　　 'WSH（Windows Script Host）shellオブジェクト
187. 　Dim cmd As String　　　　　　 '実行コマンド
188. 　Dim result As Object　　　　　 'コマンド実行結果
189. 　Dim rowArray As Variant　　 '実行結果を各行ごとに配列化したもの
191. 　Set wsh = CreateObject("WScript.Shell")
192. 　cmd = "getmac"
194. 　Set result = wsh.exec("%ComSpec% /c " & cmd)
196. 　Do While result.Status = 0
197. 　　DoEvents
198. 　Loop
200. 　rowArray = Split(result.StdOut.ReadAll, vbCrLf)
202. 　MACaddress2 = Left(rowArray(3), 17)
204. 　Set result = Nothing
205. 　Set wsh = Nothing
206. End Function

図７－１７

図７－１２の「WSHでIPアドレスを取得」する方法と、実行コマンド以下が異なるだけです。
今回の１９２行目では、実行コマンドとして「getmac」を使用しました。もちろん「ipconfig /all」でもMACアドレスは取得できますが、getmacはMACアドレスのみが取得できるので、後の処理が楽だと思います。

コマンドプロンプト上で「getmac」を実行すると、図７－１８のような結果が得られます。図７－１８は、実行するPCにネットワークアダプタが２つ存在する場合での実行結果ですが、アダプタが１つの時（有線LANまたは無線LANの一方しかない）は、１行しか表示されません。

図７－１８

この「getmac」コマンドを１９４行目で実行させ、１９６～１９８行目で実行が完了するまで待ち（この「待ち」もcmd.exeがconsoleアプリですので、無くてもＯＫです）、２００行目のSplit関数で各行を配列化しています。

図７－１８では、実際のMACアドレス（コマンドプロンプト上では物理アドレスと表示されている部分）は、getmacコマンドと「物理アドレス」の間には空白行が１行入っていますので、４行目からになります。
Split関数で配列化したものは、インデックスがゼロから始まりますので、実際のMACアドレスはrowArray(3)以降になります。

図７－１８の場合だとrowArray(4)にも物理アドレスが入りますが、１つしかネットワークアダプタが存在しないPCでは「空白行」を取得してしまいますので、確実に物理アドレスを取得するためには、
　①取得する行をrowArray(3)にする
　②rowArrayのサイズをUbound等で取得し、必要な物理アドレスのインデックスを求める
　③getmacで得られる「トランスポート名」に注目し、必要な物理アドレスを探しあてる
などが考えられます。
なお、使用されているアダプタを取得するのであれば上記の③の方法になりますが、有線LAN・無線LANの両方とも使用されている状態で、例えば「有線LANのMACアドレスを取得したい」ような場合であれば、図７－１９のように実行コマンドを「getmac /v」として取得情報を多くし「接続名」から探し出すか、「ipconfig /all」を使うかの方法が良いと思います。

図７－１９

２０２行目では「Left(rowArray(3), 17)」を使ってMACアドレスを取り出しています。これは、MACアドレスは「XX-XX-XX-XX-XX-XX（xは0～Fの16進数）」という４８ビットで出来ていますので、文字数としては１７文字になることから１７文字を取り出しています。

７ー３ー５．ユーザー名①

WSH（Windows Script Host）のWshNetworkオブジェクトを使ってユーザー名を取得するのが、図７－２０です。

207. '==========　⇩(10)　WSHNetworkでユーザー名を取得　====================
208. Private Function USERname1()
209. 　Dim wsh As Object　　　 'WSH（Windows Script Host）Networkオブジェクト
211. 　Set wsh = CreateObject("WScript.Network")
213. 　USERname1 = wsh.USERname
215. 　Set wsh = Nothing
216. End Function

図７－２０

２１１行目ではWshNetworkオブジェクトを生成しています。
WshNetworkオブジェクトのプロパティには３つのプロパティがあります。
　① ComputerName（コンピュータ名）
　② UserDomain（ドメイン名）
　③ UserName（ユーザー名）
ここでは③のUserNameプロパティを使用して、２１３行目でユーザー名を取得し、関数プロシージャの戻り値にしています。

なおExcelの「Application.UserNameプロパティ」で取得できるユーザー名は、「Microsoft Officeのユーザー名」であり、今回取得しようとしている「Windowsへのログイン名」とは別扱いになります。

７ー３ー６．ユーザー名②

WSH（Windows Script Host）のWshShellオブジェクトを使ってユーザー名を取得するのが、図７－２１です。

217. '==========　⇩(11)　WSHShellでユーザー名を取得　====================
218. Private Function USERname2()
219. 　Dim wsh As Object　　　　　　　　 'WshShellオブジェクト
220. 　Dim cmd As String　　　　　　　　 '実行コマンド
221. 　Dim result As Object　　　　　　　 'コマンド実行結果
222. 　Dim rowArray As Variant　　　　　 '実行結果を各行ごとに配列化したもの
223. 　Dim i As Long　　　　　　　　　　　 'カウンタ変数（実行結果の行位置）
224. 　Dim addressPos As Long　　　　　　 '行の中でのユーザー名の文字位置
226. 　Set wsh = CreateObject("WScript.Shell")
227. 　cmd = "echo %USERNAME%"
229. 　Set result = wsh.exec("%ComSpec% /c " & cmd)
231. 　Do While result.Status = 0
232. 　　DoEvents
233. 　Loop
235. 　rowArray = Split(result.StdOut.ReadAll, vbCrLf)
236. 　USERname2 =rowArray(0)
238. 　Set result = Nothing
239. 　Set wsh = Nothing
240. End Function

図７－２１

２２６行目でWshShellオブジェクトを生成し、２２７行目は、コマンドプロンプト上での実行コマンドで、「echo %USERNAME%」としています。

ここで使用している「echo」コマンドは、「そのあとに続く文字列を出力する」という機能があります。今回、そのあとに続く文字列は「%USERNAME%」ですが、「%」で囲まれているものが「環境変数」であるため、「環境変数の値」が出力されることになります。
つまり、環境変数「USERNAME」に登録されている「ユーザー名」が出力されるのです。

ですので２２９行目を実行することで、実行結果の変数resultにはユーザー名が入ります。
実際には、改行マークや空白行がありますので、２３５行目で改行マーク（vbCrLf）で分割し配列化したのち、２３６行目でその配列の１つ目の要素（Split関数で配列にした場合は、インデックスがゼロからスタートします）を取り出して、関数プロシージャの戻り値にします。

なお、環境変数を使用する別な方法として、２２７行目の実行コマンドを「set USERNAME」とする方法もあります。
「set」は「環境変数の値の参照と設定」を行うもので、コマンドプロンプト上で「set」とタイプしリターンキーを押せば、そのPCの環境変数の一覧がABC順に表示されます。また「set user」などとタイプすれば、環境変数の内で「user」から始まる環境変数だけが表示されます。
ここで「USERNAME」と指定すれば、USERNAMEが付く環境変数は１つ（のはず）ですので、「USERNAME=〇〇〇〇」とユーザー名（〇〇〇〇の部分）が表示されます。
あとは、Split関数で「vbCrLf」で各行ごとに配列化し、次に最初の要素に対して再びSplit関数を使って「=」で分割して配列化し、２番目の要素を取り出せば、ユーザー名が得られます。

７ー３ー７．コンピューター名①

WSH（Windows Script Host）のWshNetworkオブジェクトを使ってコンピューター名を取得するのが、図７－２２です。

241. '==========　⇩(12)　WSHNetworkでユーザー名を取得　====================
242. Private Function PCname1()
243. 　Dim wsh As Object　　　　 'WSH（Windows Script Host）Networkオブジェクト
245. 　Set wsh = CreateObject("WScript.Network")
247. 　PCname1 = wsh.ComputerName
249. 　Set wsh = Nothing
250. End Function

図７－２２

構文は図７－２０と全く同じで、異なるのはWshNetworkオブジェクトのプロパティとして「ComputerName」を使用し、コンピューター名を取得しているところです。

７ー３ー８．コンピューター名②

WSH（Windows Script Host）のWshShellオブジェクトを使ってコンピューター名を取得するのが、図７－２３です。

251. '==========　⇩(13)　WSHShellShellでコンピューター名を取得　====================
252. Private Function PCname2()
253. 　Dim wsh As Object　　　　　　 'WshShellオブジェクト
254. 　Dim cmd As String　　　　　　 '実行コマンド
255. 　Dim result As Object　　　　　 'コマンド実行結果
256. 　Dim rowArray As Variant　　　　 '実行結果を各行ごとに配列化したもの
257. 　Dim i As Long　　　　　　　　　 'カウンタ変数（実行結果の行位置）
258. 　Dim addressPos As Long　　　　 '行の中でのコンピューター名の文字位置
260. 　Set wsh = CreateObject("WScript.Shell")
261. 　cmd = "ipconfig /all"
263. 　Set result = wsh.exec("%ComSpec% /c " & cmd)
265. 　Do While result.Status = 0
266. 　　DoEvents
267. 　Loop
269. 　rowArray = Split(result.StdOut.ReadAll, vbCrLf)
271. 　For i = 0 To UBound(rowArray, 1)
272. 　　If InStr(rowArray(i), "ホスト名") > 0 Then Exit For
273. 　Next i
274. 　addressPos = InStr(rowArray(i), ":")
276. 　PCname2 = Mid(rowArray(i), addressPos + 2)
278. 　Set result = Nothing
279. 　Set wsh = Nothing
280. End Function

図７－２３

構文は図７－１２、図７－１７、図７－２１と同じです。
２６１行目の実行コマンドとしては、今回「ipconfig /all」を使用します。このコマンドは「すべて（/all）のネットワーク構成情報」を表示しますので、なんでも取得できる代わりに、多くの情報の中から自分の欲しい情報を取り出す処理が面倒になるというデメリットも考えてコマンド種類を選ぶべきと思います。

「ipconfig /all」の実行結果は、図７－２４のように得られます。長々と表示されますので後半を省略していますが、その先頭に「ホスト名」という内容でコンピュータ名が表示されます。

図７－２４

２６９行目で各行単位の配列にしたのち、２７１～２７３行目のFor～Nextで「ホスト名」と書いてある行を探します。その行のデータ中から「：（コロン）」を探し出し、２７４行目で文字位置を変数addressPosに代入します。
コンピュータ名は、「：（コロン）」の後のスペースを挟んだその後ろに書かれているので、文字列を取り出し２７６行目で関数プロシージャの戻り値にしてます。

７ー３ー９．コンピューター名③

環境変数を使ってコンピューター名を取得するのが、図７－２５です。

281. '==========　⇩(14)　環境変数でコンピューター名を取得　====================
282. Private Function PCname3()
283. 　Dim wsh As Object　　　　　　 'WshShellオブジェクト
284. 　Dim cmd As String　　　　　　 '実行コマンド
285. 　Dim result As Object　　　　　 'コマンド実行結果
286. 　Dim rowArray As Variant　　　 '実行結果を各行ごとに配列化したもの
288. 　Set wsh = CreateObject("WScript.Shell")
289. 　cmd = "set COMPUTERNAME"
291. 　Set result = wsh.exec("%ComSpec% /c " & cmd)
293. 　Do While result.Status = 0
294. 　　DoEvents
295. 　Loop
297. 　rowArray = Split(result.StdOut.ReadAll, vbCrLf)
299. 　PCname3 = Split(rowArray(0), "=")(1)
301. 　Set wsh = Nothing
302. 　Set result = Nothing
303. End Function

図７－２５

ここで使用する実行コマンドは「set COMPUTERNAME」です。
図７－２１で「echo %環境変数%」を実行することで直接「環境変数の値」を出力するのと同様に、「echo %COMPUTERNAME%」とすることで直接「コンピュータ名」が得られますが、別な手法として「set」を使って説明します。

「set」は「環境変数の値の参照と設定」を行うもので、「set 環境変数」というコマンドを実行することで「COMPUTERNAME=〇〇〇〇」とコンピュータ名（〇〇〇〇の部分）が表示されます。
なお、入力コマンド内の環境変数は「COMPUTERNAME」と大文字にしていますが、小文字でも問題ありません。

「set COMPUTERNAME」を実行して得られる結果は、通常は図７－２６の左のようになります。環境変数として「COMPUTERNAME」から始まるものは（たぶん）１つのみですが、たとえ「COMPUTERNAME1=C:¥」みたいなものを「ユーザー環境変数」として作られてしまったとしても、「set COMPUTERNAME」と環境変数名を略さずに指示すれば、図７－２６の右側のように本来の「COMPUTERNAME」の方が先に表示されます。

図７－２６

ですので、処理する対象行は１行目（Splitで配列化しているためインデックスは０番目）となるため、２９９行目で「rowArray(0)」に対して「＝（イコール）」でSplit分割をし、その２番目（Splitで配列化しているためインデックスは１番目）である「Split(rowArray(0), "=")(1)」を関数プロシージャの戻り値にしています。

８．アンケート集計プログラム（Module３）

アンケートを集計するには、図８－１の「questCountプロシージャ」を呼び出します。シート上に集計ボタンを作っても良いのですが、ユーザーに勝手に集計ボタンを押されても困りますし、ただ１回限り（多くても数回？）の実行ですので、直接マクロ実行をしてもらうことにしました。もちろんボタンに登録しても構いません。

８ー１．アンケート集計・出力

集計時に呼び出されるのが図８－１の「questCountプロシージャ」です。ほぼ１つのプロシージャ内で完結させていますが、汎用性のある「あるフォルダ―内に存在するファイルの一覧を取得」する部分だけは、図８－４として別プロシージャにしました。

304. '==========　⇩(15)　アンケートの集計と出力　====================
305. Public Sub questCount()
306. 　Dim FL As Variant　　　　　　　　　 '回収済みアンケートのファイル名を格納した配列
307. 　Dim i As Long　　　　　　　　　　　 'カウンタ変数（アンケートファイルの数）
308. 　Dim j As Long　　　　　　　　　　　 'カウンタ変数（設問選択肢の順番）
309. 　Dim ansExcel As Workbook　　　　　 '１つ１つのアンケートファイル
310. 　Dim ansAddress As Variant　　　　　 'コントロールの位置配列
312. 　FL = existsFiles(SERVER & "*.xlsm")
313. 　If IsEmpty(FL) = True Then Exit Sub
315. 　Application.ScreenUpdating = False
317. 　　ansAddress = makeArray1(ThisWorkbook, ThisWorkbook)
319. 　　For j = 1 To UBound(ansAddress, 1)
320. 　　　Select Case ansAddress(j)(2)
321. 　　　　Case "onoff", "num"
322. 　　　　　ansAddress(j)(1).Value = ""
323. 　　　　Case "down"
324. 　　　　　If Not ansAddress(j)(1).Value = "" Then
325. 　　　　　　If ansAddress(j)(1).Offset(1, 0).Value = "" Then
326. 　　　　　　　ansAddress(j)(1).Value = ""
327. 　　　　　　Else
328. 　　　　　　　Range(ansAddress(j)(1), ansAddress(j)(1).End(xlDown)).Value = ""
329. 　　　　　　End If
330. 　　　　　End If
331. 　　　　Case "right"
332. 　　　　　If Not ansAddress(j)(1).Value = "" Then
333. 　　　　　　If ansAddress(j)(1).Offset(0, 1).Value = "" Then
334. 　　　　　　　ansAddress(j)(1).Value = ""
335. 　　　　　　Else
336. 　　　　　　　Range(ansAddress(j)(1), ansAddress(j)(1).End(xlToRight)).Value = ""
337. 　　　　　　End If
338. 　　　　　End If
339. 　　　End Select
340. 　　Next j
342. 　　For i = 1 To UBound(FL, 1)
343. 　　　Set ansExcel = Workbooks.Open(filename:=SERVER & FL(i), ReadOnly:=True)
344. 　　　ansAddress = makeArray1(ansExcel, ThisWorkbook)
346. 　　　For j = 1 To UBound(ansAddress, 1)
347. 　　　　Select Case ansAddress(j)(2)
348. 　　　　　Case "onoff"
349. 　　　　　　ansAddress(j)(1).Value = ansAddress(j)(1).Value + Abs(ansAddress(j)(0).Value) Mod 2
350. 　　　　　Case "num"
351. 　　　　　　ansAddress(j)(1).Value = ansAddress(j)(1).Value + ansAddress(j)(0).Value
352. 　　　　　Case "down"
353. 　　　　　　If ansAddress(j)(0).Value = "" Then
354. 　　　　　　　ansAddress(j)(1).Offset(i - 1, 0).Value = "ー"
355. 　　　　　　Else
356. 　　　　　　　ansAddress(j)(1).Offset(i - 1, 0).Value = ansAddress(j)(0).Value
357. 　　　　　　End If
358. 　　　　　Case "right"
359. 　　　　　　If ansAddress(j)(0).Value = "" Then
360. 　　　　　　　ansAddress(j)(1).Offset(0, i - 1).Value = "ー"
361. 　　　　　　Else
362. 　　　　　　　ansAddress(j)(1).Offset(0, i - 1).Value = ansAddress(j)(0).Value
363. 　　　　　　End If
364. 　　　　End Select
365. 　　　Next j
367. 　　　ansExcel.Close savechanges:=False
368. 　　　Set ansExcel = Nothing
369. 　　Next i
371. 　Application.ScreenUpdating = True
372. 　MsgBox "アンケート総数　" & UBound(FL, 1) & "　件"
373. End Sub

図８－１

３１２行目では、図８－４の「existsFilesプロシージャ」を呼出し、戻って来た「ファイル一覧の配列」を変数FLに代入しています。
「existsFiles」には、「フルパス＋ファイル名」を引数として渡し、ファイルが存在する場合にはファイルの配列を、存在しなかった場合にはEmpty値を戻すようにしています。
引数に「フルパスとファイル名を分けて渡す」方法も考えられますが、フルパスの最後に「￥マーク」を付けるか否かは人によって異なる可能性があると思い、「フルパス＋ファイル名」を一体にして指定することとしました。

戻り値がEmptyの時は処理するファイルが無いことを意味しますので、３１３行目でプロシージャを抜け出し、マクロ終了させます。

３１７～３６９行目は、アンケート回答済みファイルの開閉と、セル値の書き換え（含：セル値の消去）を行っている部分です。ブックが上下したりセル値が変更する画面更新処理は、非常に時間がかかるのと見た目がバタバタするため、３１５行目で画面更新をストップさせています。
しかし画面ストップさせてしまうと、「本当に処理が進んでいるのか、異常で止まっているのか」がユーザーには分からなくなってしまいます。ですので、「ファイル１が終了」「ファイル２が終了」・・・等と途中経過を示す手法もあります（今回は、その機能は入れませんでした）。

３１７行目では、図６－４・図６－６の「コントロールの位置配列 makeArray」を呼出し、変数ansAddressに代入しています。ここでは図６－４のActiveXコントロールのmakeArray1（Sheet1のアンケート画面）を呼び出していますが、フォームコントロールの場合はmakeArray2（Sheet2のアンケート画面）になります。
３１７行目で得た変数ansAddressの値は、３１９～３４０行目の「データ集計セルの消去」工程に使用するのですが、「自分のファイル（ThisWorkbook）の集計するセル」の位置しか使用しません。つまりmakeArray関数に渡す引数の内２番目の引数しか使用しませんので、１番目は何を渡しても問題ありません。但しこの時点で開いているファイルは自分のファイル（ThisWorkbook）だけですので、２つの引数とも同じThisWorkbookを渡しています。

３１９～３４０行目は「データ集計セルの消去」を行っています。
３１９行目は「コントロール位置配列」のコントロール数を全てチェックします。コントロール位置配列の３番目の要素（インデックス＝２）には「集計方法」が格納されていますので、３２０行目のSelect Caseで仕訳けています。

集計方法が「onoff」と「num」の場合（３２１行目）は、１セルのみに対して値の読み書きをしていますので、３２２行目で「値の消去（＝長さゼロの文字列の書込み）」をしています。

集計方法が「down」の場合（３２３行目）は、３２４～３３０行目を実行します。
消去すべきデータが何個あるかによって、図８－２のように処理が変わります。

図８－２

まず、消去すべきデータが「０個の時」は、そもそも消去する必要がありません。ですので、３２４行目では「記入する先頭セル」に値が入っているかを調べ、値が入っている場合のみ３２５～３２９行目を実行します。
次に、「記入する先頭セル」には値が入っているが、その１つ下のセルには値が無い（消去するデータは１個）場合は、「記入する先頭セル」のみを消去（３２６行目）すれば良いことになります。
最後に、「記入する先頭セル」には値が入っており、その下のセル以降も値が入っている（消去するデータは複数個）場合は、「記入する先頭セル」を基準とし「ENDキー ＋ ↓（下矢印キー）」を使って「連続した値の入っているセル範囲の一番下のセル」を求め、「記入する先頭セル」からのセル範囲に対して値を消去（３２８行目）してます。

注意するところとしては、消去するセルが１つの時に「記入する先頭セル」から「ENDキー ＋ ↓（下矢印キー）」を使ってしまうと、ワークシートの一番下まで飛んで行ってしまいます。
またそれを避けようとして、「ENDキー ＋ ↓（下矢印キー）」の基準とするセルを「記入する先頭セルの１つ上のセル」に持ってくると、うまく「記入する先頭セル」で止まってくれるので「消去セル＝１個」と「消去セル＝複数個」を１つの式で表せそうですが、落とし穴があります。
それは「記入する先頭セル」がワークシートの１行目にある場合です。この場合「１つ上のセル」は存在しませんのでエラーが発生してしまいます。ということで、今回は３つに分岐させて消去をしています。

集計方法が「right」の場合（３３１行目）は、３３２～３３８行目を実行します。
内容は「down」の時とほぼ一緒で、動く方向が「右方向」になるだけです。

３４２行目のFor～Next内で、１つ１つ回答ファイルを調べていきます。調べる個数は、３１２行目で得た配列の要素数（＝ファイルの個数）になります。
まず３４３行目では、配列FLの要素の中から１つの回答ファイルを開き、開いたファイルを指定し易いように変数ansExcelに代入します。Openメソッドには２つの引数を渡します。
１つ目の引数は、開くファイルを「filename」で指定します。３１２行目で得た配列FLには「ファイル名のみ」が入っていますので、フルパス名をくっつけて「SERVER & FL(i)」とします。
２つ目の引数は「ファイルをどの状態で開くか」の設定で、今回は「ReadOnly:=True（読み取り専用）」で開いています。
「ReadOnly」で開く理由は、開くファイルを書き換える必要が無いからですが、それよりも、もし書き込み可能な設定でファイルを開いていた時に、マクロやO/Sの異常で書き換えてしまったりファイルを壊してしまうのを出来るだけ防ぐためです。
せっかく回答してくれたアンケートがダメになってしまっては、元も子もありませんし、「ミスったから、もう一度回答して下さい」なんて恥ずかしくて言えません。

３４４行目では、図６－４・図６－６の「コントロールの位置配列 makeArray」を呼出し、変数ansAddressに代入しています。
この時makeArrayに渡す２つの引数の内、第一引数には「ansExcel」を渡します。第一引数は「状態や値を調べる対象」である各コントロールのオブジェクト（Workbook）になりますので、状態や値を調べる「アンケート回答済みファイル（＝変数ansExcel）」を指定します。
また第二引数には「ThisWorkbook」を渡します。第二引数は「集計結果を書き込むセル等」のオブジェクト（Workbook）になりますので、自分のファイル（＝集計ファイル）を指定します。

３４６行目のFor～Nextは１つ１つのコントロールごとに集計処理を行っていきます。
３４７行目のSelect Caseでは「集計方法（ansAddress(j)(2)）」で分岐をさせます。

まず集計方法が「onoff」の場合（３４８行目）は、図７－４の「アンケートのチェック」でも使用した「コントロールがOnの時 → １、Offの時 → ０」の数式を使用します。その数式は「Abs(ansAddress(j)(0).Value) Mod 2」（３４９行目）で、書込みセル「ansAddress(j)(1).Value」に値を積み上げていきます。

集計方法が「num」の場合（３５０行目）は、拾い上げる値は「TextBoxや単一セルに入っている値（＝ansAddress(j)(0).Value）」ですので、それを書込みセル「ansAddress(j)(1).Value」に値を積み上げていきます（３５１行目）。

集計方法が「down」の場合（３５２行目）は、まず３５３行目で「TextBoxや単一セルに入っている値（＝ansAddress(j)(0).Value）が空か否か」を調べます。（なお、今回のアンケートチェック（図７－４）では空は許していませんので、基本的に３５４行目は実行されません。）
空の場合は、３５４行目で「ー」の文字列を記入する事としました（文字列であれば何でも構いません）。この理由は、空白を集計セルに書き込んでしまうと、３２３～３３０行目のデータ消去時に「データとデータの間に空白が生じ」てしまい、「消去し残し」が発生する可能性があるからです。
（なお回収アンケート数が増えていく一方であれば、消去し残しが発生したまま再集計しても、旧データを全て上書きしてくれるため実際には問題は無いことになります。）

空では無い場合は、３５６行目で「TextBoxや単一セルに入っている値」を書き込みます。一番目のファイルの時は i = 1 ですので、書き込む先は図８－３のように「ansAddress(j)(1).Offset(i - 1, 0)」 → 「ansAddress(j)(1).Offset(0, 0)」 → 「ansAddress(j)(1)」の場所になります。

図８－３

式の後ろについている「Offset(i - 1, 0)」で使われているOffsetプロパティは「(行数 , 列数)分だけ移動する」という意味ですので、i=1の時は「移動せず」、i=2の時は「１行下に移動する」ことになりますので、カウンタ変数 i が１つずつ進むに従って１つずつ下に下がることになります。
なお、プラス値は下に下がっていきますが、マイナス値の場合は上に上がります。ただし、１行目より上には上がる事ができませんので、その時にはエラーが発生します。今回、何通の回答ファイルがあるか分かりませんので、上方向には羅列させず下方向のみへの移動としています。

集計方法が「right」の場合（３５８行目）は、移動方向が異なるだけで内容は「down」と同じになります。
Offsetプロパティを使って書込みセルの移動を行っていますが、Offset(移動する行数 , 移動する列数)ですので「移動する列数」の方を変更して右へ右へと移動させています。

コントロールの状態・値を各セルへ書込みが終了したら、３６７行目で回答ファイルを閉じます。ファイルを開くときに「読み取り専用」で開きましたが、もし異常があってファイルへの書込みをしてしまった時には、ファイルを閉じる時に「変更内容を保存しますか？」のダイアログが発生してしまいます。ですので、ファイルを閉じる時には「savechanges:=False」と「保存しない」のオプションを付けて閉じています。

全てのファイルの処理が完了したら、３７２行目で「アンケートが何件あったか」をダイアログ表示し終了します。
この件数を表示する機能は、集計した結果の平均値などを計算する際に必要になるだろうと思って付けてあります。なお、集計方法downでの表示行数を調べるとか、OptionButtonの各件数を合計する（OptionButtonは、どれか１つしかONに出来ないので）とかの方法でも総件数を知ることは可能です。
また総件数は、集計するワークシート上のどこかに書き込んだり、合計値を総件数で割って平均値に値を置き換えたりしても、もちろん良いと思います。

８ー２．回収されたファイルの一覧作成

図８－１の３１２行目から呼び出されるのが図８－４です。引数として、今回は「フルパス＋ファイル名（＊.xlsm）」を受け取ります。（＊はワイルドカードを表し、この場合は拡張子が「xlsm」である全てのファイルを表すことになります。
この関数プロシージャの機能は、引数で渡された「フルパス＋ファイル名」（今回は、フルパス ＋ ＊.xlsm）に合致するファイル名を配列の形で戻します。なお、引数に「フルパス（最後は￥印）」のみを渡すと「そのフルパス内の全てのファイル名」を配列の形で戻します。

374. '==========　⇩(16)　アンケート回収場所に存在するファイルの一覧作成　====================
375. Private Function existsFiles(FLname As String) As Variant
376. 　Dim buf As String　　　　 'Dir関数で戻されるファイル名
377. 　Dim bufs() As String　　　 'ファイル名を格納する動的配列
378. 　Dim i As Long　　　　　　　 'カウンタ変数（ファイル数）
380. 　buf = Dir(FLname)
382. 　Do While buf <> ""
383. 　　i = i + 1
384. 　　ReDim Preserve bufs(1 To i)
385. 　　bufs(i) = buf
386. 　　buf = Dir()
387. 　Loop
389. 　If i = 0 Then
390. 　　existsFiles = Empty
391. 　Else
392. 　　existsFiles = bufs
393. 　End If
394. End Function

図８－４

３８０行目では、この関数プロシージャが引数として受け取った「フルパス＋ファイル名」をDir関数に渡して、まず取り出せたファイル名を変数bufに代入します。Dir関数で取り出すファイルの順番は、NTFSフォーマットではファイル名順で、FATフォーマットではディスクに保存された順で取り出します。
なお、フォルダ―内にファイルが無い場合には「" "（長さゼロの文字列）」が、また指定したフォルダー名が間違っていた（＝存在しない）場合にも「" "（長さゼロの文字列）」が戻ります。

３８２行目のDo～Loopの継続条件は「buf<>" "」となっており、「ファイル名が取得される間」はDo～Loop内が実行されます。反対に「ファイル名を全て取得しきった」または「ファイルが空のフォルダー」「存在しないフォルダー名」の場合はDo～Loopを抜けて３８９行目以降を実行します。

３８３行目でカウンタ変数ｉを１つ増やし（初回は、既定値０→１）、３８４行目のReDimで配列bufsのサイズを大きくします。ReDimにはキーワードPreserveを指定し、それまでに格納した値を初期化しないようにしています。
３８５行目は、「i = 1」の場合は３８０行目で取得したファイル名（＝変数buf）を、「i = 2 以上」の場合は３８６行目のDir()で取得したファイル名（＝変数buf）を配列bufsに代入します。

３８６行目の「Dir()」は、２つ目以降のファイル名を取得します。Dir関数をカッコ内が空の状態で実行すると、その検索条件は１つ前の条件、つまり３８０行目の「FLname（引数値）」が適用されます。なお、取り出すファイル名が無くなった時は「" "（長さゼロの文字列）」が戻ります。
戻されたファイル名（取り出すファイル名が無くなった時は「" "」）は変数bufに代入されます。

例えば、２番目のファイル名が３８６行目で取得された時は、Do～Loopで３８２行目に戻り、条件の「buf<>0」は成立していますので、Do～Loop内を３８３→３８４→３８５行目と進み、２番目のファイル名が配列bufsに格納されます。
もし、フォルダー内にファイルが２つしか無い場合は、３８６行目で「" "」が変数bufに入ります。またDo～Loopで３８２行目に戻りますが、条件の「buf<>0」は成立しないため、Do～Loopを抜けて３８９行目に飛びます。
よって、ファイルの数だけの要素数の配列bufsが出来上がります。

３８９行目では、カウンタ変数ｉの値を調べています。「ｉ＝０の場合（ｉが規定値のまま）」は「Do～Loop内を１度も実行していない」ことを示していますので、「指定フォルダー内にファイルが１つも無かった」ことになります。
ですので関数プロシージャexistsFilesの戻り値は３９０行目でEmptyとします。
それ以外（ｉ＝１以上）の場合は、配列bufsにファイル名が格納されていますので、その配列を392行目で関数プロシージャexistsFilesの戻り値にします

９．その他のプログラム（Sheet1のシートモジュール）

図４－２のようにActiveXコントロールでアンケート画面を作る場合、送信ボタンもActiveXコントロールで作ると、直接ボタンに「マクロ登録」することが出来ません。ですので、作ったアンケートのシートモジュールに図９－１のようにClickイベントプロシージャを作成します。

395. '==========　⇩(17)　アンケート送付ボタンのClickイベント（ActiveX対応）　=============
396. Private Sub CommandButton1_Click()
397. 　Call questionSend
398. End Sub

図９－１

内容としては、アンケート送信ボタンをクリックした時に時にClickイベントが発生しますので、３９７行目で「questionSend」プロシージャ（図７－１）を呼出します。

１０．最後に

今回は、PCから情報を取り出す方法を色々と紹介しました。例えばIPアドレス取得は、多くのサイトでは図７－７（WMIを使ってレジストリから情報を得る）の方法を紹介していますが、図７－１２（WSHShellを使って実行コマンドの表示結果を加工）を使う方法の紹介は少ないように思いました。
コマンドプロンプト上で「Ipconfig /all」などを実行してみると実に多くの情報が得られ、工夫さえすれば自分が使いたい情報を取り出すことが出来ます。確かにコマンドプロンプトの表示に時間がかかるデメリットはありますが、自由度は高いと思います。
（ちなみに Application.ScreenUpdating = False ではコマンドプロンプトは停止できません。コマンドプロンプトはApplication（Excel）ではありませんので。）

但しコマンドプロンプトにしてもPowerShellにしても、「間違えると本当に大変な事になります」ので充分慎重に扱って頂きたいと思います。また、実行コマンドの出力値を加工する際は、Windowsのバージョンによって出力表示が変わらないかを確認した方が良いと思います。


アンケートの回収と集計方法（it-045.xlsm）