Funny how plans can change isn’t it? This week’s video was supposed to be about automating pivot table refresh.

I was doing very nicely. I’d built the demo file. I’d created the thumbnail image for Youtube. I’d even got partway through scripting the demos. And then I hit this error…”Variable uses an Automation type not supported”

Roadblock. Brick wall. Call it what you like. This error was preventing me from recording the video.

It was also preventing me from using the VBA Editor to create event-driven macros. Turns out it’s been there since 2017 but I’d never come across it before because I don’t do any serious VBA development on the Mac. That’s what Windows is for.

But I appreciate that I’m lucky. Not only do I have an iMac with the capability to run Windows as a virtual machine, I also have a real Windows machine in the shape of a Microsoft Surface.

Other people aren’t so lucky. They’re stuck with an iMac or MacBook as their only device (I don’t count phones and tablets as they don’t support VBA)

So instead of getting cranky, I turned it into an opportunity. The automating pivot table refresh video can wait for a week. Instead I present to you…

How to fix the “Variable uses an Automation type not supported” error in the VBA Editor in Excel for Mac