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 ?
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!!!!
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!
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!?
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!!
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.
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 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.
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!
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.
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! ;)
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 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).
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.
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?
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 =(
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] );
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..
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.
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!
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 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
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!
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
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!
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.
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 am learning VBA ,by watching your videos for my project,I am facing a problem in VBA coding,when i run macro,other live feed data in different column getting freeze not updating data and i cant able to access other sheets/tabs of the Workbook as long as macro running in background.when i press ESC tab,only i can access. my project is copy and paste data between selected range for every 15 min. --------code follows--------- Sub Copy_PasteSpecial() Application.Wait Now + TimeValue("00:00:05") Sheets("LATEST").Range("E2:F150").Copy Sheets("P_OI").Range("B3:C153").PasteSpecial Paste:=xlPasteValues Application.Wait Now + TimeValue("00:15:00") Sheets("LATEST").Range("E2:F150").Copy Sheets("P_OI").Range("D3:E153").PasteSpecial Paste:=xlPasteValues Worksheets("P_OI").Columns("B:G").AutoFit Application.CutCopyMode = False End Sub ----------------------- Mentioned 15 minutes of wait time,i cant able to update live data feed a column which is in same sheet and cant able to access other sheets/tabs. please guide me on my issue. tq
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?
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...
@@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.
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.
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.
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
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, 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 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++ } }) })
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!!!
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
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!
23:08 - Using filter to modify the array in-place is pretty unconventional. In general it would be better to do the following: let result = fruitRange.values.filter(function(value,index){ return value[0] == "Limes"; }).map(function(row){ row[1]=row[1]*2; return row; }); Office Scripts are not only great for the modern language, but also because you can do some amazing UI in HTML, CSS and JS which is very tricky to do in VBA. By the way, lookup VBA-STD-Library on github if you want to see VBA implementations of Javascript arrays. It might help you get used to using javascript... :)
@@Excelmacromastery FYI I did a decent comparison over here - www.reddit.com/r/vba/comments/gvdlr4/interview_responses/fsqkdd5 I find it unfortunate that Excel Javascript APIs have these weird sync() and load() functions myself. I understand why they are there but find them extremely counter intuitive, difficult to debug and generally feel there are better ways they could have accomplishing the same task...
@@johnfromireland7551 Hi John, When you say dispensed, do you mean gotten rid of them? I do hope they are just optional rather than having been disposed of completely...?
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
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.
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....
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.
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.
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!!
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
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 ?
I learned COBOL in 1971 using a Honeywell 200 mainframe with 32K memory!
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!!!!
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."
Great, Great. You summarize many hours of study, which encourages me to try Office Scripts. Thank you very much indeed.
You are welcome!
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!?
Not sure how I’d get on with Office Scripts. Perhaps I’m a bit too comfortable with good old VBA.
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.
Very good introduction to Office Scripts, in comparison with VBA
Thanks. Glad you like it.
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.
Can you please discuss/ compare execution speeds, efficiency, etc. in an upcoming video?
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!
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!
Great Tutorial! I'm a Developer of Office Scripts, and I learned a lot from this video.
Glad it was helpful!
Thanks Paul. Very informative.
Very welcome Wyatt
Hi Paul, thx very much to covering this argument, would be nice if you consider to make a serie of videos about this.
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.
I‘ve been waiting for TypeScript vids to emerge. This is where I want to go.
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!
Thank you very much, I've been waiting for this kind of info
You're welcome James.
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.
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!
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.
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.
Regards from Brazil! Thanks for share your experience Paul!
Glad you enjoyed it!
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! ;)
Thanks so much Paul, delivered with your usual clarity....
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/
Can you make one on loops and more useful things in Type Script?
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.
New Office Script editor looks alot like IntelliJ IDEA. Thank you so much for this info.
You're welcome.
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).
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%!
This is really cool introduction! Thanks!
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.
Does the client need to have scripts enabled too or only the developer needs scripts enabled?
This will be Absolutely genius!
I'm looking forward to the full release.
Really hope you do more videos using typescript in excel!
Hello Paul, would appreciate you if you make a small video on Excel VBA iif function.
Thanks
Does anyone know if Microsoft has indicated when Office Scripts will stop being a preview feature and will get a full editor?
Very cool features, it’s bringing sort of VBA ‘online’ this will certainly give a rejuvenated feeling to Excel..
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 =(
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.
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] );
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!
Excellent tutorial and I hope you release more video on Office Scripts. Can anyone recommend a good book on using Office Scripts with Excel.
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.
How similar or different is Office Script with Google App Script? If you could make a video comparing these two would be great.
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.
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!
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 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
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, 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.
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
A modern platform for Excel. Awesome. Looking forward to your Office Scripts handbook!
Thanks David.
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!
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.
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 am learning VBA ,by watching your videos for my project,I am facing a problem in VBA coding,when i run macro,other live feed data in different column getting freeze not updating data and i cant able to access other sheets/tabs of the Workbook as long as macro running in background.when i press ESC tab,only i can access.
my project is copy and paste data between selected range for every 15 min.
--------code follows---------
Sub Copy_PasteSpecial()
Application.Wait Now + TimeValue("00:00:05")
Sheets("LATEST").Range("E2:F150").Copy
Sheets("P_OI").Range("B3:C153").PasteSpecial Paste:=xlPasteValues
Application.Wait Now + TimeValue("00:15:00")
Sheets("LATEST").Range("E2:F150").Copy
Sheets("P_OI").Range("D3:E153").PasteSpecial Paste:=xlPasteValues
Worksheets("P_OI").Columns("B:G").AutoFit
Application.CutCopyMode = False
End Sub
-----------------------
Mentioned 15 minutes of wait time,i cant able to update live data feed a column which is in same sheet and cant able to access other sheets/tabs.
please guide me on my issue.
tq
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.
I can see that the learning curve for scripts is going to be steep, but hey I love a challenge.
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?
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.
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.
In example 1, why is Sheet2 activate and used to explain the concept when Sheet1 is referenced in the code?
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.
I wanted to know how we can convert vba code into office 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.
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
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
Does anyone know when this will leave the preview phase?
No idea. I think we won't know until it happens.
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, 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 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
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 :-)
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 :)
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.
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.
its confusing using result.filter to do the multiply by 2. This is why there is a resut.map function
Can we Unprotect a password protected sheet, modify and then Protect the sheet again with the same password
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!
23:08 - Using filter to modify the array in-place is pretty unconventional. In general it would be better to do the following:
let result = fruitRange.values.filter(function(value,index){
return value[0] == "Limes";
}).map(function(row){
row[1]=row[1]*2;
return row;
});
Office Scripts are not only great for the modern language, but also because you can do some amazing UI in HTML, CSS and JS which is very tricky to do in VBA.
By the way, lookup VBA-STD-Library on github if you want to see VBA implementations of Javascript arrays. It might help you get used to using javascript... :)
Thanks for the feedback and explanation. I plan to look at map/filter more closely in the comming weeks.
@@Excelmacromastery FYI I did a decent comparison over here - www.reddit.com/r/vba/comments/gvdlr4/interview_responses/fsqkdd5
I find it unfortunate that Excel Javascript APIs have these weird sync() and load() functions myself. I understand why they are there but find them extremely counter intuitive, difficult to debug and generally feel there are better ways they could have accomplishing the same task...
@@Sancarn Hi Sancarn, the latest version of Office Scripts has, now, dispensed with sync, load, async, and await.
@@johnfromireland7551 Hi John, When you say dispensed, do you mean gotten rid of them? I do hope they are just optional rather than having been disposed of completely...?
@@Sancarn The language has been made leaner and there is, now, no need to add those lines of code to sync or wait for responses etc.
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
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 there any tool to convert vba macro code to office script code?
Not at this time.
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....
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.
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.
How to make ScriptLab scripts run on the workbook open in OneDrive?
Using Power Automate.
When using scripts on a desktop PC do we stil need to use "sync"?
We do in ScriptLab.
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...
Is Office script more powerful than Google sheets script?
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
Also, office scripts are still incomplete, for example, I could not find the AdvanceFilter method. If someone found it, please share the knowledge.
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.
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
Excel is trying to integrate with Python as well. Which one will win the race? Python or Office Script?
Office Scripts won.