Office Scripts vs Excel VBA (Everything you need to know)

hello and welcome in this video I'm going to cover everything you need to know about the new Microsoft Office scripts now in case you haven't heard other scripts are Microsoft's to pull successor to VBA and are currently available to preview in this video I will explain what author scripts are and how they compared to VBA and I will also show you how you can try them for yourself now you don't actually need any tree or office 365 subscription to try them there's actually an easier and better way to use office scripts and it works with any version of Excel from 2013 onwards towards the end of this video I'll show you an amazing office script example and it does something that you won't have seen before so what our office scripts well VBA hasn't changed since 2007 and Microsoft many years ago have decided that they're not going to do any further development with the VBA platform so they decided to rewrite the API a few years ago so the API is essentially to program an interface and it's the Parrot of Excel that VBA talks to they originally used JavaScript as the language with this new API but now they're using typescript so typescript is a superset of the languages JavaScript the project has had many names over the last few years and Microsoft have finally settled on office scripts so you might be asking why the change so let's look at one of the main reasons here so at the moment are four main platforms and VBA is only available on the desktop and on the Mac even on the two platforms that VBA is available to some differences in the code for example user forms are no longer available on the Mac and Windows libraries like the dictionary are not available to use on the Mac either so you need to write a bit of different code to get around this now the big advantage of using typescript over VBA is that the same code will work on all four platforms and I think you'll agree that this is a pretty good advantage now it's doubtful data will be used much on the mobile platform but still the option is there if necessary now another advantage is that the typescript has many language features that make VBA look quite old in comparison and we'll be looking at an example of one of these features later in the you so will vba become obsolete when the full version of office scripts is released well the resounding answer is no vba will continue to be supported Microsoft still supports an early version of Mac rollers back from the early 1990s and that wasn't even used that much at a time now we don't know how many lines of VBA call is out there but there's many many millions a hundred million maybe even a billion who we can only guess at what it is but Microsoft aren't going to pull the plug on DS they're not gonna make all these applications obsolete now very few popular languages ever become obsolete C was replaced by C++ in the 1990s and it's still as popular as ever Cobalt the punch card language which was invented in 1959 is currently back in demand because there are so many applications that were built using COBOL and that are still in operation in many big companies so going forward will offer scripts replace VBA as the language of choice for Excel well this is a very tough question and we'll have to wait for the full release of office scripts to know because over the years there's been so many new technologies some have been very successful some have completely failed and some have been somewhere in between so we never really know in advance now one disadvantage I see is that there is a steeper learning curve for non-programmers who want to use office scripts and nowadays most people who use VBA tend to be business analyst financial analysts are some other kind of advanced Excel user with a background in data very few of these tend to be programmers or have a programming or software engineering background so now that we've discussed all of our other scripts what it is etc let's have a look at how we can actually use it if you have the office 365 III re5 license then you can look at the office scripts preview so how you do this is as follows we go to Microsoft 365 admin Center and then we go to settings now we click on settings and then under settings we have org settings now this used to be settings up until recently and when we click on that we've got a list of items we go down here on we find office script and then we click on office script ok this panel that comes up and in the panel what it tells us is that letter users Automator tasks in office on the web so we check this on and this may take 48 hours to take effect now once it's it's ready when we go into office grid examples so this is our file on Excel online so we go to Excel online and you can see that we've got automate in our ribbon so we click on automate and we get the option to record something so this is like the macro recorder the code editor and then a list of scripts that I've recently created so we click on the code editor and you'll see that it will load on the right-hand side so this shows the scripts and we can actually create new scripts if we want so the first thing that we want to kind of keep in mind is so where are the actual scripts stored and where is the document stored so if we look at our onedrive so you can see that on my onedrive office clip tips example so that's the file we're in is stored here and then if I go to documents and office scripts you can see that the previous office scripts are stored here as OST s files so this is where these files are actually stored so let's have a quick look at the editor so that we can see exactly what it does so I want to go into a basic excel so this is one I've created already and what this does is it gets to worksheet and arranged first of all it loads the values and then it prints orange to the console so the console is basically very similar to the immediate window and then the second one will print limes to the console so we click on one to get it to run you can see it says the script is running and I wanting to keep in mind is that this is a bit slower than running a macro but the reason for this is that I'm running this online so it's obviously going to be slower so if this was in the actual desktop it's going to run faster so you can see the output that we have here is oranges and limes because we're writing out what was in position one zero which was oranges and position three zero which was limes so one thing to keep in mind here is that the court editor is very very basic so we can't really stretch it that much we can pop it out we can't do a lot of stuff with it so these are kind of that the disadvantages but then again it's just a preview now another thing is we can't debug to the code we can't step to the code line by line so for the moment we'll have to use our console log which is debug print to find errors now one thing that's really useful in this is that there intellisense so for example if I use something like my sheet you can see that it brings it up without having in VBA we have to use control space this automatically brings it up and you can see when we do the dot here it gives us all the available items now I know it does this in VBA as well but it's actually a bit more clever here so if I do get range and let's say I do it doesn't matter a one now when I put the full stop here and I'm looking for current region because I use current region in VBA and I don't know what the current region thing is here if I'm looking for region say I don't know what it is I type region it actually brings up the ones with that word so it's not just doing it by the first alphabetical order it's searching within that word so I can say Oh get the sound rounding region that sounds like something that I could use and you can see underneath here it actually explains what it is returns a range object that represents the surrounding region so the intellisense here is very very good now another thing that I like is that the error had the actual error reporting seems to be a bit better so for example if I leave out these lines here so say let me just comment them out and then I run this code let's see what it says it gives us a very detailed error the property's value is not available and it says before reading the property's value called the Lord method on the containing object and call context sinks on the Associated requests so you can see here the two lines I left out load and a weird contact sink it's actually told us why we have an error is because I'm missing these two exact lines so this kind of of error is actually very very healthful for us and if you've been using VBA macros for any length of time you can see that this is a vast improvement over the error messages that we get there so this is the cord editor in offer scripts and I don't want to get too much into the cord now because I'll be doing that in later videos just want to give you a kind of a brief look at what it's like now if you don't have the office 365 III or a 5 license then there's an alternative way that you can use offer scripts so let's have a look at that now so if you don't have the office 365 III re5 license you can also try out office scripts what you can do is go to insert on your menu and then go to get add-ins now this could be called store on some versions of Excel but basically it should be there for 2013 onwards so when we click on this it opens the Microsoft stores or office add-ins and what we're looking for is this one here script lab so when you find that you can just click Add and it will add it to your Excel menu now once it's added you'll see that it appears on your ribbon here so you can see script lab and if we click on script lab it will open and you can see code run functions so we click on code to open script lab and once we open it up you see that we've got some code there already now one thing to keep in mind with the files here is that if you want to store the files you have to store them on github which is like a programming website but what you can also do is you can just store these as text files so you can do control a on this just ctrl a and then you can store it in notepad so if you're just playing around at the moment it's no problem to do this at all so you just paste it here in notepad and just save it as a as a txt file now they're also stored your snippets are also stores you can see here on the right-hand side as we go down but it says here they get a raised if you clear your browser cache I'm not sure how true this is on the desktop but it's probably there for a reason so that's just to keep in mind the things you need to know about your files so I think the easiest way if you starting off is just to save them as text files so one really useful thing about the cold window is that we can move it around so we can do this we can stretch it and we can even move it to a second screen if we have one so this is very easy because we can run it on one screen and see what happens in Excel on the other screen so let's run the code we had earlier just to see how it works here so we can start again by just going to a sample so let's take that tight script sample we take it like this and just load it and you'll see that the some extra code here it is like run and then as we go down we've got these extra tanks here now where our code logo is right here so this is where we're going to put our code so we can delete the code that's already there and then we can go to the previous code that we have and you can see this code so what we do is we take the code here and we just copy this code and then we go back to the other code and then we can just paste our code in here so when we run this code it should run and give us the exact same result so to run it we go to the ribbon and we click on run and this opens up the run window now takes it a while the first time to open it but once it is open it will run fine so it's not actually running at the moment it's just opening this window so what we have here is a button highlight selected range and that's just because we've got some HTML and that's how we run it it gives us a button and we click on now we can just change this it if you want to just change it to make it more clear we can just change the button very easy to do we just say run and you can see that it appeared there now you don't have to do this but it just makes I think things a bit clearer so now that we have our Run button we can just click on this button and see what happens so let's click run and you can see the results here oranges and limes were printed to the results window so this code works exactly as it worked in the online version so in the preview version that we looked at the very same code and it produces the very same result but I think this editor is just a bit nicer to use never we know what other scripts are let's use them to perform a task in Excel so this is our task first I'm going to do it in VBA and then I'm gonna do it using typescript so you can see some amazing features of typescript so let's start by having a look at what we're trying to do so it's quite simple really we've got this fruit data on the left hand side and what I want to do is I want you filter this so that I just get the fruit that is limes so all the lines data and I want to copy it over here so let me just copy it over here but what I want to do is well when I'm filtering I want to multiply the second column by two so the final result should look like this and that's what I'm gonna do in VBA and then I'm gonna do it in typescript so let's start with the VBA code so as normal the first thing we want to do is we want to get the range so we get the range and we do it like this we say dim fruit range as range and then set fruit equal to sheet 1 thought range a 1 and the current region so the client region gives us back all the adjacent data and now we're going to create a collection because we need a collection to store our data and then what we want to do is we choose range so we reach ooh the range I as a long and we say for I equals to 2 the fruit range rows the dog count so we're going we're starting at 2 because we're not including the header so then we say if and we want to say if the range so if fruit range dot cells in current row one tough value if it equals lines then we want to start our filtering what we want to do first of all is we want to get the row as an array and then we want to multiply the values as an array so we declare our array has variant and then we store the current role in here so we say fruit ranged out rose I thought value and that's in array and then we say array so the current role is one because this is just for this role and we want the second item and what we want to do with the second item is we want to multiply it by two so in other words we filtered the role we said if it's line then we want to copy that role into an array and we want to multiply the second value of that array it by two and then finally we're going to add this to our collection so we can write it out at the end so let's do a debug compile everything seems good so far and then what we're gonna do is we're gonna write it out back to our sheet to write it to the sheet we say dim item has variant I'm safe our each item in collection and we'll put our next down here now what we also want is we also want the rule that wanna write it out so we say row as long andrew is gonna start at rule one and then we say she 1 dot range and it's column e ampersand the control that's what we want to write out we want to resize it so it's the same size as the rule were writing out so obviously the role is one because it's a role so we don't we don't have multiple roles and then the number of columns is basically what's in our array so it's the second dimension so basically the second dimension is columns we do resize an app and then we set the value and that equals the item and remember the item is just the role that we added to the collection now every time we add a role we need to update our row so the next time it'll write to the row below so we do row equals row plus 1 now always when we finish we do a debug compile and then let's run the code and see what happens so let's go to our spreadsheet and let's just get rid of these just to make our cool clear and let's press f5 and run the code and you can see that we got the result that we were expecting so the cord is a bit long-winded so let's try and write the same code in typescript and see if we can do it any better so let's see how we can do the exact same task but this time using typescript so the first thing that we want to do is get the worksheet and we do it like this we have a variable called selected sheet and we use left light dim except we can also assign while we do it I'm gonna say equal context workbook which is the current workbook worksheets which is the collection of worksheets so quite similar to VBA you can see at the moment and then what we want to do is get item and the item is the worksheet so we say sheet 1 so now we've got a worksheet we want to get our range so we say let fruit range and we'll assign that to the selected sheet and we want to get the range on that sheet and the range we want to get is a 1 now we want to use current region now just like we use it in VBA and current region is surrounding region so we can type su or are you see that it brings it up and we just hit tab key and we got surrounding region and all the lines you can see in this language end with a semicolon so we've got the range and now what we do which is different in typescript is that we have to lower the value so use fruit and we do load and what we're loading is the values as I said and then we need to use this bit of code and this is because typescript is an online language and we have to sync our data using this command so now these are the two extra lines that we don't have to do in VBA so so far VBA seems to have the site slight lead but I think you'll see that typescript is very powerful when we write the next line so we create another variable result and we want result to equal so we want to filter the data and this is how we filter the data we get our range and we say the values of that range we want to filter and we use function and we use value and index and then what we're going to do is want to have our function in here and we can have it on one line but I'm just putting it on over multiple lines just to make it look a bit clearer and within here what we do is we say it returned and we want to return value so that's the value 0 is the first column of the array and we want to say where it is equal to so double equals in typescript is like a comparison so it's one confusing thing in VBA we have equals for a sign and for our comparison but in many other languages we have two equals and that means comparing where that equals lines and we put a semicolon here so this will filter our data and put the result in the result array and now we just want to write out a result array so we do selected sheet get the range and we do II 1 and then we want to do a resize now again if you didn't know what we size was you could just type resize and it says get absolute resized range and that means that it's gone from 1 so the absolute value and we want to get result lent and we want to get there the column so the column let me just move this across a little bit so the column is result and it's in the first dimension or in the second dimension it's the length of that so it's in take a current role which is result 0 and what's the length of that role which is the number of columns so we're really sizing that and we basically just say values and that's assigned to result it's very similar to VBA code so let's run this code and see what happens so we click on run and you can see that it filtered the data with just that one line of code to filter so we didn't need a far loop so the next thing that we want to do is we want to multiply these values by two so six hundred and five hundred we want to multiply by two so how we do it is very similar to the last one we want to have another variable called let new result so let's put this back here and we do new result a new result will equal two result filter so we're filtering the array that we've already filtered and we do the same thing again but this time our filter is slightly different our filter function details so it's one which is the second column so the second column is assigned to itself multiplied by two now as I said assigned as one equals this which is just a comparison and let's run this code then now first before we run this code actually we need to do one small thing and that's to put a new result here because it's new result that we want to write out so let's run this code then and see what we get and you can see that the result is as expected we got twelve hundred and a thousand so you can see that the typescript is incredibly powerful and just a few lines of code we're able to update all the values and we don't need a far loop to do so so let's compare that to VBA and you can see that the typescript code is much neater to write so we're able to do the filter very very neat indeed we didn't have to worry about declaring collections or anything like that so I think you can see that there's a lot of potential from using typescript so now in this video I cover the differences between VBA and alpha script a shortage of what alpha script was how you can use it and even if you don't have the III re5 subscription you can still use it by using the script lab which I think has very nice editor and very nice the way you can move the windows around and then we looked at the code and compared how to do certain tasks using VBA versus using typescript so if you could let me know in the comments if you think what you think about typescript did you think some of it maybe is a bit complex or do you think it's powerful just let me know what you'd think and if you like this video please click the like button and if you'd like to get notified of my upcoming videos then please click on the subscribe button below if you want to get the source code for this video then go to the description below and click on the link now if you're planning to build Excel VBA applications then it's worth checking out my course the excel vba Handbook now this course teaches you how to build Excel VBA applications from scratch it goes to ten different applications and it teaches you step by step teaching you all the core concepts that you need to build an applications in Excel VBA so I hope you enjoyed this video and I hope to see you on the next one

Channel: Excel Macro Mastery

Views: 54,586

Rating: 4.955224 out of 5

Keywords: excel, microsoft excel (software), vba, excel vba, visual basic for applications, office scripts, office scripts for excel, typescript excel macro

Id: ohgwGMlAY8M

Length: 25min 7sec (1507 seconds)

Published: Wed May 20 2020