Sad to know that VBA will not be updated, it is such a simple and useful tool. I am hopeful that Microsoft will one day realize the enormous influence that Excel and VBA have on the world and decide to maintain VBA. Thank you so much to take the time and effort to do VBA videos, I enjoy them verry much!
Been quite a while since this had been released so things may be different but this is a current question regarding an application I am working on in my current role. Ultimately I feel VBA has much stronger features and has so many more resources out there that scripts just doesn't fit the need for more complex applications that need to grab data from other workbooks, and work immediately without any extra layers needed. Without the same tools as the VBA IDE this makes VBA much stronger as well in my opinion.
I would use it if it allowed a workbook to interact with other workbooks but that is pretty essential and it is disappointing that it isn't a planned feature. Or if you were able to interact with information in a workbook and a doc or file in OneDrive or SharePoint.
Great video! It’s good to know the future of VBA. Have you heard anything about Microsoft possible integrating Python into Excel? I would love to see some videos or webinars about OfficeScript and how to use it in conjunction with VBA.
Events can be programmed if you do it with the Office Javascript API. I did it with Script Lab or VSC, I do not know why Office Scripts does not allow it. Just yo say that Office Scripts is one of many ways to develop Javascript/TypeScript for Excel
The reason events don't work is because Office Scripts are synchronous where as the javascript API is Asynchronous. "The Office Scripts APIs are an optimized, synchronous subset of the Excel JavaScript API model."
@@Excelmacromastery Too many diferent ways to programme JS-TS for Excel. I think Microsoft is not doing it well prommoting this as for VBA coders is not easy to start.
Two years later and the SecOps team in my organization rolled out a large Windows and security update, with the net result being that opening a macro-enable Excel sheet invariably causes Excel to crash a couple of times before it loads in Safe mode. At this point, I can run the VBA macro, but, this is no good if I'm using a Power Automate Desktop flow to run the VBA code, because Excel crashes. Weirdly, if I use an Excel control sheet with nothing in the sheet, but code underneath that recalls various sheets to do things, Excel doesn't freak out. I've just replaced the VBA code with an Office Scripts code and yes, it now works with a least one other open workbook and it works on the desktop versions of Excel (if you have Office 365). You can even save the Office Script to the worksheet, where it creates a button to click on. However, Power Automate Desktop doesn't have the ability to trigger an Office Script directly, like it does with VBA, so the work around is to use "click a button in the Window" command in the flow and select the button via the UI tool. I suspect adding Office Scripts functionality in PAD is going to be a premium service in the future. Caveat: when I say I replaced the VBA code with Office Scripts, it's not a complete code-for-script replacement - at least not yet. Because I had to do it fairly quickly, I also used SendKeys commands in PAD to filter the other sheet, navigate to specific cells etc. The flow itself works well and is reasonably quick, especially as it is downloading source data, saving it to a specific location, renaming the files, then adding a date suffix at the end, before sending a message to a Teams channel as well. PS: don't ask free ChatGPT 3.5 to figure out your Office Scripts code or PAD flows - it gets the Office Scripts code wrong and gives you PA cloud flows instead. I'm too cheap to upgrade to ChatGPT 4 😂
Most of my vba has been automating multiple different files and often between different applications using the different object models. Often with lots of userforms. I just don't see anything that as easy to do this as vba.
Things like office script, power automate, Are very disjointed and sprawling from the pov of deploying and maintaining solutions. They are Frankenstein like solutions.
So what is the conclusion, I need to learn Java script ? Or JavaScript, Typescript & office script ? Please can someone clarify ..Also when will the switch over happens where excel can be programmed with JavaScript or office scripts
So the takeaway: -VBA is too important/useful it is staying indefinitely. -Office scripts sacrifices vital core functionality such as multiple workbook use and events, while offering very minor benefits (works in a browser). It's also arguably harder to read.
For home users or people with E3 license, this seems like a disadvantage but there are millions of employee around the world who only work with an E1 license. These people can only use their browser and for them, this is a game changer.
@@harryriley2696 E1 users are not allowed to install Excel locally on their machine and can only use it from their browser. VBA will only work for a local install. E1, E3 are different tiers of licensing offered by Microsoft for O365. It would be better if you google about these as I am aware about all the differences between them :)
Thanks for this video! All the other videos I'd seen on this topic either weren't clear as to the differences between VBA and TypeScript or just seemed to be offering opinions.
At 6mins you showed a comment that mentions 'addins', but I don't see any videos in your channel talking about them. I'm curious if instead of going from VBA for applications to scripts, whether we should start looking to develop addins for Office online instead?
I am a novice VBA user in Excel. I have many automation covered with a VBA code. My question - migration to Excel 365, will I loose my automation code in VBA? Would it be better to remain with pc based Office? Thank you. Am looking forward to any response. Blessings🙏🙏
Hi Sean, This actually causes a lot of confusion for many people. Office 365 contains the desktop version of the Office Applications like Word, Excel. The difference with Office 365 is that it gets regular updates and is subscription based. You can still use Excel and VBA as normal.
So can I use a change event in a short VBA script to run an office script (I know that this might seem odd, but I can only use excel on Mac and online at present - I have no access to a PC, so I cannot use all of the features to write a VBA script, specifically user forms)
Hello, I found your video useful. I do need one help though I have a requirement whereby I need to use Power Automate (web version) to automatically trigger some macros present in an excel file everyday (that resides in SharePoint). I am stuck and unable to find any useful resources that explain how to do this. Can you please advice?
I think Microsoft ripped event triggering from Office Script to do away with server overload, anyone could setup applications, solutions or services based on many events that could potentially overload MS Office servers.
The point about scripts not being event-driven…my understanding from the video is that worksheet buttons won’t be linkable to scripts, but what about custom task panes in an office add-in? And, scripts allow for any sort of web page to be displayed as an overlay…does this mean we need to rethink how we create interfaces, but we can still build complex app inside Excel using office scripts? Thanks!
I am new to VBA. My job is moving me to Office 365. Can someone please tell me if you can use VBA with Excel 365? I cannot seem to get a clear answer anywhere.
You can. Office 365 comes with a desktop version. The only difference is you get regular updates and it's on a subscription model since everyone's nan and their cat is obsessed with SAAS
Why not allow Python as a programming platform for all the flavors of Office? It is popular, and I have seen at least one video where it made Excel programming much simpler.
JavaScript is a language already available in all browsers and Microsoft owns Typescript. The inventor of Python, Guido, works at Microsoft at the moment. He was given an opportunity to choose any project he likes and he chose to create a team which would focus on CPython optimizations. The closest we have would probably come from C# (VB's chad twin brother). Although solutions like pyxll exists, I am having a hard time imagining a world where Office is shipped with a bundled Python interpreter.
The reason why microsoft couldnt use VBA on sharepoint is beyond me. Now I have to spend weekends searching for some demo and tutorials just to be up to speed to finalize some projects. As if VBA wasnt broken enough, this office scripts look like broken too. aren't we fucking blessed
In our company we prefer to use the standard excel, not the online. I doubt if the online version with Office Script will largely replace the standard excel with vba in the (large) corporate world. In that sense, Excel with VBA will still long be used. Taken into account, that VBA will still long time be used, Microsoft could at least enhance the VB-Editor with the same features like in Office Scripts-editor. In VBA are things which I adore like the "with" - Statement, which saves me repeating the object-name. Whereas the object oriented programming in VBA is not quite elegant.
I feel VBA is good at its place. About office scripts, I think more improvements needed. At the moment Google App Scripts are much better than office scripts (personal experience, I may be wrong)
In other words, you can build an entire multiuser enterprise application with VBA, but not with Office Scripts. But…. If Office scripts had events, userforms and access to Windows APIs, it would be perfect and I would gladly switch. (Owner of an Excel Application Development company since 2010 and developer since 1999)
You have a company that is dedicated to Excel Application Dev? I didn't realise there was enough of a demand. I really wish there was much more of a demand though, enough for MS to actually care about VBA and provide people like us with decent tools that aren't from the last millennium (literally). I love VBA, and I still use it rather frequently despite being an "actual programmer" who uses languages that have modern tooling available for them. Sometimes it's just easier to bang something together in Excel or Access with VBA, and sometimes I just don't want to have to deal with Interop and COM, Marshalling, and being careful to clean up when finished with objects, as you do when working with something like C# and the Office.Interop libraries. And besides, VBA isn't just for Office, I have a few VBA Applications I've built that are still in use that automate CorelDRAW, Solidworks, and AutoCAD, while also pulling data from multiple locations including plain old CSV files, Text files, Excel spreadsheets, Access and MYSQL DB's, and even an old AS400 system in a few cases. You just can't do that kind of stuff with Office Scripts. Can you at least do file system stuff with it? Just simple things like iterating through directory contents, creating new directory structures and populating them with files, reading from files, writing to files, what about opening a shell and executing a binary? I doubt that's possible considering it is an online tool. And how does it not have events? No ActiveX components? How are buttons and other components handled? Now I'm really confused..
@@mileswilliams527 yes, i do own an Excel VBA app dev company, and for 10 years now clarian.com.br. I am in Brazil. VBA allows you to do literally anything, mainly when using Class Modules. We have done more than 200 projects for fortune 500 companies. We use Excel just as a Client, with all data stored in a SQL Server or any other DB. Excel is extremelly powerful as a Client. It is like using Excel instead of a browser. We have huge systems running for over 5 years with 5000 concurrent users. Over the years we’ve built our own VBA framework which makes it easy to create large Systems, with proper authentication, access control, protection, etc.
@@MarceloNogueiraGoogle Wow I am just seeing this in 2023 and I am blown away by what VBA can do. So you are saying office scripts aren't really needed if you understand VBA?
The simple lack of ability to access other workbooks is a complete deal breaker for me. It's too bad, because if it had that I could see myself making the switch.
VBA will be around longer than the tech companies would like to. The switching costs at the companies are just too big. There are millions of small scripts in the companies and people with VBA skills that can't be retrained easily.
In typescript for vs2019 it's allowed to define your own events! Does it really not work in office script? That's not so comfortable! Nice Video Paul!👍👍👍🤟😎
Thanks John. The reason events don't work is because Office Scripts are synchronous. "The Office Scripts APIs are an optimized, synchronous subset of the Excel JavaScript API model."
But seriously, they really need to do something about the f***ing code editor. It's 2022 and it is exactly the same f***ing editor since the 90's!( or 80s??)
Subscription based anything will die before VBA expires. It's like charging for breathing while you're sitting at your computer or not! Greed never wins!
*Since I found Python I divorced VBA, married Python and never looked back! Well, occasionally I drop by for a night together to remember old times! Python is not jealous!*
You need to clarify that VBA be used in web applications, but it does not mean VBA can't be used "online". You can use VBA to fetch data from web pages and other online sources just fine. You are just not going to code VBA directly in HTML files like javascript.
I'd rather go to Python than Office Script. Office Script actually tries to emulate what Pyhon does through its powerful libraries to manipulate Excel. Although Office Script is a native Microsoft solution and aims the whole MS Office lineup, I don't like the way they try to steer people into thinking the way Microsoft thinks as the best solution. They're doing what Google has already done, binding everyone to their ecosystems!
Sad to know that VBA will not be updated, it is such a simple and useful tool. I am hopeful that Microsoft will one day realize the enormous influence that Excel and VBA have on the world and decide to maintain VBA. Thank you so much to take the time and effort to do VBA videos, I enjoy them verry much!
Thank you very much for sharing most important knowledge,
Here is Mayur (VBA developer since : 2005 )
You're welcome!
Been quite a while since this had been released so things may be different but this is a current question regarding an application I am working on in my current role. Ultimately I feel VBA has much stronger features and has so many more resources out there that scripts just doesn't fit the need for more complex applications that need to grab data from other workbooks, and work immediately without any extra layers needed. Without the same tools as the VBA IDE this makes VBA much stronger as well in my opinion.
I would use it if it allowed a workbook to interact with other workbooks but that is pretty essential and it is disappointing that it isn't a planned feature. Or if you were able to interact with information in a workbook and a doc or file in OneDrive or SharePoint.
I think a lot of people feel this way.
Great video! It’s good to know the future of VBA. Have you heard anything about Microsoft possible integrating Python into Excel? I would love to see some videos or webinars about OfficeScript and how to use it in conjunction with VBA.
Python works with Power BI and the founder of Python now works with Microsoft. That's all I know:-)
@@Excelmacromastery with Mr Python working for MS, maybe it’s time to switch to Julia.
Events can be programmed if you do it with the Office Javascript API. I did it with Script Lab or VSC, I do not know why Office Scripts does not allow it. Just yo say that Office Scripts is one of many ways to develop Javascript/TypeScript for Excel
The reason events don't work is because Office Scripts are synchronous where as the javascript API is Asynchronous. "The Office Scripts APIs are an optimized, synchronous subset of the Excel JavaScript API model."
@@Excelmacromastery I recommend you to use Script Lab where you can do it, also you can programe HTML and CSS, also JS / TS and its free
@@Excelmacromastery Too many diferent ways to programme JS-TS for Excel. I think Microsoft is not doing it well prommoting this as for VBA coders is not easy to start.
Two years later and the SecOps team in my organization rolled out a large Windows and security update, with the net result being that opening a macro-enable Excel sheet invariably causes Excel to crash a couple of times before it loads in Safe mode. At this point, I can run the VBA macro, but, this is no good if I'm using a Power Automate Desktop flow to run the VBA code, because Excel crashes. Weirdly, if I use an Excel control sheet with nothing in the sheet, but code underneath that recalls various sheets to do things, Excel doesn't freak out.
I've just replaced the VBA code with an Office Scripts code and yes, it now works with a least one other open workbook and it works on the desktop versions of Excel (if you have Office 365). You can even save the Office Script to the worksheet, where it creates a button to click on. However, Power Automate Desktop doesn't have the ability to trigger an Office Script directly, like it does with VBA, so the work around is to use "click a button in the Window" command in the flow and select the button via the UI tool. I suspect adding Office Scripts functionality in PAD is going to be a premium service in the future.
Caveat: when I say I replaced the VBA code with Office Scripts, it's not a complete code-for-script replacement - at least not yet. Because I had to do it fairly quickly, I also used SendKeys commands in PAD to filter the other sheet, navigate to specific cells etc. The flow itself works well and is reasonably quick, especially as it is downloading source data, saving it to a specific location, renaming the files, then adding a date suffix at the end, before sending a message to a Teams channel as well.
PS: don't ask free ChatGPT 3.5 to figure out your Office Scripts code or PAD flows - it gets the Office Scripts code wrong and gives you PA cloud flows instead. I'm too cheap to upgrade to ChatGPT 4 😂
Most of my vba has been automating multiple different files and often between different applications using the different object models. Often with lots of userforms. I just don't see anything that as easy to do this as vba.
Things like office script, power automate, Are very disjointed and sprawling from the pov of deploying and maintaining solutions. They are Frankenstein like solutions.
Thanks Paul. Always great to hear your insights :)) Thumbs up!!
So what is the conclusion, I need to learn Java script ? Or JavaScript, Typescript & office script ? Please can someone clarify ..Also when will the switch over happens where excel can be programmed with JavaScript or office scripts
It's a new tool in the toolbox. Not every Excel workbook that requires coding has to work in the cloud but it is great to have the possibilities now!
That's very true.
So the takeaway:
-VBA is too important/useful it is staying indefinitely.
-Office scripts sacrifices vital core functionality such as multiple workbook use and events, while offering very minor benefits (works in a browser). It's also arguably harder to read.
For home users or people with E3 license, this seems like a disadvantage but there are millions of employee around the world who only work with an E1 license. These people can only use their browser and for them, this is a game changer.
@@top10ofanything51 I don't understand what you mean with the licensing, could you explain?
I thought everyone had access to VBA?
@@harryriley2696 E1 users are not allowed to install Excel locally on their machine and can only use it from their browser. VBA will only work for a local install.
E1, E3 are different tiers of licensing offered by Microsoft for O365. It would be better if you google about these as I am aware about all the differences between them :)
Thanks for this video! All the other videos I'd seen on this topic either weren't clear as to the differences between VBA and TypeScript or just seemed to be offering opinions.
Glad you like it.
At 6mins you showed a comment that mentions 'addins', but I don't see any videos in your channel talking about them. I'm curious if instead of going from VBA for applications to scripts, whether we should start looking to develop addins for Office online instead?
I am a novice VBA user in Excel. I have many automation covered with a VBA code. My question - migration to Excel 365, will I loose my automation code in VBA? Would it be better to remain with pc based Office? Thank you. Am looking forward to any response. Blessings🙏🙏
Hi Sean, This actually causes a lot of confusion for many people. Office 365 contains the desktop version of the Office Applications like Word, Excel. The difference with Office 365 is that it gets regular updates and is subscription based. You can still use Excel and VBA as normal.
'Record action' option not available in my excel automate group. Any help
So can I use a change event in a short VBA script to run an office script
(I know that this might seem odd, but I can only use excel on Mac and online at present - I have no access to a PC, so I cannot use all of the features to write a VBA script, specifically user forms)
Good morning is it possible to run a typescript script when opening an excel file in Teams ? Thank you.
Hello, I found your video useful. I do need one help though
I have a requirement whereby I need to use Power Automate (web version) to automatically trigger some macros present in an excel file everyday (that resides in SharePoint). I am stuck and unable to find any useful resources that explain how to do this. Can you please advice?
I think Microsoft ripped event triggering from Office Script to do away with server overload, anyone could setup applications, solutions or services based on many events that could potentially overload MS Office servers.
The point about scripts not being event-driven…my understanding from the video is that worksheet buttons won’t be linkable to scripts, but what about custom task panes in an office add-in? And, scripts allow for any sort of web page to be displayed as an overlay…does this mean we need to rethink how we create interfaces, but we can still build complex app inside Excel using office scripts?
Thanks!
I have Webstorm, will that be able to debug the scripts?
Doesn't matter if it is VBA, Powershell, C#, Python... so long as it is not an RPA solution 😁
😄😄
I am new to VBA. My job is moving me to Office 365. Can someone please tell me if you can use VBA with Excel 365? I cannot seem to get a clear answer anywhere.
You can. Office 365 comes with a desktop version. The only difference is you get regular updates and it's on a subscription model since everyone's nan and their cat is obsessed with SAAS
Why not allow Python as a programming platform for all the flavors of Office? It is popular, and I have seen at least one video where it made Excel programming much simpler.
Python is hugely popular for sure. The reason for JavaScript/Typescript was to have a language that runs independently of the platform.
JavaScript is a language already available in all browsers and Microsoft owns Typescript.
The inventor of Python, Guido, works at Microsoft at the moment. He was given an opportunity to choose any project he likes and he chose to create a team which would focus on CPython optimizations.
The closest we have would probably come from C# (VB's chad twin brother). Although solutions like pyxll exists, I am having a hard time imagining a world where Office is shipped with a bundled Python interpreter.
The reason why microsoft couldnt use VBA on sharepoint is beyond me.
Now I have to spend weekends searching for some demo and tutorials just to be up to speed to finalize some projects.
As if VBA wasnt broken enough, this office scripts look like broken too. aren't we fucking blessed
Have you tried emailing Bill Gates about this? I’m sure he’d be most responsive.
@@TP-om8of nah he's too busy with his divorce.
Why microsoft did not upgrade VBA?
In our company we prefer to use the standard excel, not the online. I doubt if the online version with Office Script will largely replace the standard excel with vba in the (large) corporate world. In that sense, Excel with VBA will still long be used.
Taken into account, that VBA will still long time be used, Microsoft could at least enhance the VB-Editor with the same features like in Office Scripts-editor.
In VBA are things which I adore like the "with" - Statement, which saves me repeating the object-name.
Whereas the object oriented programming in VBA is not quite elegant.
I agree. The desktop will be around for a while yet.
Thanks for this excellent clip, very insightful
I feel VBA is good at its place. About office scripts, I think more improvements needed. At the moment Google App Scripts are much better than office scripts (personal experience, I may be wrong)
I haven't used Google Scripts but the seem popular.
In other words, you can build an entire multiuser enterprise application with VBA, but not with Office Scripts. But…. If Office scripts had events, userforms and access to Windows APIs, it would be perfect and I would gladly switch. (Owner of an Excel Application Development company since 2010 and developer since 1999)
Exactly Macelo.
You have a company that is dedicated to Excel Application Dev? I didn't realise there was enough of a demand.
I really wish there was much more of a demand though, enough for MS to actually care about VBA and provide people like us with decent tools that aren't from the last millennium (literally).
I love VBA, and I still use it rather frequently despite being an "actual programmer" who uses languages that have modern tooling available for them. Sometimes it's just easier to bang something together in Excel or Access with VBA, and sometimes I just don't want to have to deal with Interop and COM, Marshalling, and being careful to clean up when finished with objects, as you do when working with something like C# and the Office.Interop libraries.
And besides, VBA isn't just for Office, I have a few VBA Applications I've built that are still in use that automate CorelDRAW, Solidworks, and AutoCAD, while also pulling data from multiple locations including plain old CSV files, Text files, Excel spreadsheets, Access and MYSQL DB's, and even an old AS400 system in a few cases.
You just can't do that kind of stuff with Office Scripts.
Can you at least do file system stuff with it? Just simple things like iterating through directory contents, creating new directory structures and populating them with files, reading from files, writing to files, what about opening a shell and executing a binary? I doubt that's possible considering it is an online tool.
And how does it not have events? No ActiveX components? How are buttons and other components handled? Now I'm really confused..
@@mileswilliams527 yes, i do own an Excel VBA app dev company, and for 10 years now clarian.com.br. I am in Brazil. VBA allows you to do literally anything, mainly when using Class Modules. We have done more than 200 projects for fortune 500 companies. We use Excel just as a Client, with all data stored in a SQL Server or any other DB. Excel is extremelly powerful as a Client. It is like using Excel instead of a browser. We have huge systems running for over 5 years with 5000 concurrent users. Over the years we’ve built our own VBA framework which makes it easy to create large Systems, with proper authentication, access control, protection, etc.
@@MarceloNogueiraGoogle Wow I am just seeing this in 2023 and I am blown away by what VBA can do. So you are saying office scripts aren't really needed if you understand VBA?
The simple lack of ability to access other workbooks is a complete deal breaker for me. It's too bad, because if it had that I could see myself making the switch.
But we cannot use a shape as a button to trigger a script..
VBA will be around longer than the tech companies would like to. The switching costs at the companies are just too big. There are millions of small scripts in the companies and people with VBA skills that can't be retrained easily.
Very true.
In typescript for vs2019 it's allowed to define your own events! Does it really not work in office script? That's not so comfortable! Nice Video Paul!👍👍👍🤟😎
Thanks John. The reason events don't work is because Office Scripts are synchronous. "The Office Scripts APIs are an optimized, synchronous subset of the Excel JavaScript API model."
Empressed by your slides.
so elegant
Thanks! Glad you like them.
but i got to hear from official microsoft people that microsoft ending suppport for VBA
I think in future they will just kill desktop excel maybe
Yes, I have started learning office scripts and power automate to use it in my workflow
Power Automate is very buggy.
Thanks
Add in mobile support and the whole platform becomes increasingly more undesirable from a development point of view
Relice?
But seriously, they really need to do something about the f***ing code editor. It's 2022 and it is exactly the same f***ing editor since the 90's!( or 80s??)
Subscription based anything will die before VBA expires. It's like charging for breathing while you're sitting at your computer or not! Greed never wins!
A good ideea is to make it work with desktop versions
They are working on the Office Scripts desktop version now.
VBA will hang on for a while, but it is inevitable Im afraid. If it doesnt work online line then its going to be a dead donkey.
*Since I found Python I divorced VBA, married Python and never looked back! Well, occasionally I drop by for a night together to remember old times! Python is not jealous!*
😄
You need to clarify that VBA be used in web applications, but it does not mean VBA can't be used "online". You can use VBA to fetch data from web pages and other online sources just fine. You are just not going to code VBA directly in HTML files like javascript.
I'd rather go to Python than Office Script. Office Script actually tries to emulate what Pyhon does through its powerful libraries to manipulate Excel. Although Office Script is a native Microsoft solution and aims the whole MS Office lineup, I don't like the way they try to steer people into thinking the way Microsoft thinks as the best solution. They're doing what Google has already done, binding everyone to their ecosystems!
👍👍👌
I am not a bleeding edge developer, so I am staying with VBA.
"VBA will be around until you retire.." So I've got another 15 years of this... I'm groaning...
Yes ! VBA won't die ...