イマドキのモダンVBA～VBAをGitソース管理しVBAからWebAPIを利用する～

この記事は マイナビ Advent Calendar 2020 の15日目の記事です。

はい！気づけば早いものでもう12月！またまたこの季節がやってまいりました。
今年は昔懐かしいExcelVBAと戯れることになったので、「VBAのシステムをいかにモダンな感じ¹にしてみたか」といった感じのことを記して今年を締めくくろうかと思います。

ちなみに今年のクリスマスは子供のリクエストでターキー丸鶏に挑戦することになり先日取り寄せをしてみました。
(ここで唐突にクリスマス感を )

要約

この記事の内容は、だいたい以下を見ればおしまいです。忙しい方はここだけ見ればOKです。

• ExcelVBAでGitによるソース管理をするには
  • vbacを使えばOK
    • ExcelからVBAソースだけ取り出せる。(ソースのエクスポート的な)
    • Excel内のVBAソースを一式差し替えられる(ソースのインポート的な)
• ExcelVBAでWebAPIを利用するには
  • Http(s)通信
    • ServerXMLHTTPを使えばOK。
      • 特にハマりはありませんでした
  • Jsonパース
    • VBA-JSONを使えばOK。
      • 特にハマりはありませんでした。
• Backendの話(VBAではなくてPythonやGCPのWeb周りなどなど)
  • Pythonの軽量Webフレームワークの１つであるFastAPIなかなか良かったです
    • Pythonのtype hintとPydanticの型情報からOpenAPIのドキュメントが自動生成できて便利
    • OpenAPIのドキュメント上でAPIの動作確認ができるのでテストも簡単
    • PydanticでJsonの構造体定義からValidationまで。非常に楽
  • GCPのCloud Runが安くて使いやすく便利でした
    • だいたい半月程度運用しましたが、ネットワーク通信費用の$0.14だけでした。(他は毎月の無料枠で収まった模様)
      • 重要度はそれなりにあってもアクセス数は少ないサービスなので、いい選択だったのではないかと
      • (こういうユースケースでは常時時間課金だと高くなると思う)
    • 自動デプロイも簡単に構成できそう
      • Dockerfile作るだけで、3回程度のコマンドで簡単にデプロイできる

システム構成

ExcelVBA(Visual Basic for Application)とは？

Excelで表計算だけでは表現できないような処理(例えば外部DBの検索処理など)をさせたい時に利用するマクロ言語になります。(最近の若い子は知らないかもな)
ちなみにVBAは、Excel以外のMicrosoftOffice関連ソフトでも利用できるようです。(私はやったことありませんが)

ExcelでVBAを利用する場合「開発」メニューの「Visual Basic」ボタンをクリックすると以下のスクリーンショットのようなIDE的なもので開発を進める形になります。それなりにはIntelliSenseが動いたりと、そこそこには快適な環境ではあります。²
Excelのデフォルト設定では「開発」メニューが隠されていたりするのでメニューを表示する設定が必要です。

なぜExcelVBAを利用したのか？

本システムはとあるデータの集計システムなのですが、元々5年くらい前にExcelVBAで作られていたようでVBAからAWSのRedshiftへ接続しクエリを実行してExcel上にデータを展開し集計結果をExcelシートに表示するようなシステムでした。(私は入社前なので詳しくは分かりません)
これを今年、とある事情でGCPのBigQueryを利用するようシステムリプレースすることになったため、再設計のチャンスとなりました。
せっかくの再設計のチャンスなので、ExcelVBAではなく別のツールにすることも一応可能ではあったものの、元のシステムのユーザとの接点(User Interface)がExcelであったためユーザ影響を少なくするため、UI部分はExcelで今までの操作感から大きくは変わらないようにすることやその他いくつかの事情³によりExcelを選択した、という経緯となります。
ユーザが日本全国にいるため、操作説明等にコストがかかることを避けたのも理由の1つです。

VBA開発・運用の辛さ

一般的なシステム(Webアプリ等)と比べ、ざっと辛い面を挙げますと大体以下のような感じでしょうか。

Git等によるソース管理できない

ソースファイルがExcelファイルの中に埋め込まれる形になりますので、Git等によるテキストベースでの差分管理が出来ません。
Excelファイル単位でのファイル名によるバージョン管理しか出来ないので、↓みたいな感じになります。
辛い！やりたくない！

ソース改修時のプログラムの配布がめんどくさい

VBA側のソース改修が発生した場合、修正版のExcelファイルを全国のユーザに配布し、ユーザにExcelファイルを差し替えてもらう必要があります。
これはとても大きな手間になりますし、ユーザがいつ差し替えてくれるか完全にはコントロール不能であり、旧バージョン利用を起因とした無用な問い合わせをうける可能性も考えられ、こんな状況正直しんどい。
辛い！やりたくない！

もう少し詳しく

では、簡単にもう少し細かいところをご紹介して、締めに行きたいと思います。
と言っても結構情報が多いので、参考サイトの紹介くらいになってしまいます。

VBACを使うには

それなりにたくさん情報があるので、以下のリンクなどを参考にして頂ければ特にハマりどころはないと思います。

• VBAソースコードをVBACを使ってGitで差分管理する方法
• VBAのソースを、Gitとかで管理する

VBACを導入すると晴れてVBAソースがExcelという呪縛から解き放たれ、好きな開発環境で開いたりGit管理したりできるようになります。
下記はVSCodeで開いた場合。これで少しモダンに近づいたのかもしれない。

ServerXMLHTTPを使うには

こちらもあまりハマりどころはないと思いますが、一つ注意なのは「ServerXMLHTTP」とは似て非なるモジュールで「MSXML2.XMLHTTP」というモジュールもあります。
→私は結局「ServerXMLHTTP」を利用しました。

判断理由は2つ。以下2つのサイトです。
明確な根拠とまでは行かない気もしますが、上位モジュールである旨の記述から問題ないのではと判断しています。

• 【ExcelVBA】HTTP/HTTPS通信でWebページを取得する
  • 「MSXML2.XMLHTTP」はTLS1.2非対応である旨の記述
• 本家マイクロソフトのマニュアルページ
  • 「ServerXMLHTTP」は「XMLHTTP」の下位互換性を保ちつつ、上位のモジュールである旨の記述あり

VBAからのAPI実行の実装の雰囲気としてはこんな感じです。

Public Function getXXX(ByVal xxx As Long, ByVal yyy As Long) As Dictionary
    Dim methodPath As String
    Dim params As Dictionary
    Dim response As String
    
    methodPath = "api/no/url/wo/shiteisuru"
    
    ' URLパラメータを生成
    Set params = New Dictionary
    Call params.Add("xxx", xxx)
    Call params.Add("yyy", yyy)
    
    ' APIを実行し、結果を取得
    response = doGet(methodPath, params)
    Set getXXX= JsonConverter.ParseJson(response)
End Function

' Getメソッドの実行
Private Function doGet(ByVal methodPath As String, ByRef params As Dictionary) As String
    doGet = httpRequest("GET", methodPath, params, "")
End Function

' Postメソッドの実行
Private Function doPost(ByVal methodPath As String, ByRef params As Dictionary, ByVal body As String) As String
    doPost = httpRequest("POST", methodPath, params, body)
End Function

Private Function httpRequest(method As String, ByVal methodPath As String, ByRef params As Dictionary, ByVal body As String) As String
    Dim http As Object
    Set http = CreateObject("MSXML2.ServerXMLHTTP")
    http.Open method, getFullUrl(methodPath, params), False
    
    Call http.setRequestHeader("Content-Type", "application/x-www-form-urlencoded")
    
    ' キャッシュクリア　参考：https://dampgblog.hinohikari291.com/cache-control/
    Call http.setRequestHeader("Pragma", "no-cache")
    Call http.setRequestHeader("Cache-Control", "no-cache")
    Call http.setRequestHeader("If-Modified-Since", "Thu, 01 Jun 1970 00:00:00 GMT")
    
    '認証トークンの指定
    Dim longToken As String
    longToken = getLongToken()
    If Len(longToken) > 0 Then
        Call http.setRequestHeader("XXXXXXX-token", longToken)
    End If
    
    ' http通信
    On Error GoTo HttpError
    http.send body
    On Error GoTo 0
    
    ' ステータスコードが何百番台かに丸めたもの
    Dim httpStatusSeries
    httpStatusSeries = (http.Status \ 100) * 100
    
    If httpStatusSeries = 400 Then
        '400番台はクライアント起因のエラー(基本的にはValidationエラー)
        Call clientErrorMessage(http.responseText)
    ElseIf httpStatusSeries = 500 Then
        '500番台はサーバ起因のエラー
        Call serverErrorMessage(http.responseText)
    Else
        httpRequest = http.responseText
    End If

    Exit Function
HttpError:
    MsgBox "通信中にエラーが発生しました"
End Function

VBA-JSONを使うには

以下のサイトを参考にすれば特に問題なく対応できるかと思います。

宇宙一わかりやすい？VBA-JSONを使ったJSONパースのしかた

FastAPIを採用してみた

やはりWebAPIを開発すると、API仕様(APIの入力パラメータと出力Json形式等)のドキュメントをどう書くか、メンテナンスするか、というのが気になります。(私はなりました。)
APIのドキュメントといえばOpenAPI(Swagger)ですが、ソースとAPI仕様の相互変換で結構手間になりがちです。
例えばAPIのパラメータが増えた時など、Swaggerドキュメントを修正して、Pythonコードも追従するように修正して…というような流れになります。（PythonのFlaskでconnexionというOpenAPI対応用のフレームワークではこんな流れになりがち)

一方、FastAPIではPythonコード中の関数にパラメータを追加するだけでOpenAPIドキュメントの修正は不要となるので簡単です！

イメージだけざっと説明したいと思います。

まず、Pythonで以下のようなAPI関数のコードを書く

@app.get('/aggregate_by_day', response_model=AggregateByAnyResponseModel[AggregateByDayViewRecord])
async def aggregate_by_day(
    xxx_year: int,
    yyy_year: int,
    x_token: str = Header(None)
) -> AggregateByAnyResponseModel[AggregateByDayViewRecord]:
    """日別集計の取得

    - Args:
        - **xxx_year (int)**: xxx年
        - **yyy_year (int)**: yyy年
        - **x_token (str, optional)**: 認証トークン

    - Returns:
        - AggregateByAnyResponseModel[AggregateByDayViewRecord]: 日別集計の結果データ
    """
    aggregate_query = AggregateQueryByDay(xxx_year, yyy_year)
    aggregate_data = aggregate_query.get_aggregate_data()
    return AggregateByAnyResponseModel[AggregateByDayViewRecord](
        display_infos=aggregate_query.get_display_infos(),
        aggregate_columns_count=aggregate_query.get_all_items_count(),
        results=aggregate_data,
    )

そしてFastAPIで実装したPythonコードをローカルPCで動かし、ブラウザで「http://localhost:8080/docs/」へアクセスすると、以下のようなAPIドキュメントがブラウザに表示される。もちろん「Try it out」でAPIの動作確認も可能。

Pythonの実行を止めて、パラメータを1つ追加(第3引数に「zzz_year: int」を追加)をして、もう1回起動をすると、見事にパラメータ追加完了となります。非常に簡単です！

上記の例では、GETメソッドなのでリクエストにBodyが指定できないため簡単なパラメータしか指定できませんが、
POST等ではリクエストにBodyが指定できるため、パラメータ部にもJSONのような構造体が指定できます。(面倒なので説明だけ)

また、コード中にある「response_model」というやつで、レスポンスのBodyに指定するJSON構造を定義しています。
これはPydanticというライブラリを利用した構造定義となっており、
Pythonのtype hintの情報を利用した構造体定義ができる代物です。
以下が少し詳しいかもしれません。

Pydantic 入門

今回のコード例でいうと、「AggregateByAnyResponseModel[AggregateByDayViewRecord]」が指定されています。
AggregateByAnyResponseModelは以下のようになっています。

class AggregateByAnyResponseModel(GenericModel, Generic[AGGREGATE_RESULT_TYPE]):
    """集計結果を返却する際のJsonの大枠の雛形をGenericsにて
    """
    # 動的項目の表示方法
    display_infos: List[AggregateColumnsDisplayInfo]
    # 集計項目(動的項目)の列数
    aggregate_columns_count: int
    # 各種結果セット
    results: List[AGGREGATE_RESULT_TYPE]

そして、「AggregateByDayViewRecord」はAggregateByAnyResponseModelのGenerics型による型指定となっており、
「AggregateByDayViewRecord」は以下のようになっていました。

class AggregateByDayViewRecord(BaseModel):
    """日別集計APIの集計結果レコード
    """
    xxx_year: int
    yyy_year: int
    dt_data: date
    aggregate_values: Optional[List[Optional[OrderedValue[Optional[Decimal]]]]]

上記のPydanticモデル定義により、上記OpenAPIのJson構造が定義されていたわけです。
このような形でPydanticの構造を利用して、JSONの入れ子構造などを簡単に定義することが出来ます。

gcpのcloud runを採用してみた

最後に、cloud runですが、もう力尽きました。
便利だった点など、箇条書きして終わります。

• Httpインターフェースを処理可能なDockerコンテナをデプロイすることで、コンテナのオートスケール等をフルマネージドで運用できるGCPのサービスです。
• SSL通信や証明書はcloud runで管理しているものを利用可能です。
• コンテナはリクエストが来た時に初めて起動し起動に時間がかかる(コールドスタート)。リクエストが定期的に来ていれば起動しっぱなしのようですが、暫くだんまりだと停止されます。(これは最小インスタンス数を0にした場合)
  • 最小インスタンス数を1以上にすると初回コンテナ起動の時間はかからなくなりますが、起動している間課金されるようになることに注意
• cloud runはリクエストが来るたびにコンテナが起動するので、最小インスタンス数が0であれば停止している間は課金0円です。
• 今回のユースケースでは1日に数十リクエスト程度だし、BigQueryへの集計処理であるためリクエスト1回1回のレイテンシをそこまで気にしないため最適な選択のような気がします。(処理時間は多少犠牲になっていますが)

あと、デプロイが簡単だったので、デプロイコマンドの１例をご紹介しておきます。
アプリのソースコードのルートディレクトリにいる状態を想定して、以下のコマンドのような感じです。
大体以下3コマンド程度でデプロイが完了します。

# デプロイ先のGCPプロジェクトIDを指定するコマンド
$ gcloud config set project <デプロイ先のプロジェクトID>

# cloud buildでリモートでのDockerビルド＆コンテナイメージをGCR(GoogleContainerRegistory)へアップロード
$ gcloud builds submit --tag gcr.io/<デプロイ先のプロジェクトID>/<アプリ名>

# コンテナレジストリからCloudRunへデプロイ(メモリ１GB、最大インスタンス数5、同時並行処理リクエスト数10の場合)
$ gcloud run deploy <アプリ名> --image gcr.io/<デプロイ先のプロジェクトID>/<アプリ名> --platform managed --memory=1Gi --service-account=<サービスアカウント名> --region=<リージョン> --max-instances=5 --concurrency=10

後もう一つ。今日(12/14)たまたま見かけた記事で怖い話がありました。

無料プランのはずがたった1日の利用で750万円をGoogleから請求された企業が破産寸前に

cloud runのデフォルト設定では、最大インスタンス数1000になっているので、これは下げたほうが良いことが多いような。
cloud runの設定もそうですが、この記事ではそもそものバグもあるようなので、バグを起因とした大量課金にも注意が必要かと思います。
対岸の火事ではありませんね。

おわりに

いかがでしたでしょうか。
VBAといえでもGitでソース管理できるし、細かいビジネスロジックはサーバサイドに逃したりすることでプログラム配布を楽にしたりと、
多少はモダンな環境にできたのではないでしょうか。
でも最善の解は業務の整理をちゃんとして 脱Excel をすることだったりすることは言うまでもありません。
おしまい

1. なにをもってモダンとするかはあんまりよくわからないので、この点については触れないでくださいね・・・ ↩

2. とはいえRedoのショートカットキーがなかったり、かなり古い開発環境なのでVSCode等の最近の環境からは様々な面で劣り今となっては決して快適な環境ではありません。 ↩

3. 他に、別のExcelファイルに部分的に集計結果を転記できるようにする要件があり、それを実現するためにはExcelかスプレッドシート以外では難易度が高そう。などなど、もう少し実際は状況が複雑ですが、本題から外れるので詳細は割愛します。(どの理由も最終的には業務自体を最適化できれば脱Excelできそうではありますが、Excelが非常に便利なだけに業務に密結合してきてしまうんですよね) ↩