Mathias Brandewinder on .NET, F#, VSTO and Excel development, and quantitative analysis / machine learning.
by Mathias 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;

namespace ClearLines.MacroForensics.Reader
{
   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);
                     string comments = "[No comments]";
                     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();
      }
   }
}

A few comments:

  • 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?

Comments

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

Jon Peltier

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.

Jon Peltier United States | Reply

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

Jan Karel Pieterse

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.

Jan Karel Pieterse Netherlands | Reply

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

Mathias

@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.

Mathias | Reply

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

Mathias

@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.

Mathias | Reply

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

Jon Peltier

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.

Jon Peltier United States | Reply

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

Mathias

@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.

Mathias | Reply

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

Jan Karel Pieterse

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).

Jan Karel Pieterse Netherlands | Reply

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

Mathias

Jan,
Fair point - I don't expect the "occasional" Excel user to have much interest in comparing his/her VBA code between workbooks Smile 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.

Mathias | Reply

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

sonam


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

sonam United States | Reply

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

trackback

Longest Common Subsequence in F#

Longest Common Subsequence in F#

Clear Lines Blog | Reply

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

kalyani

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

kalyani India | Reply

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

kalyani

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.

kalyani India | Reply

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

mathias

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

mathias United States | Reply

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

Barb J

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?

Barb J United States | Reply

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

Mathias

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;
var newComponent = project.VBComponents.Add(VBA.vbext_ComponentType.vbext_ct_StdModule);
newComponent.Name = "MyNewModule";
newComponent.CodeModule.AddFromString("'This is a comment");
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

Mathias | Reply

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

Jon Peltier

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.

Jon Peltier United States | Reply

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

Los Angeles Search Engine Marketing

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).

Los Angeles Search Engine Marketing United States | Reply

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

David Tanner

Try VbaDiff http://www.technicana.com/products/vbadiff.html as that's exactly what it does.

David Tanner United Kingdom | Reply

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

pdwalker

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

pdwalker Hong Kong S.A.R. | Reply

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

clooge

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!!

clooge United States | Reply

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

Mathias

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

Mathias United States | Reply

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

officeguy

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  Smile

officeguy United States | Reply

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

Mathias

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

Mathias United States | Reply

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

Jason

This article is very interesting to me because I'm currently in the discovery phase of a new project for my dept.  We are trying to take MS Word docs and add in functionality (menu button) that will allow the end-user to export their "tagged content" into a customized XML format according to a particular schema.  I'm also trying to figure out how to appropriately pseudo-tag the Word content (text, tables, images).  I've been trying to figure out what the best solution would be.  I've looked into trying to buy a 3rd party component that will allow us to do that after install and haven't been able to find anything relevant.  Since I'm a web developer, I've been exploring writing my own solution.  If I go this route, I'm trying to figure out what would be the best language/IDE to get the job done.  I've been wanting to learn C# anyway for it's ASP.NET abilities and think this may be an excellent time to do so...BUT I don't want to muddy the waters if VBA is still the right way to go.  I'd prefer to learn something that won't be obsolete in a few years (VBA?).  The other caveat to this dilemma is that we presently have Office 2003, but will be upgrading to Office 2010 in the near future, but when is anybody's guess!  Also, I'm tethered to Visual Studio 2005 for the foreseeable future.  Please advise this web developer how to best handle this desktop application question.  Thanks!

Jason United States | Reply

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

Mathias

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

Mathias United States | Reply

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

Jan

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.

Jan Belgium | Reply

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

Snorehorse

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

Snorehorse United States | Reply

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

pingback

Pingback from friendslosangeles.march9online.net

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

friendslosangeles.march9online.net | Reply

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

aries544

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.

aries544 Ecuador | Reply

11/18/2014 2:08:15 PM #

identity theft protection tips

Hello! I could have sworn I've been to this site before but after browsing through some of the post I realized it's new to me. Nonetheless, I'm definitely delighted I found it and I'll be bookmarking and checking back frequently!

My web page ::  identity theft protection tips - http://www.identityheistprotection.com/

identity theft protection tips United States | Reply

7/17/2015 3:01:37 AM #

pingback

Pingback from magicafricatours.com

Traveling Salesman Problem Vba Excel | magicafricatours.com

magicafricatours.com | Reply

9/26/2019 2:33:40 PM #

inneka.com

Pingback from inneka.com

A Way to Automate the “Compile” Function of MS Office’s VBA Code – inneka.com

inneka.com | Reply

Add comment




  Country flag

biuquote
  • Comment
  • Preview
Loading



Comments

Comment RSS