Excel is blocking my macro. How do I make it stop?

Question

I wrote a very simple VB macro, a few years ago. The macro only accesses information located in its own workbook and is only used to verify if the date should be 30 days in the future or 15 days in the future. The workbook itself does pull information from other workbooks and the files are all stored on the our company Google Drive, which I use Drive for Windows to access through File Explorer. I am using the 2016 desktop version of Excel. I keep getting a #VALUE! error and when I try to get Excel to walk through the formula using the [!] shortcut Excel crashes and says my Macro isn't safe and has been disabled.

It walks through it fine when I use the ribbon Evaluate Formula button, although I still get the #VALUE! error, and I've tried just running the VB code with manual dates instead of pulling from the Sheet, and that produces the expected results. Did something change in the last few years that I am un aware of?

Any advice would be appreciated.

Answer 1

Thanks for reaching out.

I understand how confusing that can be. What’s happening is Excel now blocks macros in files it thinks came from the internet, and cloud-synced folders like Google Drive often carry that tag, so your VBA gets disabled. The #VALUE! error usually means the formula is hitting text instead of a real date. Here’s what I’d do: close Excel, right-click the workbook in File Explorer, go to Properties, and check Unblock if it’s there. Then reopen the file. If you use this workbook often, add your Google Drive folder as a Trusted Location in Excel under File > Options > Trust Center > Trusted Locations. Also make sure you’re opening it in the desktop app, not in a browser, because macros won’t run online. For the formula, use Evaluate Formula to see which cell turns into text and confirm those dates are actual Excel dates, not text strings.

Can you check if the Unblock option shows up and share the formula that’s throwing the error?

Answer 2

Got it, that helps narrow things down. Adding Google Drive in Internet Options doesn’t affect Excel’s macro security, because Excel uses its own Trust Center settings, not Windows’ Internet zones. The root cause is still the “Mark of the Web” tag on files synced from cloud storage, which makes Excel treat them as coming from the internet. Even if macros are enabled in the workbook, that tag can override it. The best fix is to either remove the tag by right-clicking the file, going to Properties, and checking Unblock, or add the local Google Drive folder as a Trusted Location inside Excel (File > Options > Trust Center > Trusted Locations). Can you confirm if the Unblock checkbox appears in the file’s Properties? Also, are you opening the file directly from the synced folder in File Explorer, not through a browser? If those look good, we can dig into the #VALUE! error next. Could you share the formula that’s failing?