Coding Microsoft Excel 1

[As-Lag]

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

 

[IRstuff]

2016 is the same

TTFN (ta ta for now)
I can do absolutely anything. I'm an expert!

https://www.youtube.com/watch?v=BKorP55Aqvg

faq731-376 forum1529 Entire Forum list

http://www.eng-tips.com/forumlist.cfm

 

[3DDave]

https://support.microsoft.com/en-us...-the-web-98784ad0-898c-43aa-a1da-4f0fb5014343

So - on-desk Yes. On web version, it retains, but doesn't execute or allow editing.

 

[IDS]

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/

 

[dik]

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

 

[Denial]

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

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

 

[DCBII]

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.

 

[As-Lag]

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.

 

[DCBII]

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