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.
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.