BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
Posted by Andy Brown on 28 June 2020
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Microsoft dislike VBA in Microsoft Office, and would like to get rid of it (I'm never quite sure why). They're now on their second bite of this (indigestible) cherry. The two bites are:
|Visual Studio Tools for Office||VSTO was introduced a fair few years ago, and we proudly announced a course in it, believing that this would eventually allow people to develop applications based on Microsoft Office. We never got a single enquiry about this course, and it has never run.|
Reason 1 - Set-up and Deployment Overheads
Want to write a program in VBA within Excel? Here are the instructions:
- Open Excel.
- Press Alt + F11.
- Start coding.
- Run your macros to test them by pressing F5.
- Save your workbook as a macro-enabled file.
- Send it out into the world!
|What you'll need||Notes|
|A development environment||If you're a Microsoft developer, it makes sense to use Visual Studio as your IDE (Integrated Development Environment). The Community edition is available free of charge, although you'll have to get your IT department's blessing to install this.|
|Yeoman generator||This is a bit like a wizard, allowing you to create Office add-ins more easily.|
So I tried doing this. I ended up having to run a program from my command line, which generated reams of messages before finally hanging.
Even when you've got the above installed, you still have a long way to go, as this misnamed Microsoft Quick Start guide shows. And don't get me started on packaging your finished apps and deploying them ...
Reason 2 - The Language is Harder
- It's less like English.
If you're an experienced developer, there's plenty to find fault with about VBA, as summarised by this article, but the vast majority of VBA users will never need to know what classes, delegates and lambda functions are.
- When you tell VBA to do A and B, it waits until A is complete before beginning to do B.
But ... try making a cake with this method. The basic instructions are:
- Cream together butter/margarine and sugar.
- Mix in eggs.
- Mix in flour and baking powder.
- Decorate and serve.
Apologies if this was your picture that I took from Google Images. I hope the birthday went well. Realistically, though, we won't get anything this recognisable as a cake from our process.
Almost every macro that you write in Excel requires that steps occur in a certain order. A typical algorithm might be:
- Go to the last non-blank cell in a column.
- Copy its value.
- Paste it into the cell below it.