エクセルアンケートフォーム VBAユーザーフォームで、Sheetに作成した質問事項からフォームを自動作成する。
質問用のフォームを自動作成する。
エクセルを使ってアンケートを作る場面で、いろんな種類のアンケートを何度も作っていくと「また１からアンケートのフォームを作らないといけないのか…」という場面があるのではないでしょうか？
「年に１回、決まった内容のアンケートを取る」なら、１回フォームを作ってしまえば終了ですが、
「たびたび、アンケート内容が変わりその都度アンケート用紙を作り直している」という人は地獄だと思います。
そんな悩みを解決するための、VBAを作成しました。
この記事は過去記事を元に作成しております。↓↓
bimori466-1.hatenablog.com
１ 閲覧対象者
１ エクセルVBAのユーザーフォームでアンケートマクロを作成したい初心者の人。
２ エクセルでアンケートをたくさん作っている人。
２ 得られる効果
質問Sheetに作成したアンケート入力のFrameを自動作成して、回答を他BookにＳＱＬ文で反映する。
処理イメージ
＜まずは動画をごらんください＞
アンケートフォーム自動生成 pic.twitter.com/VvhvRyh43U
— かずやん (@y8bV4ty1wbkTjPd) 2020年11月12日
「アンケート入力用」、「回答書き込み用」の２つのエクセルを準備します。
１ 下のような、質問Sheetを作成し、
２ ユーザーフォームを起動し、回答開始をクリックします。
３ 「回答用Ｆｒａｍｅ」と「← 前へ」「次へ →」のコマンドボタンが出現します。質問に回答して「次へ →」で進んでいきます。
４ 最終項目になったら、「回答を送信」が出現します。すべて回答したら、「回答を送信」をクリックする。
５ 他ＢＯＯＫにデータが反映される。
集計後のグラフのまとめ方は、今回は触れません。あくまで、質問Ｓｈｅｅｔを作って、アンケ―ト回答の結果を書き込むところまでです。
この記事でお伝えしたいのは、「質問Ｓｈｅｅｔを作れば、アンケートフォームは自動でできる」という部分です。
３ 設計
概要
回答用Sheetに作成したアンケート内容を、ユーザーフォームで回答用フォームを作成する。
回答用フォームで入力されたアンケート結果を、他のＢＯＯＫにＳＱＬ文で反映する。
詳細
回答用Sheet
１ 列Ａに問題番号を入れる。質問が複数ある場合は、質問ごとに番号を入れる。
例：年齢（１Ｆｒａｍｅに、６項目） ↓↓
２ 単一回答はOptionButton。複数回答はCheckBox。フリー入力はTextboxを使用。
３ OptionButton、CheckBoxは必須項目のみ。Textboxは必須or任意とする。
（変更したいなら、コード追加すれば可能。とりあえずは、「その他ありましたらご記入ください」みたいなときに空白でもＯＫというニュアンスで作ってます。）
４ 質問に対しての項目数は７つまで。
（Frameのサイズ変えれば何個でもＯＫ。）
↓↓今回の作りでは、７つまでがＦｒａｍｅぎりぎりです。
回答集計Sheet
ユーザーフォームからのアンケート回答データを受け取るＳｈｅｅｔです。回答用Sheetがある別ＢＯＯＫのユーザーフォームで「回答を送信」したらこのＳｈｅｅｔに書込まれます。
↓↓今回のアンケートは、質問数が６なので、列が６までです。
ここまでで、アンケート集計を処理する準備は完了です。次にユーザーフォームで何をやっているのかを解説します。
４ ユーザーフォームの処理を解説
質問ＳＨＥＥＴ作成済の前提で書いていきます。
アンケート回答処理の流れ
１ ユーザーフォームの表示（UserForm_Initialize）
２ 「回答開始」をクリック（cmd_strart_Click）
３ 「次へ→」をクリック（cmd_Next_Click）
４ 最後の質問になったら、「回答を送信」をクリック（cmd_回答送信_Click）。回答集計Sheetにアンケート結果が反映される。
以上の１～４までです。
では、順番に解説していきます。
まえおき ＦＲＯＭについて
ＶＢＥのユーザーフォームについて
上の画像ののように、ＶＢＥからフォームを見ると７つのオブジェクトがあります。
しかし、標準モジュールからユーザーフォームを開いた時には「回答開始」のみを表示しています。
コードの記述上、非表示（Visible＝False）にしています。なぜ非表示にするかは、「回答の流れを誘導するため」です。おいおい流れの中で説明をします。
それぞれのフォームの説明
今回の記事の肝（言いたいこと）は、「ＦＲＡＭＥ部分は回答Ｓｈｅｅｔから自動で作ります」ということです。
１ ユーザーフォームの表示（UserForm_Initialize）
UserForm_Initializeの処理（標準モジュールで「Userform．Show」したときの処理）
処理
プロジージャ「uf_open」を実行する。
結果
ＦＲＯＭが表示され、「回答開始」のみ表示。
解説
まず、ＶＢＥのＦＲＯＭを表示します。
ここのメイン処理は「lst_Answer」の作成です。
最初はlst_Answer（アンケート結果）の中身は空です。
質問Ｓｈｅｅｔから、lst_Answerを作成します。
lst_Answerの結果は下の画像で（Visible＝Trueにしてます）↓↓
今回の質問数は６つなので、lst_Answerに１～６のレコードが作成されます。
項目は、「Question_No」、「アンケート回答」、「入力必須or任意」の３つです。
次に、Ｆｏｒｍのサイズ変更処理をします（ここでＦｏｒｍのサイズを変更できます）。
最後にPublic変数「SQN」に「１」を代入します。（SQN＝Start_Question_No）という意味で作った変数です。
Public変数にすることで、フォームモジュールの処理を何か実行しても、ＳＱＮの値は保持されたままです。
主に、コマンドボタン「← 前へ」「次へ →」の処理をするときの開始位置として使います。なので、結構大事な変数になります。
２ 「回答開始」をクリック（cmd_strart_Click）
処理
「回答開始」をクリック
結果
コマンドボタン「← 前へ」「次へ →」を表示します（Visible＝True）。
アンケート回答用のＦＲＭＡＥを作成します。
解説
この記事で主張しているＦｒａｍｅの自動作成の処理です。
質問Ｓｈｅｅｔの列（ＲＯＷ）を、変数「S_Line」に取得します。
この場合、InitializeでSQN＝１としたので、質問Ｓｈｅｅｔの最初のＲＯＷ番号は「２」となります。
↓↓＊SQN＝１のとき、ＲＯＷ番号は「２」
ＦＲＯＭの中には、Frameを「３つまで」作ります。あとは、回答入力します。
このように、回答の流れを誘導しています。
＊ちょっと小話
ＶＢＥからＦＲＯＭをみると、問題検索という機能を作っています。これを最初から表示させておくと、 いきなり問題検索をする人もいます。
「そんな人いない」と言われそうですが、こちらが思いもしない行動をユーザーはとるものです。なので、表示の仕方にも工夫が必要なんですね。
３ 「次へ→」をクリック（cmd_Next_Click）
処理
回答を入力して、「次へ →」をクリックする。
結果
次の質問が表示される（Ｑ４～Ｑ６）。最終問題になったので「回答を送信」を表示する。
解説
まず、問題を回答します。該当するOptionButtonをクリック。
cmd_Next_Clickの処理の流れ
１ Next_Answet_Flgをたてる。
２ Frame名を調査。
３ Frameの入力チェック。
４ lst_Answerに、回答を書込む処理。
５ 次のFrameを作るため、現在のFrameを削除。
６ 次のQuestionを作る（cmd_strart_Click）。
７ Next_Answet_Flgを切る。
１～７まで順番に解説します。
１ Next_Answet_Flgをたてる。
SQN（Start_Question_No）に＋３して、Last_Question_Noより大きくない場合、変数Next_Answet_Flg = Trueとする。
（「6 次のQuestionを作る」時に「Next_Answet_Flg = True」ならば、SQN＋３して次の質問Frameを作成する。）
２ Frame名を調査。
現在表示されているFrame名を取得する。
「入力チェック処理」時、lst_Answerへの「回答書込処理」のときに、ループ処理するFrameの特定のために使用する。
３ Frameの入力チェック。
チェックが入っていないFrameがあれば、メッセージを出す。そのため、Frame３つに回答がされていなければ次には進めない。
その他の項目は、チェックがあってもTextbox内が空白であればエラーとなる。
４ lst_Answerに、回答を書込む処理。
現在表示されているFrameの「Question_No」とlst_Answerの「Question_No」が等しいとき、lst_AnswerにFrameの回答を書き込む。
Frame内のOptionButton、CheckBox、Textboxで分岐して書込処理を作っている。
５ 次のFrameを作るため、現在のFrameを削除。
Q1～Q３のFrameを削除する。
６ 次のQuestionを作る（cmd_strart_Click）。
cmd_strart_Clickの処理を呼び出しています（Call）。cmd_strart_Clickの処理の中で、「Next_Answet_Flg = True」ならば、SQN＋３して次の質問Frameを作成する。
７ Next_Answet_Flgを切る。
「Next_Answet_Flg = False」とする。Next_Answet_Flg はPUBLIC変数なので、何かFrom処理が行われても記憶されたままです。
４ 最後の質問になったら回答後、「回答を送信」をクリック（cmd_回答送信_Click）。回答集計Sheetにアンケート結果が反映される。
処理 回答を入力し、「回答を送信」をクリックする。
結果 メッセージボックスが表示される。回答終了。
回答集計Ｓｈｅｅｔ アンケート結果が反映される。
解説
アンケートが最終質問になったら、「回答を送信」ボタンが出現するので回答後クリックする。
cmd_回答送信_Clickの処理の流れを解説。
１ Frame名を調査。
２ Frame項目の「入力チェック処理」
３ lst_Answerに、回答を書込む処理。
４ 回答集計Ｓｈｅｅｔ更新用のＳＱＬ文作成
５ 回答集計Ｓｈｅｅｔ更新する。
１～３は、「cmd_Next_Clickの処理の流れ」と同じです。４，５を解説します。
４ 回答集計Ｓｈｅｅｔ更新用のＳＱＬ文作成
アンケートの回答結果を書き込んでいたlst_Answerから、ＳＱＬ文を作ります。
ループ処理して、カンマで区切って文字列として回答をくっつけます。
作成するＳＱＬ文のコード
strSQL = " INSERT INTO [回答集計2$](1,2,3,4,5,6) VALUES(" & SQL_VALUES & ")"
変数SQL_VALUES が更新するＳＱＬ文の値です。
変数strSQLが回答集計Ｓｈｅｅｔを更新するためのＳＱＬ文です。
回答集計Ｓｈｅｅｔの、列名１～６は事前に作成しておく必要があります。
以上がユーザーフォーム処理の説明です。
５ 反省点
１ 「質問Ｓｈｅｅｔ作成入力フォーム」を作る。
理由：エクセルを直接編集するのではなく、専用の入力フォームを作ればエラーが減る。
２ アンケートの質問文字列を「FrameのCaption」ではなく、「LABELのCaption」にすればよかった。
理由：アンケ―トの質問が長い場合、「FrameのCaption」は文字の改行ができないため、幅を横に広げるしかなく、見にくいものになってしまう。「LABELのCaption」なら文字の改行が可能。つまり、質問を「LABELのCaption」で作り、回答欄をFrameで作ればよかった。
３ 「← 前へ」をクリックしたときに、前回回答した問題にチェックを入れる。
理由：もう一度入力するユーザーの手間を省ける。
４ 回答集計Ｓｈｅｅｔも自動で作成するようにする。
理由：今回はＳＱＬ文で更新する「回答集計Ｓｈｅｅｔ」は事前に作成していました。しかし、反省点１の「質問Ｓｈｅｅｔ作成入力フォーム」を作り終わった段階で自動作成するようにすれば、ＳＱＬ文で更新する列を手動入力する必要がなくなる。
