Posts

Showing posts from January, 2017

Parsing UserForm and Code Modules in VBA

Image
Some research into what's possible with VBA and code generation of JavaScript for a proof-of-concept project.

In order to parse a UserForm and VBA code modules, first enable Microsoft Visual Basic for Application Extensibility in the Tools / References feature:



For this example create a simple UserForm:


The Parse button should be wired up to call the Parse() subroutine in the Module1 code module. The entire source will be listed in the worksheet when this Parse routine is run.
Here's the code to place in Module1:
1: Option Explicit 2: 3: Sub Macro1() 4: Form1.Show 5: End Sub 6: 7: Sub Parse() 8: Dim cmpComp As VBIDE.VBComponent 9: Dim cCont As Control 10: Dim lRow As Long 11: Dim lLine As Long 12: 13: Sheets("Sheet1").Activate 14: ActiveSheet.UsedRange.ClearContents 15: 16: lRow = 0 17: lRow = SetRow(lRow, "Show UserForm and VBA Code Modules") 18: lRow = SetRow(lRow, "Workbook…

Converting Excel VBA Prototypes to Production Reports on the Web

Image
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…