VBScriptで始めるExcelアドイン入門 — 自動作成・インストール・実行まで

Option Explicit

Dim excel, wb, vbProj, vbComp, codeMod
Set excel = CreateObject("Excel.Application")
excel.Visible = False   ' 処理中は非表示（デバッグ時はTrueに）
excel.DisplayAlerts = False

' 新しいワークブックを作成
Set wb = excel.Workbooks.Add

' VBAプロジェクトにモジュールを追加（要：トラスト設定）
Set vbProj = wb.VBProject
Set vbComp = vbProj.VBComponents.Add(1) ' 1 = vbext_ct_StdModule
Set codeMod = vbComp.CodeModule

' マクロ（関数）のコードを挿入
Dim macroCode
macroCode = ""
macroCode = macroCode & "Public Sub HelloFromAddin()" & vbCrLf
macroCode = macroCode & "    MsgBox ""Hello from MySimpleAddin!""" & vbCrLf
macroCode = macroCode & "End Sub" & vbCrLf
codeMod.AddFromString macroCode

Dim addinPath, addinName
addinName = "MySimpleAddin.xlam"
addinPath = CreateObject("Scripting.FileSystemObject").BuildPath(excel.Application.UserLibraryPath, addinName)

' アドインとして保存（XLAM形式）
wb.SaveAs addinPath, 55
wb.Close False

' AddInsコレクションに追加して登録・有効化
excel.Workbooks.Add
Dim ai
Set ai = excel.AddIns.Add(addinPath)
ai.Installed = True

' マクロ実行（アドイン名!マクロ名）
excel.Run "'" & addinPath & "'!HelloFromAddin"

' 後処理
excel.Quit
Set codeMod = Nothing
Set vbComp = Nothing
Set vbProj = Nothing
Set wb = Nothing
Set excel = Nothing

WScript.Echo "Add-in created and installed at: " & addinPath

Option Explicit

Dim excel, addinPath, addinName, ai, fso, i, found
addinName = "MySimpleAddin.xlam"
Set fso = CreateObject("Scripting.FileSystemObject")
addinPath = "C:\Users\Public\Documents\Addins\" & addinName

' ファイルが存在するかチェック
If Not fso.FileExists(addinPath) Then
    WScript.Echo "Add-in file not found: " & addinPath
    WScript.Quit 1
End If

Set excel = CreateObject("Excel.Application")
excel.Visible = True
excel.DisplayAlerts = False

' マクロを自動実行する際のセキュリティ設定（必要に応じて変更/削除してください）
' 1 = msoAutomationSecurityLow （注意：セキュリティリスク）
On Error Resume Next
excel.AutomationSecurity = 1
If Err.Number <> 0 Then
    Err.Clear
End If
On Error GoTo 0

' AddIns コレクションの中を探す（名前またはフルパスで比較）
found = False
For i = 1 To excel.AddIns.Count
    On Error Resume Next
    Dim aiItem
    Set aiItem = excel.AddIns.Item(i)
    If Err.Number = 0 Then
        If LCase(aiItem.Name) = LCase(addinName) Or LCase(aiItem.FullName) = LCase(addinPath) Then
            Set ai = aiItem
            found = True
            On Error GoTo 0
            Exit For
        End If
    End If
    On Error GoTo 0
Next

' 見つからなければ Add で登録
If Not found Then
    On Error Resume Next
    excel.Workbooks.Add
    Set ai = excel.AddIns.Add(addinPath)
    If Err.Number <> 0 Then
        WScript.Echo "Failed to add add-in: Err=" & Err.Number & " Msg=" & Err.Description
        Err.Clear
        excel.Quit
        WScript.Quit 1
    End If
    On Error GoTo 0
End If

' 有効化
ai.Installed = True

' マクロ実行（必要ならシングルクォートで囲む）
On Error Resume Next
excel.Run "'" & addinPath & "'!HelloFromAddin"
If Err.Number <> 0 Then
    WScript.Echo "Run failed: Err=" & Err.Number & " Msg=" & Err.Description
    Err.Clear
End If
On Error GoTo 0

' 終了処理：Excel を残すならコメントアウト
excel.Quit

Set ai = Nothing
Set excel = Nothing
Set fso = Nothing

WScript.Echo "Done."

Option Explicit

Dim excel, wb, sht, savePath
savePath = "C:\Temp\hello.xlsx"

Set excel = CreateObject("Excel.Application")
excel.Visible = False
excel.DisplayAlerts = False

Set wb = excel.Workbooks.Add
Set sht = wb.Worksheets(1)

sht.Range("A1").Value = "こんにちは、世界"

' 保存（フォルダが存在することを確認）
wb.SaveAs savePath
wb.Close False

excel.Quit
Set sht = Nothing
Set wb = Nothing
Set excel = Nothing
WScript.Echo "Saved to " & savePath

Option Explicit

Dim excel, wb, vbProj, vbComp, codeMod, path
path = "C:\Temp\CalcAddin.xlam"

Set excel = CreateObject("Excel.Application")
excel.Visible = False
excel.DisplayAlerts = False

Set wb = excel.Workbooks.Add
Set vbProj = wb.VBProject
Set vbComp = vbProj.VBComponents.Add(1) ' 標準モジュール
Set codeMod = vbComp.CodeModule

Dim code
code = ""
code = code & "Public Function AddTwoNumbers(a As Double, b As Double) As Double" & vbCrLf
code = code & "    AddTwoNumbers = a + b" & vbCrLf
code = code & "End Function" & vbCrLf

codeMod.AddFromString code

wb.SaveAs path, 55
wb.Close False

excel.Quit
Set codeMod = Nothing
Set vbComp = Nothing
Set vbProj = Nothing
Set wb = Nothing
Set excel = Nothing

WScript.Echo "Add-in created at: " & path

Option Explicit

Dim excel, addinPath, result
addinPath = "C:\Temp\CalcAddin.xlam"

Set excel = CreateObject("Excel.Application")
excel.Visible = False
excel.DisplayAlerts = False

' Add-in 登録/有効化
Dim ai
On Error Resume Next
Set ai = excel.AddIns("CalcAddin.xlam")
If Err.Number <> 0 Then
    Err.Clear
    Set ai = excel.AddIns.Add(addinPath)
End If
On Error GoTo 0
ai.Installed = True

' マクロ（関数）をRunで呼ぶ。引数を渡すと結果が返る
result = excel.Run("CalcAddin.xlam!AddTwoNumbers", 3, 4)

MsgBox "3 + 4 = " & result

excel.Quit
Set excel = Nothing