【VBA】自動更新する座席表を作ろうとした件について

こんにちは、宮﨑です。

今回はタイトルにもある通り、VBAで自動更新する座席表を作ろうとして頓挫した件についてお話ししようと思います。

※タイトルにもある通り、こちらは作ろうとして頓挫した記事です。完成しませんでしたが、VBAの参考にはなると思います。

なんで作ろうと思ったの？
座席表を作る
図形を入れる
座席リストを作る
VBAのコードを書く
なぜ頓挫したのか
最後に

なんで作ろうと思ったの？

インプルはリモートワーク・出勤どちらもOKかつオフィスがフリーアドレスの会社です。

そのため

用事のある人がどこにいるかわからない
そもそも出社しているかすらわからない
ふらっと出社した時に席の空きが全然あるかわからない

という問題点がありました

そこを解決しようと社内チャットツールであるTeamsとExcelをうまいこと連携させてリアルタイム座席管理できないかなと思いました。

座席表を作る

まずexcelを立ち上げて罫線を使って簡単な座席表を作っていきます。

作りました(こちら架空の座席表に変更しております)

そしてそれぞれに番号を割り振っていきます(A1,A2,A3……)

座席レイアウトによってはここが一番時間がかかります。

図形を入れる

こちらに図形を入れていきます。

図形の色や文字色についてはお好きに設定してください。

そして、座席番号に対応する図形の名前をその座席番号にします。

オブジェクトの選択と表示がぐちゃぐちゃになっているのは私は仕様でした……

綺麗に直しても次開いたときにはまたぐちゃぐちゃだったので諦めました。

座席リストを作る

別シートに座席リストを作成します。

色に関してこちらもお好みでどうぞ。

VBAのコードを書く

今回私が書いたVBAコードは下記のようになります。

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Row >= 3 And Target.Row <= 59 Then
        If Target.Column = 3 Then
            Dim target_address As String
            Dim target_values As String
            
            target_address = Cells(Target.Row, 2).Value
            target_values = Target.Value
            Sheets("座席表").Select
            If target_values = "" Then
                ActiveSheet.Shapes(target_address).Visible = False
            Else
                ActiveSheet.Shapes(target_address).Visible = True
                ActiveSheet.Shapes(target_address).TextFrame2.TextRange.Text = target_values
            End If
        End If
    End If
    ActiveSheet.Cells(1, 1).Select
End Sub

こちらを座席リストのシートを対象に記述しています。

こちらのコードを簡単に解説すると

Worksheet_Changeを設定する(こちらを設定することでワークシートまたは特定範囲に対して特定の操作が行われた時にマクロが実行される。)
座席リストの名前行を特定範囲(３列目の3行目から59行目)に設定する。
target_addressとtarget_valueを宣言する。
target_addressに更新された行の座席番号の値を格納する。
target_valueに更新された行の名前の値を格納する。
if文を使いtarget_valueが空白(“”)の場合、対応する図形(ActiveSheet.Shapes(target_address))のVisibleをFalseとする。
それ以外の場合は対応する図形のVisibleをTrueとして図形内にTextFrame2.TextRange.Textを用いてtarget_valueの文字列を入れる。

となっております。

こちらで

C1に宮﨑と入力すると

C1に図形と名前が表示されるようになりました。

実質作成時間は

座席表作成　1.5時間

コード作成　2.5時間

となっております。

これでTeamのPowerAutomateを用いて、座席表をメンションにし本文に座席番号を入力して投稿すると、メッセージから送信者と座席番号、送信時間を取得し座席リストに反映。マクロがその変更を感知して座席表に反映されるという仕組みを考えていました……この時までは…………

なぜ頓挫したのか

team内でエクセルを開いた場合、アプリケーションで開かないとマクロが起動しない。
PowerAutomateに組み込むためには、Excel Onlineでなければならない。
Excel Onlineではマクロの作成、実行、編集を行うことができない。

…………以上の理由からマクロそのものが使えなかったため頓挫となりました。

最後に

事前調査不足とマクロを頼りすぎたことが圧倒的敗因となります。みなさんも事前調査は丁寧にやりましょう。

それでも今回、VBAでイベントプロシージャーを用いて実装できたこと、少しでもPowerAutomateに触れられたことは収穫かなと思います。

それでは次回、「宮﨑、スクリプトでリベンジ」でお会いしましょう。