How to setup VBA macro with PowerShell?

This is part of the How to Excel with PowerShell series. Links to all the tips can be found in this post.
If you would like to learn more about the module with an interactive notebook, check this post out.

In this last post of the series, I am going to mix Excel, PowerShell and VBA. If you weren’t using PowerShell to manage Excel files before, you might have used VBA (Visual Basic for Applications) to do so. Excel is a powerful tool and even this area can be improved a little bit with PowerShell.

Preparation

For this scenario, we are going to prepare an empty worksheet – as we do not need data at the moment. There will be one minor change comparing to other blog posts. The Excel file extension is going to be XLSM instead of XLSX. The XLSM format support macros, so we need that in order to work with the VBA scripts.

# set location for the files - I am using the temporary folder in my user profile
Set-Location $env:TEMP

# that is the location of our files
#Invoke-Item $env:TEMP

# cleanup any previous files - this helps in case the example has been already ran
$excelFiles = "ImportExcelHowTo013.xlsm"
Remove-Item $excelFiles -ErrorAction SilentlyContinue

# create an empty worksheet
$null | Export-Excel -Path $excelFiles -WorksheetName HowToVBA

Adding Excel user defined functions with PowerShell

Microsoft Excel has a number of functions. Even though it is so many of them, sometimes the goal we need to achieve requires something more, better suited for our needs. At this point, we can add custom user-defined functions.

In this example it will be a FileName() function that returns a full path of the Excel workbook.

' the VBA code for the user-defined function
Public Function FileName () As String
    FileName = ThisWorkbook.FullName
End Function

Let’s go back to our Excel workbook. There is no function in the ImportExcel module to add macros, but we can work directly on the Excel package object. Firstly, open it and create the VBA Project, as currently, VbaProject.Modules returns nothing. Using CreateVBAProject() method we will add default Modules: one for the workbook, and the other the worksheet.

# open the package
$excelPackage = Open-ExcelPackage -Path $excelFiles

# list VBA modules is empty
$excelPackage.Workbook.VbaProject.Modules.Name

# create an empty VBA project
$excelPackage.Workbook.CreateVBAProject()

# once the project is created we can see separate modules: for the workbook and for the worksheet
$excelPackage.Workbook.VbaProject.Modules.Name
<#
ThisWorkbook
HowToVBA
#>

Next, we are going to add a custom module caled MyModule:

# add new module
$module = $excelPackage.Workbook.VbaProject.Modules.AddModule("MyModule")

# list modules
$excelPackage.Workbook.VbaProject.Modules.Name
<#
ThisWorkbook
HowToVBA
MyModule
#>

After we’ve created the project and the module, we can now add our user-defined function to the Code property:

# VBA code to be added to the module
$vba = @"
Public Function FileName () As String
    FileName = ThisWorkbook.FullName
End Function
"@

# add the code
$module.Code = $vba

# save and open the workbook
Close-ExcelPackage $excelPackage -Show

Once we open the Excel file we can use the function right away.

This is how it looks like in the VBA Project Exlporer:

Run macro when the file is open (PowerShell edition)

In this example, I am going to show you how to add a macro that starts when you open the Excel workbook. This is possible with Workbook_Open() event.

First, define what is our action: Select a range of cells, and change its background colour, add X to each cell in the range and apply auto size columns involved. The macro is Private, so it will not be visible on the list of Macros from the Excel interface.

$vba2 = @"
Private Sub Workbook_Open()
    Range("A1:R29").Select
    With Selection.Interior
        .Color = 65535
    End With
    Selection.FormulaR1C1 = "X"
    Selection.EntireColumn.AutoFit
End Sub
"@

Unlike before we are going to use a built-in module called ThisWorkbook to store the VBA code, so it is always in the workbook.

# open the package
$excelPackage = Open-ExcelPackage -Path $excelFiles

# select the worksheet
$excel = $excelPackage.Workbook.Worksheets['HowToVBA']

$module = $excelPackage.Workbook.VbaProject.Modules['ThisWorkbook']
$module.Code = $vba2

Close-ExcelPackage $excelPackage -Show

One note, my environment is setup to prompt before enabling macros hence I need to click the Enable Content button. Once the macros are enabled the one at the startup kicks off.

Running macro at the workbook startup

Summary

Being able to add VBA code to the Excel workbook with PowerShell seems like a brain-melting idea, but it is possible and if you even need to add macro to your spreadsheets – you no longer need to click it through.

Thank you,
Mikey