… or in some cases, old but forgotten tricks.
The first comes from a Quora question, asking for the most underutilised feature in Excel.
When de-bugging a VBA routine you can set a break point where the code will stop:
To jump to a different line you can right-click, select “set next statement”, and click the line you want to jump to, but a quicker way is to just click and drag the yellow arrow to where you want to go:
The next is a post from Jeff Weir at Daily Dose of Excel, looking at how to display tool-tips with user defined functions (UDFs). If you enter a UDF name, including opening bracket, then press Ctrl-Shift-A, all the argument names are entered automatically, which can then be replaced with the actual range or value you want:
I had totally forgotten about this, but I must have known about it once, as I have posted two comments on it.
Finally, another tool-tip trick that I had forgotten about, but that has appeared here before. If you hover over a variable whilst de-bugging a VB routine, the first 77 characters of the current value of the variable are displayed as a tool-tip:
If the value is a long string, and you want to see the last 77 characters, hold down the Ctrl key before you hover:
Newton Excel Bach, not (just) an Excel Blog 
A much simpler way to run to the next statement after a break is to right click on the statement you want and select “Run to cursor”
LikeLike
I’m not sure how that is much simpler, but it does something different. “Run to Cursor” runs all the code in between the break and the selected line; “Set Next Statement” jumps straight to the selected line. As said in the text, you can either drag the yellow arrow, or use the right click menu to set the next statement.
LikeLike
Thanks for pointing these out, I was unaware of that ctrl trick to see the extra characters in the vba tooltip.
Another undocumented trick relates to range references within cell formulas. It’s well known that pressing [F9] in edit mode can resolve the selected part of a formula as a value or as an array of values. Less well known is that pressing [F5] followed by [Enter] in edit mode can resolve the selected part of a formula as a range reference (including names and R1C1-style references – see examples below). In either case, to revert the last change, just press [Ctrl+Z] while still in edit mode.
1. In a formula with multiple range references like,
=SUM(x,y,z)
selecting “x,y,z” and pressing [F5] shows the cells and displays the goto dialog, pressing [Enter] then replaces with cell references:
=SUM(A1,A2,A3)
2. Selecting single references like “A2” within a formula and pressing [F5] then [Enter] replaces the selected cell with the corresponding range name, “y”.
3. In a formula with a dynamic range like,
=SUM(C1:INDEX(C:C,4))
selecting the SUM argument and pressing in sequence [F4], [F5] and [Enter] resolves to “C1:C4”. (Note the [F4] key ensures the reference is interpreted as A1 and not R1C1 notation.)
LikeLike
Thanks Lori, very useful.
I tried to try your example with “a,b,c” instead of “x,y,z”, and discovered that you can’t use c or C as a range name (or R).
I also discovered that the name box acts like pressing F5:
If you enter C or R in the name box it selects the entire column or row
You can enter a cell address in the name box (in A1 or R1C1 notation) and it goes to that cell.
You can select a range name to go to from the name box
A good article on range names is:
https://www.lifewire.com/define-a-named-range-in-excel-3123627
LikeLike