This weekend we released our latest pack for compiling Visual Basic code into Coda Buttons and Automations.
This is useful for people who are migrating business workflows from EXCEL. Its trivial to import the data tables into Coda, and migrating the formulas is easy, as formulas are written using very similar languages.
But EXCEL Macros for buttons and automations are written in the ‘procedural paradigm’ of Visual Basic, which is far more difficult to translate into the ‘pure-functional paradigm’ of the Coda’s formula language.
With this pack, EXCEL experts can use the familiar structures, statements and techniques of Visual Basic when migrating or building automations in Coda.
(We are also launching our packs for Python and JavaScript for those migrating from Google Sheets and Apache Open Office Calc.
Lets start with an example of a canvas button that does some simple math using if and for statements.
Our beta testing showed us that makers preferred to edit their VBA code inside the button itself, rather than storing the code in a table elsewhere. So this is the method we devised to support that.
Inside the Division Table button formula we have the following code…
The first and last lines stores the VBA code in the Basic variable and then runs the VBA compiler to translate and execute the VBA code. The variable N is a canvas control value, set by the user to select the ‘Division Table’ to be printed. Those familiar with Visual Basic will instantly understand what this code does.
When you click on this button you see this pop-up dialog (the _Console)
The VBA code does the following;
- Uses an if-then-else-endIf statement group to check that the divisor N is not zero.
- If it is zero, it uses the error statement to display an error message at the bottom of the screen like this,
- Otherwise, a for-next statement block is executed with the variable I going from 1 to 12.
- On each pass of the loop the print is executed to print the calculation to the _Console.
- At the end, we increment the N variable by one (unless N=12 when it is set to 1)
When you install the Visual Basic pack in a document, it provides a Diagnostics page that shows the source code, the corresponding Coda code and the JSON of the executable object. So if the cde does not perform as you expected, you can investigate and resolve the issue.
There is also an option to show a Wayne Orr Structure Diagram of the executable object, which is easier to interpret if you are not familiar with JSON.
The next example is a button inside a table where the compiler must understand the Context of the button the user clicked, ie: which Row of which Table the clicked button is on. This allows the code to use the thisTable and thisRow variables. It also allows the interpreter to resolve the use of column names for this table to the values on the correct row of the table.
This simple example uses values in thisRow to set the values of a new row in another table. The scenario is as follows
The code inside the Add Order button is as follows…
In this case the first line passes the variable thisRow to Basic along with the VBA code so the compiler can use it to define the Context of the action.
This button does the following when clicked (again it is all familiar code to VBA developers)
- uses the with new statement group to create a new row in the Order table
- uses the
.<attrubute>=<value>statements to set the Product, Amount, and DeliverBy columns of the new Order row - notice how Quantity and Price are assumed to be from thisRow, ie: the row where the button was clicked
- the let OrderedAt=now() statement sets the OrderedAt attribute of thisRow to the current time-stamp
- finally the show thisrow statement displays the current row as a pop-up dialog (similar to OpenRow() in Coda, like this…
Note that the executable object has the correct context values for thisTable (ObjectID) and thisRow (RowID) as seen below
We have refined the compiler process extensively over the last 18 months to fine-tune its performance. At present it takes between 2 milliseconds and 4 milliseconds per VBA source-line to compile the code because it is done on the client-side and does not involve a round-trip to the coda servers. So the additional time taken to do the JIT compilation is not noticeable in most real-world cases.
The Visual Basic pack is available now and the Python and JavaScript packs are to be released this week.
Please private-message me here, or mail me at Max.Xyzor@Agile-Dynamics.com if you are interested.
Respect
Max Xyzor
Agile-Dynamics