Office Scripts vs Excel VBA (Everything you need to know)
HTML-код
- Опубликовано: 2 июл 2024
- Office Scripts vs Excel VBA (Everything you need to know)
In this video I explain the brand new Office Scripts for Excel. Office Scripts is Microsoft's proposed replacement for VBA and it's currently available in preview mode. Office Scripts uses the programming language TypeScript.
I also explain the background to Office scripts and discuss if it will replace VBA and will VBA become obsolete.
Then I show how to use Office Scripts with office online and with any version of Excel from 2013 onwards.
Finally we get to the best part! A Task where we compare VBA Code with TypeScript code. I think you will agree that TypeScript is a game-changer.
SUBSCRIBE TO THE CHANNEL: bit.ly/36hpTCY
DOWNLOAD THE CODE: Get the VBA code for this video here: bit.ly/2XdftAr
Excel VBA Training
The Excel VBA Handbook Course(TheExcelVBAHandbook.com)
Webinar Archives - 60+ Hours of VBA training(excelmacromastery.com/excel-v...)
Free Excel VBA Resources
Excel VBA Articles (excelmacromastery.com/vba-art...)
Useful VBA Shortcut Keys
========================
Search keyword under cursor: Ctrl + F3
Search the word last searched for: F3
Compile the code: Alt + D + C OR Alt + D + Enter
Run the code from the current sub: F5
Step into the code line by line: F8
Add a breakpoint to pause the code: F9(or click left margin)
View the Immediate Window: Ctrl + G
View the Watch Window: Alt + V + H
Auto complete word: Ctrl + Space
Get the definition of the item under the cursor: Shift + F2
Go to the last cursor position: Ctrl + Shift + F2
Switch between Excel and the VBA Editor: Alt + F11
View the Project Explorer Window: Ctrl + R
Get the current region on a worksheet: Ctrl + Shift + 8(or Ctrl + *)
To move lines of code to the right(Indent): Tab
To move lines of code to the left(Outdent): Shift + Tab
Delete a Line: Ctrl + Y
View the Properties Window: F4
![ian - Fit Check (Feat. VonOff1700) [Official Music Video]](http://i.ytimg.com/vi/sDGWDBg45Dw/mqdefault.jpg)
I‘ve been waiting for TypeScript vids to emerge. This is where I want to go.
Welcome to this new world, I have been experimenting with Script Lab, Office Scripts, VS, VSC, Node.js Yeoman... during last 10 months and it´s amazing!!!!
Great, Great. You summarize many hours of study, which encourages me to try Office Scripts. Thank you very much indeed.
You are welcome!
Thanks so much Paul, delivered with your usual clarity....
Thank you very much, I've been waiting for this kind of info
You're welcome James.
Hi Paul, thx very much to covering this argument, would be nice if you consider to make a serie of videos about this.
Great. Last I did VBA software for a client and he wanted to use also in his Mac. Now with Typescript, all those limitations will be over.
I am eager to learn more! My organization has most people on Excel online, so being able to automate things in the online version would be amazing!
A modern platform for Excel. Awesome. Looking forward to your Office Scripts handbook!
Thanks David.
Great Tutorial! I'm a Developer of Office Scripts, and I learned a lot from this video.
Glad it was helpful!
This is really cool introduction! Thanks!
Haha, was a Cobol programmer in the 90's. Good language that get's the job done! Don't listen to those who try to bad mouth it. Probably those who wrote all the shitty code that I had to fix.
Great to see that Microsoft is moving on and offers Office Scripts now. Office Scripts aka TypeScipt is so popular already in the web development area, you can benefit from all the available knowledge there! Furthermore it increases the possibility to use Excel as calculation engine in the background and integrate it with Web applications etc.! Exciting opportunities!
"Don't fold, spindle or mutilate."
Thanks Paul. Very informative.
Very welcome Wyatt
I learned COBOL in 1971 using a Honeywell 200 mainframe with 32K memory!
Regards from Brazil! Thanks for share your experience Paul!
Glad you enjoyed it!
Very cool features, it’s bringing sort of VBA ‘online’ this will certainly give a rejuvenated feeling to Excel..
Hi Paul.. thanks for the look into TypeScript. I hope for a long life remaining for VBA.. but always interested in new technologies. As you demonstrated.. some things will be easier.. maybe some things harder. It's a brave new world in more ways than one. I'm sure with some effort and the good instruction available at your channel, your web site and your courses, we'll all get the hang of if eventually. I guess you'll have to change your name to.. Excel Macro and TypeScript Mastery.. haha!! Thanks again and looking forward to more insights into TypeScript. Thumbs up!!
Thanks Wayne. It's going to be interesting to see how this goes.
Great introduction to typescript working with excel. I’m looking forward to working with typescript to find new methods for moving data in and out of excel worksheets with json objects then are available in native VBA.
Great to hear!
Really hope you do more videos using typescript in excel!
Can you please discuss/ compare execution speeds, efficiency, etc. in an upcoming video?
Wow, Thank you very much. I use VBA every day but I like TypeScript. I think it's the way of the future as more applications are run exclusively on the net.
Once again, cool stuff, Paul. I started a couple of months ago with this new technology out of curiosity - I could already program in JS, not TS though -.
Although not the topic of this video, since you made the use of Interfaces possible, I was wondering if you knew if it's possible to use Delegates as we do in C#. Any idea?
Thank you and keep up the god work!
I am interested to see more examples using type script. There have been certain instances where I would have liked to use pop out frame (not sure of technical definition but the window you get when you press the 'Ideas' button is an example) and I think this can be done with type script.
It´s so true, I am an Engineer and use VBA; but what is spectacular is that I just learned how to really really really nicely programm stuff that is a huge mess in my code. A COLLECTION!?
Very good introduction to Office Scripts, in comparison with VBA
Thanks. Glad you like it.
Hi. I found your video a very well structured and explained one! Congrats for that!. A good one appetizer!!
I'd like to know, if it is posible to edit the Office Script code in Visual Studio Code (it would be more confortable to work with), of course, using the excel-API. After this amazing start, do you plan, in next videos, to explain the new code singularities ? Thanks in advance!
I like the approach to integrated Typescript in Excel. Currently I have my Typescript with Angular for a website project and you can do a lot with it.
Just image to build your UI in HTML/CSS and even add packages like Angular or other stuff. That could be amazing in the end.
Yes, The potential is huge.
Not sure how I’d get on with Office Scripts. Perhaps I’m a bit too comfortable with good old VBA.
New Office Script editor looks alot like IntelliJ IDEA. Thank you so much for this info.
You're welcome.
Loved it!, much useful for VBA developers, and TS is used in Web development too is any feature is there like using HTML screens on Excel.
Thanks for all your knowledgeable videos over the years. I’m in the middle of transitioning what I can from VBA over to typescript as my organization leans heavily on office 365, one drive, and SharePoint. One thing I can’t quite figure out yet is how to refer to other workbooks by their file path, either on a local network, or at some URL (for SharePoint documents). Getting and using the active workbook is no problem, but trying to pull in data from other workbooks has left me scratching my head.
Any luck figuring this out ?
Use Power Automate to create the link between workbooks. Use an Excel connector to transfer your table data in one workbook to another table in another workbook. Linking to external files from within an Excel Online workbook is also, now, possible. Microsoft are rolling out that out now.
@@bli240 Use Power Automate to create the link between workbooks. Use an Excel connector to transfer your table data in one workbook to another table in another workbook. Linking to external files from within an Excel Online workbook is also, now, possible. Microsoft are rolling out that out now.
This will be Absolutely genius!
I'm looking forward to the full release.
I've been dabbling a bit with script lab. I quite like it so far. It doesn't move easily with the workbook, so it's not that useable for general day to day stuff, but I do create a number of com addins, so I'll definitely be converting and improving those with typescript and HTML.
Thanks for sharing!
Thanks for your great videos! Truly helpful! I guess there's a lot of potential for typescript, because i know js a bit (I already coded pacman in a html) and it's really powerful coding language! BR from Germany! ;)
Thanks BR. I wrote Space Invaders in JS a few years back:-)
@@Excelmacromastery that's also pretty cool!:) did you use external pictures? For the reason having one file, I used only css for graphical feedback. It was also interesting!
I hope you'll also continue with you vba videos, because my current project is an access db for the work and you did help me truly much!
Thanks for everything! ;)
Hello Paul, would appreciate you if you make a small video on Excel VBA iif function.
Thanks
Great video! I knew it was good idea to follow You. I will be very happy with more Office/TypeScript videos! And probably not only me :)
By the way, would it be easier/faster to use here advanced filter instead of looping in VBA?
Great video! I'm super excited to start trying Office Scripts as soon as my Company enables them. Until then, do Office Scripts have access to user's OS file system? Like fso does for VBA, os for python etc. I'm aware that in JS you need node.js for that, but not sure about that whole Microsoft's OfficeJS enviroment. Thanks and once again - awesome video!
Excellent tutorial and I hope you release more video on Office Scripts. Can anyone recommend a good book on using Office Scripts with Excel.
I see its importance with companies storing workbooks on the cloud (ie SharePoint). I however fear that those same companies IT departments will not allow the administrative access to manipulate online files. This is one of the factors that stopped Python libraries from taking off, as third party downloads are restricted.
VBA becomes one of the few tools I can use to automate my work. I've even done a little Acrojavascript with pdfs. I see the benefit of having the skills and knowledge to do this. But it's like being hired as someone that knows how to operate a digger and being told your only allowed to use a garden spade. :-(
I heavily automate my excel work with vba. Officescript addins are certainly powerful but they're for full time developers. Development and deployment of an addin isn't as easy as with vb.
Many thanks for letting us know the new development. This is excellent video and hugely helpful. Office Scripts seems to be more powerful than the VBA..
Glad it was helpful!
Thank you for another informative and helpful video. I think I will wait until Office Scripts become part of the desktop version of Excel. Personally, it's disappointing that Microsoft didn't integrate the .NET framework with office and produce a new version of its flagship suite named Office.NET. Since .NET already has the Interop libraries(?) I would hope that they could be directly integrated into Excel, where the full power of .NET could be exploited within the workbook environment. I can dream, can't I? LOLZ
Can you make one on loops and more useful things in Type Script?
You are a great tutor!
Can you please upload the Advanced filter webinar in RUclips? That's very useful. Like how you did arrays.
Thank you!
All of my previous webinar are available here excelmacromastery.com/excel-vba-webinars/
I can see that the learning curve for scripts is going to be steep, but hey I love a challenge.
IMPORTANT: Office Scripts were updated after this video was released. One big change was that synchronous calls were made available. This makes quite a difference to how scripts are written. The newer Office Scripts API is not available through Script Lab.
PLEASE SEE THESE VIDEOS FOR MORE UP TO DATE INFORMATION ABOUT OFFICE SCRIPTS:
"Introduction to Office Scripts for Excel" for the London Excel Online Meetup(ruclips.net/video/2fHLfyOEx4Y/видео.html)
"Create a Powerful Office Script " for the Toronto Excel Online Meetup(ruclips.net/video/2cbMglpj6pE/видео.html)
"How to Automate Your Work with an Office Script Bot" on this channel(ruclips.net/video/me4q6g04vsM/видео.html)
I hope you enjoy this video on office scripts. Let me know what you think of Office Scripts in the comments below.
You can download the code in the video from here bit.ly/2XdftAr
Is the final script embedded in the workbook and can we link the script to a button?
Hi Mate, i am on office 365 e3 i guess. I made sure that the group org setting allow office script. but still unable to see the automate ribbon and can access it at all. any advice? or its still available to selected users ?
Thank you!
You're welcome!
The VBA code has the advantages that it is easier to read and therefore self-documenting. And you could easily make it neater, for instance by defining a default value when declaring. Typescript is different but not necessarily better.
Hi, Paul,
Thanks for yet another excellent video! I have a few questions for you.
1. How easy will it be to learn OfficeScripts? (Background comment: I'm mostly self-taught in programming. I learned BASIC, FORTRAN, and COBOL formally decades ago as a college undergraduate, and I wrote programs on punch cards. I know this dates me. LOL! However, I have taught myself modern programming through online resources such as yours. JavaScript is not a language I have learned, though it is on my list of languages to acquire in the future. Perhaps I should now say, "near future.")
You pointed out (starting at 5:20) that OfficeScript programs reside under OneDrive at OfficeScripts\Documents (at 5:35).
2.a. Is this going to be the way that scripts will be associated with Excel files permanently? Or is this a temporary feature for purposes of this OfficeScript programming preview?
2.b. If I write scripts for multiple Excel files and reuse code (which is something I do now for different projects), how does Excel know which code file is associated with which Excel file?
3. Notepad++ allows text to be stored with language support. Do you know if anyone has added (even experimentally) language support for OfficeScript? (I know it's not available in the most recent version of Notepad++.) I've never tried to use User-Defined language support on Notepad++, so Normal Text would be my fallback if there is no OfficeScript language support that is available.
4. I support a range of Excel from 2010 to 2019. (My employer has no plans to move to Office 365.) If I write Excel OfficeScript macros, will they run on these older versions of Excel? I'm doubtful, but have you heard anything about OfficeScript macros running on older Excel versions?
5. As a manufacturer, we have lots of computer-controlled equipment that doesn't have online access outside of our local network. Will OfficeScript macros run on computers that are not connected to the Internet?
Overall, the language seems to write tighter code. That should help with long-term maintenance, especially for long, complicated scripts. However, the slower execution speed does not impress me.
Again, Paul, thanks for another helpful video!
Thank you looks very powerful but i dont know if i will be able to completely change my vba code to officescripts easily. I need to make my application cloud based
Does anyone know if Microsoft has indicated when Office Scripts will stop being a preview feature and will get a full editor?
Nice video and example Paul. Is it possible to run a PowerShell or Bat file from the Office Script? I am trying to do but I can't to see how to and the documentation of Office Script there's no any information about it =(
thanks
Cool!!!
great
Hi Paul, excellently explained! Do you know of any literature (book) about Office Scripts for Excel?
I don't think there is any at the moment. I think we'll have to wait until the full release of Office scripts.
if we can start coding in excell like we do in javascript using typescript, it's huge !!!!! Next excell apps will be coded into vs code :)
Thank you for the video!
Please, can we ready/write persistent browser data (IndexedDB, HTML Storage, Cookies, etc) from Excel 365 typescript?
Yes, you can read data from a URL.
Amazing. Did you see the "fruitRange.values.filter" and "result.filter" functions passed a function as a parameter?
A functional language - in the form of the Office Scripts - is coming to Excel and other office products. It will make Excel THE go-to platform for data analytics and modeling. A new era is dawning. We all will be better for it.
It's very exciting.
Agree with you 100%!
How similar or different is Office Script with Google App Script? If you could make a video comparing these two would be great.
Hi, does somebody knows wich is the most efficient way to find a piece of vba code in a folder with tens workbooks without open them under windows 10? Using file explorer I cant find them. thank you!!
I have hoped to find some examples of establishing communication between Excel and my different measuring instruments. The idea is to have my data into Excel, where I can do all the common operations like filtering, finding minimum, average, maximum etc. And of course plot trends other interesting ways. But the issue is how to get the data from the instrument into one or two columns of Excel. The hardware can use IEEE-488, USB or or Ethernet interface. Some even the old original Serial port, but that can be run through an adapter cable into the new USB. So, how do I proceed? Old instruments had their unique remote control "language", mostly 2 or 3 character commands. Newer ones have a more general language called SPI that covers essentially all manufacturers and all kinds of instruments. But the same issue remains: How do I handle the necessary commands to the instruments within the Excel (VBA or other means). And then collect the data, as the final hurdle.
VBA can interface to the API of a dll-file. Many years ago, I used VBA to access an oscilloscope that was attached to the parallel Centronix-port. I could read the digitised signal-array through the Centronix interface by means of the dll that offered an API. A declaration in the VBA-code was necessary of course. Something similar was also available to access the RS232-port. I could read from it and write to it from Excel.
I expect TypeScript to allow for such functionality as well (now of course focused on USB and not Centronix or RS232 anymore).
In example 1, why is Sheet2 activate and used to explain the concept when Sheet1 is referenced in the code?
Does the client need to have scripts enabled too or only the developer needs scripts enabled?
does this have a CopyFromRecordset yet so I can pull data from Access and SQL Server ?
I have SO MANY vba scripts that automate database queries and pivot table creations
Hi. well explained video. Do you know is there any TypeScript Handbook for Excel? Thanks.
Not yet because it's still a work in progress.
If userforms are no longer supported in Excel 365 with Office Script, then what userform alternatives are available using Office Scripts? Does Office Script support the creation of dynamic tags to create simulated userforms? Or, will one of the Sheets need to be reserved as a "userform" alternative and controlled by Office Script?
The recommended solution to this is to use Microsoft Forms
(forms.office.com) in conjunction with Power Automate.
The second filter function should be "map" instead of "filter". Filter function is supposed to return boolean function but it is returning a computed value which is coincidentally getting evaluated as truthy and thus being added to the returned array of filtered value. So no actual filter is happening and achieving same results as map function.
I would like to see an example of a user defined function, which is most of my VBA code in Excel. Also wondering if there will be any translation options to convert VBA to TypeScript.
I doubt there will be a translation option unless there is a 3rd party release. But who knows?
One of the things that makes Office Scripts easy to get started with is by using the Macro Recorder. You can learn about the syntax that way.
I didn't like the fact that two separate method calls were required to do the filtering and data processing intended (as well as two distinct variables to hold the results). Was that a language feature or a coding choice? Can the function passed to the filter method accomplish more than a one-step data processing/filtering in one call?
That is possible. I believe he has done it to be more explanatory.
The callback function in filter can have mulitple lines / steps. The final result is that:
let result = fruitRange.values.filter(function(value, index) {
value[1] = value[1] * 2;
return value[0] == 'Limes';
});
I wondered myself about the usage of filter to change the value in the array. I believe this works since an array is an object and not a single property like string/number. For just a simple filtering and a 2-wide array you can have this one liner (using arrow functions, in other languages called lamdba function or blocks)
let result = fruitRange.values.filter(value => value[0] == 'Limes').map( value => [value[0], value[1] * 2] );
I wanted to know how we can convert vba code into office script?
Hi Paul,
Good to see that Microsoft have finally settled on a successor to VBA, not before time. I assume Office Script will allow us to tap into the huge JavaScript ecosystem? I have used JavaScript to program Google Sheets and it's really good, they call it Google Apps Script but it is really just JavaScript. Sheets has been using an old JS version based on ES5 until recently but they have now updated to a modern V8 version that supports all the latest JS syntax. I'm looking forward to seeing more on Office Script, thanks for sharing!
Glad you enjoyed the video Michael. It's going to be interesting to see how it develops in the coming months.
Hi Michael, Excel Online uses TypeScript. Office Scripts is TypeScript. TypeScript is a super set of Javascript. Microsoft are, currently, updating Office Scripts to match a lot of the functionality that Google Sheets provides.
@@johnfromireland7551 Cool! Not used TS but it looks cool and is supposed to make working with JS less painful. Having said that, I think JS has really improved in recent releases. Either way, I'm looking forward to trying out TS in Excel for real. Cheers - Mick
its confusing using result.filter to do the multiply by 2. This is why there is a resut.map function
Great video. Problem in having is I don't really know the security model I need to implement this. We are slowly moving to a locked down cloud environment and how will I give end users the appropriate permissions.
There again if I'm accessing databases and such do they also have to be in Azure. So many questions.
It's still in preview mode so there are many questions still to be answered.
@@sparky191 I would use Azure Logic Apps to integrate with Excel Online. It has an Excel Scripts Connector. Within your Logic App you can, of course, use the Blob Container Connectors which would then link to your Excel Script Connector. Explore Azure Logic Apps and its Connectors to learn more.
If Microsoft wants to rule out VBA, it would be better for it to choose Python which is much easier. Python is the solution, but for older VBA is the best
Many people would have liked Python. I'm not sure why TypeScript was chosen over Python. It may be more suitable for what Microsoft are trying to achieve.
Hi...okay lets assume that office script/type script that came in java script or maybe python will replace vba in the future, then what will happen to the millions of workbooks that contains VBA?Can it still be use in microsoft office lower version like 2010 that i used?hope that still be use in lower version...
Excel now has a built in user written Filter and Sort by function commands too.
Really? It has filter and sort for arrays in VBA?
@@Excelmacromastery So its just as good as Typescript then. Bet they bring out a translator. It Is all good considering what will be possible with websites pulling up dashboards and stuff, not just limited to using SQL and ASP.net.
I am using Macros with the VBA language in which I use the feature "change". Is this also possible on Excel Online? Because I see in your videos that the "Macro/program" only runs everytime you click the "RUN" button. I want my Excel online to run everytime a cell changes and not to hit the run button. Is this possible?
Will you do more office scripts videos?
More videos from Paul are always welcome. Here's one source where you can find Office Scripts projects:github.com/sumurthy/officescripts-projects
I hope they make it super easy to work and manage a project in VS Code and source control. If you could control your excel instance like a local dev server, without needing to worry about boiler plate around connecting a project to Excel then uptake will sky rocket.
Absolutely. I'm looking forward to the full release and I think they will definitely try to utilise VS Code.
You can use Vs code to build functions and addins in Excel already using node and yeoman. there is documentation on Microsoft website. Sigma coding also has a good starting point on RUclips too to create a function. I could see using the script lab for getting base code and then switching to Vs code to formalise it. I hope they'll do something that allows workbook specific code like VBA. I'm surprised that they haven't included this as the code is just simple text. Save it as part of the excel document XML so that it remains part of the workbook. That way script lab knows where to look to retrieve it. It's almost like Microsoft are proposing that everything takes the form of an add-in, which anyone who's built a spreadsheet with VBA knows isn't the case. The "snippet" will often not be reusable and would often be best contained within the workbook.
@@ricos1497 Hi Rico, Microsoft's new way of building Office extensibility is, now via "Office AddIns". It allows you to build one AddIn that runs everywhere - Mobile, Apple, Windows, Cloud. Office Scripts is another programming extensibility model separate to the one for Office AddIns. The Office Scripts Programme Team, at Microsoft, are extending the Script Pane and the Automate menu to make coding an easier experience. Script Lab is yet another option separate to Office Scripts. The significant difference with Office Scripts is that it reacts to Power Automate event triggers. That is, there is no way to trigger an event for your Office Script (except for clicking the Run button!) without building a Power Automate Flow first. That said, the Flow is incredibly simple. It has only two connectors! SharePoint File Changed and Excel Script Connectors. I am hopeful one day Microsoft will enable a method for allowing VBA code to run against Cloud resident files. However it must be noted that Microsoft has stopped support and development for VBA because it has decided that TypeScript, Javascript and .NET will be the languages for the future. It is my expectation that sooner rather than later the millions of VBA applications already written will have to be rewritten in TypeScript(Office Scripts) because the whole planet is moving to cloud based systems. I will be posting Office Script videos on my Channel here in the near future. ruclips.net/video/HBiGEkzmkgo/видео.html
Regards, Les
@@johnfromireland7551 yes, interesting times. Obviously, since writing my comment, lambda has appeared to take the place of VBA functions, so it looks like VBA could be for the bin in the next few years. With dynamic arrays, lambda and the power tools, much of my VBA can be deprecated anyway. Most of it relates to refreshing workbooks and selecting cells on open, manipulating shapes, that sort of thing. Easy to do in type script, but not particularly reusable across workbooks. I'll check out your videos!
Can we Unprotect a password protected sheet, modify and then Protect the sheet again with the same password
Hi Friend!! Fom my point of view i would say that Type Scrip will replace VBA , i think has a more friendly capabilities than VBA and furthermore offers you the possibility to get connected to the Web and serveal other applications as well. A GREAT WELCOME TO TYPE SCRIPT !! Now it is time to lear this new lenguague!!
Thanks for sharing
Just tell me the VBA code to press the ALT+tab keys WITHOUT turning the numlock off. This has been the most frustrating thing I have encountered in Access but I will settle for an explanation in Excel if it can be translated to VBA for Access.....I need to temporarily shift control to other programs, but cannot find a way to do it without turning the numlock key off....
The funny thing is when you want to share your office addins with other people in other computers or run them from the ribbon (I mean not running them from the editors "Script-Lab or Office Scripts"), that´s not easy at all!!!
When the full version is released I'm sure they will improve this.
Don't think it's everything I need to know, but it's a start.
What was missing?
@@Excelmacromastery For example, what are these brackets - { }? For you, the purpose may be obvious, but it wasn't explained.
Amazing thanks Paul. I think scripts are somehow complicated though. I'm so used to VBA maybe its just mindset.
Use the Office Scripts macro recorder to get started on your learning journey with Office Scripts. This is what everyone did to learn VBA.
@@johnfromireland7551 Agreed thanks John for the motivation :-)
Maybe I am missing something but the fact that the typescrip files cannot be directly pinned with an excel file seems like a major weakness. Obvioualy vba does this, but also Google app scripts has the ability to pin scripting code to a specific file.
Most automation I do is specific to the file so having to go to such extensive effort seems like I will be inclined to stick with vba or continue to explore what Google app scripts is offering.
Good video though. I think there is a lack of such videos like this that on the web at the moment that offer a bridge for vba users to typescript.
UPDATED : Hi Steve, Office Scripts for Excel Online has been updated since the Public Preview started. There is, also, no need to use the async and await keywords. The code is a lot simpler than the example shown in this video. You can, also, pin your script to a workbook.
👍👍👍
Hello Paul, thank you for this video. It would be nice to use Office script to write applications for excel online. This is one of the drawbacks I find with vba, the users can't access the files online.
I have a question regarding the vba codes, I dont get the line of code - Sheet1.Range("F" & row).Resize(1, UBound(item, 2)).Value = item.
the part i dont follow is == Resize(1, UBound(item, 2)...what is the number 2 referring to? does it mean, second column in the Write range? thanks, Paul.
Ubound(item, 1) is rows, Ubound(item, 2) is columns. The number refers to the dimension of an array.
You shouldn’t use filter to edit the values, it just happens to work. You should use forEach or Map. It’s not working the way it appears to. Filter doesn’t create copies of the values it just puts the references in a new array. Here you’re just abusing filter to get a loop. There’s other more appropriate higher order functions that will achieve this without strange possible side effects.
Interesting point! The example script in this video does appear less than easy to read. And certainly not for beginners.
Does anyone know when this will leave the preview phase?
No idea. I think we won't know until it happens.
Looks officescript could process jobs where VBA crawls when you have millions of rows in excel . Such huge excel can be easily loaded in Excel Online and I can run a xlookup 30secs, which takes 40min on excel desktop with 0.2 million rows
houlala trop compliqué pour des povres secretaires ; sinon magnifique pour des programmeurs !
It looks great! Thank you very much.
I am struckling with the following.
My school works in Microsoft 365. I have to invite 24 groups of students to the online classes with Microsoft Teams. In Outlook, I create a Team Meeting and invite the students. I have the schedule in Excel, with date, start time and end time of the student's lesson and email address. The lessons are scheduled randomly. So I can't use the recurrence option. Because all is in the online versions, I can’t use VBA for this. I think Typescript is the solution, but Typescript is new to me. Do you know if there's a script available?
KR
Willem
Hi Willem, Use Power Automate and Office Scripts in Excel Online to do this. You can use the Loop Connector in Power Automate.
When using scripts on a desktop PC do we stil need to use "sync"?
We do in ScriptLab.
Great 👍
Is it possible to protect these scripts as We do VBA Project Protection using password?
Hope in comparison VBA code part, We can use a single For loop without collection to achieve the desired output
Yes. The security of scripts will be different thought and we won't know the full story until future releases of office scripts.
You can use Loops in Office Scripts. Here is an example :
RangeStrValues.forEach((rowItem, rowIndex) => {
RangeStrValues[rowIndex].forEach((columnItem, columnIndex) => {
let cellValues = RangeStr.getCell(rowIndex, columnIndex).getValue()
if (cellValues != '') {
Rangecount++
}
})
})
The => symbol is known as mapping. You can put protection on your Office Script files (with osts extension) using the SharePoint Online (SPO) functionality.
Also, office scripts are still incomplete, for example, I could not find the AdvanceFilter method. If someone found it, please share the knowledge.
How to make ScriptLab scripts run on the workbook open in OneDrive?
Using Power Automate.
I am not sure why you seem to equate less readable code with being more powerful. a FOR loop although it uses more lines of code( though you can actually put it one line) it is quite simple and readable. I would more equate power with function, some function that cannot be done or is so much simpler. what you describe is terseness which just making code more unreadable. I would, of course, have to use typescript a lot more to see if it is actually more powerful. The real use of typescript is for my applications to be usable on any platform
Hi Steve. I don't equate less readable code to be more powerful. The array filtering I did with Typescript does not exist in VBA.
Once you understand the typescript code I think you will find it's actually as readable as VBA.
Hi Steve, Here is one method for looping through cells in a worksheet. Office Scripts works for Excel files online. That is, the SharePoint Online platform.
RangeStrValues.forEach((rowItem, rowIndex) => {
RangeStrValues[rowIndex].forEach((columnItem, columnIndex) => {
let cellValues = RangeStr.getCell(rowIndex, columnIndex).getValue()
if (cellValues != '') {
Rangecount++
}
})
})
@@johnfromireland7551 it seems useful Let me give it a try thanks
Is Office script more powerful than Google sheets script?
Is there any tool to convert vba macro code to office script code?
Not at this time.