[Return to Top Page](/D2MatE/D2MatE_programs.html)

### **Note:**

  * **Files containing VBA (macros) must be saved with the .xlsm extension.**
  * **Add-ins should be saved with the .xlam extension.**

* * *

# Preparing to Use VBA in Excel

### Display the "Developer" Tab

  1. Open Excel and create a new workbook or open an existing one.
  2. Select the "File" tab on the ribbon, then choose the "Options" menu.
  3. When the "Excel Options" dialog box appears, click "Customize Ribbon."
  4. On the right side, check the "Developer" box under "Customize the Ribbon."
  5. Click the "OK" button to close the dialog box.  
Ensure the "Developer" tab is now visible on the ribbon.

### Create Code in the VBA Editor

  1. Click the "Developer" tab, then the "Visual Basic" button.
  2. In the VBA Editor, select "Module" from the "Insert" menu.
  3. Enter the following code in the new module:  
  `  
Sub HelloWorld()  
  MsgBox "Hello, World!"  
End Sub  
  `

  4. Close the VBA Editor and return to Excel.
  5. Click "Macros" on the "Developer" tab (or press Alt + F8) to open the "Macro" dialog box.  
Select "HelloWorld" and click the "Run" button to execute.

* * *

# Adding a Button in Excel to Run VBA

### Note: You need to enable macros in the settings. Be cautious of security risks.

  1. Open "File" => "Options"
  2. Click "Trust Center" => "Trust Center Settings"
  3. Under "Macro Settings,"  
change to "Enable all macros"  
check "Trust access to the VBA project object model"

  4. Restart Excel

### Add a Form Control Button

  1. Click the "Developer" tab => "Design Mode" to enter design mode
  2. Click the "Insert" button
  3. Select "Button" under "Form Controls" and drag to place the button on the sheet

  4. The "Assign Macro" dialog will appear; create a new macro or select an existing one

### Enter VBA Code

  1. In the VBA Editor, enter the following code  
  
Sub RunMacro()  
MsgBox "Button was clicked!"  
End Sub  


  2. Save with the save button or Ctrl+S
  3. Close the VBA Editor
  4. Press the OK button to close the dialog
  5. Exit design mode by clicking the design mode button

### Execute

  1. Click the button

### Modify Assigned Macro

  1. Right-click the button
  2. Select "Assign Macro"

* * *

# Using VBA from External Files

### This can be achieved in the following ways.

### (i) Import External File

_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 VBA from External File

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

  Application.Run "otherWorkbook.xlsm!MacroName"

  wb.Close False ' Close without saving  
End Sub

### (iii) Use Add-ins

  1. Save other files as add-ins (.xlam)
  2. Enable the add-in in Excel
  3. Execute the macro with Application.Run "AddInName.xlam!MacroName"

### (iv) Library Reference

  1. Open "Tools" → "References" in the VBA Editor (VBE)
  2. Add other Excel files as libraries.  
Call included VBA functions directly.

  3. Sub RunVBAFromLibrary()  
  Dim obj As New MyLibrary.Module1  
  Call obj.MySub  
End Sub

### (v) Execute External Script (VBS) (Deprecated in 2024, not recommended)

  1. Save the following as 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") # Macro name to execute  
$workbook.Close($false) # Close without saving  
$excel.Quit()  
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)  


  2. Execute  
> powershell -ExecutionPolicy Bypass -File "test.ps1"