ExcelVBAでフォームを利用した座席管理システムぽいものを開発してみた
【目次】
ビジュアルベーシックアプリケーションという名前は知ってました
緊急事態宣言中のある日のこと
ある日、「これからは在宅勤務率も増えるので、オフィスの固定座席の一部をフリーアドレスに開放しよう！」というお達しがあり、ありがちですが、まずはどこがフリーアドレスか分かるようにExcelで座席表を作りました。
しかし、それだけでは誰がどこに座ったのかまでは逐一管理できないため、毎日、誰がどこに座るかを見える化しよう、という話になりました。
その結果、プリントアウトした座席表の上に、社員名の書かれたマグネットを貼っていく、という超アナログな運用が始まりました。
そして、毎日の終業時にそのマグネットの情報をExcelに転記保存して、元の場所にマグネットを戻すリセット作業を行うことになりま・・し・・・た・・・。
・・・面倒くさすぎる！
というわけで、ITでなんとかしたいと思ったのが発端です。
ど素人がVBAを何も知らない状態からどこまで開発できるのでしょうか。
いきなり使い方がわからない
まず、私のスキルレベルですが、もちろん、Excelは普段使いますが、それこそ人並み（？）レベルでハードに使ったことはなく、がんばってもせいぜいVLOOKUPどまり。
複雑なマクロなんて組んだことはありません。
元の座席表がEXCELなので、なんとなく、別ウインドウで開くフォームでから入力した「名前」と「座席情報」から、座席表Excelに名前が記入されるようなものを妄想してみました。
「Excel フォーム」で検索すると、VBAというのを使うのが良さそうです。
開発モードにはデフォルトではなっていない
しかし、いきなり開発のための画面の立ち上げ方がわかりません。
早速調べますと、Excelのバージョンにもよるみたいなのですが、最初にオプションから、開発のチェックボックスをONにする必要があるようです。
これで開発というタブが追加されました。
エディタの起動
さて、これで開発できるようになりましたが、次はどうやってプログラムを作るのでしょうか？
プログラミングといえばエディタですね。「開発」タブから「Visual Basic」をクリックで、エディターのようなものが起動しました。
標準モジュールの追加
まずは、最初にモジュールというものを追加するようです。
いきなり難しそうで心が折れそうですが、初級者ページを見ながら進めてみます。
今回、作りたいものがフォームだったので、すぐ上にあった「ユーザーフォーム」というボタンに誘惑されましたが、初心者なので基本に沿って、標準モジュールを追加してみます。
ここにプログラムを書いて、上にある再生ボタン「|＞」か、「F5」を押すとプログラムが実行されます。
初心者なので、とても簡単な、「実行するとA1セルに数字が入る」プログラムなどで慣れていきました。
Sub テスト()
Range("A1").Value = 100
End Sub
以前に少しだけGoogleActionScript(GAS)を書いたことがあったので、それを思い出しました。
ものとしては近い感じがしますが、GASはネットワークや他のアプリとの連携が前提で、VBAはEXCEL上での操作が前提のように感じました。
フォームの作成
とりあえず、簡単なセルの操作は理解したので、とっとと作りたいフォームの作成に取り掛かります。
先程のユーザーフォームを選びます。
なんとなく、見慣れた画面が出てきました。
ここでボタンやプルダウンメニューなどを組み立てていくようです。
デザインするのは初心者でも直感的でわかりやすいですね。
でも、これと連動するプログラムソースはどこに書くんだろう？といきなり悩みました。
作成したフォームが「UserForm1」という名前になっているので、それをプロジェクトというウィンドウ内から探して、右クリック＞コードの表示で、プログラムの入力画面が出てきました。
もしも、間違えてユーザーフォームを追加してしまっていらなくなったUserFormは、削除ではなく解放というらしく、これまた最初、気づかずググりました。いちいちgoogle先生頼みです。万歳。
フォームをデザイン
とりあえず、初心者なので深く考えずにデザインしてみます。
コントロールから、文字、コンボボックス（ドロップダウン、プルダウンメニュー？）、コマンドボタンを追加して、名称を変更してみました。
あるセルの一覧をコンボボックス内に表示
部門名や従業員名の一覧を、座席表とは別のシート内に保存しておき、そこからプルダウンメニュー内に取り込みたいと考え、そこを作ってみました。
やり方は色々あるようですが、最初はこんな感じで作ってみました。
コメントの付け方
その過程でプログラムの可読性をあげるために、コメントをつけました。「’」の後ろがコメントになるようです。
Private Sub CommandButton1_click() '座席情報登録ルーチン
Excel VBAでコメントを記述する方法 | UX MILK :
セルの最終行を取得する
コンボボックスへ社員一覧や座席一覧を取り込む際に、情報が入っている最終行まで取り込む必要があるため、それを取得する方法があるようです。
Office TANAKA – Excel VBA Tips[入力されているデータの最終セルを取得する] :
『RANGE()の変数入り』（初心者） エクセル Excel [エクセルの学校] :
複数のコンボボックスにおいてリストから要素を指定してあるかを論理和でチェックする
座席に名前を登録するには、「座席の位置」と「名前」の両方の要素が指定されている必要があるので、論理和（OR）を利用しました。
どちらかが指定されていないと、エラーメッセージがでるように、MsgBoxも利用しています。
VBAでAndやOrの論理式を使う | ノンプログラミングWebアプリ作成ツール – Forguncy（フォーガンシー）| グレープシティ株式会社 :
Dim ListNo1 As Long
Dim ListNo2 As Long
ListNo1 = ComboBox1.ListIndex
ListNo2 = ComboBox2.ListIndex
If (ListNo1 < 0 Or ListNo2 < 0) Then
MsgBox "名前と座席を選択してください" '名前と座席の両方が指定してあるかチェック
Exit Sub
End If2つのコンボボックスの要素を連動する
とにかく全社員をずらっと並べても良いのですが、所属している部門（部・課）がそれぞれあるので、部門を選択すると、連動して、そこに所属されている社員だけに絞り込まれるようにしたいと思いました。
そこで、下記のサンプルコードを元に作成してみました。
都道府県から、市区町村を絞り込む例
１、Listという名前のシートを準備
２、A列には県名（１行目から任意の行まで）を記入
３、B列にはA列１行目の県の市町村名（１行目から任意の行まで）を記入
４、C列にはA列２行目の県の市町村名
５、以下同様
６、ユーザーフォームにコンボボックスを２つ作成し、下記のコードを貼り付け
Private Sub UserForm_Initialize()
Worksheets("List").Select
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
ComboBox1.AddItem Cells(i, 1).Value
Next i
End Sub
Private Sub ComboBox1_Change()
ComboBox2.Clear
Worksheets("List").Select
myCol = ComboBox1.ListIndex + 2
For i = 1 To Cells(Rows.Count, myCol).End(xlUp).Row
ComboBox2.AddItem Cells(i, myCol).Value
Next
End Sub
座席番号からセルの情報を取り出す
座席番号は「2-2」「4-6」とかなのですが、名前を書き込むところは「A4」「L10」などのセルの番号になっているため、座席情報のシートには２列にして情報を格納したのですが、コンボボックスには、座席番号を表示し、名前を書き込むときは、セル情報を取り出す方法がうまくわかりませんでした。
結論、VLOOKUPを使うことで解決しました。VBAにもあるんですね。
VLOOKUP関数を使ってセルの値を取得する：Excel VBA｜即効テクニック｜Excel VBAを学ぶならmoug :
フォームの終了ボタンをつける
フォームを終了するボタンは必須ではないのですが、動作テストのたびに停止の「■」ボタンを押すのが面倒だったので、練習がてらつけてみました。
Private Sub CommandButton3_Click() 'とじるルーチン
Unload UserForm1
End Sub
後から気づいたのですが、このフォームを閉じないとExcelも終了できないので、とじるを押すとあわせてExcelも終了するとよいのでしょうか。
Excel起動時に自動で登録フォームを開く
これも、実際に運用するときをイメージして、やり方を調べました。
Excel VBAブックを開いたときにユーザーフォームを表示する :
Private Sub Workbook_Open()UserForm1.ShowEnd Sub
ワークシートからボタンでフォームを呼び出す
自動でフォームを呼び出してますが、間違えて閉じたときのために、とりあえずどうやるのかだけ、調べてみました。
【エクセルVBA】Showメソッドでユーザーフォームをコマンドボタンから開いてみよう！ :
保存時に毎回出るアラートを消す
プログラムの保存の度に、謎の鬱陶しい警告文が表示されるのをOFFにする方法です。
Excel – 解決！ドキュメント検査機能では削除できない を消す方法 | かとう中小企業診断士事務所 :
シートを隠す（非表示のシートは操作できないので注意）
社員リストや座席リストのシートは、ユーザに見える必要はないので、非表示にするようにしました。
非表示にしたシートは操作ができない仕様のため、操作時は表示して、その後非表示にしましたが、もう少しスマートな方法があるように思いました。
セルの上書きチェックポップアップ
すでに別の誰かが登録した名前が入っているセルに、間違えて上書きしてしまうケースがあるということで、すでに名前が入ってる場合は、上書きできないようにして、別途、一度クリアをしてから、（クリアしてよいかの確認をする）、入力してもらうように実装しました。
Office TANAKA – Excel VBA関数[MsgBox] :
MsgBox関数の使い方（2） – ユーザーが選択したボタンの処理：Excel VBA｜即効テクニック｜Excel VBAを学ぶならmoug :
フォームのサイズを固定で指定
原因がわからないのですが起動する度にUserform1のサイズがどんどん小さくなる？現象に見舞われました。
完全に直っていない感じもしますが、固定サイズでフォームを表示するようにして対処しました。
Excel VBA を学ぶなら moug モーグ | 即効テクニック | ユーザーフォームの表示位置とサイズを指定する :
マクロからしかセルを更新できないようにする
座席入力フォームをずっと起動させっぱなしだと画面が小さい場合、座席表のセルが見えづらく、そこを操作するにはフォームを終了させないといけない。フォームを終了すると、セルに直接書き込めてしまうので、そもそもこのプログラムの意味がなくなってしまうので、フォームからしか更新できないようにしてみました。
【エクセルVBA】保護しているシートでマクロの操作だけ有効にする方法 :
Microsoft flow による、ファイルの自動バックアップ
いくつか自動化したいことがあったのですが、試行錯誤の末に、更新されたファイルを６時間おきに別名で保存しておく、というのを、Microsoft flowで実現できました。
これだけいまや、RPAなど自動化がブームなのに、このflowは、恐ろしく使いにくく、あんまり流行っていないのでもったいないなあ、と思いました。IFTTくらいわかりやすくするのは難しかったんでしょうね。
追記：Onedrive上にファイルをおいておけば自動で更新履歴が作られ続けるので、そのファイル自体を削除されなければ、GoogleSuiteと同様に、過去のバージョンを取り出せました。
完成
まだまだ、改善すべきところはありますが、一旦、こんなものかなということで。
気が向いたらまた書きますw
残った課題
●毎日、利用した座席をリセットしたい。例えば、MicrosoftFlowで、定時にマクロを実行、もしくは特定のセルを埋めるという方法で実現したい。
●One drive上においていあるExcelファイルをブラウザーで開くとマクロを実行できない。アプリで開くともちろん実行できるが、毎日座席登録の度にこれを行うのはやや煩雑なため、例えばデスクトップショートカットからアプリ版が起動するようにしたい。
参考にしたサイト
Excelに入力フォームを作成、コントロールを追加、表示、ボタンでイベント実行 (3/3)：Excelマクロ／VBAで始める業務自動化プログラミング入門（12） – ＠IT :
Excel VBA コンボボックスにリストを設定する 選択された値を取得する :
Office TANAKA – 今さら聞けないVBA[Withって何ですか？] :
ユーザーフォームの利用 | エクセル職人 : レベルが高すぎて参考にできず・・・