For 16 years, people asked me if Excel VBA will be around forever. I always said, "Don't worry - they can't kill it until there is a viable replacement with a macro recorder." Today, in Public Preview, Office Scripts offer a macro recorder that records TypeScript. This will be the product that eventually kills Excel VBA. Listen as deal with the stages of grief as I try to figure out if I can figure out typescript.
This is a first look at the Macro Recorder and trying to understand TypeScript.
Microsoft Tutorial: Sample scripts for Office Scripts in Excel on the web - Office Scripts
This is a first look at the Macro Recorder and trying to understand TypeScript.
Microsoft Tutorial: Sample scripts for Office Scripts in Excel on the web - Office Scripts
Transcript of the video:
Learn Excel from MrExcel Podcast episode 2322.
The VBA killer: first look.
Hey, welcome back to the MrExcel Netcast. I'm Bill Jelen.
Well, I've been afraid of this day.
It's a question that's been asked since I wrote my first book for QUE back in 2004.
How long will Excel VBA survive?
Through six editions of that book, I've always said, "Don't worry...
It's not going to go away until there's a viable alternative, including a macro recorder and: Excel 4 macros are still supported 27 years after the viable alternative debuted.
So (A) get to a viable alternative (B) wait 27 years, that's 2047 - With any luck, I won't be working by that.
Alright, public preview.
In order to try this, you needed an E3 or E5 level of Office 365, not the home edition. It has a macro recorder.
It's hard to find. The macro recorder?
- it's not perfect, but hey, neither is the VBA macro recorder.
They don't have a good distribution story. It's not in Win32 yet.
They started in Office Online, but it'll come to Win32.
And once it comes to Win32 I think the 27-year clock starts to run.
Hard to find. Here's what you have to do. Go to Admin.Microsoft.com Top right-hand corner.
Click this button for the New Admin Center.
Over here on the left navigation choose "Show All". Within "Show all", Go to Settings.
Within Settings go to Settings (again). Like I said, it's buried inside of settings.
Scroll down here to Office Ccripts.
Turn it on: "Let Users Automate Their Tasks in Office on the Web".
We turn this on.
It takes about 3 or 4 minutes, but then when you open Excel online and open a file in Excel online.
You have this tab. The automate tab right?
And there's a code editor. And a macro recorder.
Here's my Hello World script that I recorded a few days ago.
So what it did is it goes down here to the bottom of this data set goes to B19 It types the word Total.
It puts a total there in C19 and copies, pastes, pastes.
Click inside. Watch over here while I click run.
There we are.
Totals total total. How did I get this out?
Let's start here on this data set, go to record actions.
Alright, so first thing I want to do is I want to press Control down-arrow.
Alright that let's us handle a whole bunch of different size datasets. So from B1 Press control down arrow.
And then down arrow again.
Type Total.
Here I'm going to press alt-equals.
And Ctrl-Enter to stay in that cell. Copy.
Control V to paste.
Simple enough. Stop recording.
Script name? Second hello world.
Click save.
Run, edit or delete.
I'll choose edit. Alright, so.
I don't know TypeScript, but The thing is I didn't know VBA when I had to switch over from Lotus macros to VBA.
That question is can we look at this and figure out what's going on and figure out you know how to do whatever, right?
So bad news, good news is they can't, they can't seem to record control down arrow! So this is always going to be 14 whether there are 13 rows or 17 rows or 100 rows.
Unknown event received with event ID 502.
What the heck does that mean? But they selected C14 and put in a formula there.
And then did a copy from C14 to D14 to E14.
The great news I guess.
This doesn't look horrible. I can sort of figure this out.
Well, they're calling it Office Scripts and there is a web page from Microsoft where they have some sample scripts, right?
Reading and log 1 cell to the console. Work with dates.
Apply conditional formatting. This is deep.
Create a sorted table. Delete resolved comments.
There's tutorials.
Boy, I love some VBA, right?
I understand VBA, I can write VBA from scratch. Never used TypeScript ever.
The big question is given some code, can we figure out what the heck is going on right?
So if you're a TypeScript pro, just stop right here 'cause you already get it.
But let's say you're a VBA person in the big question is can we figure out what's going on? So here's a data set. It has 18 students.
Their score and assignments, midterms and finals.
We want to calculate a total. We want to calculate a grade.
The name of the sheet is called Scores. Alright, so.
Over here, Async Function.
Let sheet equals Context dot Workbook dot Worksheets dot GetItem "Scores".
That's the name down there. Cool, OK, GetUsedRange.
And then this await, right? This is asynchronous.
That means wait till we tell that the process is finished like Application.DoEvents And this is Debug.Print right here.
Total students tells us how many students we have.
Now this one I can already tell is going to take some getting used to: Column A, in this world is column zero. It's zero based, so this is one.
This is to this is 3. This is 4, so when they talk about Student.Range .GetColumn 4 they're talking about column E.
All those great facts you know, like T is 20 sorry T is now 19 because A is zero. Get rows below.
Get rows below an it's row count minus one. Clear. They're clearing some formats.
Clear some formats alright, and then wait for that to finish. Declaring variables.
Hey Jordan Goldmeir. There you are. Look at me.
My second macro ever an I'm declaring variables! For this is for I equals 1 to row count minus one.
This is the TypeScript that we're starting at zero. Going until I is...
I don't know, and then that's an incrementer right there, right?
Get Max scores from first row, so if I is equal to 0 (in other words, if we are in row one). This is Row zero to the script.
Check this out, get the Max value for assignments.
That's a little RegEx right there. How cool is that?
And then for the midterm Max again get the digits. FinalsMax.
Get the digits. I'm bad at RegEx, but I'm excited that it's supported.
That's great and they're doing Debug.Print of the assignment max, midterm max, and the finals max.
Let row equals studentsRange values I, so that's saying that's assigning an object variable called row and it's equal to this row, right?
So if we're on like the third pass through here row three, so that's Row Zero, Row 1, Row 2, Row 3 is going to be talking about Carol.
So that variable Row is an object variable and this variable called Total is the first item in row plus the second item in Row, plus the third item in Row, and Remember, this is the Zeroth item in Row.
This is the first, second and third, so we're doing columns B, C and D just a little math in your head there where you have to add or subtract 1 Alright then.
Let Valid equals true, so there have a variable name Valid there, setting up as true.
If the first item is less than zero, so Row..
You know this row item one. This is item zero. That's item one.
If it is less than zero or if it's greater than Assignment Max, then we're going to color it in red.
I wonder what are the strings we can use here?
Don't know for now and then Valid becomes False.
If the second column Midterm is less than zero or greater than 30 then color that in red, right and then down here if it's not valid then continue. This is a Select Case in VBA.
Switch true if it's less than 60 then an F. Less than 70 D.
Less than 80 C.
Less than 90 B and then Case Else is now called Default. Alright, I'm getting this.
I'm following the flow here even though I've never written TypeScript before. It's close.
And then get cell.
I is the row number. Four is zero, one, two, three, four so column E.
They're writing the the variable total, and then F writing Grade. Color.
The grade; if it's equal to a, then we're going to green.
If it's equal to F or D, then orange, so we know we can use red, green, and orange, and then they're going to center the 5th column Remember...
Column 0 1 2 3 4 5 so center column F and then wait for the whole thing to finish.
I don't know. Let's go to try.
Holy sh*t.
OK.
Yeah it it works Alright.
Look at that.
Down here, the console. Total students 18.
Assignment Max Midterm Max. Finals Max.
Problems: no problems. No problems, no problems all right.
Yeah, perspective, right?
Perspective I've been afraid of this day for 16 years because now the 27-year clock starts ticking. But you know what?
I've been afraid of it for 16 years, but it's not that bad, right? It's TypeScript.
We'll learn TypeScript, just like we learn VBA. VBA from Lotus Command Line Macros was a huge jump.
This isn't that far of a jump. Its the same constructs.
We got to learn some new syntax, not the end of the world.
This is the point that video where I decide what book is most appropriate.
I don't have a book on this, but my friend Suat Ozgur who wrote a book on JavaScript UDF's - probably doesn't cover the scripting language at all, but might get you a little bit up to speed. I don't know how I feel today.
I really honestly don't know how I feel. Hey, but here we are.
Whether it's good news or bad news and I'm still not sure what news this is. I'm committed to bringing you the latest in Excel.
If you're interested in learning more about when new things hit Excel.
Please down below this video - click Subscribe and ring that Bell.
Feel free to post any questions or comments down in the comments below.
I want to thank you stopping by.
We'll see you next time for another netcast from MrExcel.
The VBA killer: first look.
Hey, welcome back to the MrExcel Netcast. I'm Bill Jelen.
Well, I've been afraid of this day.
It's a question that's been asked since I wrote my first book for QUE back in 2004.
How long will Excel VBA survive?
Through six editions of that book, I've always said, "Don't worry...
It's not going to go away until there's a viable alternative, including a macro recorder and: Excel 4 macros are still supported 27 years after the viable alternative debuted.
So (A) get to a viable alternative (B) wait 27 years, that's 2047 - With any luck, I won't be working by that.
Alright, public preview.
In order to try this, you needed an E3 or E5 level of Office 365, not the home edition. It has a macro recorder.
It's hard to find. The macro recorder?
- it's not perfect, but hey, neither is the VBA macro recorder.
They don't have a good distribution story. It's not in Win32 yet.
They started in Office Online, but it'll come to Win32.
And once it comes to Win32 I think the 27-year clock starts to run.
Hard to find. Here's what you have to do. Go to Admin.Microsoft.com Top right-hand corner.
Click this button for the New Admin Center.
Over here on the left navigation choose "Show All". Within "Show all", Go to Settings.
Within Settings go to Settings (again). Like I said, it's buried inside of settings.
Scroll down here to Office Ccripts.
Turn it on: "Let Users Automate Their Tasks in Office on the Web".
We turn this on.
It takes about 3 or 4 minutes, but then when you open Excel online and open a file in Excel online.
You have this tab. The automate tab right?
And there's a code editor. And a macro recorder.
Here's my Hello World script that I recorded a few days ago.
So what it did is it goes down here to the bottom of this data set goes to B19 It types the word Total.
It puts a total there in C19 and copies, pastes, pastes.
Click inside. Watch over here while I click run.
There we are.
Totals total total. How did I get this out?
Let's start here on this data set, go to record actions.
Alright, so first thing I want to do is I want to press Control down-arrow.
Alright that let's us handle a whole bunch of different size datasets. So from B1 Press control down arrow.
And then down arrow again.
Type Total.
Here I'm going to press alt-equals.
And Ctrl-Enter to stay in that cell. Copy.
Control V to paste.
Simple enough. Stop recording.
Script name? Second hello world.
Click save.
Run, edit or delete.
I'll choose edit. Alright, so.
I don't know TypeScript, but The thing is I didn't know VBA when I had to switch over from Lotus macros to VBA.
That question is can we look at this and figure out what's going on and figure out you know how to do whatever, right?
So bad news, good news is they can't, they can't seem to record control down arrow! So this is always going to be 14 whether there are 13 rows or 17 rows or 100 rows.
Unknown event received with event ID 502.
What the heck does that mean? But they selected C14 and put in a formula there.
And then did a copy from C14 to D14 to E14.
The great news I guess.
This doesn't look horrible. I can sort of figure this out.
Well, they're calling it Office Scripts and there is a web page from Microsoft where they have some sample scripts, right?
Reading and log 1 cell to the console. Work with dates.
Apply conditional formatting. This is deep.
Create a sorted table. Delete resolved comments.
There's tutorials.
Boy, I love some VBA, right?
I understand VBA, I can write VBA from scratch. Never used TypeScript ever.
The big question is given some code, can we figure out what the heck is going on right?
So if you're a TypeScript pro, just stop right here 'cause you already get it.
But let's say you're a VBA person in the big question is can we figure out what's going on? So here's a data set. It has 18 students.
Their score and assignments, midterms and finals.
We want to calculate a total. We want to calculate a grade.
The name of the sheet is called Scores. Alright, so.
Over here, Async Function.
Let sheet equals Context dot Workbook dot Worksheets dot GetItem "Scores".
That's the name down there. Cool, OK, GetUsedRange.
And then this await, right? This is asynchronous.
That means wait till we tell that the process is finished like Application.DoEvents And this is Debug.Print right here.
Total students tells us how many students we have.
Now this one I can already tell is going to take some getting used to: Column A, in this world is column zero. It's zero based, so this is one.
This is to this is 3. This is 4, so when they talk about Student.Range .GetColumn 4 they're talking about column E.
All those great facts you know, like T is 20 sorry T is now 19 because A is zero. Get rows below.
Get rows below an it's row count minus one. Clear. They're clearing some formats.
Clear some formats alright, and then wait for that to finish. Declaring variables.
Hey Jordan Goldmeir. There you are. Look at me.
My second macro ever an I'm declaring variables! For this is for I equals 1 to row count minus one.
This is the TypeScript that we're starting at zero. Going until I is...
I don't know, and then that's an incrementer right there, right?
Get Max scores from first row, so if I is equal to 0 (in other words, if we are in row one). This is Row zero to the script.
Check this out, get the Max value for assignments.
That's a little RegEx right there. How cool is that?
And then for the midterm Max again get the digits. FinalsMax.
Get the digits. I'm bad at RegEx, but I'm excited that it's supported.
That's great and they're doing Debug.Print of the assignment max, midterm max, and the finals max.
Let row equals studentsRange values I, so that's saying that's assigning an object variable called row and it's equal to this row, right?
So if we're on like the third pass through here row three, so that's Row Zero, Row 1, Row 2, Row 3 is going to be talking about Carol.
So that variable Row is an object variable and this variable called Total is the first item in row plus the second item in Row, plus the third item in Row, and Remember, this is the Zeroth item in Row.
This is the first, second and third, so we're doing columns B, C and D just a little math in your head there where you have to add or subtract 1 Alright then.
Let Valid equals true, so there have a variable name Valid there, setting up as true.
If the first item is less than zero, so Row..
You know this row item one. This is item zero. That's item one.
If it is less than zero or if it's greater than Assignment Max, then we're going to color it in red.
I wonder what are the strings we can use here?
Don't know for now and then Valid becomes False.
If the second column Midterm is less than zero or greater than 30 then color that in red, right and then down here if it's not valid then continue. This is a Select Case in VBA.
Switch true if it's less than 60 then an F. Less than 70 D.
Less than 80 C.
Less than 90 B and then Case Else is now called Default. Alright, I'm getting this.
I'm following the flow here even though I've never written TypeScript before. It's close.
And then get cell.
I is the row number. Four is zero, one, two, three, four so column E.
They're writing the the variable total, and then F writing Grade. Color.
The grade; if it's equal to a, then we're going to green.
If it's equal to F or D, then orange, so we know we can use red, green, and orange, and then they're going to center the 5th column Remember...
Column 0 1 2 3 4 5 so center column F and then wait for the whole thing to finish.
I don't know. Let's go to try.
Holy sh*t.
OK.
Yeah it it works Alright.
Look at that.
Down here, the console. Total students 18.
Assignment Max Midterm Max. Finals Max.
Problems: no problems. No problems, no problems all right.
Yeah, perspective, right?
Perspective I've been afraid of this day for 16 years because now the 27-year clock starts ticking. But you know what?
I've been afraid of it for 16 years, but it's not that bad, right? It's TypeScript.
We'll learn TypeScript, just like we learn VBA. VBA from Lotus Command Line Macros was a huge jump.
This isn't that far of a jump. Its the same constructs.
We got to learn some new syntax, not the end of the world.
This is the point that video where I decide what book is most appropriate.
I don't have a book on this, but my friend Suat Ozgur who wrote a book on JavaScript UDF's - probably doesn't cover the scripting language at all, but might get you a little bit up to speed. I don't know how I feel today.
I really honestly don't know how I feel. Hey, but here we are.
Whether it's good news or bad news and I'm still not sure what news this is. I'm committed to bringing you the latest in Excel.
If you're interested in learning more about when new things hit Excel.
Please down below this video - click Subscribe and ring that Bell.
Feel free to post any questions or comments down in the comments below.
I want to thank you stopping by.
We'll see you next time for another netcast from MrExcel.
