Here’s A Recent Challenge…
I had to work through a challenge recently on a spreadsheet developed for a project scraping #infographic tweets from Twitter.
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
Troubleshooting is done by using the VBA Editor (“Alt + F11” is keyboard shortcut to access).
Here are the steps:
1 — Access the Macro that is causing the problem via “Step Into” button
2 — The VBA Editor opens up ready to step through the macro
3 — Now, tap the F8 Key to Step through the macro
TIP — Make sure the VBA Editor is positioned next to the spreadsheet so that you can see the steps as they are executed!
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.
- 20K + rows updated with hyperlinks!
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
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!
“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?”
I frequently get this question. My response (it’s not what you think!)? Check it out here!