Continue to Site

Eng-Tips is the largest engineering community on the Internet

Intelligent Work Forums for Engineering Professionals

  • Congratulations Danlap on being selected by the Eng-Tips community for having the most helpful posts in the forums last week. Way to Go!

VBA and Office.js

Status
Not open for further replies.

phamENG

Structural
Feb 6, 2015
7,396
With the move toward cloud computing and decentralized file storage and access as well as the ubiquity of mobile devices and their rapidly advancing computing power, what does everyone think about running calculations using Office.js rather than VBA?

Is anyone aware of mobile/web version support for VBA in the future?

Has anyone attempted to use Office.js or translate VBA calculations to Office.js? Was it straightforward or did you have to start completely from scratch?

 
Replies continue below

Recommended for you

Not aware of anyone who uses office java script. But looking through the API many of the functions at least appear mildly similar to the VBA equal. So in theory once you know java script nomenclature I'd imagine it's not too hard to refactor VBA to office.js.

I'd imagine the biggest barrier would be simply getting familiar enough with java script if you're not already. I've written a small amount in bluebeam to do various tasks and I didn't find it anywhere near as intuitive as VBA or python. But I was a total beginner.

Personally I'm hanging out for them adding native python support to open up a world of possibilities with the libraries available. It seems like it may happen one day.

I can't imagine they will ever add mobile support for VBA. But you never know.
 
It doesn't take much research to conclude that the on-line versions of Office programs will never support VBA.

Windows versions will continue to support VBA. Until there are no more Windows versions of Office, or until MS decides to no longer support VBA (probably unlikely).

 
Agent - thanks for your insight. Python is on my to-learn list. I just want to make sure whatever I choose to learn will have the widest range of applications going forward - use in web based calcs, mobile apps, etc. SMath has some interoperability with Excel (dik is running a thread on that right now), and they have a fully operational mobile version.

MintJulep - that's what I had concluded as well, but I know there are several people here that are much more adept and aware of industry trends when it comes to programming languages. Figured I'd ask and see if anyone knew something I didn't.
 
Rewriting UDF (User Defined Functions) in Excel from VBA to Offic.js looks to be "relatively trivial". At least the code itself. Generating all the support files, manifests, html, etc. doesn't look difficult, but compared to VBA where there is nothing else to do after writing the code there is extra effort involved.


I've created some 10,000 lines of VBA code that automate business processes. Someday it seems inevitable that they will need to be ported to Office.js. Since programming is not actually my primary job, I'm hoping that I won't be the one doing it.
 
Support for user defined functions is only fairly recent in Office.js, and I did have a look at the process shortly after that functionality was announced. Whilst agreeing with MintJulep that the process is "relatively trivial" and that generating all the support files isn't all that difficult, it was all sufficiently much of a hassle with little real benefit (to me) that I haven't gone back to it.

I also agree that Microsoft's position is that whilst they will keep supporting VBA they will not be introducing a Web-enabled version, and all the new development is focussed on JavaScript.

Regarding Python, Microsoft said they would be working on native Python support in Office about 3 years ago, but they seem to have gone quiet on that. But there is really no reason to wait for that; there are several options for developing Python code that interacts directly with Excel, including xlwings (free) and Pyxll (commercial but inexpensive).

I am focussing on Pyxll at the moment and whilst the debugging isn't quite as smooth as in VBA, the development process is otherwise pretty much as easy as using VBA, once you have got used to the Python peculiarities.

Those interested in making Excel macros available on line might be interested in: "This article demonstrates how Anvil can be used to create web apps that can also power Excel spreadsheets with PyXLL"

Doug Jenkins
Interactive Design Services
 
Definitely you can already integrate python with excel and VBA quite effectively. Python can also integrate into Web based stuff, long history of this (I've no experience in this though).

It's just not native support in excel (yet) which means you're invariably going to potentially have some issues. Really opens up a lot of possibilities though that vba just cannot do. I'm currently using xlwings with Python/excel, seems to be actively supported and developed with updates on a reasonably regular basis.

Python is fairly simple to grasp, just took a while to come to grips with some of the formatting. But get VSCode and some of the automatic formatting extensions and you're good to go more or less after a few YouTube videos.
 
Thanks, everyone. I appreciate you taking the time to respond. I think I'll stick with my previous plans to explore Python and not worry about Office.js.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor