Hi mike,
That's a very elegant solution indeed. Thank you.
I see now. Good to know so I can pick-up some testing skills.How did I know from your test data?
"='C:\[sumif_countif.xls]Sheet1'!M8" - closed precedent CORRECT
"='[Test with Notes.xls]TestData'!D22" - open other wb ERROR
"='Ext Links 2'!F32" - same wb other sheet ERROR
The string parsing routine Post #29 defined a "reference to a closed workbook" as any sub-string that begins with the pattern ' (anything) '!
VBA:
testString Like " '*"! ' " : Rem spaces added for clarity
VBA tags courtesy of www.thecodenet.com
The two failed cases both involved sheet names with spaces, which has a syntax that matches that definintion.
To exclude that situation, the post #31 correction "defines" a "reference to a closed workbook" as any sub-string that
begins with ' (anything) ] (anything) '!
VBA:
testString Like " '*]*"! ' " : Rem spaces added for clarity
VBA tags courtesy of www.thecodenet.com
It turns out that that is not specific enough. The correction below defines "external reference" as any sub-string that begins with
apostrophy (required, any character except [) (anything) ] (anything) '!
VBA:
If testStr Like "'[![]*]*'!*" Then
VBA tags courtesy of www.thecodenet.com
BTW, I am marking this as solved, but will keep you posted of any more 'conditions' that keep popping up. I will also be going through your code more thoroughly and would like to ask you about the logic as I understand it better.
This will definetely have a use.I'm also wondering what this is for. Is there an end use for this or is it an intellectual exersize at the moment?
From post #14:
Basically I'm trying to recreate the trace precedents Userform, from the audit toolbar, but by clicking the links you will actually open up the relevant links (even if link workbook is closed) and go to the relevant range.
- The trace precedents as you know doesn't open the link up for you especially for a closed workbook, Hence the deficiency I am trying to correct for, and learning about simple Userforms in the process.
I will now be understanding how to design and buil my first Userform using the code you have kindly helped build.
As an aside, from my post #19 image, is it possible just to open up the trace precedents dialog (as shown) and pick up the list of links/ precedents from it directly as they appear.
Thanks again for your kind help and efforts. Thanks also to Bob and Dave for their helpful insights into this tough problem and for introducing me to RegExp, hope to learn more about this from the VBAX community.
- That is, the trace-precedents is effectively a Userform listbox, is it possible to tap into it directly and extract the listbox items as they appear directly? Or must we always parse as per the above methods?