Return to Top Page

Note: 

• Files containing VBA (macros) must be saved with the .xlsm extension.
• Save the add-in as .xlam.

Preparing for VBA in Excel

Display the "Developer" Tab

1. Open Excel, create a new workbook or open an existing one.
2. Ribbon's "File" Tab => Select "Options" Menu.
3. When the "Excel Options" dialog box appears, click "Customize Ribbon."
4. In the "Customize Ribbon" section on the right, check the "Developer" box.
5. Click the "OK" button to close the dialog box.
   Ensure the "Developer" tab is displayed on the ribbon.

Creating Code in the VBA Editor

1. "Developer" tab => Click "Visual Basic" button.
2. The VBA editor opens, select "Module" from the "Insert" menu.
3. Enter the following code into the newly created module.
    
Sub HelloWorld()
MsgBox "Hello, World!"
End Sub
 
4. Close the VBA Editor and return to Excel.
5. Click the "Macros" under the "Developer" tab (or press Alt + F8) to open the "Macro" dialog box.
   Select "HelloWorld" and click the "Run" button to execute

Add a Button in Excel to Run VBA

Note: Macros need to be enabled in the settings. Please be aware of the security risks.

1. Open "File" => "Options"
2. "Security Center" => Click "Security Center Settings"
3. Macro Settings
   Change to "Enable All Macros"
   Check "Trust access to the VBA project object model"
4. Restart Excel

Add Form Control Button

1. "Development" Tab => "Design Mode" Press to enter Design Mode
2. Click the "Insert" button
3. "Select 'Button' under 'Form Control'"
   Place Buttons by Dragging on the Sheet
4. The "Record Macro" dialog box appears, Create New (or select existing macro)

Enter VBA Code

1. The VBA editor will open, so enter the following code
    
   Sub RunMacro()
   MsgBox "Button clicked!"
   End Sub
    
2. Save using the Save button or Ctrl+S
3. Close the VBA Editor
4. Close the dialog by pressing the OK button
5. Press the Design Mode button to exit Design Mode

Execution

1. Click the button

Changes to Registration Macro

1. Right-click on the button
2. Select "Record Macro"

How to Use External File VBA

Can be achieved using the following methods.

(i) Import External Files

Note: Enable "Trust access to the VBA project object model"

1. Sub ImportModule()
   Dim vbProj As Object
   Set vbProj = ThisWorkbook.VBProject
   vbProj.VBComponents.Import "\path\to\module.bas"
   End Sub

(ii) Execute External File VBA

1. Sub RunMacroFromAnotherWorkbook()
   Dim wb As Workbook
   Set wb = Workbooks.Open("\path\to\otherWorkbook.xlsm")

   Application.Run "otherWorkbook.xlsm!MacroName"

   Close without saving
   End Sub

(iii) Use Add-ins

1. Save Other Files as Add-in (.xlam)
2. Enable Add-ins in Excel
3. Run the macro with Application.Run "AddInName.xlam!MacroName"

(iv) Library Configuration (Reference Settings)

1. Open "Tools" → "References" in the VBA Editor (VBE)
2. Add Other Excel Files as Library
   Can directly call included VBA functions.
3. Sub RunVBAFromLibrary()
   Dim obj As New MyLibrary.Module1
   Call obj.MySub
   End Sub

(v) Execute External Scripts (VBS) (Deprecated as of 2024)

1. Save the following to test.vbs
    
   Dim objExcel, objWorkbook
   Set objExcel = CreateObject("Excel.Application")
   Set objWorkbook = objExcel.Workbooks.Open("C:\path\to\otherWorkbook.xlsm")

   objExcel.Application.Run "otherWorkbook.xlsm!MacroName"
   objWorkbook.Close False
   objExcel.Quit
    

2. Execute
   > cscript test.vbs

(vi) Execute with PowerShell

1. Save the following as test.ps1
    
   $excel = New-Object -ComObject Excel.Application
   $excel.Visible = $false # Hide Excel
   $workbook = $excel.Workbooks.Open("C:\Users\YourName\Documents\test.xlsm")
   $excel.Run("MyMacro") # 実行するマクロ名
   $workbook.Close($false) # Close without saving
   $excel.Quit()
   [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)
    
2. Execution
   > powershell -ExecutionPolicy Bypass -File "test.ps1"