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!!!!
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!!
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!?
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!
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.
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.
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.
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.
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.
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.
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! ;)
@@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! ;)
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.
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.
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 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.
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.
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..
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.
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).
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] );
Comparison of VBA to office script is like comparing SpaceX rocket to turtle. I believe all those folks who think office script can kill VBA have no idea what VBA is. I have 13+Y experience in VBA and use office script since its very first day. VBA is a complete powerful language that can connect with COM, OLE and covers incredibly wide range of features. Office script?? Have you ever tried to debut a complex code in it? It is an infant compared to VBA, not to say anything about its slowest speed vs VBA or event handling. Office script does not even handle excel-level events. Office Script better supports web services?? What do you know about VBA & Selenium? VBA has complete control of HTML DOM with Selenium. No bro, don't get fool! Yes, its true VBA is more complex to learn, but when mastered it is a complete power tool in your hands. Hundred's of millions of powerful VBA application are currently running in the world. What can stop and replace it? A toy Office Script that does not even connect with mostly needed desktop technologies? One thing is clear. Replacement of VBA with Osct is a dream of those people who wanted but could never master VBA. And they hope that one day the complex guru(VBA) will go away and more friendly Office Script will make their life easier and happier. But great things never come easy. You get what you pay for and this will be ever true for office scripts. VBA masters will continue development of most wanted applications, while Office Script fans will continue playing with trivial automations and web services.
Hi! I agree with you, I love Vba! Have you ever tried using classes do make dynamic animation in user forms? I did it and I was amazed what you Can achieve with Vba, which is considered Low level programming language for some reason...
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 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.
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.
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
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
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 =(
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!!!
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.
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!
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.
@@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.
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!
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!
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++ } }) })
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!!
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.
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....
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 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!
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
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?
I don't think I, as a non-developer that has been using basic as only language for 40-odd years, will ever learn this kind of language. It will take me weeks of training since I don't understand the concept. By the time I know a little about it, much less than the average 'code knocker', I will be retired... So, I'll pass. Nice video though but it is not my glass of beer!
Happy retirement. VBA does not run online so Office Scripts was built to fill that gap. Millions of VBA applications will fail and are failing, now, because their clients have moved their systems to the Cloud. Specifically Office 365 (SharePoint Online).
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?
Hi Sir, How are you? Recently I've come across 2 major problems in my job. Can you please tell me or better if you make videos on these 2 topics separately. It has become now day-to-day job in my office for last 2 months. How should I approach to these 2 problems through VBA given below? 001) How to auto fill Web Form using Excel Worksheet Data through VBA? Once it fills up the entire Form, the Submit Button (in Web Form) will be clicked automatically to store the Data in Server. And, then proceed to the next Row of Excel Data. This is how the entire procedure goes on till it reaches the Last Cell Data in Worksheet. 002) How to convert Word to PDF, PDF to Word or Excel to PDF and PDF to Excel using VBA? Suppose, I have 10 Doc Files, 5 Excel Files in my "D" Drive. I want to convert them into PDF and put them in a newly created folder.
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...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...
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!!
@@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 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.
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 UA-cam 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. ua-cam.com/video/HBiGEkzmkgo/v-deo.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!
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.
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
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.
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(ua-cam.com/video/2fHLfyOEx4Y/v-deo.html) "Create a Powerful Office Script " for the Toronto Excel Online Meetup(ua-cam.com/video/2cbMglpj6pE/v-deo.html) "How to Automate Your Work with an Office Script Bot" on this channel(ua-cam.com/video/me4q6g04vsM/v-deo.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
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 ?
They might! If you upload your Libre Office worksheet file (it needs to be saved as xlsx first) then try and run your script against it. Libre Office does allow you to save your files as xlsx, right?
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
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!!!!
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.
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!?
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."
Very good introduction to Office Scripts, in comparison with VBA
Thanks. Glad you like it.
Great, Great. You summarize many hours of study, which encourages me to try Office Scripts. Thank you very much indeed.
You are welcome!
I learned COBOL in 1971 using a Honeywell 200 mainframe with 32K memory!
Great Tutorial! I'm a Developer of Office Scripts, and I learned a lot from this video.
Glad it was helpful!
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!
Not sure how I’d get on with Office Scripts. Perhaps I’m a bit too comfortable with good old VBA.
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.
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!
Hi Paul, thx very much to covering this argument, would be nice if you consider to make a serie of videos about this.
I‘ve been waiting for TypeScript vids to emerge. This is where I want to go.
Can you please discuss/ compare execution speeds, efficiency, etc. in an upcoming video?
Thanks Paul. Very informative.
Very welcome Wyatt
Regards from Brazil! Thanks for share your experience Paul!
Glad you enjoyed it!
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.
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.
Thank you very much, I've been waiting for this kind of info
You're welcome James.
Thanks so much Paul, delivered with your usual clarity....
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!
This is really cool introduction! Thanks!
Really hope you do more videos using typescript in excel!
This will be Absolutely genius!
I'm looking forward to the full release.
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.
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! ;)
New Office Script editor looks alot like IntelliJ IDEA. Thank you so much for this info.
You're welcome.
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.
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 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.
Very cool features, it’s bringing sort of VBA ‘online’ this will certainly give a rejuvenated feeling to Excel..
You are a great tutor!
Can you please upload the Advanced filter webinar in UA-cam? That's very useful. Like how you did arrays.
Thank you!
All of my previous webinar are available here excelmacromastery.com/excel-vba-webinars/
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%!
Does anyone know if Microsoft has indicated when Office Scripts will stop being a preview feature and will get a full editor?
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!
Can you make one on loops and more useful things in Type Script?
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.
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).
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] );
Comparison of VBA to office script is like comparing SpaceX rocket to turtle. I believe all those folks who think office script can kill VBA have no idea what VBA is. I have 13+Y experience in VBA and use office script since its very first day. VBA is a complete powerful language that can connect with COM, OLE and covers incredibly wide range of features. Office script?? Have you ever tried to debut a complex code in it? It is an infant compared to VBA, not to say anything about its slowest speed vs VBA or event handling. Office script does not even handle excel-level events. Office Script better supports web services?? What do you know about VBA & Selenium? VBA has complete control of HTML DOM with Selenium. No bro, don't get fool! Yes, its true VBA is more complex to learn, but when mastered it is a complete power tool in your hands. Hundred's of millions of powerful VBA application are currently running in the world. What can stop and replace it? A toy Office Script that does not even connect with mostly needed desktop technologies? One thing is clear. Replacement of VBA with Osct is a dream of those people who wanted but could never master VBA. And they hope that one day the complex guru(VBA) will go away and more friendly Office Script will make their life easier and happier. But great things never come easy. You get what you pay for and this will be ever true for office scripts. VBA masters will continue development of most wanted applications, while Office Script fans will continue playing with trivial automations and web services.
Hi! I agree with you, I love Vba! Have you ever tried using classes do make dynamic animation in user forms? I did it and I was amazed what you Can achieve with Vba, which is considered Low level programming language for some reason...
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.
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!
Hello Paul, would appreciate you if you make a small video on Excel VBA iif function.
Thanks
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.
Excellent tutorial and I hope you release more video on Office Scripts. Can anyone recommend a good book on using Office Scripts with Excel.
Does anyone know when this will leave the preview phase?
No idea. I think we won't know until it happens.
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.
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
I can see that the learning curve for scripts is going to be steep, but hey I love a challenge.
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
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 =(
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.
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.
How similar or different is Office Script with Google App Script? If you could make a video comparing these two would be great.
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.
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!
Does the client need to have scripts enabled too or only the developer needs scripts enabled?
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.
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.
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.
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!
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!
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
I wanted to know how we can convert vba code into office script?
I can't see this ever doing what my VBA does - I am too old to learn more code !
Your VBA code has, already, stopped working for files uploaded to SharePoint Online and OneDrive...
In example 1, why is Sheet2 activate and used to explain the concept when Sheet1 is referenced in the code?
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!!
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.
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 :)
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
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.
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 :-)
Can we Unprotect a password protected sheet, modify and then Protect the sheet again with the same password
How to make ScriptLab scripts run on the workbook open in OneDrive?
Using Power Automate.
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....
Is there any tool to convert vba macro code to office script code?
Not at this time.
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.
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
I already enable the Office Script and waiting for 2 days. but can't showing Automate tab for excel online.
Do you have any idea about this?
Check with Microsoft support is your best bet.
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?
I don't think I, as a non-developer that has been using basic as only language for 40-odd years, will ever learn this kind of language. It will take me weeks of training since I don't understand the concept. By the time I know a little about it, much less than the average 'code knocker', I will be retired...
So, I'll pass.
Nice video though but it is not my glass of beer!
Happy retirement. VBA does not run online so Office Scripts was built to fill that gap. Millions of VBA applications will fail and are failing, now, because their clients have moved their systems to the Cloud. Specifically Office 365 (SharePoint Online).
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?
When using scripts on a desktop PC do we stil need to use "sync"?
We do in ScriptLab.
Hi Sir,
How are you? Recently I've come across 2 major problems in my job. Can you please tell me or better if you make videos on these 2 topics separately. It has become now day-to-day job in my office for last 2 months. How should I approach to these 2 problems through VBA given below?
001) How to auto fill Web Form using Excel Worksheet Data through VBA? Once it fills up the entire Form, the Submit Button (in Web Form) will be clicked automatically to store the Data in Server. And, then proceed to the next Row of Excel Data. This is how the entire procedure goes on till it reaches the Last Cell Data in Worksheet.
002) How to convert Word to PDF, PDF to Word or Excel to PDF and PDF to Excel using VBA? Suppose, I have 10 Doc Files, 5 Excel Files in my "D" Drive. I want to convert them into PDF and put them in a newly created folder.
You can do both 1 and 2, above, using a combination of Office Scripts and Power Automate.
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...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...
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
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 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 UA-cam 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. ua-cam.com/video/HBiGEkzmkgo/v-deo.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!
Thank you!
You're welcome!
its confusing using result.filter to do the multiply by 2. This is why there is a resut.map function
Excel is trying to integrate with Python as well. Which one will win the race? Python or Office Script?
Office Scripts won.
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.
Is Office script more powerful than Google sheets script?
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.
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(ua-cam.com/video/2fHLfyOEx4Y/v-deo.html)
"Create a Powerful Office Script " for the Toronto Excel Online Meetup(ua-cam.com/video/2cbMglpj6pE/v-deo.html)
"How to Automate Your Work with an Office Script Bot" on this channel(ua-cam.com/video/me4q6g04vsM/v-deo.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 ?
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.
Will these run on LibreOffice?
They might! If you upload your Libre Office worksheet file (it needs to be saved as xlsx first) then try and run your script against it. Libre Office does allow you to save your files as xlsx, right?
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