Mathias Brandewinder on .NET, F#, VSTO and Excel development, and quantitative analysis / machine learning.
12. December 2009 11:43

A few days back, I stumbled upon this page, where Frank Rice describes how to use VBA to list all VBA macros and functions a Workbook contains. I thought that was interesting: it’s not the type of VBA code most commonly seen, and the idea of VBA code interacting with VBA code is fun. So I tweeted it, and Charts GrandMaster Jon Peltier, in his own words,  could not “leave anything alone, and made some changes to how the procedure worked”. Nice changes, if I might add.

I am not one to leave anything alone, either, and wanted to check how well that would work using C#.

Disclaimer: I have done enough checking to know that the code works in non-twisted cases, but this is far from polished. This would need some handling for exceptions before making it to anything shipped to a client you care about, for instance. My goal was to provide a solid code outline, feel free to modify to fit your needs.

The class/method below takes in a fully-qualified file name (i.e. with the full path, just what you would get from an OpenFileDialog), and searches for all the procedures (sub or function) defined in VBA.

As a bonus, I added some extra code to extract the signature of the procedure, and the header comments. The signature - what arguments it takes as input, and what it returns - is a much better summary than simply its name, and I figured that if the author bothered to add comments, it was probably extracting that, too. It also illustrates nicely some of the functionalities of the API.

Without further due, here is the code, followed by some comments:

using System;
using Excel = Microsoft.Office.Interop.Excel;
using VBA = Microsoft.Vbe.Interop;

{
public class OpenWorkbook
{
public void Open(string fileName)
{
var excel = new Excel.Application();
var workbook = excel.Workbooks.Open(fileName, false, true, Type.Missing, Type.Missing, Type.Missing, true, Type.Missing, Type.Missing, false, false, Type.Missing, false, true, Type.Missing);

var project = workbook.VBProject;
var projectName = project.Name;
var procedureType = Microsoft.Vbe.Interop.vbext_ProcKind.vbext_pk_Proc;

foreach (var component in project.VBComponents)
{
VBA.VBComponent vbComponent = component as VBA.VBComponent;
if (vbComponent != null)
{
string componentName = vbComponent.Name;
var componentCode = vbComponent.CodeModule;
int componentCodeLines = componentCode.CountOfLines;

int line = 1;
while (line < componentCodeLines)
{
string procedureName = componentCode.get_ProcOfLine(line, out procedureType);
if (procedureName != string.Empty)
{
int procedureLines = componentCode.get_ProcCountLines(procedureName, procedureType);
int procedureStartLine = componentCode.get_ProcStartLine(procedureName, procedureType);
int codeStartLine = componentCode.get_ProcBodyLine(procedureName, procedureType);
if (codeStartLine != procedureStartLine)
{
comments = componentCode.get_Lines(line, codeStartLine - procedureStartLine);
}

int signatureLines = 1;
while (componentCode.get_Lines(codeStartLine, signatureLines).EndsWith("_"))
{
signatureLines++;
}

string signature = componentCode.get_Lines(codeStartLine, signatureLines);
signature = signature.Replace("\n", string.Empty);
signature = signature.Replace("\r", string.Empty);
signature = signature.Replace("_", string.Empty);
line += procedureLines - 1;
}
line++;
}
}
}
excel.Quit();
}
}
}

• Besides Microsoft.Office.Interop.Excel, you need to include a reference to Microsoft.Vbe.Interop. You need to “tell” Excel to grant outsiders access Visual Basic Project for this to work. I could not figure out a way to have the code itself check whether access was granted (but I think it’s possible); a try/catch block around the line var project = workbook.VBProject should allow you to isolate that issue.
• The CodeModule class is the container for the VBA code, either a module, or the code behind a worksheet or workbook. It behaves as a list of lines of code, and has a few interesting methods accessible.
• get_ProcOfLine(line, out procedureType) will return the name of the procedure that “owns” the selected line. procedureType is an enum, Microsoft.Vbe.Interop.vbext_ProcKind.vbext_pk_Proc, which filters procedures. Note that this includes any comments above the code itself. get_ProcStartLine(procedureName, procedureType) gives you the starting line of a procedure, and get_ProcBodyLine(procedureName, procedureType) returns the line where actual code begins. get_Lines(firstLine, numberOfLines) will return in one string, including escape characters (new line, etc…), the lines of code you specify, starting at firstLine.
• To extract the signature, I had to take into account that a “unit of code” in VBA can be spread across multiple physical lines of code; in such situations, a “false” line break is marked by the character “_”. To my great relief, I realized that the editor did not allow for whitespace after the “_” character, which simplified work.
• The other piece of information I wish I could extract is global variables and constants. This is a very important piece of information when analyzing code, because it’s a potentially insidious source of side effects.

I had begun to write a small UI around this, but I figured it wasn’t really worth it: if you are interested in that chunk of code, you would most likely use it inside your own project, and not use that UI anyways.

One potential use of this would be to write a procedure or application to automatically inject “standard” modules into a workbook. The API allows not only reading from VBA, but also writing VBA into a Workbook. If you happen to have a bunch of Excel VBA utilities that you typically add to your workbooks, you should be able to write a small application (or add-in) to automate that process.

More modestly, I think I’ll use this API to add a new feature to Akin – wouldn’t it be nice to be able to compare the differences between the contents of two workbooks, and also what changed in their code?

11/30/2009 2:29:15 AM #

Mathias -

I can't say too much intelligent about your code. Your intended uses for it are interesting, particularly the code comparing feature in Akin.

If you want to programmatically insert some VBA into a project, you can do it more easily than pasting in a bunch of lines of code. You can insert a module from a file on disk into the project. Put your favorite functions into such a module, and keep it in a handy directory.

12/1/2009 7:38:29 AM #

Hi Matthias,

Interesting project!
Of course there is MZ tools for VBA which already has the code template/library  functionality, so I wouldn't spend much time on that.
On the other hand; I haven't seen a VBA code difference tool yet, so that would prove very handy indeed.

12/1/2009 6:36:06 PM #

@Jon: thanks for the feedback! I would agree with your comment that inserting a module is easier/smarter than inserting code into a module. I haven't quite thought this through yet, but I was thinking that  the code insertion might be interesting in this scenario: I could store all the procedures I use regularly in a version control system, each as an individual text file, and have an app which allows me to pick them and inject a selection into the workbook of my choice. That's probably overkill, though, because typically I would use the same selection of procedures, and I might as well add an entire module... That being said, I think that an app that takes all procedures and version-controls them, and puts them back in, could be a nice time-saver. I sometimes do this by hand, but it's tedious to insert modules that way.

12/1/2009 6:48:47 PM #

@JKP: I didn't know about MZ Tools, thanks for the pointer. The add-on looks only marginally useful for Visual Studio, where I spend most of my time these days, but on the other hand it seems like a good addition to the VBA editor, which feels a bit crude by today's standards.
Regarding the code diff feature, now that I heard from you and Jon that this would provide value, I am pretty motivated! I had thought about it, but hadn't had time to look into what it took to code against the VBE because I expected this to be pretty complex. As it turns out, the code involved is actually fairly straightforward, so I'll get to it.

12/1/2009 7:20:34 PM #

I used to be afraid of programming to the VBE, but there's nothing magical about it. I don't do it much. Some people envision a huge infrastructure that goes around updating this or that line of code. Hey guys, I just send out a new setup file with a new set of files inside that are all up to date. This way, the updated folks and the new people have the same versions, and it's easy.

Also this way doesn't require a sendkeys hack to unprotect the VB project, and doesn't require the user to have allowed programmatic access to VB projects.

12/1/2009 7:39:22 PM #

@Jon: another area where the magic is gone indeed... I fully appreciate the security concern: requesting to grant programmatic access makes me queasy, because this should clearly NOT be the default setting on Excel.

12/2/2009 7:18:05 AM #

I guess Office developers are used to having turned the programmatic access on anyway, otherwise many of their tools do not work. And this tool is clearly a tool for the Office dev, not for the general public.
I knew programming the VBE isn't very hard. It would be a bigger challenge (for me) to find out differences between two VBA projects intelligently (spot both inserted and deleted lines, spot edits, etcetera).

12/3/2009 6:47:30 PM #

Jan,
Fair point - I don't expect the "occasional" Excel user to have much interest in comparing his/her VBA code between workbooks And from what I understand, it seems that the protection of the VBA code is actually pretty weak, and can be circumvented, so that shouldn't be a problem in the end.

12/24/2009 3:23:40 AM #

Hi,

This artical is very useful for me. I am a .NET developer and always looking to

learn something new. I would like to introduce another good C# blog, Have a look.

http://CSharpTalk.com
Sonam

1/4/2010 5:15:29 PM #

Longest Common Subsequence in F#

Longest Common Subsequence in F#

1/11/2010 4:30:27 AM #

i want 2 knw how can convert excel macros in vba 2 c#.

1/11/2010 4:34:10 AM #

for example we can write a macro for charting which creates addins.i want to convert that  in to c# with addins in excel.how can i?plz send reply as fast as u can.

1/11/2010 4:39:13 PM #

Hi Kalyani,
In general, anything you can do with VBA you should be able to do with .Net / C#, through interop - and if you are looking to convert an existing VBA add-in into .Net, you should look into VSTO, visual studio tools for office, which have a template for Excel add-ins. The VSTO team has recently revamped their developer page, that would be a good place to start:
http://msdn.microsoft.com/en-us/vsto/default.aspx
One thing you might want to consider, if you are porting existing code, is to use VB.Net rather than C#. I personally feel much more comfortable with C# as a language in general, but VB.Net code is much closer to VBA and makes porting easier - and there are some other technical reasons which give VB.Net some advantages over C# in this context, like optional arguments.
Hope this helps!
Mathias

2/26/2010 3:09:21 AM #

You make inserting  VBA programatically into a project sound really easy. I'm writing a program in C# that analyzes some data and then outputs resulting data into a (new) excel file. I also have a VBA macro  that operates on this output data. I would like to have the macro embedded into the output excel file. That is to say, I would like to write the Macro, from C#, into the output excel file when I create it. Thus far I have been unable to find any references/examples of this in books or on the web. Do you have any pointers or suggestions?

2/26/2010 4:00:21 AM #

Hi Barb,
I haven't done it yet, but it should be possible. I gave it a quick look, and replaced the big loop in the code above by this:
...
var project = workbook.VBProject;
newComponent.Name = "MyNewModule";
workbook.Save();
The part which works is that it does create a module and insert some code; besides AddFromString, there is also a AddFromFile method, which might suit your needs - and indicates that it must be possible to do what you are after.
On the other hand, while workbook.Save() executes without failing, it doesn't seem that the module gets saved with the workbook, which is definitely a problem. I'll look into it some more later, and try to figure out what is going on there. If you find something before me, please let me know!
Mathias

2/26/2010 8:43:48 AM #

I haven't done this myself either, but I have a couple impressions.

It might be easiest of all to export the module to a .bas file and keep it in a handy location. Then use VBComponents.Import to import it into the target workbook.

If you're saving as a 2007 workbook, make sure you use the macro enabled version, .xlsm. The default would be .xlsx, which strips out any code when saved.

4/1/2010 3:05:03 AM #

I knew programming the VBE isn't very hard. It would be a bigger challenge (for me) to find out differences between two VBA projects intelligently (spot both inserted and deleted lines, spot edits, etcetera).

2/5/2013 11:44:46 PM #

6/10/2010 8:26:27 PM #

Thanks, that was a useful tip that helped me solve a problem I had.

6/18/2010 4:00:10 PM #

Excellent, excellent article.

Many of the other missed the point, this to problematically extract the macro, for those that need to do it on the back end. Not for the front end office user.

I wanted to see if this could also take the macro code and convert it to C# code, where the corresponding input fields  and output fields are captured. This can me made into easy to use classes!!

6/23/2010 7:27:07 AM #

Hi Clooge,
Thank you for the words of encouragement!
Converting VBA code into C# classes sounds like a very ambitious project - a pretty interesting one, though! More modestly, I would like at some point to write a simple static analysis tool to diagnose VBA code (something similar to StyleCop in .Net), but I have been postponing it because I know even that would take quite a bit of work. If you ever get started on this, let me know, I would love to hear about it.
Cheers,
Mathias

6/18/2010 4:04:07 PM #

I have tried to convert the actual macros into C# code, its easier in 2010 with all the add-ins. So first you need 2010, & the new VSTO.

Then this gets easier.

In my office a lot of the office users have put their macros into the spreadsheets. Now, they want this library of unmanageable documents to re-factor all that into a website that can do the same computations.

I am really not sure how easy it will ever be.

Ideas and a start sample would be appreciated - you may even get paid

6/23/2010 7:20:21 AM #

Hello Officeguy,
Yes, you should be able to convert pretty much any VBA macro in a C# equivalent in VSTO; it's not always worth it (why change something that works?), but it can be very beneficial in some situations.
Can you explain a bit the part about the website? Do the users want their workbooks to use a central website/server to perform computations, or do they want to just use a website, without using workbooks?
Cheers, and thank you for the feedback,
Mathias

7/14/2010 2:28:20 AM #

7/15/2010 5:16:01 PM #

Hi Jason,
That does sound like an interesting project, albeit not necessarily a trivial one! In general, you can use either VBA or VSTO (Visual Studio Tools for Office) to automate Office applications. Both are viable options, and depend a bit on the project. They both give a similar type of control over Office. What you gain with VSTO is that you can also use the whole .Net framework; for instance, you can use existing libraries to work with xml. The other benefit is that you can develop using Visual Studio. The downside is that to leverage it, it helps to have a decent background in C# or VB.Net - and installation is much more complicated than VBA. VSTO does work with Office 2003 (the version you want to look at is VSTO 2005 SE), but has more capabilities for Office 2007 and higher.
I don't have much experience with Word automation, so I can't really give you good advice on the tags part, but one good place to check out is the VSTO forum (if you go that route!): social.msdn.microsoft.com/Forums/en/vsto/threads
As for learning C#, if you are interested in ASP.Net (or Windows development in general), it's definitely a good idea. What language do you currently use?
Mathias

11/3/2011 1:22:14 AM #

Excellent code!

We are using it to build a file scanner which is looking for Excel files containing VBA, and list out the macro names.
By doing this, we can have an idea of the complexity and amount of files to check before upgrading to the new version of Office - 2010.

11/30/2011 2:48:23 AM #

So wishing you had a Print button. Useful article, thank you.

6/27/2012 5:24:38 PM #

Pingback from friendslosangeles.march9online.net

Brian A. Lucas Blog ? Choosing A Powerful Way To Unlock Your … | Friends Los Angeles

7/16/2012 1:49:59 AM #

Hi,
Thank you for the article, its so good, I have a question, how can I do if I need to change some strings and save the excel file with other name, maybe you can do this???
Thanks a lot for your time and help.