How do I trust all files with macros that I've written?

[Johnny C]

This may sound bizarre. I've written a lot of macro's in my time. Some I'm not proud of but some I am (the one that copied ranges charts to Powerpoint, the one that identified external sources in a file) but maybe not the one that on a 10,000th entry opened the mousedraw and asked for cheese to be put in it, or another that one the 10,000th entry bounced a smiley face around the screen for 10 secs.). Anyway, a lot of files I've written have macros. Excel has started telling me macros are disabled because 'The source of this file [me] is unstrusted'.
I'm fairly sure I'm not schizophrenic, and oddly, perhaps weirdly in Microsoft eyes, I trust myself. Except with naga chilli sauce but that's a different problem.
I know I can digitally sign a macro (involving a middle finger), but it seems I have to go through every folder I've had a macro in, add it to the circle of Trust (Microshaft Trust centre, within FBI remit, stick your badges where the sun don't shine, I'm sure you're monitoring MrExcel) Then go and digitally sign (middle finger) every spreadsheet.

I know MS want to kill Excel, but is there a simple way I can end this misery.

Dear Billy Goats. I will be dead shortly. Please let me live out my dying days without having to put my balls through a wrencher.

 

[Johnny C]

It was handy (until recently) when xlsb's bypassed macro security.
At my last company I had to add XLSTART as a Trusted Location because the default setting didn't include OneDrive. Which I turned off anyway as it trashed pivot tables with dynamic ranges as sources (maybe they've fixed that now?)

 

[Johnny C]

PS Mark, my first use of Excel was Excel 2.0 in 1992/3(?) when Windows 3.0 came out. I had been programming on PCs since 1984 and no-one else in our IT dept had used them so I was the 'goto' PC person.
I'm old school, I learnt macros when you can to code them on a special sheet. I was a programmer in those days, when printouts were dot matrix and the paper had every other line darker (known as pyjama paper). We got a laser printer which didn't do that so it was hard to read across lines so I wrote a macro to colour every other line light grey. I still have it, updated for VBA. Sadly (or not) my time in IT ended after I wrote a VAX/VMS graphics utility that let the user click items such as menus, textboxes with a mouse, plus a version of Tetris that ran on the VAX but killed the processor. The Head of Technical was old school, didn't understand PCs or Graphics and went berserk because it showed him up. I was asked to move departments or leave, so I moved departments and became an accountant but one who understood technology which was rare and got me some very lucrative jobs

 

[MARK858]

I learnt macros when you can to code them on a special sheet

Believe it or not you aren't the only one, although I am not sure what it has to do with your current issue

 

[Johnny C]

Believe it or not I know that. I've been emailing Excel MVPs for about 20 years.
Just some background about why old school macro writers like me get annoyed about having to 'trust' macros I've written.

We all have unique MS id's so why not build it into a spreadsheet and give Trust Centre the blanket option of 'Trust files/macros I've written'?

Since 365 came out, 'updates' are half thought and corrected without you knowing what's changed. MS know my email address, they have done for 20 years, so why not send an email with what changes have been made and how to deal with them? It doesn't cost much and let people opt in or out as they want.

No-one has ever asked me what I'd like fixing - such as Excel still using ancient standards for calculations with more than 256 elements so you get bizarre answers such as 0.00000000014 when pivot tabling a thousand currency values that net to zero.

Oddly enough, a Function Evaluate macro I wrote in 2003 was sent to MS (not by me) and appeared in XL2007 as a standard function.

 

[MARK858]

The security message isn't an Excel alteration. It is a Office setting that affects all Office Apps.

Microsoft to block Office VBA macros by default

It’s a new security push from Microsoft.

www.theverge.com

 

[Johnny C]

It's an update that affects Excel users, it doesn't matter if it's an Office Update. I couldn't care less if it's Office or Excel. It will impact people with Word, PP and Outlook VBA (like myself). Some warning and advice how to deal with it rather than having to google MS support would have been nice. If they impose changes, tell us. Most Office users will have large IT departments to deal with it, but smaller companies won't and most end users will be flummoxed. As an 'Office user' fpr 30 years I had to google and post here, and at least I understand the techy bit. Most won't. Ask most Excel users about Trust Centre and the reply 'What?' will be resounding.

 

[Joe4]

It's an update that affects Excel users, it doesn't matter if it's an Office Update. I couldn't care less if it's Office or Excel. It will impact people with Word, PP and Outlook VBA (like myself). Some warning and advice how to deal with it rather than having to google MS support would have been nice. If they impose changes, tell us. Most Office users will have large IT departments to deal with it, but smaller companies won't and most end users will be flummoxed. As an 'Office user' fpr 30 years I had to google and post here, and at least I understand the techy bit. Most won't. Ask most Excel users about Trust Centre and the reply 'What?' will be resounding.

It is not a new thing - it has been around for quite a while. It just may be new to you, if you are moving off of a very old version of Office/Excel that operated differently.
I would say that most Excel users don't use Macros/VBA, so it probably doesn't apply to them. Those that do use it, the concept of Security and Trust Center should not be that foreign an idea.
Anyone who uses any sort of electronic media this days is probably well aware of protecting those devices from things like viruses. We are literally bombarded with things like virus protection, hacking, spam, phishing, etc every day.

 

[MARK858]

Where I live in the UK it was covered (moaned about) by the national press (like The Daily Express for example) as well as all the tech press well in advance of June 2022 when it was originally going to be implemented

 

[Johnny C]

I don't read the Daily Express. I've had 365 for 4 years. Yes most people don't use VBA, this is why, when most would benefit from using it. There's a weird rationale MS hid the Developer ribbon, which I have never understood. A lot of people, when shown what VBA can do to simplify mindless repetition, say why did no-one ever teach me that? MS make it constantly harder for people to use Excel who only want to use a minimal amount. A lot of people who consider themselves experts still use VLOOKUP and INDEX/MATCH, when XLOOKUP does all that. But you have to look it up, I'm still the only person using XLOOKUP I have worked with. I used VLOOKUP only once (in 35 years) in a contract where it was clear people inheriting my workbooks couldn't understand INDEX/MATCH OFFSET/MATCH and recently XLOOKUP.