VBA Development Skill

This skill allows you to develop VBA macros in your preferred external editor (like VS Code) and automatically inject them into Microsoft Excel workbooks. This enables a modern development workflow with version control, copilot assistance, and better editing tools.

Scripts

1. Enable VBA Trust

Before you can inject code, Excel must be configured to allow programmatic access to the VBA Project Object Model.

• •Script: scripts/enable_vba_trust.ps1
• •Usage:
  powershell

  .\scripts\enable_vba_trust.ps1
  

• •Action: Sets the necessary registry keys to enable "Trust access to the VBA project object model".

2. Inject VBA

The main tool for taking a local .vba text file and injecting it into an Excel workbook.

• •Script: scripts/inject_vba.ps1
• •Parameters:
  • •-VbaFilePath (Required): Path to the .vba source file.
  • •-ExcelFilePath (Optional): Path to the target Excel file. If omitted, creates a new workbook.
  • •-ModuleName (Optional): Name of the VBA module to create/overwrite. Defaults to "InjectedModule".
  • •-Live (Switch): Live Edit mode. Injects into the currently active Excel workbook without saving/closing.
  • •-AutoOpen (Switch): Keeps the Excel file open after injection.
• •Examples:
  powershell

  # Inject into a new workbook and keep open
  .\scripts\inject_vba.ps1 -VbaFilePath .\my_macro.vba -AutoOpen
  
  # Inject into an existing workbook
  .\scripts\inject_vba.ps1 -VbaFilePath .\my_macro.vba -ExcelFilePath .\existing.xlsm
  
  # Live update the active workbook
  .\scripts\inject_vba.ps1 -VbaFilePath .\my_macro.vba -Live
  

3. Inspect Excel

Analyzes an open Excel workbook and reports its structure (sheets, used ranges, values). Useful for understanding the state of a workbook before writing macros.

• •Script: scripts/inspect_excel.ps1
• •Usage:
  powershell

  .\scripts\inspect_excel.ps1
  

• •Action: Connects to the active Excel instance and prints a summary of all sheets and their contents.

Workflow

1. •Write Code: Create a .vba file (e.g., macro.vba) and write your Sub/Function.
2. •Inject: Run inject_vba.ps1 to push the code to Excel.
3. •Test: Run the macro in Excel.