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"));
}

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();

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;
   }
}

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 changes 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;
}

See also

We have made a sample that implements a BattleShip game using VBA. This sample contains some more details on how to work with VBA via EPPlus. See Sample 8.2-C# or Sample 8.2-VB