🎢

AIコードエディタでVBAを書く枠組みを作った話

に公開
VBA
Cursor
tech

AIコードエディタでVBAを書く枠組みを作った話

1. はじめに

Excel VBA を使った業務ツールを作る際、AIコードエディタで記述したいが、Excel外でコードを管理し、Excelに反映させるプロセスが煩雑である。
実直に実施するのであれば、

  1. 新規モジュールを作成する。
  2. モジュールをbasファイルとしてエクスポートする。
  3. コードを記述する。
  4. コードをインポートする。

といった手順を踏む必要がある。
また、コードをインポートするときも、もともと「Module」というモジュールがあった場合は、「Module1」といった名前で取り込まれてしまうため、一度「Module」というモジュールを削除（解放）した後、「Module.bas」をインポートする必要がある。
そのため、

  • VBAモジュールの最新版を反映したい（なるべく手順少なく）
  • 手作業でのコピペは事故が起きやすい
  • 反映前にバックアップを必ず取りたい

といった課題を解決するため、今回は VBS（Windows Script Host）を使って
.bas ファイル群を .xlsm に安全に一括反映する仕組みを作った。

2. やりたかったこと

  • .vbs をダブルクリックするだけで実行
  • 指定フォルダの .bas を xlsm に反映
  • 既存の同名モジュールは削除して置き換え
  • 実行前に必ずバックアップを取得
  • 成功／失敗をダイアログで通知

3. 設計方針

採用した考え方

  • Excelを裏で起動する
  • 人の操作を一切介さない
  • 失敗しても元に戻せる構成にする

そのために、

  • FileSystemObject によるファイル操作
  • Excel COM オブジェクト操作
  • backup フォルダによる世代管理

を組み合わせた。

4. フォルダ構成

ProjectRoot
│  Sample.xlsm
│
├─ bas
│   ├─ ModuleA.bas
│   ├─ ModuleB.bas
│
└─ backup
    ├─ Sample_20260112_003015_backup.xlsm

5.実装のポイント

5.1 VBS から Excel を操作する

Set xlApp = CreateObject("Excel.Application")
Set wb = xlApp.Workbooks.Open(excelFilePath)

VBAプロジェクトを操作するため、
「VBA プロジェクト オブジェクト モデルへのアクセスを信頼する」
を有効にする必要がある。

5.2 バックアップを必ず取る

処理前に xlsm をコピーし、日時付きで保存する。

fso.CopyFile excelFilePath, backupPath

これにより、万が一モジュール反映に失敗しても復旧できる。

5.3 モジュールの置き換え方法

  • bas ファイル名をモジュール名として扱う
  • 同名モジュールがあれば削除
  • Import 後に Name を明示的に設定
Set importedComp = wb.VBProject.VBComponents.Import(file.Path)
importedComp.Name = moduleName

これによりModule1問題を回避できる。

6. 完成した VBS スクリプト

Option Explicit

'=========================
' 設定ここから
'=========================
' 対象のExcelマクロ有効ブック
Dim excelFilePath
excelFilePath = "D:\Work\VBA\Sample.xlsm"

Dim basFolderPath
basFolderPath = "D:\Work\VBA\bas"
'=========================
' 設定ここまで
'=========================

Dim xlApp, wb, fso, folder, file
Dim importedCount
importedCount = 0

Set fso = CreateObject("Scripting.FileSystemObject")

'=========================
' バックアップフォルダ準備
'=========================
If Not fso.FileExists(excelFilePath) Then
    MsgBox "Excelファイルが存在しません。" & vbCrLf & excelFilePath, vbCritical
    WScript.Quit
End If

Dim parentFolder, backupFolder
parentFolder = fso.GetParentFolderName(excelFilePath)
backupFolder = parentFolder & "\backup"

If Not fso.FolderExists(backupFolder) Then
    On Error Resume Next
    fso.CreateFolder backupFolder
    If Err.Number <> 0 Then
        MsgBox "backupフォルダを作成できません。" & vbCrLf & Err.Description, vbCritical
        WScript.Quit
    End If
    On Error GoTo 0
End If

'=========================
' バックアップ作成
'=========================
Dim backupPath, baseName, ext, timeStamp
baseName = fso.GetBaseName(excelFilePath)
ext = fso.GetExtensionName(excelFilePath)

timeStamp = Year(Now) & _
            Right("0" & Month(Now), 2) & _
            Right("0" & Day(Now), 2) & "_" & _
            Right("0" & Hour(Now), 2) & _
            Right("0" & Minute(Now), 2) & _
            Right("0" & Second(Now), 2)

backupPath = backupFolder & "\" & baseName & "_" & timeStamp & "_backup." & ext

On Error Resume Next
fso.CopyFile excelFilePath, backupPath, False
If Err.Number <> 0 Then
    MsgBox "バックアップ作成に失敗しました。" & vbCrLf & Err.Description, vbCritical
    WScript.Quit
End If
On Error GoTo 0

'=========================
' Excel起動
'=========================
On Error Resume Next
Set xlApp = CreateObject("Excel.Application")
If Err.Number <> 0 Then
    MsgBox "Excelを起動できません。" & vbCrLf & Err.Description, vbCritical
    WScript.Quit
End If
On Error GoTo 0

xlApp.Visible = False
xlApp.DisplayAlerts = False

' ブックを開く
On Error Resume Next
Set wb = xlApp.Workbooks.Open(excelFilePath)
If Err.Number <> 0 Then
    MsgBox "Excelファイルを開けません。" & vbCrLf & Err.Description, vbCritical
    xlApp.Quit
    WScript.Quit
End If
On Error GoTo 0

'=========================
' .bas フォルダ確認
'=========================
If Not fso.FolderExists(basFolderPath) Then
    MsgBox ".basフォルダが存在しません。" & vbCrLf & basFolderPath, vbCritical
    wb.Close False
    xlApp.Quit
    WScript.Quit
End If

Set folder = fso.GetFolder(basFolderPath)

'=========================
' モジュール置き換え
'=========================
For Each file In folder.Files
    If LCase(fso.GetExtensionName(file.Name)) = "bas" Then
        
        Dim moduleName, comp, importedComp
        moduleName = fso.GetBaseName(file.Name)

        ' 同名モジュール削除
        For Each comp In wb.VBProject.VBComponents
            If comp.Name = moduleName Then
                wb.VBProject.VBComponents.Remove comp
                Exit For
            End If
        Next

        ' インポート
        Set importedComp = wb.VBProject.VBComponents.Import(file.Path)

        ' ★ モジュール名を bas ファイル名に強制変更
        importedComp.Name = moduleName

        importedCount = importedCount + 1
    End If
Next


'=========================
' 保存・終了
'=========================
wb.Save
wb.Close False
xlApp.Quit

MsgBox "処理が完了しました。" & vbCrLf & _
       "バックアップ保存先：" & vbCrLf & backupPath & vbCrLf & vbCrLf & _
       "インポートしたモジュール数：" & importedCount, vbInformation

' 後始末
Set folder = Nothing
Set fso = Nothing
Set wb = Nothing
Set xlApp = Nothing

7. ハマりどころ・注意点

  • bas ファイルは Shift-JIS で保存する
  • UTF-8 だと日本語コメントが文字化けする
  • Excel 側のセキュリティ設定が必須
  • 実行中に xlsm を開いてはいけない

8.実行確認

空のエクセルを用意。


モジュールが何も登録されていない。

フォルダにbasファイルを配置。

スクリプトを実行。

実行結果をメッセージ表示

⇒反映されたことを確認。


文字化けもなし。

「backup」フォルダも自動作成され、ファイルが退避されています。

9.まとめ

VBS を使うことで、以下を実現しました。

  • VBA資産をコードとして管理できる
    (変更管理ができる)
  • 手作業による事故を防げる
  • 配布・更新が非常に楽になる

Excel VBA を「属人化させない」、またAIを活用して業務改善を行うための
シンプルかつ強力な仕組みとしておすすめできるものになったかなと思います。

Discussion