この記事では、フォームを簡単に作れる「VBAとGoogle Apps Scriptのコードサンプル」 を紹介します。
プログラム初心者の方で、今までコードをあまり書いたことがない方ほど、どこからどうやって書いたらいいのか迷うし、時間がかかって大変ですよね。
その悩みを簡単に解決するのがコードサンプルです。「コードのひな型」のことですね。
このサンプルを知っているだけで、簡単にフォームが作れるので、時間短縮になります。
VBAとGASの違い
VBAとはVisual Basic for Applicationsの略でExcelやAccessなどで利用できるプログラミング言語の1つです。VBAを使うことで手動で行っている定型的な業務などを自動化させることができます。
GASとはGoogle Apps Scriptの略で、Googleが提供するサービスをクラウド上でJavaScriptをベースとしたスクリプトを実行することで操作できるサービスです。今回はスプレッドシート上で実行します。
VBAとGASの違いは実行環境が必要かどうか、そして実行できる場所が違うことがわかりますね。
他にもVBAは大文字小文字を意識せず、基本小文字入力で問題ありませんが、GASは大文字小文字を判別します。
また、VBAはExcel上など動作するため、PCの起動が必要ですが、GASはクラウド上で実行するのでPCを起動していなくても実行が可能です。
下記の表を参考にして下さい。
上記に加えて、Excelは有料アプリですが、GoogleAppsは無料で利用できます。
この点も大きな違いですね。
フォームの作成
それでは、それぞれの言語で「フォームを使って入力した内容をシートに転記する」ツールを作成してみます。
作成する前に共通の要件を定義していきます。
目的：科目ごとに学習したことを記録する簡易的な記録帳を作成したい。
処理内容
- フォームを起動して、入力を受け付ける。
- 送信ボタンが押されたら入力値をシートに転記する。
- 転記は新規行に行う。
作業手順
- フォームをデザインする。
- 処理内容を記述する。
- 表示形式を整える。
VBAを用いて簡単なフォームを作る
要件定義に沿ってまずはVBAでフォームを作っていきます。
最初は、簡単なフォームをデザインするところから始めましょう。
Excelを起動し、Ctrl+F11でVBEを立ち上げてください。
次に、挿入タブからユーザーフォームを追加します。
そして、ツールボックスからLabelとComboBox、TextBox、CommandButtonをドラック＆ドロップで配置してください。
さらに、表示される文字を変えたいので、プロパティウィンドウから「UserForm」、「Label」、「CommandButton」の各オブジェクトを選択して「Caption」の値を変更していきます。
【フォームの完成例】
これでフォームのデザインが完成しました。
次に送信ボタンが押された時の処理を記述していきます。
送信ボタンをダブルクリックして下さい。
開いたページの内容を一度すべて消してください。
そこに下記のコードを貼り付けます。
【UserForm1】
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
Option Explicit Private Sub UserForm_Initialize() ComboBox1.AddItem "数学" ComboBox1.AddItem "英語" ComboBox1.AddItem "国語" ComboBox1.AddItem "日本史" End Sub Private Sub CommandButton1_Click() Dim table As Range Dim newRec As Range Set table = Worksheets(1).Range("a1").CurrentRegion Set newRec = table.Offset(table.Rows.Count).Resize(1) With newRec .Cells(1, 1).Value = Format(Now, "m月d日 aaa曜日") .Cells(1, 2).Value = ComboBox1.Value .Cells(1, 3).Value = TextBox1.Text .Cells(1, 4).Value = TextBox2.Text End With End Sub |
ソースコードの解説（UserForm1）
2～7行目のUserForm_Initialize()ではコンボボックスの内容を追加しています。
この記述があることで、フォームの中で科目選択ができます。
9～11行目は新規行取得のテンプレートなのでそのまま覚えちゃえばOKです！
14～19行目で新規行の左のセルから右のセルへ「日付」、フォームの入力値である「科目名」、「学習内容」、「気づき」を順番に、転記しています。
マクロの実行（F5）で実行して確認してみましょう。
フォームにデータを入力して、送信ボタンを押してください。
Excelシートに転記できました。
GASとGoogleフォームを用いて簡単なフォームを作る
次にスプレッドシートに転記する場合を見ていきましょう。
まずはGoogleにログインして「Googleフォーム」を検索します。
「Googleフォームを使ってみる」を選択します。
新規に作成したいので、「空白」を選択して、画像のように項目を設定します。
作成出来たら右上のプレビューで実行してみます。
送信ボタンを押すと「回答を記録しました。」と表示されるので、Googleドライブから追加されたGoogleスプレッドシートを開きます。
このように自動的にフォームに紐づけされたスプレッドシートが作成され、その中にフォームを送信するたびに、新規行に転記されていきます。
しかし、タイムスタンプの形式が「2020/06/0310:28:11」のようになっています。
この列は自動で追加されるため、基本的に表示形式は後から手動で変更するしかありません。
そこでGoogle Apps Scriptを利用して、フォームから追記されるたびに自動で表示形式が変更されるようにしてみましょう。
スプレッドシートのタブからツールを選択します。
次にツールタブの中からスクリプトエディタを選択します。
下記を参考にコードを作成します。
【コード.gs】
|
1 2 3 4 5 6 7 8 9 |
function setNumberFormat() { var sheet = SpreadsheetApp.getActiveSheet() var range = sheet.getRange('A:A') range.setNumberFormat('m"月"d"日 "dddd') } |
setNumberFormat()の中で、アクティブなシートを取得して、A列に日付型のデータが入力された場合は「6月4日 木曜日」のような形式にするように指定しています。ソースコードの解説（コード.gs）
この関数をフォームが送信されるたびに呼び出す設定をすることで、求めるフォーマットで転記できます。
処理を自動で実行するためにトリガー（きっかけ）の設定をしていきます。
まずはスクリプトの上の方にある「時計のようなマーク」をクリックします。
次に「トリガーを追加」をクリックします。
トリガーの設定項目が表示されますので、順番に設定していきます。
実行する関数 ：setNumberFormat()
実行するデプロイ ：Head
イベントのソースを選択 ：スプレッドシートから
イベントの種類を選択 ：フォーム送信時
最後に「保存」をクリックしてください。
承認に関するダイアログが表示されることもあると思いますので、承認してあげてください。
これでトリガーの設置が完了です。
この状態で実行するとこんな感じになります。
ちゃんと形式が変わっていますね。
Excelに転記したのと同じ形で、スプレッドシートに転記することができました。
まとめ
VBA、GASでそれぞれフォームを作成して、シートに入力値を転記しました。
まずVBAを用いた方法は、フォームデザインの作成が慣れるまでは大変な印象でした。
コードは比較的シンプルなので、VBAの基礎をしっかり学習している方であれば問題ないでしょう。
次にGASを用いた方法は、Googleフォームの機能のおかげで、フォーム作成が直感的に行えたので、時間短縮できました。
表示形式の部分はGASで書く必要があったので、スプレッドシートのマクロ機能を活用して記述しました。
VBAはExcel上で、GASはスプレッドシート（その他Apps）上で動きます。
なのでExcelを使うならVBAの出番です。
そしてスプレッドシートなどでスクリプトを動かしたいならGASを使いましょう。
GASはトリガーを使えばスマホからもスクリプト実行できるのでとても興味深いです。
終わりに
この記事では、フォームを簡単に作れる「VBAとGoogle Apps Scriptのコードサンプル」をご紹介しました。
慣れてくると「もっとこっちの方がいいぞ！」と思える書き方が見つかると思うので、どんどん変えていってください。
ぜひ「自分なりのプログラムの書き方テンプレート」を磨き上げてみてください。
【参考】私がプログラムを書くときの順番
まずやりたいこととその内容を決める（構想）
↓
処理の内容をまずは日本語で箇条書きにする（プラン）
↓
フォームをデザインする
↓
プログラムコードを作成する（実行）
最後までお読み頂き、ありがとうございます。
![[VBA]日付から文字列への変換、文字列から日付への変換](https://www.kenschool.jp/blog/wp-content/uploads/2017/06/2342_thumb.png)
