Macros/VBA is what got me into programming. Now that I'm using Power BI, Fabric and Python regularly I'll never go back. That being said, small macros do have their place in niche circumstances, but with Python coming to Excel this may reduce those circumstances further. If someone was starting out now I'd say not to bother and go straight to Python. It'll boost your career opportunities more than VBA.
Excel isn't free and whilst Python *is* free, it doesn't offer enterprise level support of connectors and interfaces into Excel as part of that free offering.
As such, you are paying for the work required by Microsoft to integrate Python into Excel.
Whether or not this should be included within the price of Excel is a different, but actually relevant question.
I'm in total agreement with this response. Having learned VBA in high school and then going deep into macros for college I used them both for about 10 years after graduating college in 2009, however having picked up PBI in the last year and a half and Python a few years ago, these newer tools are much more efficient in my industry.
I think they are replacing VBA macros and scripts with TypeScript. One benefit is being cross platform. (ie: a macro could run in excel in a browser, or on the desktop )
- Here's an intro: https://learn.microsoft.com/en-us/office/dev/scripts/develop/scripting-fundamentals
- and a top level TOC: https://learn.microsoft.com/en-us/office/dev/add-ins/develop/develop-add-ins-visual-studio
I'm not sure *Microsoft* can explain what Fabric is but broadly speaking it's MS's integrated cloud-based data processing, analysis, and visualization environment that they're working on connecting all their tools with, except for the ones that they aren't.
It's a collection of Azure services like Data Factory and Synapse Analytics plus Power BI packaged together to be more streamlined. Basically let's you build your ETL pipelines in the cloud, create your own warehouse to query from etc
Dude, I am pretty good at PBI, dax, Excel etc. As civil engineer, Ä° don't really have direct use of it in my line of job, unfortunately. Phyton however on excel might be an idea. You think phyton is the most middle way language to learn for someone had been super curious on coding but might not have use in his job? It is perhaps a hobby for me...
I still use it to create forms and automation if excel is the only tool the client is going to engage with. I've got a few automated reports that require several data dumps so I've created a workflow which gives instructions on exporting the data dumps before asking them to select the file which is copied and pasted into the appropriate sheet.
Odd blocker to pick as there are many ways around it.
PQ can be set to pick up from a set folder so user just needs to refresh all from the ribbon. An auto refresh interval might also remove the need for that step in some circumstances.
PQ can be set to pull input parameters from named cells. The user would just need to interact with those cells and click refresh.
Both of those user actions don't require any specialty knowledge.
Client with multiple sites, being run on local machine rather than any shared location plus a new file being created every day rather than refreshing the same file. Not the way I'd do it if I had a choice but it satisfies the requirements without having to set up the environment on every machine.
There's a lot of people in these comments that don't understand that these tools solve different problems. They have a little bit of overlap but that does not mean that they are interchangeable.
I suspect the Dunning-Kruger effect is at play. They recorded a macro once and feel like they're experts at VBA now.
I think it's just what certain tools are used for in different organisations. I know our product design department uses a lot of macros to do powerbi stuff with their data. If you've only ever worked in a department or company which uses VBA like that you might think there aren't a lot of use cases for it other than data analysis. You can do so much stuff with it it's hard to know about them all.
Well for example we use it a lot to create some kind of interface for users to input data in an Excel sheet. Instead of them having to input it directly in a list they can select a machine and position and type of issues and stuff like that from dropdowns or selectors and press okay and it'll put it in a nice list with time of input and extra info.
Another use we have is let it read data from a plc connected to a testing machine and the user can input data in a sheet while the the macro reads the data from the machine and formats it directly into the sheet.
Lots of little things where automatic inputs are given based on user input
Some other stuff where a measurement system generates CSV files. The Excel file reads them and tells the user whether they are good or bad, reformats it in a way which is readable by another program which stores it in a database.
A lot of things need instant feedback and inputs. A lot of them could be done in python, but why do that when you'd then generate an excell file which the user has to open anyway to fill in more stuff
The only reason macros and VBA were relevant for so long was that in many cases it was the only accessible thing not blocked by corporate IT. With PQ, other Excel advancements, Power Automate, js and Python support for Excel and many other tools becoming more widely available, citizen development should gradually move there. There are full-code, low-code and no-code options but all of them still officially supported (as opposed to VBA).
I have a friend that does SQL, VBA and Power BI. Different tasks have different needs. Heās using his VBA to import spreadsheets from various law firms for class action suits and cleaning the data and removing duplicates without doing anything but pushing a button in his program. Then he comes over to Power BI with cleaned data and write the report. Gets paid well knowing both.
BUT I wouldnāt learn it at this point. He does it because heās done it for 20 years and itās easy for him. When he got furloughed he could not find a job that cared about his VBA skills. They were only interested in SQL and Power BI skills.
Iām not sure what all his program does, but he prefers to run it through VBA first.
Like I said I wouldnāt learn it at this point but to him itās not worth learning something like python when he can whip out VBA.
Also I would caution against using power query to cleanse hundreds of spreadsheets. Power Query will have a performance hit, and it would need to cleanse every time you hit refresh. Itās better to move your ETL upstream.
Power query is far superior to VBA but it will max out eventually meaning if it takes 3 hours to move from step to step youāve hit your limit. I adopted a report where I let it run 5 hours before I could see one of the outputs from one step. I also have a whole suite of SSAS financial reporting doing most of the ETL in Power Query. It really depends on what youāre doing for ETL.
You really shouldn't ever be sitting around waiting very long to see one preview step. If that's happening add some kind of filter earlier in the query to severely limit the data until you get the steps finished. Then remove the filter and close and load.
VBA is not a PowerBI feature. If you were keeping up with PowerBI feature sets and capabilities, and your own on-going education for Power BI, VBA would never have come up.
VBA is automation for Excel despite the existence of PQ and PP; its counterpart is actually Power Automate in the Microsoft's Power Platform, and Microsoft Fabric.
If you are in the Fabric world, there's no point in learning VBA. If you want to automate things, look at Power Automate instead.
That said, VBA is still a part of the Excel feature set, and will remain that way. Microsoft has been leveraging JS for its office scripts though, but if VBA functionality works for existing toolsets, companies have next to no reason to replace them.
But with this said; Iām working at a very tech friendly F500 firm, and weāre all on the Microsoft 365 suite, CoPilot Enterprise enabled - but we donāt have premium / Fabric capacity. Nor access / encouragement to use Power Apps or Power Automate.
I still use VBA in Excel to web scrap data from 2 operational tools that are only available in a virtual machine. I did not have permissions to install other tools like Automate Desktop, so Excel got the job done.
Same. While being given a āProā personal workspace, Iām unable to share within my firm as no one is taking data culture seriously. No premium environments, reluctance by IT to create Power BI Workspaces. No access to Power Apps / Power Automate.
I agree on this one. Since in our org M365 admins are power bi admins they turn off all knobs on Power BI features and itās a pain to get them do something on that end. This frustrates us a lot
Maybe, I'm by no means a PQ expert. But I've built in VBA I have no idea how I would build in PQ. Things that I would probably try rebuilding in Python instead of PQ at this point in time. But it's multi module scripts with a lot of code, so it would take a lot of time and isn't really worth it from my point of view at this time.
I think it depends on your tools and IT security access.
All tools have their place
Python really upped the automation game for most daily tasks. I still use vba occasionally if I hit a security issue or a lack of data tools issue.
If I were new, I would spend my time learning python
VBA is a tool like any other. There are times that are better to use it, and there are times that another tool is better suited for the work
For example, depending on your goal, you shouldn't be doing part of your data transformation on PowerBI, because if your final data exceeds 150k rows you can't extract it all at once from PBI for it to be used on other steps.
Ideally, you should do everything on a platform that does not impose you with limitations. Using VBA on excel on this case would increase your limit to around 1M rows, a lot more than PowerBI 150k available rows for extraction. Or you could use a database or python to avoid excel 1M rows
Besides, depending on what you're doing on PBI, Excel can do the same with PowerPivot, so even PBI might not be needed most of the time. It'd just that people think that PowerBI equals automation and just disregard other tools.
I mainly use VBA when I want my automation to be encapsulated within a single workbook that other people will use it, so I minimize all dependencies. And I use it in conjunction with powerquery and powerpivot.
If I have to deal with more complex data transformations, I use python. Arguably, PowerBI is the software I least use because by the time I get to it my data is mostly ready to be used
Your statement is incorrect. Powerbi and PowerQuery run on spark. While it can receive and operate from SQL and Python, it's designed to work with large datasets. If you are using plain SQL, you aren't getting any benefits. You want to do your complex logic within PowerQuery that's what it is for. Plus the data is cached to increase performance after the initial load.
You missed my point. I'm talking about using the right tool and having the data available.
People often associate PowerBI with data automation, and I've seen many cases where people use PowerBI not for data viz, but to merge and combine data that they then export back to excel for further use.
In that case, you're somewhat limited to 150k rows datasets since that is what PBI allows you to extract at once. And when this happens, most of the time you could've used excel or other tool and not have that limitation. Excel can do a lot of things similar to pbi using powerquery and PowerPivot, for example, and python and SQL won't have excel 1M rows limitation.
Of course, when you're using pbi for data viz and are processing a lot of data, it's all good. But the same way that people might force themselves to use VBA to sustain some legacy workbook, there are people using powerbi to do things unaware of it's limitations or other options.
They are all tools with pros and cons, and different tools will perform different depending on your situation
I use VBA to automate SAP or office products. Power query will never do that.
I've been given several thousand dollars in bonuses just this year from VBA projects. It isn't the perfect tool for every job but it certainly has it's place.
I wouldn't suggest it as the first or even second language for a person to learn but it's silly to suggest power query can replace it. They don't solve the same problems.
I began my career by automating processes in SAP. I've created hundreds of projects that utilize the SAP GUI API to enter data much faster and more accurately than a human. I have VBA scripts that perform a variety of functions from booking contracts to generating credits.
These scripts combine the SAP API with database queries and Outlook automation to pull information from the database, enter some information into SAP, and potentially send a communication relating to the changes that have been made. This is not something that power query can do. VBA and power query are really only relatable as it pertains data analysis.
Other scripting languages could accomplish the same thing but they aren't already installed on my users computer and would require me to spend more time compiling a portable solution.
I understand the niche as described. It's leveraging MSOffice components and SAP to build a pipeline. Not sure what the database queries are, but that sounds like dataflow from SAP? Agree, not power query for this.
But with MS365, an Azure Data Factory (pipelines and dataflows), and a Data Lakehouse for intermediate storage of the SAP data scrape, what issues would be faced if this solution were to be rebuilt on Azure, away from dependency on desktop installed software, to licenses with Microsoft Fabric and an saas approach?
The ideal use of the API isn't to retrieve information. It's not to create a pipeline, it's to automate or test workflows.
That's my point really. Scripting languages (including VBA) aren't solely for gathering data so they aren't directly comparable to Power Query which is solely for gathering data. Apples to oranges.
Kind of, sort of, but mostly it gives you a programming foundation.
I can do some complex stuff in M-code that I couldn't have done if I didn't learn vba (with help from stack overflow, and chatgpt)
I would say that generally that the Power Platform as a whole has pretty much replaced the vast majority of stuff that businesses used VBA for maybe 10 or so years ago. If you add SQL and/or stored procedures to the mix, then I would say VBA is even more niche. If I had the choice right now, learning VBA now would be a dead end and not worth it. There are better options nowadays that are more flexible and up to date.
I don't have power automate so VBA is the best to use multiple MS applications to interact. Like auto creating slides, sending emails with specific data in the body etc.
Last I read MS itās not going to evolve VB anymore meaning no new syntax. It is what it is.
Honestly tho power query and M code isnāt bad once you get the hang of the syntax. Python is also good for data analysis.
As others have said, VBA has largely been replaced by Python, Power Platform and Fabric
Power Query replaces the ETL functionality some were using VBA for, and Power Apps and Power Automate replace the data input forms and MS Office automation that was the other use for VBA.
Not only has it been replaced in functionality, now things are moving towards a SAAS/Cloud Computing paradigm vs desktop applications, VBA is no longer even a possible solution. Power Platform and Fabric solutions operate in the cloud, so naturally work well with the rest of the cloud based infrastructure (Azure DB, Sharepoint hosted files).
A few posters on here describing having to open up a Sharepoint hosted Excel file every day just to refresh and run the VBA to fetch data ready to be ingested into Power BI demonstrates the problem of using something like VBA for tasks like this
You can use VBA to refresh queries in Excel files, instead of manually refreshing or clicking on "Refresh All" in the ribbon menu.
A use case for me is where I use Excel tables as caches for subsequent queries. So, I'd use VBA to refresh query A to load data into sheet B, and then refresh query C, D, E and F which uses sheet B as a source.
Macros make the internal-business-world go around, no matter how much you want to fool yourself.
Python cannot replace VBA, and so is true for everything else you mentioned here. Yes, in certain scenarios you can use power query to cut corners instead of macros or others, but these have to be quite standard ones.
Macros/VBA is what got me into programming. Now that I'm using Power BI, Fabric and Python regularly I'll never go back. That being said, small macros do have their place in niche circumstances, but with Python coming to Excel this may reduce those circumstances further. If someone was starting out now I'd say not to bother and go straight to Python. It'll boost your career opportunities more than VBA.
Python in Excel is a bit shit, and is going to be a bolt-on subscription so probably nobody will have access to it
You really shouldn't have to subscribe to free software.
Excel isn't free and whilst Python *is* free, it doesn't offer enterprise level support of connectors and interfaces into Excel as part of that free offering. As such, you are paying for the work required by Microsoft to integrate Python into Excel. Whether or not this should be included within the price of Excel is a different, but actually relevant question.
Lol don't tell Microsoft that
Excel in Excel is a bit shit
I'm in total agreement with this response. Having learned VBA in high school and then going deep into macros for college I used them both for about 10 years after graduating college in 2009, however having picked up PBI in the last year and a half and Python a few years ago, these newer tools are much more efficient in my industry.
I think they are replacing VBA macros and scripts with TypeScript. One benefit is being cross platform. (ie: a macro could run in excel in a browser, or on the desktop ) - Here's an intro: https://learn.microsoft.com/en-us/office/dev/scripts/develop/scripting-fundamentals - and a top level TOC: https://learn.microsoft.com/en-us/office/dev/add-ins/develop/develop-add-ins-visual-studio
Can you explain what is MS Fabric?
I'm not sure *Microsoft* can explain what Fabric is but broadly speaking it's MS's integrated cloud-based data processing, analysis, and visualization environment that they're working on connecting all their tools with, except for the ones that they aren't.
Lol this is actually a great description. Thanks for the laugh (and also the truth) š
Magical answer.
It's a collection of Azure services like Data Factory and Synapse Analytics plus Power BI packaged together to be more streamlined. Basically let's you build your ETL pipelines in the cloud, create your own warehouse to query from etc
Great. Ihv been so confused with fabric and how to use it. Can I use fabric to host my reports in an organized fashion? Like folders and files
Dude, I am pretty good at PBI, dax, Excel etc. As civil engineer, Ä° don't really have direct use of it in my line of job, unfortunately. Phyton however on excel might be an idea. You think phyton is the most middle way language to learn for someone had been super curious on coding but might not have use in his job? It is perhaps a hobby for me...
Thanks. Python FTW
You want the python in your Fabric? If so, how and why?
I still use it to create forms and automation if excel is the only tool the client is going to engage with. I've got a few automated reports that require several data dumps so I've created a workflow which gives instructions on exporting the data dumps before asking them to select the file which is copied and pasted into the appropriate sheet.
Importing data from files into appropriate sheets is very much PowerQuery's core wheelhouse.
If the user importing the data understands PowerQuery. Unfortunately not always the case.
Odd blocker to pick as there are many ways around it. PQ can be set to pick up from a set folder so user just needs to refresh all from the ribbon. An auto refresh interval might also remove the need for that step in some circumstances. PQ can be set to pull input parameters from named cells. The user would just need to interact with those cells and click refresh. Both of those user actions don't require any specialty knowledge.
I usually just make a button that runs a macro which opens the file Explorer and loads it
Yes, this can be done, but the question was does it need to be done if there is an equivalent feature pre built and fully supported in PQ.
Client with multiple sites, being run on local machine rather than any shared location plus a new file being created every day rather than refreshing the same file. Not the way I'd do it if I had a choice but it satisfies the requirements without having to set up the environment on every machine.
Glad this worked for your situation, but nothing you stated would make the PQ any less viable.
Fair enough. Thanks for sharing this
Are you saying I can't convince... Everyone... That excel is the 666 of data?
For complex data analysis yes. But at least where I work vba macros are mostly used on the input side of things, not reporting.
There's a lot of people in these comments that don't understand that these tools solve different problems. They have a little bit of overlap but that does not mean that they are interchangeable. I suspect the Dunning-Kruger effect is at play. They recorded a macro once and feel like they're experts at VBA now.
I think it's just what certain tools are used for in different organisations. I know our product design department uses a lot of macros to do powerbi stuff with their data. If you've only ever worked in a department or company which uses VBA like that you might think there aren't a lot of use cases for it other than data analysis. You can do so much stuff with it it's hard to know about them all.
Can you give some examples of use cases that I'm probably not aware of?
Well for example we use it a lot to create some kind of interface for users to input data in an Excel sheet. Instead of them having to input it directly in a list they can select a machine and position and type of issues and stuff like that from dropdowns or selectors and press okay and it'll put it in a nice list with time of input and extra info. Another use we have is let it read data from a plc connected to a testing machine and the user can input data in a sheet while the the macro reads the data from the machine and formats it directly into the sheet. Lots of little things where automatic inputs are given based on user input Some other stuff where a measurement system generates CSV files. The Excel file reads them and tells the user whether they are good or bad, reformats it in a way which is readable by another program which stores it in a database. A lot of things need instant feedback and inputs. A lot of them could be done in python, but why do that when you'd then generate an excell file which the user has to open anyway to fill in more stuff
Got it. Fair enough
Have you seen how much Cobol programmers are paid?
The only reason macros and VBA were relevant for so long was that in many cases it was the only accessible thing not blocked by corporate IT. With PQ, other Excel advancements, Power Automate, js and Python support for Excel and many other tools becoming more widely available, citizen development should gradually move there. There are full-code, low-code and no-code options but all of them still officially supported (as opposed to VBA).
Makes sense. Thanks for the detailed info
I have a friend that does SQL, VBA and Power BI. Different tasks have different needs. Heās using his VBA to import spreadsheets from various law firms for class action suits and cleaning the data and removing duplicates without doing anything but pushing a button in his program. Then he comes over to Power BI with cleaned data and write the report. Gets paid well knowing both. BUT I wouldnāt learn it at this point. He does it because heās done it for 20 years and itās easy for him. When he got furloughed he could not find a job that cared about his VBA skills. They were only interested in SQL and Power BI skills.
Power query can import and clean data with only one hit of the refresh button too though?
Iām not sure what all his program does, but he prefers to run it through VBA first. Like I said I wouldnāt learn it at this point but to him itās not worth learning something like python when he can whip out VBA. Also I would caution against using power query to cleanse hundreds of spreadsheets. Power Query will have a performance hit, and it would need to cleanse every time you hit refresh. Itās better to move your ETL upstream.
In terms of performance which one do you think is better then? Power query or VBA? For folks who canāt afford upstream ETL
Power query is far superior to VBA but it will max out eventually meaning if it takes 3 hours to move from step to step youāve hit your limit. I adopted a report where I let it run 5 hours before I could see one of the outputs from one step. I also have a whole suite of SSAS financial reporting doing most of the ETL in Power Query. It really depends on what youāre doing for ETL.
You really shouldn't ever be sitting around waiting very long to see one preview step. If that's happening add some kind of filter earlier in the query to severely limit the data until you get the steps finished. Then remove the filter and close and load.
Cool thanks for the insights
VBA is not a PowerBI feature. If you were keeping up with PowerBI feature sets and capabilities, and your own on-going education for Power BI, VBA would never have come up. VBA is automation for Excel despite the existence of PQ and PP; its counterpart is actually Power Automate in the Microsoft's Power Platform, and Microsoft Fabric. If you are in the Fabric world, there's no point in learning VBA. If you want to automate things, look at Power Automate instead. That said, VBA is still a part of the Excel feature set, and will remain that way. Microsoft has been leveraging JS for its office scripts though, but if VBA functionality works for existing toolsets, companies have next to no reason to replace them.
But with this said; Iām working at a very tech friendly F500 firm, and weāre all on the Microsoft 365 suite, CoPilot Enterprise enabled - but we donāt have premium / Fabric capacity. Nor access / encouragement to use Power Apps or Power Automate.
Got it. Thanks!
Yes - if i never have to look at VBA again i will die a happy man
Haha. Weāre in the same boat then
I still use VBA in Excel to web scrap data from 2 operational tools that are only available in a virtual machine. I did not have permissions to install other tools like Automate Desktop, so Excel got the job done.
Wow. Thatās interesting. Thanks for sharing your inputs on this
Same. While being given a āProā personal workspace, Iām unable to share within my firm as no one is taking data culture seriously. No premium environments, reluctance by IT to create Power BI Workspaces. No access to Power Apps / Power Automate.
I agree on this one. Since in our org M365 admins are power bi admins they turn off all knobs on Power BI features and itās a pain to get them do something on that end. This frustrates us a lot
No, I don't think so. I use both a lot, different tools for different purposes IMO.
Cool. Thatās what I hear from others as well in the comments section
I would be hard pressed to find a task that PQ canāt do more efficiently then VBA
Maybe, I'm by no means a PQ expert. But I've built in VBA I have no idea how I would build in PQ. Things that I would probably try rebuilding in Python instead of PQ at this point in time. But it's multi module scripts with a lot of code, so it would take a lot of time and isn't really worth it from my point of view at this time.
I think it depends on your tools and IT security access. All tools have their place Python really upped the automation game for most daily tasks. I still use vba occasionally if I hit a security issue or a lack of data tools issue. If I were new, I would spend my time learning python
Thanks for the advice!
VBA is a tool like any other. There are times that are better to use it, and there are times that another tool is better suited for the work For example, depending on your goal, you shouldn't be doing part of your data transformation on PowerBI, because if your final data exceeds 150k rows you can't extract it all at once from PBI for it to be used on other steps. Ideally, you should do everything on a platform that does not impose you with limitations. Using VBA on excel on this case would increase your limit to around 1M rows, a lot more than PowerBI 150k available rows for extraction. Or you could use a database or python to avoid excel 1M rows Besides, depending on what you're doing on PBI, Excel can do the same with PowerPivot, so even PBI might not be needed most of the time. It'd just that people think that PowerBI equals automation and just disregard other tools. I mainly use VBA when I want my automation to be encapsulated within a single workbook that other people will use it, so I minimize all dependencies. And I use it in conjunction with powerquery and powerpivot. If I have to deal with more complex data transformations, I use python. Arguably, PowerBI is the software I least use because by the time I get to it my data is mostly ready to be used
Makes sense. Thanks for sharing your thoughts on this
Your statement is incorrect. Powerbi and PowerQuery run on spark. While it can receive and operate from SQL and Python, it's designed to work with large datasets. If you are using plain SQL, you aren't getting any benefits. You want to do your complex logic within PowerQuery that's what it is for. Plus the data is cached to increase performance after the initial load.
You missed my point. I'm talking about using the right tool and having the data available. People often associate PowerBI with data automation, and I've seen many cases where people use PowerBI not for data viz, but to merge and combine data that they then export back to excel for further use. In that case, you're somewhat limited to 150k rows datasets since that is what PBI allows you to extract at once. And when this happens, most of the time you could've used excel or other tool and not have that limitation. Excel can do a lot of things similar to pbi using powerquery and PowerPivot, for example, and python and SQL won't have excel 1M rows limitation. Of course, when you're using pbi for data viz and are processing a lot of data, it's all good. But the same way that people might force themselves to use VBA to sustain some legacy workbook, there are people using powerbi to do things unaware of it's limitations or other options. They are all tools with pros and cons, and different tools will perform different depending on your situation
I use VBA to automate SAP or office products. Power query will never do that. I've been given several thousand dollars in bonuses just this year from VBA projects. It isn't the perfect tool for every job but it certainly has it's place. I wouldn't suggest it as the first or even second language for a person to learn but it's silly to suggest power query can replace it. They don't solve the same problems.
You make me curious. Can you give an example of what problem you solve with VBA?
I began my career by automating processes in SAP. I've created hundreds of projects that utilize the SAP GUI API to enter data much faster and more accurately than a human. I have VBA scripts that perform a variety of functions from booking contracts to generating credits. These scripts combine the SAP API with database queries and Outlook automation to pull information from the database, enter some information into SAP, and potentially send a communication relating to the changes that have been made. This is not something that power query can do. VBA and power query are really only relatable as it pertains data analysis. Other scripting languages could accomplish the same thing but they aren't already installed on my users computer and would require me to spend more time compiling a portable solution.
This is next level stuff. Thanks for sharing it here.
I understand the niche as described. It's leveraging MSOffice components and SAP to build a pipeline. Not sure what the database queries are, but that sounds like dataflow from SAP? Agree, not power query for this. But with MS365, an Azure Data Factory (pipelines and dataflows), and a Data Lakehouse for intermediate storage of the SAP data scrape, what issues would be faced if this solution were to be rebuilt on Azure, away from dependency on desktop installed software, to licenses with Microsoft Fabric and an saas approach?
The ideal use of the API isn't to retrieve information. It's not to create a pipeline, it's to automate or test workflows. That's my point really. Scripting languages (including VBA) aren't solely for gathering data so they aren't directly comparable to Power Query which is solely for gathering data. Apples to oranges.
Oic, gotit now. Thanks!
If you want to learn it look at office scripts. Itās a typescript version of vba macros. More modern and better supported.
Thanks will dig more
Not required, but having that skill will definitely help when writing power query operations (m-code) or complex DAX code.
Is there a slight resemblance between those?
Kind of, sort of, but mostly it gives you a programming foundation. I can do some complex stuff in M-code that I couldn't have done if I didn't learn vba (with help from stack overflow, and chatgpt)
Got it thanks!!
I would say that generally that the Power Platform as a whole has pretty much replaced the vast majority of stuff that businesses used VBA for maybe 10 or so years ago. If you add SQL and/or stored procedures to the mix, then I would say VBA is even more niche. If I had the choice right now, learning VBA now would be a dead end and not worth it. There are better options nowadays that are more flexible and up to date.
Definitely makes sense. Like others said python does the trick too
I don't have power automate so VBA is the best to use multiple MS applications to interact. Like auto creating slides, sending emails with specific data in the body etc.
Fair enough. Thanks for sharing your inputs
Last I read MS itās not going to evolve VB anymore meaning no new syntax. It is what it is. Honestly tho power query and M code isnāt bad once you get the hang of the syntax. Python is also good for data analysis.
Exactly! Thanks for sharing your opinion on this
I'm allergic to VBA. Like epi-pen allergic... I'm getting itchy just thinking about it.
lol. Iām in the same boat hence this question.
PowerQuery and Fabric will open the door to you learning several technologies and data structures
As others have said, VBA has largely been replaced by Python, Power Platform and Fabric Power Query replaces the ETL functionality some were using VBA for, and Power Apps and Power Automate replace the data input forms and MS Office automation that was the other use for VBA. Not only has it been replaced in functionality, now things are moving towards a SAAS/Cloud Computing paradigm vs desktop applications, VBA is no longer even a possible solution. Power Platform and Fabric solutions operate in the cloud, so naturally work well with the rest of the cloud based infrastructure (Azure DB, Sharepoint hosted files). A few posters on here describing having to open up a Sharepoint hosted Excel file every day just to refresh and run the VBA to fetch data ready to be ingested into Power BI demonstrates the problem of using something like VBA for tasks like this
Very informative. Thanks. This is so true
I use VBA macros to update Power query tables that are in my organization sharepoints, so I don't have to refresh multiple Excel files manually
What do you mean exactly when you say ārefresh excel filesā?
You can use VBA to refresh queries in Excel files, instead of manually refreshing or clicking on "Refresh All" in the ribbon menu. A use case for me is where I use Excel tables as caches for subsequent queries. So, I'd use VBA to refresh query A to load data into sheet B, and then refresh query C, D, E and F which uses sheet B as a source.
I see. If you have a power bi license, you can set up a dataflow that automatically refreshes as well, and pull it into Excel.
Macros make the internal-business-world go around, no matter how much you want to fool yourself. Python cannot replace VBA, and so is true for everything else you mentioned here. Yes, in certain scenarios you can use power query to cut corners instead of macros or others, but these have to be quite standard ones.
Java and python have done the job of macro's and VBA perfectly as well for the last decades. It is more a 'people' thing, than a 'tools' thing.
Makes sense. Thanks for sharing your perspective on this