One of the things that I’ve felt online spreadsheets have really lacked is support for Visual Basic for Applications (VBA). I use VBA macros quite frequently in my spreadsheets as a way to analyze and process data that can’t be done with formulas. Well, Zoho has decided to step up to the plate in a big way.
Today they’ve announced a huge update to Zoho Sheet that includes the ability to import and create VBA macros in your spreadsheets. Besides Excel itself they are one of the only other Office solutions in both the desktop and online realms that can boast this. Granted they only support about 50% of the functionality at this point, but it is a remarkable feat regardless. A full list of what they do support right now can be found here.
What about those people who don’t know how to code VBA macros? Unfortunately they don’t have a recording tool, but they say it’s on the way. In the meantime they have setup a Wiki where users can share macros that they have created so that everyone has a chance to benefit from them!
There are about a dozen or so other new features for Zoho Sheet listed below, but here’s a video to show off their support for VBA macros in addition to pivot tables:
Isn’t that crazy? I have to admit that Zoho is pretty impressive when it comes to bringing features from desktop office suites into the online world.
As if supporting VBA wasn’t enough they have also added more features:
- Pivot Tables: Pivot tables and pivot charts can be created using a simple drag and drop interface.
- Support for Named Ranges: Names can now be assigned to a cell or a cell range. These names can then be used in formulas / macros to refer to a range of cells. To define a name, select the cell range and then use the “Name this Cell” menu item in the More Actions menu or in the right click context menu.
- Multiline Support: Multiline support inside a cell has been added. You can use Ctrl+Enter or Alt+Enter to insert a line break inside a cell.
- New Publish Options: New publish options have been added to control use of your public spreadsheets in Zoho. You can now restrict users from exporting your public spreadsheet. You can also hide formulas used in your public spreadsheet from your users. This is useful if you want to prevent users from viewing the formulas you have used.
- Support for large spreadsheets: You can now load upto 100,000 rows of data when imported as CSV files. You would need to select the Tabular Data option while importing. We will also make our application scalable for other data formats too.
- Auto-Filter: We support auto-filters when imported as CSV files. Again you would need to select the Tabular Data option while importing. Filters for normal spreadsheets will be supported later.
- Freeze Panes (partial support): While importing CSV files containing structured data, if you choose the Tabular Data option, the top row will be freezed and you can apply freeze columns on any of the columns. However we haven’t supported freeze panes in our normal sheets. This will follow in a future update.
- TSV File Support: Import and export of .tsv files (tab separate values) is now possible.
- Set as Text: Use the Set as Text icon in the toolbar to set the cell format as text. This icon is available in the drop down next to the comma icon in the toolbar. If you want to enter data like 00002345235 without trimming those leading zeros or enter 4/4 without needing to automatically convert it into date, then you can set these cells as text or you can prefix the cell value by a single quote (‘) and it will be marked as text.
- Performance Improvement: Some performance improvements have been done in loading sheets and range selections. More improvements on the way.
- Keyboard Shortcuts: Many shortcuts have been added. Refer Keyboard Shortcuts which is available under the Product Links at the top.
- Optimal Width and Height: You can double click on the column and row separators to set the optimal width and height.
- Cell selection in Formulas: We now support selecting cell regions using keyboard while typing in formulas.