Randomize

I use EPPlus to create Excel workbooks server-side, and overall it does a great job — it’s far better than mucking around with OOXML file internals directly.

While it supports adding VBA code modules to the workbooks you create (normal modules, or Workbook, Worksheet, or class modules), the VBA code itself that you want to insert is a string, and unless your VBA code is incredibly simple, using C# string constants is going to quickly become a pain. I’ve seen code samples where the VBA was loaded from local text files, but that’s a no-go for me — I don’t want to clutter up my web application folders. Usually I would resort to storing something like this in my app’s database, but then I’d need to create an interface for managing and editing the code files, which is overkill for me.

Fortunately, I found a quick and easy solution to embed the VBA source in my compiled application without using string constants:

1. In Solution Explorer, choose Add…New Item…
2. Select Text File, but give the file an extension of “.vb” rather than “.txt”.
3. For that file, go to the Properties and change the Build Action to Embedded Resource.
4. Paste the code from Excel into the file in Visual Studio.

Boom! Not only can I edit the file without embedding it in a string, I even have partial Intellisense support! There are some caveats (which I’ll get to in a minute), but it’s good enough for me.

Retrieving the code as a string and getting it into my EPPlus file was also simple, but there are a few tricks involved. I created a utility function to grab the resource:

using System.IO;
using System.Reflection;
[...]
public string GetVBACodeFromResource(string resourceFilename) {
	var key = "MainFolder.SubfolderWithVBAFiles." 
		+ resourceFilename + ".vb";
	string result = null;
	using(var reader = new StreamReader(Assembly.GetExecutingAssembly().GetManifestResourceStream(key))) {
		result = reader.ReadToEnd();
	}
	if(string.IsNullOrEmpty(result)) return string.Empty;
	return result.Replace("\t", "    ").Trim();
}

Then later, to call the function to add a module to my package:

[...]
myExcelPackage.Workbook.CreateVBAProject();
var m = myExcelPackage.Workbook.VbaProject.Modules.AddModule("modMain");
m.Code = GetVBACodeFromResource("modMain");

Note in the key above, I’m prefixing my filename with MainFolder.SubfolderWithVBAFiles.. This is because resources are identified by their path within your solution, using . in the resource key rather than the usual / as a folder separator. If you get stuck figuring out the names of your current resources, you can use Assembly.GetExecutingAssembly().GetManifestResourceNames() to get a list of the current resources in your assembly.

I also converted tabs to spaces, because I have Visual Studio set to keep my tabs (I like tabs — I know I’m in the minority), but when raw tabs get embedded in the VBA files, Excel doesn’t convert them to spaces and indent properly.

I mentioned there are a few caveats to using Visual Studio’s VB.NET Intellisense to edit VBA code, because VB and VBA, while sharing a common ancestry, are not the same language:

1. VB.NET requires procedure call arguments to be wrapped in parenthesis (e.g., Foo(bar)), while VBA doesn’t. However, by prefixing the statement with the Call keyword (e.g., Call Foo(bar)), the syntax is legal in both languages.
2. The above doesn’t work with Debug.Print calls — you’ll have to live with VS putting red squiggles under those calls.
3. The Set and Let keywords were removed from VB.NET, so they will also always be formatted as errors.
4. Same goes for Variant and other VBA/Excel-specific types that aren’t native to VB.
5. VB.NET has no concept of Option Base or the To statement in Dim/ReDim.
6. When you paste or edit code, VS will break some code–notably, rewriting some Option directives and removing _ line continuation characters. You can fix this behavior by disabling “Pretty listing (reformatting) of code” under Options: Text Editor: Basic: Advanced, but it would impact the IDE for all VB code you have (if you usually use C#, you’re all good).

This sounds like a long list, but in reality, most VBA code will be properly color-coded, and the Visual Studio IDE even does a decent job of providing auto-complete and error detection. You won’t be able to do everything you can do in Excel’s VBA editor, but it’s great for quick edits.