How to counter objections from IT to VBA

Many an IT manager and IT folk despise Office and Visual Basic Automation (VBA). They may say "you can’t write good code in it", "it becomes a mess", "no one can maintain it" ...

That code can become a mess is true for all programming languages. One of the worst is C / C# where you can write thousands of lines of code, on one-line. Because one can make a mess, it doesn’t mean programmers do.

As far as maintainability is concerned, less code equals more. For example, in VBA one needs one statement to do a REST call, compared to a long block of code in PHP, java, or C#, to achieve the same thing. Which is easier to read / maintain, one statement, or a block of code? You do of course need to have the knowledge of what is possible – maybe that is the issue for many in IT, they simply may not know what is possible in Excel/VBA, and just repeat what others say?

Maybe these IT folks are reluctant, because they have never seen good VBA code, or they have a vision of the poor code produced by macro recording? Ask them, and then show them an example like below that we or many other skilled VBA programmer produce.

Your IT people may also raise that there is no version control, and it is hard to distribute. Excel, Word, and other Ms-Office automated tools, do not need change control and complex release management to roll them out in your organisation. Everyone has Ms-Office on their desktop already, and everyone has access to the company’s internal storage or email to distribute / pick up the latest the solution. And there are many other ways to make version control easier, such as creating Add-ins, so that all functionality is in the Add-in, and not in the workbooks. Thus, when opening older workbooks, you will have the latest functionality.

Lastly, your IT department may raise security issues, but despite their claims, as long as they have configured Ms-Office correctly, and use Windows Defender or better, it is no riskier than any other IT you use! (see the tips below on how to be as secure as).

Do you need help to argue your case? Ask us ✉.

Why VBA is so highly productive

What the IT-folk conveniently forget is the massive coding productivity Ms-Office provides. The power of Ms-Office automation is that you get a fully working User Interface of edit controls, data types, look-up tables, even REST interfaces in a single statement, straight out of the box.

All that UI-functionality represents thousands of hours of work, which the IT department would have to code and maintain if they built their own bespoke Windows App or Web-app. 

The reason Microsoft Excel, Word etc are so powerful is that - in addition to just being able to layout, add your data / text, create formula and syntax check the inputs – Ms-Office VBA gives the programmer direct access to the Office application object model.  What this means is that the VBA programmer has direct access and control over:

·          Documents, Sections, Paragraphs, tables … or

·          Sheets, tables, cell content, and cell formula

·          Charts and forms

·          Parse data strings (extract the words in a string) in a single statement,

·          Pop-up the windows file and folder pickers to open and save / save as,

·          Do a REST call to get say currency exchange information of a web-site, in one line of code,

·          Manipulate the look and feel, formats

and much, much more. All in very clean, compact code.

But best of all, if the functionality you need isn’t in Office, then VBA lets you access the Operating system, DotNet, or web-services, to let those technologies perform the required function. Now the IT folk may say that poses a security risk, but not really, as you’ll read below.

Let’s look at efficiency to get your job done. You could get a nice web application, using full stack development for web front and backends. This requires business cases, approaching IT, being put in the queue, having to wait for 12-18 months. Then, when they start, there is a hodgepodge mix of tools and languages, complex skills, analysis, testing and more.

Pointing back to their possible objections listed above, have you checked how easy that full-stack development is to maintain? If it were so easy, then, why have we seen so many projects in the last 10 years where it takes 8 days to produce a simple screen/ form in these complex toolsets, whereas it takes 3 days for a windows app, and a few hours or less in Excel. Of course a web-app has appeal as it is accessible and sharable from everywhere. But while you wait for that or to get your job done, you may as well start with a good enough VBA solution, sharable via your cloud storage.

VBA and security

One word of warning though – because VBA is so powerful and can get direct access to the deep operating system functions as if it is an administrator, it also poses security risks. At present some in the security world say ‘Office’ is the hacker’s tool of choice! And the reason is because it is so easy and so extremely productive and powerful!

The line of defence? Never open a workbook which you have not requested or have the vaguest doubt about. Always have your security settings set to “Disable all macros with notification”. Yes, it is a pain that option, but do not switch that off !

In developing our software licensing module, we have learnt a hell of a lot of how people circumvent and hack with Excel. The golden rule is : if your Office apps are set not to automatically execute, then VBA will not run, and you will be safe. Office files with extensions .xlsx, docx, pptx etc cannot contain code. Files with extensions .xlsm, docm, etc, do contain code, and are treated by office in accordance to your security settings. Old .xls file formats need to be treated with special care, as they can contain code and are now a vehicle of choice to hack and attack. The work around is simple: do not accept and open a ‘.xls’ file, ask the person to convert to the “new” (since 2007) format, and then try.

To set / check your security settings:

This is probably not needed as Ms Office sets the correct setting on installation, but your IT department or for some unknown reason it may not be right. So check as follows:

•          In Excel or Word or any Office app, Click/tap ‘File’

•          Click/tap ‘Options’ (at the bottom left)

•          Click/Tap ‘Trust Center’ (bottom left)

•          Click/tap the button ‘Trust centre settings’ (yes Ms, why make it quick)

•          Click/tap ‘Macro Settings’

•          Check or change to: “disable all macros with notification”

•          And make sure the tick mark for “Trust access to the VBA project model” is On.

•          Then check ‘External Content’ and make sure all settings are set to Prompt or Disabled.

•          And finally, check ‘File Block Settings’ and consider to set “Open selected file types in Protected view” for all files that are not Office 2007, but at least from 1995 back in versions.

There are other settings, but these are the critical ones. The more you constrain, the harder it gets to work, so prompting is the way to go.

If you observe these points, then there are no real risk. And your virus checker / internet security suite will help you safeguard against temptation (of forgetting about these rules).

If your CTO, IT manager etc is still not convinced, show him some of our code. We have posted a tool to extract system information. If they are nervous, tell them to have the “Disable all macros with notification” set ?, and then open the workbook, and use ALT+11 to inspect the code. They may even find it handy to use this to collect system information from all their machines ?.