Saturday, January 14, 2017

Converting Excel VBA Prototypes to Production Reports on the Web

I'd previously explored how to move Excel VBA prototypes into a production environment and did not find a satisfying path with Microsoft technologies.

An idea for an approach that I may try as a proof of concept:

1. The Excel VBA Macro would need to be created with a specific set of coding rules and form controls. This is not intended to be a general purpose macro converter and is just being designed for some of the practices I have used over the years. It might serve as a basis for a broader converter down the road. There is still a lot of code written in Excel VBA and it's not a market that is attracting a lot of developer tool competition. It's worth looking into building a set of libraries in VBA similar to how the Google Apps Script (GAS) functions are organized for possible leverage there some day, but the intent is not to put one prototype product into another prototype so GAS is not the target of the code generation in step 3 below. A useful site related to VBA and GAS. As I think about it, if my situation allowed for using Google for authenticating users, the GAS environment is fine for low volume usage (or scale for that matter). We just happen to be a multi-vendor environment and Google is not a primary vendor for us.

2. For Excel VBA Macros used to create reports manually it's often not practical to have end users execute it directly for complexity / security / etc. reasons. The easiest solution in a non-production environment is to distribute the reports instead of having end users access the capability to produce them directly.

3. Having written code generators in the past, they are not as challenging as you might think as long as you restrict their flexibility. Node.js is a fertile environment these days and provides a number of packages for manipulating Excel worksheets. Bootstrap is just one of many options, but I'll use it for the POC because I've been experimenting with it recently related to the AppStudio product.

4. Once the code is generated, it will probably be necessary to add enhancements to pull data directly from SQL databases and/or APIs instead of from manually extracted Excel workbooks. The goal is to move as much functionality as reasonable from Excel VBA into modern JavaScript and then enhance. Features like login / authentication don't fall into the domain of Excel VBA so would need to be provided in the web production environment.

This is labeled "low volume usage" because we're taking prototype code and placing it in a production environment as inexpensively and safely as possible. If a higher volume usage or a more sophisticated approach is needed, plan to spend more effort. For example, we'll probably invest and have development staff create an SSIS/SSRS/.NET solution consistent with much of our production environment.

Fredrick Brooks of course has a lot to say about this in The Mythical Man Month (Chapter 11: Plan to Throw One Away). And for another view check out Joel Spolsky on the perils of rewriting code from scratch.

Saturday, December 17, 2016

How to Move Excel VBA to a Production Environment?!

I was thinking of a quick-and-dirty solution for moving VBA to a server, but found it was essentially shot down by Microsoft a long while ago:

Considerations for server-side Automation of Office

I have some other ideas of using an Excel VBA metaphor (in essence a stack of sheets) within a Node.js module, perhaps implementing a set of functions similar to Google Apps Script (GAS) to reduce the learning curve. A simple a way to move Excel VBA (which is very productive in a corporate Excel environment) to a web front-end for low volume self-serve production use is needed.

Any idea has dozens if not more people around the world thinking about it - maybe someone has already solved this problem and I can just use it?

There are postings on Stack Overflow to figure this out, but consolidated here for quick review. Here's an example of calling an Excel VBA Sub and Function from PowerShell. 

PowerShell code:

1:  $objExcel = new-object -comobject excel.application  
2:  $sFile = "c:\users\dsides\sides-work\powershell\TestCall.xlsm"  
3:  $objWorkbook = $$sFile)  
4:  $objWorksheet = $objWorkbook.worksheets.item(1)  
5:  $'TestSub1', 'Word1 ', 'Word2')  
6:  $sRetVal = $objExcel.Run('TestFunc1','Word1 ', 'Word2')  
7:  Echo $sRetVal  
8:  $objExcel.quit()  

Excel VBA code:

1:  Option Explicit  
3:  Sub TestSub1(sString1 As String, sString2 As String)  
4:    MsgBox (sString1 & sString2)  
5:  End Sub  
7:  Function TestFunc1(sString1 As String, sString2 As String) As String  
8:    TestCallFunc = sString1 & sString2  
9:  End Function  

Looked at a few ways to put PowerShell/VBA behind a web front-end, but it's a bit too hacky and feels like it would be too fragile for a production environment. Here are a couple of links (not hard to find):

Wednesday, September 28, 2016

Henry's Zen Things

My uncle passed away recently after a life dedicated to art and living. I admired him very much and think that this advice on Zen Things was appropriate to post, even in this mostly career-oriented blog...


  1. Do one thing at a time
  2. Do it slowly and deliberately
  3. Do it completely
  4. Do less
  5. Put space between things
  6. Develop rituals
  7. Designate time for certain things
  8. Devote time to sitting
  9. Smile and serve others
  10. Make cleaning and cooking become meditations
  11. Think about what is necessary
  12. Live simply

Monday, January 18, 2016

Natural Order Evaluation of Spreadsheet Cells

Instead of just tossing this bit of history in the trash I decided to scan and post it for posterity. In the mid-80s I worked for EMS/McGraw-Hill on a series of products for the Gregg division that were used to train students on how to use Lotus 1-2-3 and spreadsheets in general. We created a product called McGraw-Hill Integrated Software (MHIS) which included a spreadsheet module (written in C for MS-DOS). The memo I wrote explaining the implementation of natural order evaluation in a spreadsheet vs. column-order evaluation in a spreadsheet is below. It was fun to see that I was able to use my computer science education to bring a postorder traversal of a binary tree to the project!

Dolphin Inc.

Dolphin Inc. (formerly was a boutique educational software company that worked primarily for educational publishers including McGraw-Hill, Pearson, Houghton Mifflin, and Educational Testing Services (ETS) creating software for textbooks and other educational products. Dolphin's work was nominated for Codie awards several times and the Criterion product by ETS (Dolphin worked on the web interface) was awarded a Codie in 2005. At its peak Dolphin had approximately 40 employees.

The first products developed by Dolphin were on MS-DOS and the Apple II (6502 assembler!) in the 80s and the company migrated with technology into Windows and Macintosh software and then to web-based products.

The company was acquired by Byron Preiss Multimedia in approximately 1996 and then when that merger unwound, the company was sold to American Education Corporate (AEC) in approximately 1999. Dolphin continued to develop product for external clients until 2005 when the missions was changed to working only on AEC internal projects. AEC was subsequently purchased by K12.

Some materials including screen shots from software products can be found on Dolphin's Facebook page.

Dolphin Write (WordPerfect 5.1 Compatible SW circa 1995)

Ah, remembering programming in earlier years... Dolphin Inc. created software for a variety of educational publishers including Glencoe/McGraw-Hill and its Gregg line of keyboarding software products. Part of the product included a WordPerfect 5.1 compatible word processor that I created in my basement (of course!) around 1993/1994 shortly after coming to work for Dolphin in Gibbsboro, NJ (near Cherry Hill). The product was written in C and was an interesting, if intense, project created under a tight deadline and pressure from the client to replace a word processor (NYWord) that we had attempted to adapt unsuccessfully.

A Gap Buffer approach was used for managing the insertion of text and of course implementing base WordPerfect 5.1 functionality including the Show Codes feature, endnotes/footnotes, and even basic keystroke macros was fun.

The first screen shot shows some of the features in action including indent, bold, italics, underline, footnotes, and endnotes. The Show Codes feature is at the bottom of the screen and below the (1) you can see the indent code and below the (2) the footnote (FN) code.

What follows are screen shots of the pulldown menus to give a feel for the features supported. We even had a modest set of print drivers included that covered many of the printers found it schools at the time (LaserJet III, Epson, etc.) Dolphin Write used the vLib library for the windows-like user interface.

Here's a link to the Dolphin Write folder where you can download the files needed to run Dolphin Write (dw.exe, dw.hlp, dw.lst, dw.pdb, dw.cfg, dw.err). You'll probably need to download DOSBox to run the 16-bit EXE file on any recent Windows (64-bit) system.

Saturday, June 06, 2015

Phaser for HTML5 Game Development

Just starting on an adventure in HTML5 game development using Phaser and thought I'd keep notes online. There's a bigger picture application goal that I have in mind, but it will start as a series of exercises and I want to record them while I'm learning.

First I wanted to create an example of a polygon moving around the screen using the cursor keys. After some exploration of the examples and reading documentation it boiled down to this basic example. First the index.html file:

1:  <!doctype html>  
2:  <html lang="en">  
3:    <head>  
4:      <meta charset="UTF-8" />  
5:      <title>Polygon Test</title>  
6:      <script src="js/phaser.js"></script>  
7:      <script src="js/polytest.js"></script>    
8:    </head>  
9:    <body>  
10:      <div id="phaser_game">  
11:      </div>  
12:    <script type="text/javascript">    
13:    window.onload = function() {  
14:      game = new Phaser.Game(500, 500, Phaser.CANVAS, 'phaser_game',  
15:                  { create: create, update:update });  
16:    };  
17:    </script>  
18:    </body>  
19:  </html>  

and the polytest.js file:

1:  var game;  
2:  var bmd;  
3:  var img1;  
4:  var bmdsprite;  
5:  var cursors;  
6:  function create() {  
7:    // used to detect arrow key presses in update function  
8:    cursors = game.input.keyboard.createCursorKeys();  
9:    // bitmap to draw onto  
10:    bmd = game.add.bitmapData(128,128);  
11:    // draw on the bitmap canvas  
12:    bmd.ctx.beginPath();  
13:    bmd.ctx.rect(0,0,128,128);  
14:    bmd.ctx.fillStyle = '#ff0000';  
15:    bmd.ctx.fill();  
16:    // use the bitmap data as the texture for the sprite  
17:    bmdsprite = game.add.sprite(200, 200, bmd);  
18:    // an Image is a lighterweight version of a sprite  
19:    img1 = game.add.image(50, 50, bmd);  
20:  // unfortunately, drawPolygon is not a canvas feature  
21:  // so a future effort is to draw the lines separately:  
22:  //  
23:  // 4839993/how-to-draw-polygons-on-an-html5-canvas  
24:  }  
25:  function update() {  
26:    if (cursors.left.isDown) {  
27:      bmdsprite.reset(bmdsprite.x - 15, bmdsprite.y);  
28:      img1.reset(img1.x - 15, img1.y);  
29:    }  
30:    else if (cursors.right.isDown) {  
31:      bmdsprite.reset(bmdsprite.x + 15, bmdsprite.y);  
32:      img1.reset(img1.x + 15, img1.y);  
33:    }  
34:    else if (cursors.up.isDown) {  
35:      bmdsprite.reset(bmdsprite.x, bmdsprite.y - 15);  
36:      img1.reset(img1.x, img1.y - 15);  
37:    }  
38:    else if (cursors.down.isDown) {  
39:      bmdsprite.reset(bmdsprite.x, bmdsprite.y + 15);  
40:      img1.reset(img1.x, img1.y + 15);  
41:    }  
42:  }  

as noted in the source code, I really wanted to use drawPolygon, but discovered it is a feature of Geometry in Phaser and not an HTML5 Canvas feature. From the code you'll see that both Sprite and Image objects are being used. Image may be sufficient in many cases.

Here's what the resulting screen looks like:

As far as tools, so far I'm using Brackets with Live Preview.

Handy links:

Phaser Cheatsheet:
(root site is good:

Amazon Dev Blog: