Why Office Scripts Won't Replace VBA

after my last video many people asked what about office scripts isn't that a replacement for vba today i'm going to answer the question and show you why office scripts isn't a replacement for vba i'm also going to answer once and for all the burning question what is the future of vba so let's jump right in if you like this video then please click on the like button and if you'd like to get notified of my upcoming videos then click on the subscribe button and the bell icon beside it let's take a look at the origin of office scripts so we can see where it all began back in 2007 microsoft released the last major update of vba this was version 6.5 at the time monumental changes to technology were taking place and the iphone was about to be released and cloud computing was beginning to take off microsoft realized that they needed a programming language that would work across all platforms they decided on javascript which runs pretty much everywhere however they also needed to change the extensibility model so this is essentially the inner framework that allows office to interact with programming languages and third-party tools work began on the new extensibility model around 2011 and lasted for around 10 years while this work was taken place it was possible to create applications used in javascript however using javascript required using visual studio and the skills of a professional developer this project is now called officejs but has had a number of names since its conception which you may recognize in early 2020 microsoft released office scripts in preview for users of excel online this was aimed at end users rather than professional developers in other words the typical users of vba then in june 2021 other scripts was made widely available to users of excel online so what exactly is office scripts and will it replace vba brian jones the head of product for excel describes office scripts as it's like vba but it's javascript typescript and runs in our web app this is a key point while office scripts is like vba in some ways it is actually completely different to vba in ways that most people don't realize let's take a look at the similarities overscripts is a scripting language that allows us to automate tasks for excel online scripts are written using typescript which is a superset of the javascript language in terms of dealing with the excel object model office scripts is similar to vba in many ways for example compare how we read data from a worksheet in vba and office scripts you can see that they are quite similar one big advantage of office scripts is that it has a very modern intellisense based on the engine used in visual studio code this means if you're not familiar with a property or medit it is very easy to find it and it also provides a description of each method and property another nice feature of typescript is error reporting in this vba example if you forget to use a worksheet name for the worksheets collection then the error doesn't appear until we run the code and even then the cause of the error is not obvious in typescript we get a descriptive and easy to understand message straight away overscripts comes with a macro recorder just like in vba this means we can record what we were doing in excel and office scripts will create the code this is invaluable for beginners you can see here that the code it produces is good quality and it's even very well commented from what we've done so far you can see that office scripts is very similar to vba in many ways however now it's time to look at the fundamental difference between the two which are very important to understand you've probably seen countless articles and videos over the last few years asking is vba dead well i'm going to tell you the definite future of vba right now so you never have to worry about this again vba will be around when you retire there are critical applications written in vba and microsoft will continue to support vba as long as there is desktop versions of excel now point number two vba will never be updated it doesn't matter how much we would like microsoft to update vba and the vba editor it's never going to happen that ship has sailed two decades ago and point three vba will never work online so vba will only ever work with windows and to a lesser degree the mac versions of excel now that we know where vba stands let's look at where office scripts fits into the picture one important thing to know about applescripts is that it runs to power automate so what exactly is power automate well power automate is a service that provides automated workflows between different applications and these are not just microsoft applications it deals with a large number of third-party applications as well for example your workflow could start when you receive an email with a specific subject in gmail this could then run an office script which could in turn run a convert to pdf action the possibilities are endless now power automate is similar to power query in that it doesn't require any coding and it has a ui that makes it fun to use javascript works in conjunction with power automate and we'll see shortly that this means there are significant differences between how vba and office scripts operate if you'd like to learn more about using office scripts with power automate then click on the link in the top right let's look at the major differences between office scripts and vba vba is what we call an event-driven language the code runs based on events like button clicks cell changes a worksheet being activated and so on all the scripts are not set up to work like this there are events available for the office js language but these are not supported by office scripts in the february 2021 q a the javascripts team said we don't support running scripts based on an event such as waiting for a query or sell edits it would just have to be triggered manually or from power automate and in june 2020 a member of javascript's team shed more light on it when they said currently event apis are not supported within office scripts one of the main reasons is that events can be long running and scripts have a defined start and end points unlike an add-in the script doesn't stay in the background to listen in and act on the events another question that is often asked about office scripts is the ability to reference other workbooks directly from the current script a script can only work on one workbook and which workbook this is depends on how you run the scripts if you run it manually then the script works on the active workbook if you're using power automate then the script works on the workbook that you passed as a parameter vba is different in that we can reference any number of workbooks from a procedure so is it likely that javascripts will allow this in the future well in the june q a a user asked this very question and the reply was it's not currently in our backlog so if for example you were converting a vba application that references multiple workbooks and you were converting this to office scripts you would have to completely restructure the code as office scripts deals with reference and workbooks in a very different way in other words you have to go to power automate in vba we can step through the code line by line as it's running not only is this a great way to find errors is also a great way to understand how vba code works it's not possible to do this in office scripts instead we debug using console.log to determine what happens as the code runs console.log writes messages to the output pane in office scripts it is the equivalent of vba's debug print one question that comes up a lot is is there a tool that automatically converts vba to office scripts and at this time there isn't a tool like that although someone may create one in the future so how does microsoft see office scripts well in the office scripts during q a one user asked about power query with office scripts the reply provided a nice summary of what both are for power query can handle data manipulation tasks scripts are good at formatting and custom logic we covered a lot of ground in this video so let's sum it up vba is not going anywhere but it will never work online or be updated other scripts will work everywhere but requires power automate to work other scripts does not support events at this time and this is not likely to change anytime soon javascript does not support direct interaction with other workbooks and this is not likely to change either in conclusion office scripts is definitely not a direct replacement for vba but it does provide a nice environment for creating custom scripts for manipulating excel files and in the future the same script will run across multiple platforms which is a massive advantage let me know in the comments if you plan to use office scripts for your own work if you liked this video then please click on the like button and if you'd like to get notified of my upcoming videos then click on the subscribe button and the bell icon beside it

Channel: Excel Macro Mastery

Views: 27,871

Keywords: OfficeScripts

Id: MzSTu8OR87E

Length: 8min 51sec (531 seconds)

Published: Thu Jul 29 2021