The front page of Excel for Developers now has a link entitled VBA, which takes you to:
which has a link entitled When to use VBA and why which says:
There are three principal reasons to consider VBA programming in Office 2010.
Automation & Repetition
VBA is effective and efficient when it comes to repetitive solutions to formatting or correction problems. For example, have you ever changed the style of the paragraph at the top of each page in Word? Have you ever had to reformat multiple tables that were pasted from Excel into a Word document or an Outlook e-mail? Have you ever had to make the same change in multiple Outlook contacts?
If you have a change that you have to make more than ten or twenty times, it may be worth automating it with VBA. If it is a change that you have to do hundreds of times, it certainly is worth considering. Almost any formatting or editing change that you can do by hand, can be done in VBA.
Extensions to User Interaction
There are times when you want to encourage or compel users to interact with the Office 2010 application or document in a particular way that is not part of the standard application. For example, you might want to prompt users to take some particular action when they open, save, or print a document.
Interaction between Office 2010 Applications
Do you need to copy all of your contacts from Outlook 2010 to Word 2010 and then format them in some particular way? Or, do you need to move data from Excel 2010 to a set of PowerPoint 2010 slides? Sometimes simple copy and paste does not do what you want it to do, or it is too slow. You can use VBA programming to interact with the details of two or more Office 2010 applications at the same time and then modify the content in one application based on the content in another.
That’s all very well; certainly those are three things that VBA is good for, but do Microsoft really think that encompasses all the principal things you can do with VBA? What about the ability to greatly extend the maths functionality of Excel with User Defined Functions that can for instance:
- Solve high order polynomial equations
- Carry out numerical integration
- Evaluate formulas entered as text, including conversion of units
What about creating engineering applications that would be cumbersome and very limited if relying entirely on built-in functions on the worksheet, such as:
- A frame analysis application
- Functions for elastic analysis of reinforced and prestressed concrete beams
What about linking to non-office applications such as:
- The ALGLIB maths library
- The Strand7 finite element analysis package
- The ImageMagick freeware image processing software
The reason for Microsoft continuing to undersell the versatility, power and convenience of Excel when combined with VBA remains a mystery to me.
Newton Excel Bach, not (just) an Excel Blog 
I suppose that “Getting Started…” piece has been written with the lowest common denominator in mind, but you’re right about the underselling. The next section you’ve not recreated here (“Doing Things another Way”) positively tries to persuade you not to use VBA – to the extent of suggesting doing a task once and then hitting Redo. There are people who will sit there and will press Ctrl-Y 50 times every time they run some process, and not think that there’s any issue with doing so.
There are still people about who sit at their desks with print outs of two related tables and look for the matches by eye because they’re scared of VLOOKUP (honestly I’ve seen it!). The “careful now” tone of that piece serves to make would-be VBAers similarly tentative. Microsoft should be more “go on! – dive in! – have a go! – make decent backups of everything and you can do no harm!!”
LikeLike
It is no mystery, just look to the origins of VBA. And don’t forget what the B in BASIC stands for. Bill Gates thought secretaries would be writing VBA macros, but it turns out people that don’t know much about programming will write bad programs no matter how dumb-downed the language is.
Microsoft has a different facility for integrating the things you mention into Excel – the Excel C SDK. The mystery to me is why you don’t use that. All the examples you mention are written in C/C++/Fortran. Why wedge everyting through COM and slow things down?
Sorry, rereading this I don’t think I’m coming off as very diplomatic. Having my first cup of coffee. Please try to imagine me saying this in the nicest possible way. I enjoy reading your blog and am very impressed by what you are able to do with VBA, but there are horses for courses.
LikeLike
Hi kalx, no need for apology, your perspective on it is welcome.
But!
The examples are not all written in C or Fortran. Most are entirely written in VBA, and those that link to C or Fortran have minimal code written by me in those languages. For me (and I’d suggest most people in a similar situation to me) the quickest way to develop an application is to develop a prototype on the spreadsheet, then code it in VBA for increased flexibility, speed and robustness. In the few cases where the performance of the resulting application is not adequate it may be worth linking to some compiled routines (preferably written by someone else), but even in this case the benefit of moving entirely to compiled code would usually be marginal.
It’s not just horses for courses, it’s horses for bits of courese, and horses for riders.
LikeLike
check this repository: https://github.com/rnfermincota
LikeLike
VBA is good. Just this morning, less than 20 lines of VBA (including DIM, and Const lines) saved a girl in our office an ENTIRE days work… Simple copy/paste operations into approx. 60 workbooks. BAM!
LikeLike
Hmmm…could have sworn I left a comment here a few days ago. Can you check your spam filter, Doug.
LikeLike
Actually in yesterday’s post Jeff.
Now released 🙂
LikeLike
Yes, Microsoft is underselling VBA, and I don’t quite understand why either. Fortunately, there are many things you can now do in Excel (with or without VBA) that go way beyond what Microsoft offers.
In the latest (third) edition of my book on “Advanced Excel for scientific data analysis” I give many examples, such as the 125 functions to do matrix algebra in Excel (including eigenvalue and singular value problems) or the almost 300 functions (again, working in Excel and/or VBA) that feature a user-selectable extended precision to over 32,000 decimals, including all of Excel’s Math & Trig, Statistical, and Engineering functions. Best of all, these functions are all free and open-access. And they integrate seamlessly with Excel.
I also describe when and where you might actually want or need to use these, although most likely not quite to their full 32K decimals! Have a look at my book (in your library, or available from Amazon.com, even in Australia) and judge for yourself. To my knowledge, there is no other book like it.
Happy computing!
Robert de Levie
LikeLike
Robert – an impressive collection of work there; good luck with the new release of your book.
I particularly liked your reply to “A. Scientist” on the Amazon review page (Edition 2 I think). It never ceases to amaze me the strength of feeling that many technical people have against Excel, often from a position of complete ignorance. I recall once having an on-line debate with someone who was convinced that VBA variants were single precision, and that was the best you could do in VBA.
One thing you might like to look into is that if you search on the book title on Google it takes you to the first edition at Amazon, advertised with a $200+ price tag. That page has a link to the Second Edition, but there is no link (as far as I could see) to the latest edition. You could well be losing potential customers who don’t find the Amazon page with the Third Edition and/or are put off by the high price shown on Edition 1.
LikeLike