Adding Macros to Excel::Writer::XLSX

Macros are a powerful feature of Excel and are a frequently requested feature for Spreadsheet::WriteExcel and Excel::Writer::XLSX.

It is also a feature that I've wanted to add for some time but it wouldn't be feasible, with current human lifespans, to re-implement Excel's VBA in Perl. So instead I implemented a scheme to "borrow" the VBA code from Excel and add it to an Excel::Writer::XLSX file.

At its simplest an Excel 2007+ file is a collection of XML files in a zip container. Here is a look inside a sample file that contains macros:

$ unzip -qql Book1.xlsm | awk '{print $4}'
[Content_Types].xml
_rels/.rels
xl/_rels/workbook.xml.rels
xl/workbook.xml
xl/drawings/vmlDrawing1.vml
xl/worksheets/_rels/sheet1.xml.rels
xl/theme/theme1.xml
xl/styles.xml
xl/vbaProject.bin
xl/worksheets/sheet1.xml
docProps/core.xml
docProps/app.xml

In amongst the .xml files you can see there is a vbaProject.bin file. This is an OLE Compound Document file, or rather a collection of files, that contain the VBA macros associated with the Excel workbook.

So, I wrote a small Perl application using Archive::Zip to extract the VBA project from an existing Excel file:

$ extract_vba Book1.xlsm
Extracted 'vbaProject.bin' successfully

Then I added a function to Excel::Writer::XLSX so that it could be added to a new file:

#!/usr/bin/perl

use strict;
use warnings;
use Excel::Writer::XLSX;

my $workbook  = Excel::Writer::XLSX->new( 'macros.xlsm' );
my $worksheet = $workbook->add_worksheet();

$workbook->add_vba_project( './vbaProject.bin' );

# Add the usual Excel::Writer::XLSX code to populate the workbook.
# ...

__END__

So, now we have a new Excel::Writer::XLSX file that contains macros and all the other data we want to add. This is kind of cool but not very useful. It is possible to use complex functions contained in the VBA but calling the macros is a more manual process.

So the next step was to add some form buttons to link the macros to actions on the worksheet. Unfortunately, form buttons in Excel 2007+ are implemented in VML which is a messy XML format that isn't very well documented and which contains lots of quirky backward compatibility features.

Nevertheless, a few late nights later I added form buttons. The process was too painful to add any other form types but at least I was able to add the most common Excel form element. So now you can do something like this:

#!/usr/bin/perl

use strict;
use warnings;
use Excel::Writer::XLSX;

my $workbook  = Excel::Writer::XLSX->new( 'macros.xlsm' );
my $worksheet = $workbook->add_worksheet();

# Widen the column for clarity.
$worksheet->set_column( 'A:A', 30 );

# Add the VBA project binary.
$workbook->add_vba_project( './vbaProject.bin' );

# Show text for the end user.
$worksheet->write( 'A3', 'Press the button to say hello.' );

# Add a button tied to a macro in the VBA project.
$worksheet->insert_button(
    'B3',
    {
        macro   => 'say_hello',
        caption => 'Press Me',
        width   => 80,
        height  => 30
    }
);

__END__

And when you run it you get something like this:

macros.png

This is now available in version 0.60 of Excel::Writer::XLSX on CPAN and as it says in the release notes: The portal to the dungeon dimensions is now fully open.

4 Comments

Sounds great, thanks. I recall having some fun coding custom macros and functions in Visual Basic for Apps on Excel.

BTW, you misspelled "existing" as "exiting", and may wish to correct it.

Thanks, and happy Hanukkah.

Thanks for writing this! Is there a way to read the code lines of the vbaProject.bin as text?

Hello John,
While working with your method I am running into following error with MACRO while trying to execute it on Excel created.
Run Time Error '429'

Can you please help me with some workaround?

Leave a comment

About John McNamara

user-pic Just another Perl hacker