Microsoft Excel: Macro Not Working? Here’s How You Troubleshoot It.

Don Tomoff
Sep 28, 2017 · 3 min read
Image for post
Image for post
Excel Macro Troubleshooting Example

Sometimes things don’t work exactly as planned…

And that is especially true if you develop Excel models containing automated sequences with VBA macros.

Here’s A Recent Challenge…

The data table is imported via a PowerQuery sequence and each time the table is refreshed, the “Click To Open” hyperlink column needs to be updated. This column is an additional column NOT in the query.

At over 20.000 rows — and growing — it becomes tedious to update the column data with “Copy & Paste”. So I wrote the macro to automate it.

It didn’t work. :-(

…And How You Troubleshoot Problematic Macros

Here are the steps:

1 — Access the Macro that is causing the problem via “Step Into” button

Image for post
Image for post
Access Macro (“CopyClickToOpen”) via “Step Into” mode

2 — The VBA Editor opens up ready to step through the macro

Image for post
Image for post
VBA Editor in “Step Into” Mode

3 — Now, tap the F8 Key to Step through the macro

Below are a few images showing what it looks like as you are stepping through the macro — arrow and highlighting moves through macro as steps are executed.

Image for post
Image for post
Stepping through macro — arrow and highlighting moves through macro as steps are executed.
Image for post
Image for post
Image 2 — ready to copy content into rest of column.
  • 20K + rows updated with hyperlinks!
Image for post
Image for post
Image 3 — content is updated!

If the macro encounters a problem, exit the “Step Into” mode and attempt to fix the macro code. Then repeat the “Step Into” process.

  • Exiting the VBA Editor troubleshooting mode
Image for post
Image for post
Exiting the VBA Editor “debugger” mode

That’s it. The macro editor and this process intimidate many new Excel users stepping into automating their work. Don’t be intimidated — as you can see, it is fairly straight forward and it becomes second nature once you have done it several times!

Good luck!

About Don

Image for post
Image for post

“It’s time for different”

Don is passionate about helping professionals and organizations keep up and adapt to the changing business world that we operate in.

“What Do You Do?”

Connect with Don!

Or, just Google me…I’m everywhere

Let’s Excel

Up your Excel game — Tips, tricks and efforts that have…

Don Tomoff

Written by

It’s time for DIFFERENT— On a mission to challenge the status quo to a more productive and effective end… #digital #Excel #data #analytics

Let’s Excel

Up your Excel game — Tips, tricks and efforts that have exponentially increased my productivity!

Don Tomoff

Written by

It’s time for DIFFERENT— On a mission to challenge the status quo to a more productive and effective end… #digital #Excel #data #analytics

Let’s Excel

Up your Excel game — Tips, tricks and efforts that have exponentially increased my productivity!

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store