×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Coding Microsoft Excel

Coding Microsoft Excel

Coding Microsoft Excel

(OP)
I use Excel 2007 extensively. I write VBA every day and I particularly like the 2007 VBA programming environment, it is far better than the open-office variant (still doesn't stop me using open-office - excellent program - merge pdf's without adobe). I have just bought a new PC running Windows 10 and was considering buying the latest Excel.

My question is - does the new Excel still have the VBA environment (i.e. right-click on a tab and choose 'View Code') or do I have to buy some form of Visual Studio to write code?

Thanks

Dan

RE: Coding Microsoft Excel

Yes - desktop Excel VBA remains essentially unchanged from the 2007 version and should run all your old code without a problem. If you get 64 bit Office there may be some issues with calls to dlls, but that is fairly easily fixed.

Microsoft seem determined to switch everybody to JavaScript, but the last time I looked at it (a year or two ago) the whole process was very cumbersome and complicated, and there were also some basic issues with JavaScript and big numbers, which may or may not be fixed.

Another alternative is coding in Python and linking to Excel with pyxll (commercial) or xlwings (free open source). This works well (once you get used to several Python eccentricities), but it does require learning a whole new language, and even after getting reasonably familiar with Python the coding process is nowhere near as quick and easy as using VBA. The de-bugging process in particular is much easier and quicker with VBA. The main advantage of Python (from my point of view) is access to useful libraries for just about anything, including much better curve-fitting, solvers, and linear-algebra routines than are available in VBA.

For running code in the on-line version of Excel, pyxll (but not xlwings) completely bypasses VBA, and calls the Python code directly, so in principle it should be possible to set that up on-line, but I haven't tried it.

Finally Microsoft said (several years ago) that they were looking at incorporating Python into Excel, but that seems to have gone quiet.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

RE: Coding Microsoft Excel

Quote (Another alternative is coding in Python and linking to Excel with pyxll (commercial) or xlwings (free open source). This works well (once you get used to several Python eccentricities), but it does require learning a whole new language, and even after getting reasonably familiar with Python the coding process is nowhere near as quick and easy as using VBA. The de-bugging process in particular is much easier and quicker with VBA. The main advantage of Python (from my point of view) is access to useful libraries for just about anything, including much better curve-fitting, solvers, and linear-algebra routines than are available in VBA.)


Thanks, Doug...

Rather than think climate change and the corona virus as science, think of it as the wrath of God. Feel any better?

-Dik

RE: Coding Microsoft Excel

As-Lag.  When I moved to Excel-2010 quite a few years ago I found one existing "feature" that behaved differently from my predecessor Excel version.  It was in the default values assumed by the Solver add-in.  I discuss it at the first item in
thread766-298735: Running Excel-2010's version of Solver through VBA

I did not discover any other "backwards incompatibilities" (a statement that comes without guarantees).

RE: Coding Microsoft Excel

You'll be happy to know that the VBA integrated development environment (IDE) has has virtually no changes in the last 14 years, although any ActiveX controls such as buttons embedded in your worksheets may be broken.

Microsoft has been trying to kill VBA for years with no luck. They've used a few strategies that have all failed. First there was the .NET API for Office. Then, as Doug mentioned, javascript add-ins was their latest attempt. These tools were too technical for most users, and the existing user base for VBA is just too strong. Microsoft's strategy over the last couple of years appears to be "Let's only provide the bare minimum support necessary to keep VBA functional and see if the users go away on their own."

There were rumblings of a Python IDE on Microsoft's "Uservoice" website. It was far and away the most "up-voted" item for the past several years. But they've provided no action on it and the Uservoice system is being deprecated.

So, yes: VBA is still there, if you want to use it. I have libraries of VBA code I still use for structural engineering, but 4 years ago I decided to start learning Python. I'm glad I did. Python was easy to learn, especially with a VBA background. Free and open source libraries/tools such as xlwings, Jupyter, Spyder, numpy, sympy, pandas, matplotlib and scipy make VBA all but obsolete. 4 years later I have a robust finite element analysis library (PyNite) solving complex day to day engineering problems that I could never have dreamed of building in VBA.

RE: Coding Microsoft Excel

(OP)
Thanks for the information. i have been planning on using Python for a while, particularly as I use LibreOffice extensivelly as well as Excel. Just need the IDE.

RE: Coding Microsoft Excel

Jupyter and Spyder are both great IDE's for Python. xlwings runs in VBA's IDE.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login



News


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close