Learn to Program Excel to Become a Better Monarch Modeler

Working with Monarch to extract and manage the data we need is usually a very efficient experience. Often there are just three basic steps:

1. Open the data source,
2. Apply the model that was previously created, and
3. Export the data to another location for further use or analysis.

As we know from our earlier discussions in the 30 Days to Become a Better Monarch Modeler series, we can enhance our productivity even more by applying some simple techniques to accomplish these tasks, including using project files, batch files, and visual basic scripting.

Today on day 29 of the series, we’ll push the envelope a little harder, and offer some ideas as to what can be done when we automate Monarch within a full programming environment.

Welcome to COM

The Component Object Model software interface, or COM, is now an older technology, but is still very useful, and can be used to great advantage by both new programmers and veterans.

And while that’s all well and good and very interesting (I’m sure), you’re likely asking: “What does COM do and why should I use COM to control Monarch?”

What does COM do?

In a nutshell, COM allows programs to talk to one another. One program can direct another program to perform a task on demand, and report back its results.

For years now, I’ve exploited that ability to convert routine and repetitive work into single-click ease of use, and my tool of choice has been Excel. This has been for two fundamental reasons. First and foremost: every installation of Excel offers a full programming environment with its Visual Basic for Applications. There are multitudes of resources in the form of online tutorials and such, and printed books that made it possible to learn what was needed when I needed it. Second, it was the only programming environment that was available to me.

By the time that I’d made it to the end of the Monarch v5 Learning Guide, I had a pretty good idea of what the software could do, and was overjoyed to subsequently discover that there were mechanisms built into Monarch that would allow me to run it essentially by remote control.

The Monarch Programmer’s Guide was a good tool to learn how to use the control methods that had been built into Monarch for programmers to access. But I was glad that I had a background as a programmer/analyst writing code for integrated financial and manufacturing systems, as I found the writing style of the document to be somewhat technical. That shouldn’t have been a big surprise though, really, given its intended audience.

But over time I saw many non-technical users, Monarch’s primary audience, who could have benefited from implementing the automation of Monarch, but who struggled and felt frustrated with the experience, and generally gave up without achieving their goals.

What a shame, I thought.

A Better Way

All of this led to my developing a new tool: one that anyone with Excel could use to easily control Monarch, and which could be understood by anyone with experience in using Monarch.

I offer my Monarch Class Module for Excel as a free bonus to readers who choose to subscribe to the free FeedBurner service. FeedBurner will send you an email that contains the latest update to the ExcelWithMonarch.com site.

Within the class module, you’ll find not only the program code that you can import into your other Excel programming work (and instructions on how to do so), but also full descriptions of the methods (actions for Monarch to perform, like opening a report file or using a model file), and properties (which direct Monarch to activate particular items, or describe what’s in use). It’ll even be clear to you which of these you should use, depending upon whether you use the Standard or Pro edition of Monarch.

There’s also an organized collection of links on a sheet within the file to help you find the right programming mechanism to help you accomplish specific familiar tasks.

The proper use of every method and property is completely clearly and simply documented, along with a brief example of its usage, and navigation links on each sheet to assist in getting around the documentation.

Why should I use COM to control Monarch?

Remember, COM is all about programs interacting with one another. This means that you can use the text and values that are in your worksheet models to control your Monarch models.

Here are just some of things that I’ve done to make my Monarch life easier:

• Use Excel’s data validation lists to allow the user to choose from the list of names that I know already exist as filters in a collection of models, and have Monarch open each required model in series, and apply that specific filter before exporting data.
• Use cells to contain the names of the models and project files that I want to use in a given Monarch session.
• Use check boxes to determine which elements of the model will be exported, such as the table window, the current summary, all summaries or a named summary.
• Send cells values to Monarch to be used as export file names.
• Use radio buttons on worksheets to tell Monarch which window I want to activate before exporting.
• And more…

It’s really up to your needs and creativity.

The command line interface that Monarch provides is tremendously useful, as it allows you to automate the opening of data sources and model, and the required exporting. But there’s no control available at that level to set filters, change sort orders, or easily change the file paths or drive letters used.

The combination of the infinitely configurable Excel worksheet and Excel’s integrated VBA programming environment provide everything that you’ll ever need to control your predefined Monarch models and projects.

Take it to the Next Level

Once you’ve got a handle on how to write program code that can do your Monarch work for you, learn how to create systems that create custom reporting for you. Convert the thousands of pages of data that resides in the collection of the thirty reports that your group uses each and every week to manage the business into tools that effectively and efficiently consolidate the data and spotlight what needs attention.

Learn how to open the Excel files that Monarch just created for you and use that data to build pivot tables or a single page report that contains a number of custom lists. Learn to format cells automatically. Learn to resize the columns automatically so that the reader doesn’t see cropped values or cells that display as ### because the value is wider than the current column width. Learn to automatically create Excel workbook files that only contain the final reports for distribution that shelter your audience from everything that it took behind the scenes for you to build that report that you’ve supplied.

One step at a time, you can do this.

Your Task for Today

Your homework today is pretty easy. First, sign up for the FeedBurner site update service by email (see the section at top left of this page), and I’ll personally send you my Monarch Class Module for Excel which will make your Monarch automation work far easier to accomplish. Plus, you won’t miss out on the latest developments here. Trust me, every indication is that it’s going to be an active year around here and if you’re serious about wanting to excel with Monarch, it’s a great way to stay informed.

After that, make the next steps easier on yourself and if this your first foray into Excel programming, purchase John Walkenbach’s Excel VBA Programming for Dummies.

If you’ve got a little background or some previous experience with Excel programming, purchase either of his Excel 2003 Power Programming with VBA or Excel 2007 Power Programming with VBA, depending upon which version of the Excel software that you use.

When you become aware of what aspects of Monarch you can use to your advantage programmatically, this will shape what you regularly build into your models and projects, and this insight will undoubtedly help you become a better Monarch modeler.

How to Avoid Work with Monarch

Accomplishing repetitive tasks only feels like work when all of the fun has been sucked out of it.

As much as I enjoy building solutions with Monarch, if I had to click on every menu and button to get stuff done, I’d go crazy. Instead, the whole goal of devoting time to developing with Monarch is about getting work done easily and quickly, minimizing the stress and maximizing the accuracy, while doing so in as little time as necessary. We want to return to what it is that we’re meant to be doing.

For most of us, our value is in the greater role that we provide to our organizations, not in being data analysts. That’s why we were drawn to Monarch in the first place: it shelters us from the technology and empowers us to accomplish data management tasks so that we focus on what we really need to do.

So stop working for your data and let your data work for you. Invest in learning to develop your own custom systems that provide the final results that your group needs, and you’ll be thrilled that you’ve chosen to excel with Monarch.

—

Continue your commitment to Become a Better Monarch Modeler with the conclusion, Part 30, of the series, or review Part 28.