Excel ThisWorkbook

[KoalaBear84]

I'm not an expert in VBA, but I managed to create a Workbook wide Workbook_SheetChange to support multiselect dropdown.

Extracted the vbaProject.bin from the XLSM 'zip'-file, and managed to add it as a part with DocumentFormat.OpenXml library:

using SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open("MyXlsm.xlsm", true);
VbaProjectPart vbaProjectPart = spreadsheetDocument.WorkbookPart.AddNewPart<VbaProjectPart>();
FileStream vbaProjectBinFileStream = new("vbaProject.bin", FileMode.Open, FileAccess.Read);
vbaProjectPart.FeedData(vbaProjectBinFileStream);

if (spreadsheetDocument.CanSave)
{
  spreadsheetDocument.Save();
}

But now I want to make the "vbaProject.bin" dynamic.

Is it possible build VBA code dynamically and add it to ThisWorkbook in Excel?

When I try I get a "Class Modules" or "Modules" depending on the command.

VbaCompiler vbaCompiler = new()
{
  ProjectId = Guid.NewGuid(),
  ProjectName = "VBAProject",
  CompanyName = "ACME"
};

// Class Modules
vbaCompiler.AddClass("Dropdown.vb");

// Modules
vbaCompiler.AddModule("Dropdown.vb");

Also tried with the following, but then I get System.IO.FileNotFoundException: 'Could not find document'

Yes, of course I'm doing something wrong 😅

vbaCompiler.AddModule("Dropdown.vb");
//vbaCompiler.AddClass("Dropdown.vb");

MemoryStream memoryStreamVbaProject = new();
vbaCompiler.CompileVbaProject(memoryStreamVbaProject);

MemoryStream memoryStreamExcelMacroFile = new();
vbaCompiler.CompileExcelMacroFile(memoryStreamExcelMacroFile, memoryStreamVbaProject, DocumentFormat.OpenXml.SpreadsheetDocumentType.MacroEnabledWorkbook, "Dropdown.vb");

Same with XLSM / SpreadsheetDocumentType.MacroEnabledWorkbook

FileStream fileStream = File.Open(@"SourceXLSMWithoutMacros.xlsm", FileMode.Open);

MemoryStream memoryStreamExcelMacroFile = new();
vbaCompiler.CompileExcelMacroFile(memoryStreamExcelMacroFile, fileStream, DocumentFormat.OpenXml.SpreadsheetDocumentType.MacroEnabledWorkbook);

Same with XLSX / SpreadsheetDocumentType.Workbook

FileStream fileStream = File.Open(@"SourceXLSXWithoutMacros.xlsx", FileMode.Open);

MemoryStream memoryStreamExcelMacroFile = new();
vbaCompiler.CompileExcelMacroFile(memoryStreamExcelMacroFile, fileStream, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook);