Menu
Do any of you use the javascript macro capabilities of Google Apps, particularly for spreadsheets? How do the capabilities compare? Is there anything that Google Apps can't do that can be done with VBA macros?
911
VBA merges similar capabilities to Google Forms, Apps Script, add-on-type user interfaces, and the object model into one platform. You need some or all of the Apps Script–related components to achieve the same thing.
Recreate macros in Sheets. You can convert macros in Microsoft Excel spreadsheets to Google Sheets by re-creating them using Google Apps Script. Apps Script powers macros in Sheets, just like Microsoft Visual Basic for Applications does for Excel.
I think from this and what I know is that Macro is generally recorded and short written in VBA language and VBA is generally the scripting of VBA language and can be long and so can perform more tasks as it can use both Sub and Function procedures, use variables instead of being hard-corded which the macro recorder ...
The fundamental difference is that VBA macros are developed for desktop solutions and Office Scripts are designed for secure, cloud-based solutions. Currently, Office Scripts are only supported in Excel on the web.
I am in the middle of conversion of a complex project from VBA to Google Apps Script as an experiment to see what can be done and what can't.
In general, the development environment for google apps script is primitive and frustrating. The language is of course javaScript so if you already know that then you have a head start. The problem though is that many of the advantages of javaScript are not realizable since you don't actually have access to DOM elements, and neither do you have an equivalent of Excel shapes - except through the UI object- which is essentially the same as an excel form, with the same kind of events and objects etc.
Another issue is general slowness. You need to be careful how you structure, so that you minimize calls to the spreadsheet data (I got round this by building a values cache), and scalability is very questionable.
I am equally at home with javaScript or VBA, so putting language aside, VBA is currently more fulfilling and quicker to get things done, although there are increasing capabilities built in to apps script to make it extremely promising.
I am logging the progress of my migration, and the things I come across and the battles to figure out how to minimize structural change (I am trying to see if I can come up with something that would allow dual maintenance on both platforms), so if you are interested, you can follow along here
http://ramblings.mcpher.com/Home/excelquirks/gooscript
Bruce
78
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
