Tag Archives

2 Articles

Keep macros under control

I have been getting some feedback during the past three weeks (during which I was on vacation) on my post about macros, where I claim that macros are dead. Apart from the odd hateful mail from a hardcore macro lover, the main feedback was: I get what you say but right now I am stuck with all these Excel files with macros. I do not know where to begin, can you help?

To those of you I say: do not despair. If you have a lot of Excel sheets with macros (and lets face it, Excel files are where most macros are found!) and you have Office 2013 the solution is just around the corner.

Open Excel, click File, Options, Add-ins. Then at the bottom where it says ‘Manage’, select COM add-ins and click ‘Go’. Then enable ‘Inquire’ and click OK.

This add-in enables you to investigate an Excel sheets for lots of things, such as hidden sheets, very hidden sheets (I did not even know that was possible), formula’s and macros. Also, you can check out dependencies between sheets and sources and compare two sheets. When comparing two sheets you can even spot the difference between macros down to a single line of code!

This solution is also available as server solution for some more automatic scanning of your Excel workbooks. It is called Audit and Management Control Server 2013.

More info here:

Inquire add-in for Excel: http://office.microsoft.com/en-us/excel-help/what-you-can-do-with-spreadsheet-inquire-HA102835926.aspx

Audit and Control management server 2013: http://technet.microsoft.com/en-us/library/jj631654.aspx

Get those spreadsheets under control!

Macros are dead

Macros are dead. Or soon will be. Think about it: in Office 2003 when you opened a file with a macro the macro was automatically enabled and ready to run.
Then with the arrival of Office 2007 things turned bad for macros and macro creators. Macros were treated as security risk:

As a user you explicitly have to choose to run the macro.
Then, Office 2010 came along and the security warnings became bigger (and I believe you had to click twice to enable them instead of once). The same goes for Office 2013.


Also, along the way this warning was introduced:

Look at those first lines: ‘might contain viruses or other security hazards’. That kind of says it all: macros are dead. With the extra focus on security this makes sense. Also, eliminiating macros helps you to deal with spaghetti code lurking around in your documents. I feel you should not be creating any new files with macros and files with macros should be checked and migrated to something “better”.

Do not use macros if you want your document to be opened without security warnings. Also, know that macros do not run on all mobile devices (For example, Office RT does not run macros).

So, what do you need to do if you need to program in your Office documents? Well, if you’re using Office 2007 or 2010 you should be developing a VSTO (Visual Studio Tools for Office) add-in, which is a piece of managed code built using Visual Studio, which is essentially an add-in with the big difference that the code is not sitting in the document itself, but outside of it. If done well, this code can be centrally managed and be treated as what it actually is: application code.
See: http://msdn.microsoft.com/en-us/magazine/cc163292.aspx.

Now, for Office 2013 you should be building apps: http://msdn.microsoft.com/en-us/office/apps/fp160950.aspx.

%d bloggers like this: