So we can audit and search it later!

Interesting question came my way last week: Is it possible to save macro code as text in an Excel 2007 .xlsm package?

The answer is: Yes!

Why would someone want to do this?  Well, this particular organization wants to save Excel workbooks to MarkLogic Server.  In the Server, the workbooks are automatically unzipped and they can search and run reports on the SpreadsheetML, running analytics on their analytics.

But macros are saved as .bin files within the .xlsm (in vbaProject.bin) and they want the ability to audit the macros being used, as well as search and possibly re-use them.

So wouldn’t it be dreamy if we could create an Add-in for Excel that could capture when an Analyst saves a workbook and serialize the code for any macros in the workbook as text, storing them in Custom XML Parts within the .xlsm package?

Recipe for Awesome

We can prototype this quickly using the MarkLogic Toolkit for Excel.

I’m just going to focus on the macro to text part for this post.  I choose to handwave at events right now as that’s a topic for another time.  There are many you could possibly capture, but let’s just say we (handwave, handwave) capture the WorkbookBeforeSave event.  When that event fires, we’d call some function that uses the code below to get the text for each macro, XML-ize it, and add it to the .xlsm package.  Score-Double-Bonus-Action: the Add-in that comes with the Toolkit already has a function addCustomXMLPart(string myxml), that provides a way for us to add the Custom XML Parts to the .xlsm.

I used Excel to record a simple macro that merges a couple of cells and does some formatting. To tell you the truth, what I know about macros in Office is more meta than practical, so this was an interesting learning exercise for me.  I created my macro using this tutorial, which I found useful.

All that’s left is to create two functions: one to get the count of the macros in the workbook, and another to get each macro and serialize it as text:

//you probably need to add the reference
using VBA = Microsoft.Vbe.Interop;

public int getMacroCount()
{

  VBA.VBProject proj = Globals.ThisAddIn.Application.ActiveWorkbook.VBProject;
  return proj.VBComponents.Count;

}

public string getMacroText(int idx)
{

  VBA.VBProject proj = Globals.ThisAddIn.Application.ActiveWorkbook.VBProject;
  string projName = proj.Name;
  string componentFile = "";
  object o_idx = idx;

  try
  {
    VBA.VBComponent vbComponent = proj.VBComponents.Item(o_idx);

    if (vbComponent != null)
    {
      VBA.CodeModule componentCode = vbComponent.CodeModule;
      componentFile = "";
      if (componentCode.CountOfLines > 0)
      {
        for (int i = 0; i < componentCode.CountOfLines; i++)
        {
          componentFile += componentCode.get_Lines(i + 1, 1) + Environment.NewLine;
        }
      }
    }
  }

  catch (Exception e)
  {
    componentFile = "error: "+e.Message;
  }

  return componentFile;

}
 

So we call the count, and for each one, we get the macro text, if there is any.  If a macro exists, we can just wrap it in some well formed XML and add it to the .xlsm package using addCustomXMLPart(ourmacroinxml).

Now once we’ve saved the .xlsm toMarkLogic, we can query from CQ:

     xquery version "1.0-ml";
     declare namespace xl="http://marklogic.com/xl";
     cts:search(//xl:metadata, cts:word-query("format"))

And view our results:

Notes

MSDN documentation isn’t the greatest to begin with, but I couldn’t find anything useful in the library on Microsoft.Vbe.Interop, VBProject, or VBComponents.

I did find some older blog posts and some suggestions on the forums.  Consensus seems to be that you have to rely on intellisense and just play around with these objects to figure them out.  Of course, after monkeying around with this stuff for a couple of hours I then knew what to look for and found some useful posts on stackoverflow.  But if anyone knows where I can find decent API documentation or any useful information on using  Microsoft.Vbe.Interop, that would be much appreciated.

Finally, the example here is pretty simple and you can easily add more metadata about the .xlsm and/or any embedded macros to the Custom XML Part.   Take a closer look at componentCode (vbComponent.CodeModule) above.   You’ll find properties that return the procedure name, signature, line counts, and more, which could be useful for reporting and diffing.  Enjoy!