Posts

Showing posts from 2017

A Vision for a Simple Form Definition Language

Image
In my travels as a software product manager I've experienced repeated requests to create basic form entry into a SQL database for reporting. Typically these requests don't have a significant budget behind them and may involve a smaller number of users. Pointing non-technical staff to Google Forms or similar isn't that helpful and ideally we'd like to have the data easily incorporated into enterprise reporting (Office 365 environment in particular).

Products like QuickBase have been used in the past, but aren't integrated well in our O365 environment and have significant licensing costs. There are other products (PHP/MySQL solutions seem to be common) for easy specification of forms using WYSIWYG editors, but I'm looking for something that lets me define the input form and related SQL tables/columns directly.

I've recently been impressed with a SQL Report Builder interface in an application we use at work (Genius SIS). It provides a quick way to create repor…

Using the Excel Sunburst Chart for Visualizations

Image
Though not a new concept, Office 365 (Excel 2016) has a Sunburst Chart type (a.k.a. Multi-level Pie or Donut Chart) that is very handy for presenting hierarchies. Here are some ideas to consider:

A series of project portfolios:


A phone "wheel" like a phone tree (recently used this idea myself):


There is more sophisticated color formatting available in a variety of JavaScript libraries such as FusionCharts and HighCharts, but having the chart type in Excel makes it very approachable for those wanting an ad hoc visualization that doesn't require any programming.

DOT (GV) Graph Definition Language

Image
Playing around with graphing tools and ran across DOT (.GV) which meets the needs for simplicity that I have with a current side project. Just using this page to collect information in case it's useful to someone else.

GVEdit (Graphviz)
http://www.graphviz.org/

Helpful StackOverflow posting
http://stackoverflow.com/questions/6344318/pure-javascript-graphviz-equivalent

Graphviz in the Browser
http://www.webgraphviz.com/
Based on this JavaScript library
https://github.com/mdaines/viz.js

Google Charts Image Charts API also supports DOT for now... (deprecation notice)
https://developers.google.com/chart/image/docs/gallery/graphviz

As long as Google supports it, this is a no-brainer way to show DOT scripts in the browser.

Using Google Charts Image Charts API:

https://chart.googleapis.com/chart?cht=gv&chl=digraph{node1[label="Root%20Node"][shape=box];node1->node2[dir=both][label="%202%20inches"];node2->{node3%20node4%20node5}[color=green]}&chrs=300x300

This li…

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…