Allow Excel Macros to be written in PowerShell
Allow Excel Macros to be written in PowerShell.
It would also be amazing if it could be used as an alternative to field functions such as "=PRODUCT(A1:A10))"
I believe that Microsoft Excel naively supporting PowerShell as a 21st century alternative to VBA would be the most beneficial change made to the software since release.
Almost all Microsoft products are becoming PowerShell integrated, yet Office remains only supporting VBA.
VBA is an archaic procedural language which despite its ambivalent love from the community, seems to have little to no place in the modern world.
PowerShell can directly interact with COM objects eaxctly just as VBA does, and so much more. PowerShell macros would be incredibly useful and utterly transform the scope of work which Excel is used for.
Additionally, this would be one step closer to unifying Microsoft products.
Mike Guest commented
For those complaining about security, bear in mind that whilst your usage of excel does not require the use of macros, that's not the same for everyone.
No need to decommission VBA - just bring in powershell as an alternative. There are plenty of very useful macros out there that people won't have the time/talent/patience to convert to powershell. Simply add powershell as an alternative.
There is precident for this sort of change. VBA replaced wordbasic, after all.
Brian Z. commented
I'm in favor of being able to spawn a PowerShell session and execute commands (mostly for reporting, but also updating content) directly from Excel. Particularly when I have numerous columns to be mindful of. When completed, have the option to spawn another Excel application to review the findings or verbose log output.
Bernard Schleich commented
It is a very good idea !
It is time that Microsoft stop to develop proprietary language.
If PowerShell is open I have no problem to work with it.
But VBA is a Very Bad Application to develop :-)
I gave up on this. With the ImportExcel PowerShell module I just create great excel reports and never even have to see VBA.
David Johnson commented
I understand the concerns over security, but I completely support this; it's something I've wanted for years. I find VBA horribly clunky as a macro authoring language, I can never get it to do what I want first time, whereas PS I find far more fluent. Access to at least a restricted set of .net would be such a blessing. Full access to the datetime class, imagine that! It would also move towards unifying the common windows scripting language. Who writes vbs scripts anymore?
Tom A. Vibeto commented
If they started the powershell engine with module auto-loading turned off and a requirement that non-default modules would need to be imported by the user (a separate profile folder), wouldn't it be safer than VBA?
How much trouble can be caused when running in a module-restricted session? Is it still possible to load and execute an external binary file? Can one still execute random C# code?
James Lear commented
The way that PowerShell talks to COM objects is pretty much identical to VBA so there shouldn't be too much of a learning curve. Additionally, you can write PowerShell functions which act as commands to make it even easier to use. For instance you could have something like:
Write-Cell -Worksheet $WorkSheet -Coordinates (A,120) -Value "Hello World" -Append
Or something less verbose. The point is that this could aid a lower learning curve, especially as skills learned in PowerShell can be used anywhere, while VBA is only used in Office. Since it's a procedural language, it wouldn't provide much to help you learn other languages either.
Also, I am requesting that macros can be written in PowerShell as well as VBA. It certainly looks like a step forward.
Dan Elgaard commented
VBA has one huge advantage over PowerShell: Simplicity - even the most novice user can make a smiple VBA program in less than a hour - perhaps using the macro recoreder.
Microsoft is already killing Windows, removing flexibility and ease of use, in the desperate attempt to copy everyone else with apps, emetro, thouch-screen squares, user restricktions, etc. !!!
Please, don't destroy the Office suite too?!?
Not in favour of this at all. Excel macros should be removed entirely.
Personally I'd rather Microsoft killed PowerShell and switched to bash instead.
I've spent a lot of time working with PowerShell and I'm afraid the only good thing about it for me is that I don't have to work with it any more.
A proper .Net language such as C# or VB would be much better.
James Lear commented
Hello, Mr. Annoymous.
VBA can actually open and execute PowerShell anyway (regardless of execution policy. This suggestion wouldn't introduce any new vulnerabilities. It would however, make excel manipulation via PowerShell extremely easy.
This is a horrible idea, the current VBA is already such a major source of malware that building in PowerShell capabilities would be like opening the door and inviting the malicious actors in.