This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
I have a very old spreadsheet that I created probably 10 years ago, that has a bug that I suspect is within the VBA code. It's been years since I've worked with VBA. I need help with the fundamental details of using the Debug feature. For starter, I want to pause the execution at very start of the Open routine. Do I just need to set a breakpoint at the beginning of the Open? Beyond that I need to know how step through execution one command at a time.
My target is to understand how a switch is getting set, that is supposed to be set by the user in the MAIN routine.
I'm open to consulting some resource available, such as a knowledge base article. I'm not keen on just diving in and hacking away at the code.
TIA for your help.SweetTasha
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
EliseM thanks for your reply.
I went to the website you recommend. It starts right off with
Keys to Step through Code
As mentioned, you can use the F8 key to step through code, but what happens when you reach a call to another routine?
It doesn't tell how get to the code that I want to look at.
SweetTasha.
If the next step jumps to another routine, then it will simply follow this and go there next.
Kind Regards,
Elise
ronski2 Thanks for your reply.
you say
Go into the macro editor
Put the cursor at the start of the macro
Somewhere is says Alt+F11 opens the vba editor. I did that. Now how do I know what macro I want to look at.
Thanks. SweetTasha.
I thought you already knew ...
.
I guess we have to take a step farther back. Are you asking about debugging a formula in a cell, not a macro?
.
Debugging formulas in a spreadsheet is another kettle of fish. Is this more of what you are looking for?
.
How to edit, evaluate and debug formulas in Excel
https://www.ablebits.com/office-addins-blog/2016/01/28/evaluate-debug-formulas-excel/
by Svetlana Cheusheva | updated on March 4, 2021
In this tutorial, you will learn a few quick and efficient ways to check and debug formulas in Excel. See how to use the F9 key to evaluate formula parts, how to highlight cells that reference or are referenced by a given formula, how to determine mismatched or misplaced parentheses, and more.
In the last few tutorials, we have been investigating different aspects of Excel formulas. If you've had a chance to read them, you already know how to write formulas in Excel, how to show formulas in cells, how to hide and lock formulas, and more.
Today, I'd like to share a few tips and techniques to check, evaluate and debug Excel formulas that will hopefully help you work even more efficiently with Excel.
Another way to debug formulas is to use the Precedents and and Dependants feature to find the cells that contribute to a formula or the cells that are dependent on the results of this cell.
. Trace Precedents https://corporatefinanceinstitute.com/resources/excel/study/trace-precedents/
Cells or group of cells that affect the value of the active cell
Home › Resources › Excel Resources › Study › Trace Precedents
Trace precedents are cells or groups of cells that affect the value of the active cell. Microsoft Excel provides users with the flexibility of doing complex calculations using formulas such as average, sum, count, etc.
However, the formulas may sometimes return wrong values or even give an error message that you must resolve in order to get the correct values. Excel makes it easier to resolve calculation errors by providing easy-to-use tools such as Trace Precedents that one can use to audit the calculations.
. Trace Dependents https://corporatefinanceinstitute.com/resources/excel/study/trace-dependents/
Cells or group of cells that are affected by the selected cell
Home › Resources › Excel Resources › Study › Trace Dependents
Trace Dependents are defined as the cells or group of cells that are affected by the selected cell. When using the formula auditing tool, Excel displays blue arrows that link the cells that are related to the active cell.
An Excel spreadsheet sometimes contain multiple formulas, and understanding the relationship between the cells would be a difficult task in the absence of the Trace Precedents and Trace Dependents features in Excel.
steps(3)
The comments and technical articles presented by all of you who participated are quite comprehensive. By contrast, my objective is very specific. As I mentioned it was simply to be able to debug code related tswitch/variable that is set in the spreadsheet. My recollection from years ago when I created the spreadsheet was that the workbook Open routine was the "hook" that enabled me to debug the code. And that turns out to be accurate.
Referring to some of the detail in your comments and technical articles, and a fair amount of trial and error, here's what I came up with.
*1 Open the spreadsheet file
*2 Navigate to the MAIN sheet
*3a Press Alt+ F11
**-OR- \*3b Click Developer**
choose Visual Basic
Press ENTER
* Click View
* Click Code
* Click Project Explorer button
* Click VIEW
* Click ThisWorkbook
* Right-click ThisWorkbook, then click View Code.
* Click Edit_Find_ Open
I emphasize that the above isn't a general solution to how to use debug in EXCEL, but a solution applicable only to my specific spreadsheet.
SweetTasha.