Excel reports built with ShufflePoint can get complex when you add lots of queries. Part of the difficulty is that Excel doesn't have good tools to look at all of the queries that you have added to your workbook at once. To troubleshoot customer workbooks we often turn to our "debug macro" which generates a new sheet named "QueryDebug" into the current workbook containing an "inventory" of sorts of all the queries in your workbook. I think any ShufflePoint customer using Excel will find this to be useful when trying to see which queries are generating error messages of missing different parameters, etc.

The macro itself can be found in a TXT file at this link.

The steps to use this macro in an Excel workbook that is giving you trouble are as follows:

1. Save your Excel file as 'macro enabled' (save as .xlsm).
2. Download the macro from the link above (right click, save link as...) and rename to querydebug.bas.
3. Open the Excel document you wish to debug.
4. Enable the developer ribbon.
5. Open the Visual Basic Studio.
6. In Visual Basic Studio choose file > import file - choose the querydebug.bas file that you saved in step 2 above, then close out the studio, no need to click save, etc...just close it.
7. Click on the Macros icon and run QueryDebug.

After completing step 7 you can sit back and watch as the Macro goes to work creating a new sheet and populating it with information about all the queries in your workbook (see screen grab below). From completed QueryDebug sheet you can see if there are missing parameters, or if a particular query is taking an extended period of time to run. You can also use it to look for situations where queries are "on top of each other" - or occupying the same cell-space within a sheet. None of the addresses in Column B (Address) should overlap. We have done some troubleshooting of that situation, and have some more advanced versions of this macro to share if needed.