Skip to content

VBA

EPPlus supports Creating, Reading and Writing VBA. Execution/Interpretation of VBA code is not supported. Remember that the package must be saved with the extension xlsm.

A VBA project is created by the CreateVBAProject() method of the ExcelWorkbook class. This enables you to write VBA code to the project as shown in Sample 8.2-C# or Sample 8.2-VB.

private static void VBASample1(DirectoryInfo outputDir)
{
   ExcelPackage pck = new ExcelPackage();
   //Add a worksheet.
   var ws=pck.Workbook.Worksheets.Add("VBA Sample");
   ws.Drawings.AddShape("VBASampleRect", eShapeStyle.RoundRect);            
   //Create a vba project             
   pck.Workbook.CreateVBAProject();
   //Now add some code to update the text of the shape...
   var sb = new StringBuilder();
   sb.AppendLine("Private Sub Workbook_Open()");
   sb.AppendLine("    [VBA Sample].Shapes(\"VBASampleRect\").TextEffect.Text = \"This text is set from VBA!\"");
   sb.AppendLine("End Sub");
   pck.Workbook.CodeModule.Code = sb.ToString();            

   //And Save as xlsm
   pck.SaveAs(new FileInfo(outputDir.FullName + @"\sample15-1.xlsm"));
}

Private Shared Sub VBASample1(outputDir As DirectoryInfo)
    Dim pck As New ExcelPackage()
    'Add a worksheet.
    Dim ws = pck.Workbook.Worksheets.Add("VBA Sample")
    ws.Drawings.AddShape("VBASampleRect", eShapeStyle.RoundRect)
    'Create a vba project
    pck.Workbook.CreateVBAProject()
    'Now add some code to update the text of the shape...
    Dim sb As New StringBuilder()
    sb.AppendLine("Private Sub Workbook_Open()")
    sb.AppendLine("    [VBA Sample].Shapes(""VBASampleRect"").TextEffect.Text = ""This text is set from VBA!""")
    sb.AppendLine("End Sub")
    pck.Workbook.CodeModule.Code = sb.ToString()

    'And Save as xlsm
    pck.SaveAs(New FileInfo(outputDir.FullName & "\sample15-1.xlsm"))
End Sub

Add a code module

Here is an example where we add a code module with a method that adds a BubbleChart with VBA to an existing VBA project in the workbook.

var sb = new StringBuilder();

sb.AppendLine("Public Sub CreateBubbleChart()");
sb.AppendLine("Dim co As ChartObject");
sb.AppendLine("Set co = Inventory.ChartObjects.Add(10, 100, 400, 200)");
sb.AppendLine("co.Chart.SetSourceData Source:=Range(\"'Inventory'!$B$1:$E$5\")");
sb.AppendLine("co.Chart.ChartType = xlBubble3DEffect         'Add a bubblechart");
sb.AppendLine("End Sub");

//Create a new module and set the code
var module = pck.Workbook.VbaProject.Modules.AddModule("MyBubbleChartModule");
module.Code = sb.ToString();

Dim sb As New StringBuilder()

sb.AppendLine("Public Sub CreateBubbleChart()")
sb.AppendLine("Dim co As ChartObject")
sb.AppendLine("Set co = Inventory.ChartObjects.Add(10, 100, 400, 200)")
sb.AppendLine("co.Chart.SetSourceData Source:=Range(""'Inventory'!$B$1:$E$5"")")
sb.AppendLine("co.Chart.ChartType = xlBubble3DEffect         'Add a bubblechart")
sb.AppendLine("End Sub")

'Create a new module and set the code
Dim [module] = pck.Workbook.VbaProject.Modules.AddModule("MyBubbleChartModule")
[module].Code = sb.ToString()

Sign your VBA code

You can also sign your VBA code with a code signing certificate, see below. When you set the VbaProject.Signature.Certificate property, the digital signature will be created when the workbook is saved.

X509Store store = new X509Store(StoreLocation.CurrentUser);
store.Open(OpenFlags.ReadOnly);   
foreach (var cert in store.Certificates)
{
   if (cert.HasPrivateKey && cert.NotBefore <= DateTime.Today && cert.NotAfter >= DateTime.Today)
   {
      pck.Workbook.VbaProject.Signature.Certificate = cert;
      break;
   }
}

Dim store As New X509Store(StoreLocation.CurrentUser)
store.Open(OpenFlags.ReadOnly)
For Each cert In store.Certificates
    If cert.HasPrivateKey AndAlso cert.NotBefore <= DateTime.Today AndAlso cert.NotAfter >= DateTime.Today Then
        pck.Workbook.VbaProject.Signature.Certificate = cert
        Exit For
    End If
Next

From version 6.1, EPPlus also supports the Agile and V3 types of code signing. You can also change the hash algorithm used for each version.

In the code below, we sign the workbook with a certificate stored in a pfx file. We create the v3 signature only and change the hash algorithem to SHA256:

var cert = new X509Certificate2(FileUtil.GetRootDirectory() + "\\21-VBA\\SampleCertificate.pfx", "EPPlus");     

using (var p = new ExcelPackage(FileUtil.GetFileInfo("21.3-CreateABattleShipsGameVba.xlsm")))
{
   var signature = p.Workbook.VbaProject.Signature;
   signature.Certificate = cert;

   //Only create the v3 signature on save.
   signature.LegacySignature.CreateSignatureOnSave = false;
   signature.AgileSignature.CreateSignatureOnSave = false;
   signature.V3Signature.CreateSignatureOnSave = true;

   //You can also set the hash algorithm for each signature version.
   //The Excel and EPPlus default is MD5 for the legacy signature and SHA1 for the Agile and V3 signature.
   //We want to change it to SHA256 to get better and more modern hash algorithm.
   signature.V3Signature.HashAlgorithm = VbaSignatureHashAlgorithm.SHA256;
}

Dim cert = New X509Certificate2(FileUtil.GetRootDirectory() & "\21-VBA\SampleCertificate.pfx", "EPPlus")

Using p As New ExcelPackage(FileUtil.GetFileInfo("21.3-CreateABattleShipsGameVba.xlsm"))
    Dim signature = p.Workbook.VbaProject.Signature
    signature.Certificate = cert

    'Only create the v3 signature on save.
    signature.LegacySignature.CreateSignatureOnSave = False
    signature.AgileSignature.CreateSignatureOnSave = False
    signature.V3Signature.CreateSignatureOnSave = True

    'You can also set the hash algorithm for each signature version.
    'The Excel and EPPlus default is MD5 for the legacy signature and SHA1 for the Agile and V3 signature.
    'We want to change it to SHA256 to get better and more modern hash algorithm.
    signature.V3Signature.HashAlgorithm = VbaSignatureHashAlgorithm.SHA256
End Using

See also

We have made a