Do You Need to SWITCH from Excel VBA Macros to Office Scripts?

let's talk about macros in excel if you're familiar with excel macros you know that they help you automate tasks now you can record these yourself or if you want more complex macros for example macros that are based on some type of logic you can write these out yourself in the vba editor in excel now the language you write your code in is visual basic for applications now where does office scripts fit in the picture what's the difference with vba is office scripts here to replace vba well let's take a look at the facts first [Music] [Applause] what they have in common is this both vba and office groups allow you to automate your tasks now the target group for both are officially non-programmers so business users although it's probably easier for programmers to join the party now the main difference is that vba is for desktop applications and not for the web so it can't be used in excel in the web or if you use excel in teams office scripts is currently for the web it works in teams as well since teams uses the web technology and it will probably be for desktop as well in the future so as you can see one of them has a brighter future now let's take a look at a vba example so here is a quick macro i created earlier it creates this chart based on this data set now i made it also dynamic so that if the data set is larger or smaller the chart size automatically adjusts now this is something i made a video about some time ago i'm going to add the link to the description of the video check it out if you want to know how this is done now just to show you that this works i'm going to jump to the finance tab go to view macros view macros the macros create chart let's just run it that automatically inserts the chart this part of my task is made automatically i don't need to go and insert this chart or adjust the colors every time i need to do this i just have to run my macro now another thing you can do in vba is to assign your macros to objects so instead of going and looking for the macro from the menu here i can go to the insert tab and i can insert any shape that i want or a button and assign that macro to it so i could call this insert chart and just right mouse click on it assign macro find the macro it's called create chart and then click on ok and now i can just click on this and it inserts the chart if you have a sheet and then you have different data sets and you want to assign your macro to a button you can do that you just click on insert chart and you get it inserted here's a more complex vba solution so this one is a monthly sales reporting data preparation tool where you get to import data from different files you get to automatically create reports and then create csv files that you can upload to other applications now this one is more complex the code behind this is something that can't be recorded with a macro recorder so unlike the example that we saw before which can be recorded with a macro recorder and then we can easily tweak it this one has a lot of if statements it has a lot of looping things that the macro recorder can't do so for example this part where we import the cells when i click on this button i get to browse for my file so let's say i want to import let's go with this one i click on open it basically does something similar to power query it's going to import my data here and then i can create reports and then generate csv files and so on so the code here is more complex if i bring up the visual basic editor which we can do with alt f11 this is one of the modules that's the import one and you can see we have a lot of variables here that we're declaring and error handling and so on right so all of this is things that you program although you can use the macro recorder to pick up parts of this code but then you have to incorporate it in this additional code that you're writing now in case you're interested how to write vba or to learn vba i have a complete course and i'm going to add the link to the description below this video but now let's take a look at office scripts how does that look to use office scripts first go to office.com login and because i'm using office scripts in excel i'm going to go to excel and open a blank workbook now you can also use an existing workbook and create your script based on that in this case we're just going to open a blank workbook and go to the automate tab so this is where you can record your actions you can view your scripts you can take a look at some sample scripts and so on now if you don't see the automate tab that's either because your admin has deactivated this feature for you or you don't have a business or enterprise account notice just like in excel we have a macro recorder this is automatically going to generate your code for you based on your actions now it's not as clever to pick up everything like the vba recorder although i have to say the vba recorder also doesn't pick up everything but it's a bit more complete than this one but that's because office scripts is newer it's gonna get better with future updates the fact that you can record your actions here makes it easier for business users to start to learn this code so let's just do something simple i'm just going to record actions here just to see how this part looks like it tells me that it's recording and let's see my actions are usually to add a title here and a subtitle and i want this to be let's say bold i want it to be a different color and let's also make it bigger and let's just highlight this and add a border okay so notice as i'm doing all these actions everything is being recorded here i'm gonna stop the recording we can take a closer look at the script the script was generated just let's click on this and rename this to formatting okay so if we edit the script we can actually see the code that was generated here the great thing about this is that we get comments automatically inserted for us so here we can see that the range a1 to a2 was set to this now here's a difference to anyone who knows vba is that it automatically added this type of array syntax to this it didn't do one cell at a time it actually did both of them together then it changed the font here and added the rest of the formatting so notice here we also have curly brackets and in vba where i had dim where i was declaring variables here we have let and you can also use var so the code here is called typescript and this is a subset of javascript now the macro recorder records in typescript but you can also write javascript in here if you're familiar with javascript another difference to vba here is that my script is saved in a separate file to this file this means i can easily use it with any other files i can of course connect it to this file as well so if i know that i have this file and it needs that code i can go to options here under sharing i can share this so that anyone who has access to this workbook is going to be able to view the script but i don't necessarily need to bind this to this file now just to make sure that the script runs i'm going to go to another sheet and i'm going to run my script and it should add a title and subtitle with my formatting to this now this means that i can open any other file because now when i go to the automate tab i can see my code in here i can open any file and i can run this code the script is saved as a separate file in my onedrive folder i can also share this code with others in the organization now what about looping if statements a more complex code well you can do all that in office scripts it's just that the way you write these and the way some of these objects behave is going to be different so for example here notice we do a let selected sheet equals workbook.getactiveworksheet now the selection has to be done in office scripts whereas in vba you don't necessarily need to select a sheet because if you don't by default it assumes the active sheet here you actually have to make that selection now another major difference is this one here so let's see i want to do something simple i just want to get the address of cell a1 so first we've already defined a selected sheet is the active sheet right so i'm going to use that i'll go with selected sheet now as i'm typing i get intellisense i'm going to press tab to accept it selected sheet dot i'm going to go with get cell and the cell that i want let's open bracket do 1 comma 1. this is row number column number right so i'm gonna go one for row number one for column number dot now let's do get address and open close bracket so now let's also assign this to something i'll call this let my ad right so from my address equals this now to test what we get we can use console so that's the way of debugging so console.log open bracket and let's go with my ad so let's just go to another sheet so that we can see the rest of the macro i'm going to run this i'm going to get a title subtitle and now i get sheet 3 b2 is that what i wanted i got cell one one row one column one one one is b two this is a major difference between office scripts and vba because office scripts is zero based so cell zero let's just run this again i'm just going to overwrite these is a1 okay so that i'm sure is going to hit some time to get used to now this of course is a very simple script in the next video we're actually going to create a column chart using dynamic ranges right so we're going to get the macro recorder to record it for us and then we're going to make a slight adjustment to it to make it dynamic if you want to learn more about office scripts make sure that you check out next week's video now remember when we did vba i also inserted objects and i assigned macros to it that's something we can't yet do in office script so i can't insert an object and assign a script to it at least not yet another thing we can't do is to have pop-up dialog boxes and pop message boxes and so on and we can't also use events so if you're familiar with vba you can use events for example if a sheet is selected a macro should run automatically if you select a cell if you change your sales value a macro is executed automatically that's something that we can't currently do in office scripts but something that we can do in office scripts which is amazing is that we can use power automate to run our script so we don't even have to have excel open to run a script we can use power automate based on a trigger the trigger can be a special time it can be because we receive an outlook email it can be because we select certain rows in another excel file all of those can be triggers in power automate so where does that leave us will office scripts replace vba i think eventually yes but not in the short run and as we know the short run can be quite long for microsoft products a lot of companies use excel on desktop a lot of companies will not have office 365. a lot of companies already have more complex vba solutions that they can't replace with office scripts because it's not as advanced so i think we can safely assume that vba will still be valid for at least the next 10 years and probably much longer than that what's your take on this let me know in the comments below i hope this video gave you a better understanding of what office scripts is all about i'm really excited about office scripts it's new it's something we can all learn together it's safe to say that you can expect future office scripts videos as i learn more about it so hit that subscribe if you aren't subscribed give this video a like thank you for being here and i'm gonna see you in the next video [Music] [Music]

Channel: Leila Gharani

Views: 178,559

Rating: 4.9532919 out of 5

Keywords: XelplusVis, Leila Gharani, Advanced Excel tricks, Excel online course, Excel tips and tricks, Excel for analysts, Microsoft Excel tutorials, Microsoft Excel, XelPlus, Microsoft 365, Excel 365, Excel online, excel for the web, office scripts, javascript, typescript, record macro excel online, record macro officescript, officescript, difference between vba and office script, excel typescript, office script example

Id: DOvHqukAGTY

Length: 13min 2sec (782 seconds)

Published: Thu May 20 2021