Examples/VBA/HelloWorldVBA.ps1

$xlfile = "$env:temp\test.xlsm"
Remove-Item $xlfile -ErrorAction SilentlyContinue

$Excel = ConvertFrom-Csv @"
Region,Item,TotalSold
West,screwdriver,98
West,kiwi,19
North,kiwi,47
West,screws,48
West,avocado,52
East,avocado,40
South,drill,61
North,orange,92
South,drill,29
South,saw,36
"@
 | Export-Excel $xlfile -PassThru -AutoSize

$wb = $Excel.Workbook
$sheet = $wb.Worksheets["Sheet1"]
$wb.CreateVBAProject()

$code = @"
Public Function HelloWorld() As String
    HelloWorld = "Hello World"
End Function
 
Public Function DoSum() As Integer
    DoSum = Application.Sum(Range("C:C"))
End Function
"@


$module = $wb.VbaProject.Modules.AddModule("PSExcelModule")
$module.Code = $code

Set-ExcelRange -Worksheet $sheet -Range "h7" -Formula "HelloWorld()" -AutoSize
Set-ExcelRange -Worksheet $sheet -Range "h8" -Formula "DoSum()" -AutoSize

Close-ExcelPackage $Excel -Show