メインコンテンツへスキップ
見出し画像

VBA基礎５：VS CODEでVBAを編集（ariawase）

    KIYO

    １．概要

    　普段業務効率化のためにExcelのVBAを使用することが多いですが①VBEが使いにくい、②GitHub Copilotを購入しているため有効活用したい　という理由でVS CODEを使用したいと思っております。
    　下記記事を参考にしながら、VS CODEでもVBAを編集できるようにしたいと思います。

    　なお別手法のXVBAは別記事で紹介しています。。

    ２．環境構築

    　環境構築として大きく分けて下記があります。

    1. Excelから標準モジュール（所謂VBAの中身）を抽出したり、編集後のファイルを統合してくれるスクリプトをダウンロード

    2. VS CODEに必要なExtensionを追加して設定の追加

    3. Excelとbinフォルダの準備

    　なお本当はgit(Version管理システム：変更箇所の差分を表示してくれるツール)を使った方が良いのですが、まだ勉強不足のため今回はgit無しで作成しました。

    　なお初期設定としてExcelの「開発者向けマクロの設定：VBAプロジェクトオブジェクトモデルへのアクセスを信頼する」はチェック済みだとします。チェックされていない方は下記フローより事前にチェックが必要です。

    画像

    ２－１．必要ファイルを取得：vbac.wsf

    　GitHubにExcelからVBAを抽出・結合できるスクリプトが公開しておりますのでこちらを使用していきます。手順は下記の通りです。

    1. GitHubよりCODEをダウンロード（手動でも問題なし）。”ariawase”フォルダが作成させるのを確認する。

    2. ”ariawase”フォルダから”vbac.wsf”を作業フォルダに移動させる

    3. ”ariawase”フォルダは削除

    [Terminal]
git clone https://github.com/vbaidiot/ariawase.git
    画像

    【参考：処理画面】

    画像

    【コラム：コマンドプロンプト（CMD）のショートカット】
    　最近知りましたがエクスプローラーに”cmd”と入力すると、開いているエクスプローラーのパスでCMDが立ち上がります。よって「Ctrl+L->cmd」と入力すると一瞬で作業パスでのCDMを開けます。

    ２－２．VS CODEの設定

    　VS CODEでVBAを扱うための設定をしていきます。

    　２－２－１．Extensionの追加

    　Extensionとして「VBA」と「vba-snippets」を追加します。
    （※VSCode VBAとvscode-vba-iconsは2023年1月では非推奨でありVBAが推奨されています）

    【VBA：VBA構文のハイライト】

    画像

    【vba-snippets：VBAを書くための自動補完機能を追加】

    画像
    画像

    　２－２－２．設定の追加："*.dcm":"vba"

    　Excel用のVBAの拡張子は複数あり下記があります。（ちなみに、私は普段は標準モジュール内でしか作成しておりません。）

    • bas（モジュール）：標準モジュールで作成したVBA

    • cls（クラスファイル）：クラスモジュールで作成したVBA

    • dcm（?）：シート内で作成したVBA

    　dcmはマクロファイルとの関連付けがされていないらしいので、VS CODEの設定で関連付けの条件を追加します。

    　まず初めに「歯車マーク->setting(設定)->Open Settings(JSON)」で設定ファイルを開きます。

    画像

    　次に"settings.json"に下記を追加して上書き保存すれば完了です。

    [settings.json]
    "files.associations": {
            "*.dcm":"vba",
    }
    画像

    ２－３．Excelとbinフォルダの準備

    　"bin"フォルダを”vbac.wsf”と同じフォルダに作成して、binフォルダにExcelマクロファイル.xlsmを入れます。

    画像

    　なおExcelには事前にモジュール（VBAコードを書くための土台）が必要なため適当なモジュールを作成しました。

    画像

    ３．VS CODEでVBA編集

    　本章で使用したファイル情報は下記の通りです。

    • Excelファイル名：VBA_VSCODE.xlsm

    • モジュール名：Module1

    • VBAのプロシージャ名：最初の状態()

    ３－１．標準モジュール（VBA）の抽出

    　Excelからコードが記載されたモジュールを抽出します。処理は”vbac.wsf”ファイルがあるディレクトリで”cscript vbac.wsf decombine”を実行するだけです。
    　完了後は”src”フォルダ内にVBAファイル(bas, cls, dcm)が生成されます。

    [Terminal]
cscript vbac.wsf decombine

[OUT]
begin decombine

> Target: VBA_VSCODE.xlsm
- Export: Module1.bas

end
    画像

    ３－２．VS CODEで編集

    　VS CODEの編集はsrcフォルダ内に生成されたVBAコードのファイルをVS CODEで直接開きます。なお日本語で文字化けが発生している場合は文字コードをUTF-8からSHIFT-JISに変更すれば直ると思います。

    画像

    　それではサクッとFizzBuzz関数を作成してみます。

    [basファイル（VBAコード）]
Sub FizzBuzz()
    Dim i As Integer

    For i = 1 To 100
        If i Mod 15 = 0 Then
            Debug.Print "FizzBuzz"
        ElseIf i Mod 3 = 0 Then
            Debug.Print "Fizz"
        ElseIf i Mod 5 = 0 Then
            Debug.Print "Buzz"
        Else
            Debug.Print i
        End If
    Next i
End Sub
    画像

    ４．編集後VBAの統合

    　最後に編集したVBAコードをExcelへ戻します。処理は３－１節と同様であり、”vbac.wsf”ファイルがあるディレクトリで”cscript vbac.wsf combine”を実行するだけです。

    [Terminal]
cscript vbac.wsf combine
    画像

    　binに保管してあるExcelファイルを見ると修正したコードが反映されていることを確認できました。

    画像

    ５．応用編：VBAコードをスクラッチで追加

    　３章でExcelからVBAのモジュール.binを抽出して編集しましたが、１から作成することも可能でした。参考までに手順を記載します。

    ５－１．srcにVBAコード(bas)を追加

    　”vbac.wsf”ファイルと同じディレクトリに「src/Excelファイル名/VBAファイル」が格納されております。標準モジュールとして作成するために、このフォルダ内に拡張子basのファイルを作成しました。注意点は下記の通り。

    • 名前は①basのファイル名、②ファイル内の「Attribute VB_Name = <VBA名>」があるがExcelに反映されるのはAttribute VB_Nameの方である。基本的には統一しておくと余計な心配はないと思う。

    • 編集が終了したら統合処理を実施前に処理するExcelファイルは閉じておく必要がある（basファイルは開いていても問題なかったがExcelの方は結果が反映されなくなります）。

    画像
    [Test1.bas]
Attribute VB_Name = "Test1"

'File Sytem Objectのデータ取得関数
Function GetFSO(keyNo As Integer) As Variant 
    '型宣言
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Dim workpath As String 'ワークブックのパスを格納する変数

    '変数初期化
    workpath = ThisWorkbook.Path & "\" & ThisWorkbook.Name 'ワークブックのパス

    'keyNoによって取得するデータを変更
    If keyNo = 1 Then
        GetFSO = fso.GetBaseName(workpath) 'ワークブックのファイル名
    ElseIf keyNo = 2 Then
        GetFSO = fso.GetParentFolderName(workpath) 'ワークブックのフォルダ名
    ElseIf keyNo = 3 Then
        GetFSO = fso.GetAbsolutePathName(workpath) 'ワークブックの絶対パス
    ElseIf keyNo = 4 Then
        GetFSO = fso.GetDriveName(workpath) 'ワークブックのドライブ名
    ElseIf keyNo = 5 Then
        GetFSO = fso.GetExtensionName(workpath) 'ワークブックの拡張子
    ElseIf keyNo = 6 Then
        GetFSO = fso.GetFile(workpath) 'ワークブックのファイルオブジェクト
    ElseIf keyNo = 7 Then
        GetFSO = fso.GetFolder(workpath) 'ワークブックのフォルダオブジェクト
    ElseIf keyNo = 8 Then
        GetFSO = fso.GetSpecialFolder(0) 'Windowsのデスクトップフォルダ
    ElseIf keyNo = 9 Then
        GetFSO = fso.GetSpecialFolder(1) 'Windowsのプログラムフォルダ
    ElseIf keyNo = 10 Then
        GetFSO = fso.GetSpecialFolder(2) 'Windowsのコントロールパネルフォルダ
    ElseIf keyNo = 11 Then
        GetFSO = fso.GetSpecialFolder(3) 'Windowsのプリンタフォルダ
    ElseIf keyNo = 12 Then
        GetFSO = fso.GetSpecialFolder(4) 'Windowsのマイドキュメントフォルダ
    End If

End Function

    ５－２．VBAコードの統合

    　統合は４章と全く同じ処理で対応可能です。

    [Terminal]
cscript vbac.wsf combine

    　結果は下記の通りであり標準モジュール直下に新しい"Test1"モジュールが追加されており問題なく使用できております。

    画像

    参考資料・参考記事

    あとがき

    　本当はExcelを直接読み込めればシート名もGitHub Copilotが読み込んでくれるはずだろうけど、もう一個の方でも後で試したい。


     
     

    KIYO

     
     
    普段は製造業で企画/開発/設計しております。記事はプログラミング・機械学習、IoT関係の記事をメインで作成し、なるべく１つの記事で知りたいことを網羅していきます。内容は学術的より実装・アウトプット（ほしくなるもの）を重視して作成しています。 面白そうな仕事があればやりたいです！

    あなたへのおすすめ