Programming VBA in Zoho Office
An Overview of VBA in Zoho Office
One of the great things about the Zoho Office Suite is its ability to implement VBA macros into spreadsheets. Zoho is an web-based SaaS office productivity application that includes a full range of capabilities. In an older post, I conducted a review of the popular web-based spreadsheets and I concluded that Zoho (along with Google Docs) offered the best range of capabilities.
One of the things I really liked about the Zoho spreadsheet application was its ability to import and create macros that are compatible with Microsoft's Visual Basic for Applications (VBA) programming language. For seasoned veterans of Microsoft Excel, this is a nice feature that allows the users to use previously written macros and apply their VBA programming language knowledge into online Zoho spreadsheets.
In order to try out the Zoho VBA macros, I built a very simple spreadsheet calculator that takes the input of two numbers and give the user a choice to apply simple mathematical functions (Add, subtract, Multiply, Divide) to the number and returns a results. The image below shows the final product.
Writing VBA Macros
The first step in creating the simple calculator was to write the actual macros. To access the VBA editor window, you select the Macros>VBA editor menu item from the menu bar.
After selecting the VBA editor, you then have a blank slate to start writing your macros. On the left side of the window are the spreadsheets objects and the right side has the text editor where the code is written. For those that are familiar with VBA, the Zoho VBA editor basically looks like a stripped down version of the VBA code editor you can find in Microsoft Office products.
For this tool, I chose to keep it simple by placing the sub routines with the mathematical functions in "ThisWorkbook" object. You could have also placed them either in the "Sheet" or "Macro Module" option. I created four separate sub-routines, one each for the different mathematical functions. I tied the input and return values to specific ranges on the spreadsheets (ranges B3 and B4 for input, B7 for return value). The coding is very similar to Microsoft VBA, so I had no problems putting it together.
Adding Functionality with Buttons
The last step I took was to create additional user functionality through the use of buttons. Buttons add a better user experience. Zoho has an option to add buttons to your spreadsheet very easily. To create a button within Zoho, click on the '+' icon on the main toolbar. You will then have the option to Insert a button into the spreadsheet.
Once you have inserted the button (or in the case of the simple calculator spreadsheet - 4 buttons), you can the resize, edit the label, and move them where you want. The last step in completing the spreadsheet is to tie the VBA macros that were written to the buttons. This will trigger the button to execute the appropriate macro for the desired function.