Beefy Boxes and Bandwidth Generously Provided by pair Networks
Just another Perl shrine

VBA 2 Perl

by Mr. Muskrat (Canon)
on May 08, 2002 at 18:37 UTC ( #165127=sourcecode: print w/replies, xml ) Need Help??
Category: Utility Scripts
Author/Contact Info Matthew Musgrove

vba2pl reads a VBA macro file and attempts to translate the contents to perl. It outputs to a file with the same path and base name but a .pl extension.

It's far from finished although it has come a long way since I started on it last night.

It got its start in my follow up to Win32 - M$ Outlook and Perl.

Mandatory "Dark Side" quotes...
"If you only knew the power of the Dark Side of the Force" - Darth Vader
"Once you start down the Dark Path, forever will it dominate your destiny, consume you it will..." - Yoda

#!/usr/bin/perl -w
# vba2pl version 0.5, 2002-05-09

use strict;
use warnings;
use Getopt::Long;
use Pod::Usage;
use File::Basename;

my $help = 0;
my $man = 0;
my $app = 'Excel';    # Defaults to 'Excel'
my $macro = '';        # No default... must be passed on the command l
GetOptions('help|?' => \$help, man => \$man, 'app=s' => \$app, 'macro=
+s' => \$macro) or pod2usage(2);

pod2usage(1) if $help;
pod2usage(-exitstatus => 0, -verbose => 2) if $man;
pod2usage(2) if ($macro eq '');

my ($base, $path, undef) = fileparse($macro,'\..*');
my $pl = "$path$";

my $code .= <<EndOfCode;
use strict;
use warnings;
use Win32::OLE;
use Win32::OLE qw(in valof with);
use Win32::OLE::Const 'Microsoft $app';

\$Win32::OLE::Warn = 3;

my \$$app;
eval {\$$app = Win32::OLE->GetActiveObject('$app.Application')};
die "$app not installed" if \$@;

unless (defined \$$app) {
  \$$app = Win32::OLE->new('$app.Application', sub {\$_[0]->Quit;}) or
+ die "Cannot start $app";

\$$app\->{Visible} = 1;


my $with = 0;

open(BAS, "<", $macro);
foreach (<BAS>) {
    /^\s*$/ && next;
    s/\s*Attribute .*//i && next;
    s/(\s*)Next(?:\s+\w+)?/$1}/i && next;
    s/&\s+(\w+)/. \$$1/g;
    s/(\s*)For Each (\w+) In (.*)/$1foreach \$$2 ($3) {/i && next;
    if (m/(\s*)For (\w+) = (\d+|\w+)\s+To\s+(\d+|\w+)(?:\s+Step\s+(\S+
+))?/) {
        my ($cond, $newinc);
        my ($ws, $var, $start, $end, $inc) = ($1, $2, $3, $4, $5);
        $var = "\$$var";
        if ($inc =~ s/-//) {
            $cond = ">=";
        } else {
            $cond = "<=";
        if ($inc == 1) {
            if ($cond eq ">=") {
                $newinc = "--$var";
                $newinc = "++$var";
            if ($cond eq ">=") {
                $newinc = "$var - $inc";
                $newinc = "$var + $inc";
        $start = "\$$start" if ($start !~ /\d+/);
        $end = "\$$end" if ($end !~ /\d+/);
        $_ = $ws . "for (my $var = $start, $var $cond $end, $newinc) {
    s/(\s)If (.*) Then/$1if ($2) {/i && next;
    s/\s*Sub (.*)/sub $1 {/i && next;
    if (s/(\s*)End With/$1);/i) { $with++; next; }
    s/(\s*)End .*/$1}/i && next;
    if (s/\s*With ((\w+)(->.*)*)/\$Range = \$$1;\nwith (\$Range,/i) { 
+$with++; next; }
    s/(\s*)Dim (\w+)(?: as .*)?/$1my \$$2;/i && next;
    s/^\s*(\w+.*)/\$$1/ if (!($with % 2));
    s/=/=>/ if ($with % 2);
    $with % 2 ? s/(.*[^;|^,])\n/$1,\n/ : s/(.*[^;|^,])\n/$1;\n/;
continue {
    $code .= $_;
open(PL, ">", $pl) || die "Unable to create $pl, stopped $!";
print PL $code;
print "$pl created.\n";


=head1 NAME

B<vba2pl> - VBA to Perl

=head1 SYNOPSIS -macro file [-app application] -help -man

=head1 OPTIONS

=over 4

=item B<-macro>

The mandatory filename of the VBA macro you wish to translate into per

=item B<-app>

The optional name of the MS application the macro was created in. This
will probably be mandatory in the near future.  (Default: Excel)

=item B<-help>

Print a brief help message and exits.

=item B<-man>

Prints the manual page and exits.



B<vba2pl> will read the given input file and attempt to translate it i
+nto perl.
It creates a perl file with the same name as the VBA macro but with a extension.
It is far from finished.  There is much work to do.

=head1 BUGS

There are bound to be bugs at this point in the development cycle.

=head1 HISTORY

Version 0.1 - 
First version.  Creates a perl snippet for insertion into preexisting 
+code.  Based on
the snippet I posted at

Version 0.2 - 
Now creates somewhat standalone perl code.  It still needs to be edite
+d but the shebang
and modules are added to the top as well as some variable declarations
+.  Added pod.
Now uses Getopt::Long and Pod::Usage.

Version 0.3 - 
Now uses File::Basename instead of a regex to extract the filename wit
+hout the extension.

Version 0.4 - 
Added some more regexes to handle if, for, end if, next, etc...

Version 0.5 - 
Fixed the Next bug and For is handled better now.

=head1 AUTHOR

Written by Matthew Musgrove E<lt>muskrat@mindless.comE<gt>


Replies are listed 'Best First'.
Re: VBA 2 Perl
by Mr. Muskrat (Canon) on May 08, 2002 at 22:17 UTC
    For historical archiving I give you the original
    Version 0.1 code follows:
    #!/usr/bin/perl -w # vba2pl version 0.1 my $app = shift; # I know... next version will GetOpts :) my $macro = shift; my ($name, $ext) = split('.',$macro); # I know... next version will + use file::basename my $pl = "$"; my $code = ""; open(BAS, "<", $macro); while (<BAS>) { s/\./->/g; s/=/=>/; s/"/'/g; s/\s+End With/);/; s/\s+With (\w+)->(\w+)/\$Range = \$$1->$2;\nwith (\$Range/i; s/Application/\$$app/i; s/(.*[^;])\n/$1,\n/; $code .= $_; } close(BAS); open(PL, ">", $pl); print PL $code; close(PL);

    Who says that programmers can't work in the Marketing Department?
    Or is that who says that Marketing people can't program?
Re: VBA 2 Perl
by Mr. Muskrat (Canon) on May 09, 2002 at 21:24 UTC

    Okay, version 0.5 is posted now.
    I fixed a bug involving 'Next' and redid the 'For' code.

    I'm looking into Parse::RecDescent for a later version.
    Thanks jeffa!

    Note to self... it needs more comments!

    Who says that programmers can't work in the Marketing Department?
    Or is that who says that Marketing people can't program?

      What was I thinking? It was written by TheDamian!!!!!
      I'll never understand it! :)

      Wait! What's this? A tutorial?
      And Merlyn has a column in which he talks about it?
      And Craig Smith has a tutorial as well?
      The list seems to go on and on... so perhaps given sufficient time...
      We will just have to wait and see if I can grasp it.

      Who says that programmers can't work in the Marketing Department?
      Or is that who says that Marketing people can't program?
Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: sourcecode [id://165127]
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others about the Monastery: (8)
As of 2017-01-17 07:14 GMT
Find Nodes?
    Voting Booth?
    Do you watch meteor showers?

    Results (152 votes). Check out past polls.