Solved: Splitting all addresses in a formula

[xluser2007]

Hi All,

I am currently, as a personal project to understand Userforms better, I am trying to develop the following as a userForm thru VBA:

Develop a shortcut whereby clicking on any cell, a Userform opens up to show all internal and external link addresses.

The Userform allows one to go directly to those displayed addresses (whether they be in internal or external workbooks).

For example, Consider a a sample workbook called C:\Documents\Test.xls (Which contains 3 sheets - Sheet1, Sheet2 and Sheet3)

In Cell A1 of Sheet1 of this workbook, contans the following formula (an intentionally random and long formula is chosen below, but only the various types of internal/ external addresses are important):


=IF(ISERROR(VLOOKUP($A$2,$B$2:$C$10,$D$2,FALSE)),Sheet2!A1,IF(ISNA(Sheet3!$ A$1),VLOOKUP('C:\Documents and Settings\ExcelTestFiles\[Book2]Sheet1!$A$1,'C:\Documents and Settings\VBAX\Sheet1!$B$1:$D$17,'C:\Important\Results\[Book2]Sheet2!A$1,FALSE)))+'C:\TestFiles\[Book3]Sheet1!$A$1

Out of this example, I would like to split out the links as follows ( I have colour coded as per the formula above):

[B]Internal Links
i.e. links within the Activecell in Activeworkbook (C:\Documents\Test.xls)
C:\Documents\[Test.xls]Sheet1'!$A$2
C:\Documents\[Test.xls]Sheet1'!$B$2:$C$10
C:\Documents\[Test.xls]Sheet1'!$D$2
C:\Documents\[Test.xls]Sheet2'!A1
C:\Documents\[Test.xls]Sheet3'!$A$1
C:\Documents\[Test.xls]Sheet3'!$A$1
External Links i.e. from External workbook sources in the Activecell in Activeworkbook (C:\Documents\Test.xls)
'C:\Documents and Settings\ExcelTestFiles\[Book2]Sheet1!$A$1
'C:\Documents and Settings\VBAX\[Book2]Sheet1!$B$1:$D$17
'C:\Important\Results\[Book2]Sheet2!A$1
'C:\TestFiles\[Book3]Sheet1!$A$1THUS, as per my original question, is there any way to easily extract all the internal and external addresses from an active cell and in particular from the above example i.e. treating the formula as a string and parsing internal/ external links accordingly.

(There will be other problems to consider e.g. if the links are open then only short path will be visible i.e. formula will read '[Book2]Sheet1!$B$1:$D$17 instead of 'C:\Documents and Settings\VBAX\[Book2]Sheet1!$B$1:$D$17), but for now assuming that all external links are closed (as above) is there any way to split out the links and paths?

Any VBA Guru help would be much appreciated :).

regards

Edit: Corrected spelling errors and modified final question for more clarity.

[Bob Phillips]

I'll start by saying that this seems a poor example to learn userforms with, there is little to do with them here, maybe a single listbox at the end.

That being so, some thoughts.

Links always seem to have a ! in, so you could look at the formula for that. Then look backwards and forwards for a comma and a bracket. That should get you the link, or at least 75% of them. ANy anomalies can be enhanced afterwards.

[xluser2007]

Hi Bob,


I'll start by saying that this seems a poor example to learn userforms with, there is little to do with them here, maybe a single listbox at the end.
I can see what you mean. But to be honest this is all I want to get out of this Userform. In this sense admittedly, it is more about solving the general parsing links problem in an activecell (which I've wanted to do for a while), but having the opportunity to learn a simple UserForm feature in tyhe process.

The motivation behind this issue is if you have a bunch of different external links in a single cell, double clicking (or "Ctrl + [" shortcut) only opens the first one, not all. So it becomes a slow process opening them manually through Edit.Links etc, especially when working with many external linked workbooks.



That being so, some thoughts.

Links always seem to have a ! in, so you could look at the formula for that. Then look backwards and forwards for a comma and a bracket. That should get you the link, or at least 75% of them. ANy anomalies can be enhanced afterwards.
Thanks for the hints, I was actually thinking trying to do it in this sort of fashion i.e.e first extracting formula as a string and searching accordingly for ', ! and $ signs etc. I justwanted to confirm that there wasn't a parsing formula already out there or in built that would save doing this work.

But I will have a go and report back.

Again thanks for your interest and thoughts :).

[brettdj]

In my RegExp example file I provided a formula parsing example that will give cell references (not full links)

see http://vbaexpress.com/kb/getarticle.php?kb_id=68

Be warned though, this is not an intuitive approach

Cheers

Dave

[xluser2007]

Dave,

Thanks. Looks like a great tool you have built. I will try and understand it in more detail.

For a noob like me though, the coding, from quick glance does look more suited to someone with experience.

Notheless, I am trying my own method and hope that yourself and others can help me solve this problem. I am just starting to realise there is a lot more to it than seems on the surface.

Cheers,

[mikerickson]

This will list all of the precedent cells of the Active Cell, including named ranges (returned as addresses).
Two string arrays are created:
InternalPrecedentAddresses and ExternalPrecedentAddresses

(Non-Mac users can use Join instead of the UDF rrayStr)


Sub test()
Dim internalPrecColl As New Collection, internalPrecedentAddresses() As String
Dim externalPrecColl As New Collection, externalPrecedentAddresses() As String
Dim i As Long, j As Long, xVal As Variant
Dim homeCell As Range, currentSelection As Range
Dim precCell As Range

Set homeCell = ActiveCell
Set currentSelection = Selection
homeCell.Parent.ClearArrows
homeCell.ShowPrecedents
i = 0: j = 0
Do
j = j + 1
i = 0
Do
i = i + 1
On Error Resume Next
homeCell.NavigateArrow True, j, i
Set precCell = ActiveCell
If precCell.Parent.Parent.Name = homeCell.Parent.Parent.Name Then
internalPrecColl.Add Item:=precCell, key:=precCell.Address(, , , True)
Else
externalPrecColl.Add Item:=precCell, key:=precCell.Address(, , , True)
End If
On Error GoTo 0
Loop Until precCell.Address(, , , True) = homeCell.Address(, , , True)
Loop Until i = 1
internalPrecColl.Remove homeCell.Address(, , , True)

homeCell.Parent.ClearArrows
currentSelection.Parent.Activate
currentSelection.Select

Rem collections to arrays
If CBool(internalPrecColl.Count) Then
ReDim internalPrecedentAddresses(1 To internalPrecColl.Count)
For i = 1 To internalPrecColl.Count
xVal = internalPrecColl(i).Address(, , , True)
internalPrecedentAddresses(i) = Mid(xVal, InStr(xVal, "]") + 1)
Next i
End If
If CBool(externalPrecColl.Count) Then
ReDim externalPrecedentAddresses(1 To externalPrecColl.Count)
For i = 1 To externalPrecColl.Count
externalPrecedentAddresses(i) = externalPrecColl(i).Address(, , , True)
Next i
End If

Rem display results
If CBool(internalPrecColl.Count) Then
MsgBox "Internal Precedents:" & vbCr & rrayStr(internalPrecedentAddresses, vbCr)
Else
MsgBox "No internal precedents"
End If

If CBool(externalPrecColl.Count) Then
MsgBox "External Precedents:" & vbCr & rrayStr(externalPrecedentAddresses, vbCr)
Else
MsgBox "No external precedents"
End If

End Sub

Function rrayStr(ByVal inputRRay As Variant, Optional Delimiter As String)
Dim xVal As Variant
If IsEmpty(inputRRay) Then Exit Function
If Delimiter = vbNullString Then Delimiter = " "
For Each xVal In inputRRay
rrayStr = rrayStr & Delimiter & xVal
Next xVal
rrayStr = Mid(rrayStr, Len(Delimiter) + 1)
End Function

[xluser2007]

Hi mike,

This looks like an amazing piece of code! Thank you for your great efforts :).

I will continue to test and report back!

kind regards,

[xluser2007]

Hi mike,

Firstly, I'm a non-mac user currently running Excel 2003.

I tested your code as follows.:
Opened a workbook with valid external liks.
Pasted all of your code into a Module 1.
Selected a single cell with valid external precedents and ran macro "test":Note: The external links were NOT open when running macro "test".

The 2 msgBox's came up saying "No Internal Precedents" and "External Precedents".

Please note if the external precedents workbooks are OPEN then the code works perfectly. Though the above issue arises if the external link is not open. if it is not, the formula does not puck up internal links correctly either.

Have I mispplied your code somehow.

Please let me know.

And may I say that despite the above, small issue (possibly as I may have misapplied the code), this is a brilliant approach (it avoids the need to parse with some type of fuzzy "!:$" matching as I have been, and still am trying :doh:).

regards

[mikerickson]

The .NavigateArrow method changes ActiveCell, so any precedent in a workbook that is not open will be missed.

Later:
The ShowPrecedect has only one arrow for external precedents. "External" in this case, meaning "not on this sheet". That arrow is has ArrowNumber = 1. Changing this line will insure that ArrowNumber 2 will be inspected before halting.


Loop Until i = 1 And 1 < j

If the cell has links to a closed workbook, those links will be missed. Other external links will be missed, depending on their order in the formula.

Consider the situation where this is the formula is in [WorkbookAlpha]Sheet1$A$1

=[WB1.xls]Sh1!$A$1 + [WB2.xls]Sh1!$A$2 * [WB3.xls]Sh1!$A$3 + D7

The internal precedent, D7, will always be caught no matter what other workbooks are closed.

If WB3 is closed, only [WB3.xls]Sh1!$A$3 will be missed
If WB2 is closed, both [WB2.xls]Sh1!$A$2 and [WB3.xls]Sh1!$A$3 will be missed
If WB3 is closed, all the referneces will be missed.

Note that Excel considers a precedent that is in the same workbook but on a different sheet to be "external", so they are included as a possible missed cell when a precedent workbook is closed.

=[WB1.xls]Sh1!$A$1 + [WB2.xls]Sh1!$A$2 * Sheet2!$A$3 + D7

Sheet2!$A$3 will be missed if WB2 is closed, even though D7 will be caught.

[xluser2007]

Hi Mike, Dave and Bob,

After much debate with a colleague at work and many discussions on how to parse external links here is our attempt at a working code for extracting links:

Sub test_find_external_links()

Dim cel_formula_text As String, Dim ext_link As String
Dim cel_formula_length As Double, Dim charac_position As Double

Dim startlink_ind As Boolean, Dim letter As String

'initialise
cel_formula_text = Selection.Formula
cel_formula_length = Len(cel_formula_text)
startlink_ind = False
ext_link = ""

For charac_position = 1 To cel_formula_length Step 1
letter = Mid(cel_formula_text, charac_position, 1)
If startlink_ind Then
ext_link = ext_link & letter
End If

'picks up the ext_link using the "'" value
If letter = "'" And Not startlink_ind Then
startlink_ind = True
ext_link = "'"
ElseIf letter = "'" And startlink_ind Then
While (charac_position <= cel_formula_length) And (("A" <= letter And letter <= "Z") Or ("a" <= letter And letter <= "z") Or ("0" <= letter And letter <= "9") Or letter = "!" Or letter = "$" Or letter = "'" Or letter = ":")
ext_link = ext_link & letter

charac_position = charac_position + 1
letter = Mid(cel_formula_text, charac_position, 1)
Wend

Msgbox ext_link
startlink_ind = False

End If
Next charac_position

End Sub

A few comments:

This seems to find external links from closed workbooks pretty well, haven't got it to crash as yet.
This also finds links to Open workbooks i.e. with a short path. This is fine as the whole point of the macro/ UserForm is to OPEN and GOTO the external links, doesn't matter is they are OPEN (i.e. use the shortpath) 0r closed (i.e. use the long path).
I haven;t got it to crash as yet for external links, but hoping anyone that tests it does and also fixes the bug :).
This formula does not find internal precedents/ links, which I also require for my purpose.Also:

mike, from your previous post, does that mean your code can;t effectively be adapted to find external links, if they are closed when executing the macro? Please let me know if there is a workaround for your innovative approach. At the very least your formula would be great to isolate the internal precedents that the above method doesn't catch.

Also I am really keen on your thoughts on the above approach and how it can be made more robust and rigorous and error-proof.

Bob, you mentioned that 75% of anamalies when tryoing to fuzzy capture external links can be captured using the above style approach. Can you please identify any faults in the above and kindly suggest any workarounds to make the above more robust and error-proof. I would appreciate your insisghts.

Dave (brettdj), your RegExp Example 5 is awesome. For my purposes, as you can see I only need a first level precendent. I don't understand (as I am a VBnewbie) on how the RegExp parsing methodology works. My vague understanding is that you have effectively built some sort of generalised Parsing engine and keep calling on it in innovative ways for different purposes.

Is it possible to adapt RegExp parsing principles in a more simplified manner for my pusposes. Would really appreciate your thoughts as well.

As you can see, I have been trying hard at this one. This is shaping to be a really cool complex problem and I would love to see a robust solution. Thank you all for your great help thus far.

If you could please teach me how to get the final solution I'd be really thankful.

kind regards

regards

[xluser2007]

Hi Mike, Dave and Bob,

After much debate with a colleague at work and many discussions on how to parse external links here is our attempt at a working code for extracting links:

To test, simply have a formula in a single cell with external references, select the cell and run the following, macro.

Sub test_find_external_links()

Dim cel_formula_text As String, Dim ext_link As String
Dim cel_formula_length As Double, Dim charac_position As Double

Dim startlink_ind As Boolean, Dim letter As String

'initialise
cel_formula_text = Selection.Formula
cel_formula_length = Len(cel_formula_text)
startlink_ind = False
ext_link = ""

For charac_position = 1 To cel_formula_length Step 1
letter = Mid(cel_formula_text, charac_position, 1)
If startlink_ind Then
ext_link = ext_link & letter
End If

'picks up the ext_link using the "'" value
If letter = "'" And Not startlink_ind Then
startlink_ind = True
ext_link = "'"
ElseIf letter = "'" And startlink_ind Then
While (charac_position <= cel_formula_length) And (("A" <= letter And letter <= "Z") Or ("a" <= letter And letter <= "z") Or ("0" <= letter And letter <= "9") Or letter = "!" Or letter = "$" Or letter = "'" Or letter = ":")
ext_link = ext_link & letter

charac_position = charac_position + 1
letter = Mid(cel_formula_text, charac_position, 1)
Wend

Msgbox ext_link
startlink_ind = False

End If
Next charac_position

End Sub

A few comments:
This seems to find external links from closed workbooks pretty well, haven't got it to crash as yet.
This also finds links to Open workbooks i.e. with a short path. This is fine as the whole point of the macro/ UserForm is to OPEN and GOTO the external links, doesn't matter is they are OPEN (i.e. use the shortpath) OR closed (i.e. use the long path).
Having said this, given a short path link found correctly for an external linked workbook, is it possible to infer the full long path from it?
I haven't got it to crash as yet for external links, but hoping anyone that tests it and does also fixes the bug :).
This formula does not find internal precedents/ links, which I also require for my purpose.Furthermore:

mike, from your previous post, does that mean your code can;t effectively be adapted to find external links, if they are closed when executing the macro? Please let me know if there is a workaround for your innovative approach. At the very least your formula would be great to isolate the internal precedents that the above method doesn't catch.

Also I am really keen on your thoughts on the above approach and how it can be made more robust and rigorous and error-proof.

Bob, you mentioned that 75% of anamalies when tryoing to fuzzy capture external links can be captured using the above style approach. Can you please identify any faults in the above and kindly suggest any workarounds to make the above more robust and error-proof. I would appreciate your insisghts.

Dave (brettdj), your RegExp Example 5 is awesome. For my purposes, as you can see I only need a first level precendent. I don't understand (as I am a VBnewbie) on how the RegExp parsing methodology works. My vague understanding is that you have effectively built some sort of generalised Parsing engine and keep calling on it in innovative ways for different purposes.

Is it possible to adapt RegExp parsing principles in a more simplified manner for my pusposes. Would really appreciate your thoughts as well.

As you can see, I have been trying hard at this one. This is shaping to be a really cool complex problem and I would love to see a robust solution. Thank you all for your great help thus far.

If you could please teach me how to get the final solution I'd be really thankful.

kind regards

regards

[Bob Phillips]

Dave (brettdj), your RegExp Example 5 is awesome. For my purposes, as you can see I only need a first level precendent. I don't understand (as I am a VBnewbie) on how the RegExp parsing methodology works. My vague understanding is that you have effectively built some sort of generalised Parsing engine and keep calling on it in innovative ways for different purposes.

Is it possible to adapt RegExp parsing principles in a more simplified manner for my pusposes. [I]Would really appreciate your thoughts as well.


Here is a simple function that checks for invalid characters (such as in a filename)



Function ValidFileName(ByVal TheFileName As String) As Boolean
Dim RegEx As Object
Set RegEx = CreateObject("vbscript.regexp")
RegEx.Pattern = "[\\/:\*\?""<>\|]"
ValidFileName = Not RegEx.Test(TheFileName)
Set RegEx = Nothing
End Function


and another, an email validator using RegExp



'-----------------------------------------------------------------
Public Function ValidEmail(Adress As String) As Boolean
'-----------------------------------------------------------------
Dim oRegEx As Object
Set oRegEx = CreateObject("VBScript.RegExp")
With oRegEx
.Pattern = "^[\w-\.]{1,}\@([\da-zA-Z-]{1,}\.){1,}[\da-zA-Z-]{2,3}$"
' .Pattern = "^(\w+\.)*(\w+)@(\w+\.)+([a-zA-Z]{2,4})$"
ValidEmail = .Test(Adress)
End With
Set oRegEx = Nothing
End Function


Maybe these will help you.

If you find RegExp interesting (and it is incredibly powerful), get a copy of 'Mastering Regular Expressions' by Jeffrey E.E. Friedl, published by O'Reilly.

[mikerickson]

The code posted doesn't compile for me. The declarations

Dim cel_formula_text As String, Dim ext_link As String
Dim cel_formula_length As Double, Dim charac_position As Double
Dim startlink_ind As Boolean, Dim letter As String throw error because of the commas and the second Dim statements.

When that is corrected, testing for the apostrophy misses all links in
the formula
=[Workbook2.xls]Sheet1!$A$2+C2

This will return an array of strings, the Workbook names of all the external links in the Selection's formula

Sub testFindExternalWorkooks()
MsgBox rrayStr(Selection.Formula)
End Sub

Function ExternalLinkWorkbookNames(formulaString As String) As Variant
Dim workingStr As String
Dim outRRay() As String, pointer As Long
Dim startChr As Long, subLen As Long
ReDim outRRay(1 To Len(formulaString))
workingStr = formulaString
Do
Do While InStr(workingStr & Chr(34), Chr(34)) < InStr(workingStr, "[")
workingStr = Mid(workingStr, InStr(workingStr & Chr(34), Chr(34)) + 1)
workingStr = Mid(workingStr, InStr(workingStr & Chr(34), Chr(34)) + 1)
Loop
startChr = InStr(workingStr & "[", "[")
workingStr = Mid(workingStr, startChr + 1)
subLen = InStr(workingStr, "]")
If CBool(subLen) Then
pointer = pointer + 1
outRRay(pointer) = Left(workingStr, subLen - 1)
workingStr = Mid(workingStr, subLen + 1)
End If
Loop Until Len(workingStr) < 1
ReDim Preserve outRRay(1 To Application.Max(pointer, 1))
ExternalLinkWorkbookNames = outRRay
End Function

Function rrayStr(ByVal inputRRay As Variant, Optional Delimiter As String)
Dim xVal As Variant
If IsEmpty(inputRRay) Then Exit Function
If Delimiter = vbNullString Then Delimiter = " "
For Each xVal In inputRRay
rrayStr = rrayStr & Delimiter & xVal
Next xVal
rrayStr = Mid(rrayStr, Len(Delimiter) + 1)
End Function

As to modifying the code, post #9 show how to do that. By calling references to a difference sheet in the same workbook an "internal" reference, your terms "external" and "internal" do not match Excel's usage.

The route I would go, would be to use a routine similar to the above to open all the other workbooks mentioned in the formula. Then run the earlier test routine. That would pick-up the named ranges, as well as the explicit ranges. (A routine finding the Named Ranges that point to an closed workbook could be added to ExternalLinkWorkbookNames.)

What is the purpose of this? There might be an easier course than reverse engineering Excel's formula parser.

[xluser2007]

mike,

Thanks for your informative reply (as usual), firstly:


The code posted doesn't compile for me. The declarations

Dim cel_formula_text As String, Dim ext_link As String Dim cel_formula_length As Double, Dim charac_position As Double Dim startlink_ind As Boolean, Dim letter As String

throw error because of the commas and the second Dim statements.
Sorry for this. The code I had on my PC did compile, but was not clean to post on a forum. I tried to clean up as much as possible (combining declarations in one line for example), which led to the typo error.

A Simple mistake in trying to increase clarity, glad you fixed it and got it to work for testing.

Secondly:



When that is corrected, testing for the apostrophy misses all links in
the formula
=[Workbook2.xls]Sheet1!$A$2+C2
I'm not sure how that formula works. Don't all external links have to have an apostrophe to start with? please let me know as I've tried recreating something like that.

Thirdly, I tried your both your UDF's "rrayStr" and "ExternalLinkWorkbookNames" code as stated, on a single selected cell with an external link formula. I kept getting a "Type 13 Mismatch Error" in this line for "rrayStr" UDF:

For Each xVal In inputRRay
and also a "Run-time error '9': Subscript out of Range" in the following line for "ExternalLinkWorkbooknames" UDF:

ReDim outRRay(1 To Len(formulaString))

Any idea if I'm misapplying or how to correct as I would like to test out your methods.

Fourthly, the most important question:


What is the purpose of this? There might be an easier course than reverse engineering Excel's formula parser.


My purpose is to create a shortcut userform wherby clickign a single cell will open up all valid external and internal links. The USerform will give a single click acces to open or GoTo the said links.

Basically trying to recreate the trace precedents from the audit toolbar, but by clicking the links you will actually open up (if link workbook closed) and go to the cell. (the trace precedents as you know doesn;t open the link up for you hence teh deficiency I am trying to correct for and learning about simple Userforms in the process).

The easiest way I figured was to try to reverse engineer the parsing.

Through the above discussions with yourself, Bob and Dave I'm realising that there are smarter ways with pre-built engines. Though my level is not up to understanding them to apply them confidently, yet :) (e.g. RegExp).

Can you suggest a different way to takle the parsing for external links and internal ones for my purpose All I need is a single click GoTo to each of them?

I am really open to suggestions and don't want to reinvent the wheel. My code isn't perfect as you have tested.

regards,

[xluser2007]

Here is a simple function that checks for invalid characters (such as in a filename)



Function ValidFileName(ByVal TheFileName As String) As Boolean
Dim RegEx As Object
Set RegEx = CreateObject("vbscript.regexp")
RegEx.Pattern = "[\\/:\*\?""<>\|]"
ValidFileName = Not RegEx.Test(TheFileName)
Set RegEx = Nothing
End Function

and another, an email validator using RegExp



'-----------------------------------------------------------------
Public Function ValidEmail(Adress As String) As Boolean
'-----------------------------------------------------------------
Dim oRegEx As Object
Set oRegEx = CreateObject("VBScript.RegExp")
With oRegEx
.Pattern = "^[\w-\.]{1,}\@([\da-zA-Z-]{1,}\.){1,}[\da-zA-Z-]{2,3}$"
' .Pattern = "^(\w+\.)*(\w+)@(\w+\.)+([a-zA-Z]{2,4})$"
ValidEmail = .Test(Adress)
End With
Set oRegEx = Nothing
End Function

Maybe these will help you.

If you find RegExp interesting (and it is incredibly powerful), get a copy of 'Mastering Regular Expressions' by Jeffrey E.E. Friedl, published by O'Reilly.

Bob, this is brilliant code.

I didn't know such robust elegance was possible for validity checking especially for important strings such as filenames and email strings.

I'm not sure how to directly apply them for my purposes, but will keep them in my toolkit for future use atleast.

As for that book, the amazon reviews made it seem suited more for advanced perl programmers. I'm not actually a programmer by profession (in stastistics/ finance) just use VBA to make my life easier and recently have use VBA programming as hobby to improve my logical thinking process when confronting data problems daily.

If I advance within the next year using VBA, I may take up learning this book as a complementary next step in improving programming with complex data.

Thanks for the tip and knowledge-sharing.

Also in my last email to mike, I basically stated my purpose for this link parsing most clearly as follows:


Basically trying to recreate the trace precedents from the audit toolbar, but by clicking the links you will actually open up (if link workbook closed) and go to the cell. (the trace precedents as you know doesn;t open the link up for you hence teh deficiency I am trying to correct for and learning about simple Userforms in the process).

Based on this, maybe I've strayed of course. I'm also keen to hear your thoughts if there is a simpler way to bring me in line rather than try recreating a built in formula parsing engine (The audit toolbar) as mike was suggesting.

regards,

[mikerickson]

The code I posted had a couple of errors.
Using this version, selecting a cell containing
='[Workbook space.xls]Sheet1'!$B$3+D3+'Macintosh HD:Users:mike: Desktop:[Workbook2.xls]Sheet1'!$C3
(no space before Desktop)
and running testFindExternalWorkooks returned

Workbook space.xls
Workbook2.xls
Sub testFindExternalWorkooks()
MsgBox rrayStr(ExternalLinkWorkbookNames(Selection.Formula), vbCr)
End Sub

Function ExternalLinkWorkbookNames(formulaString As String) As Variant
Dim workingStr As String
Dim outRRay() As String, pointer As Long
Dim startChr As Long, subLen As Long
ReDim outRRay(1 To 1)
outRRay(1) = vbNullString
If formulaString <> vbNullString Then
ReDim outRRay(1 To Len(formulaString))
workingStr = formulaString
Do
Do While InStr(workingStr & Chr(34), Chr(34)) < InStr(workingStr, "[")
workingStr = Mid(workingStr, InStr(workingStr & Chr(34), Chr(34)) + 1)
workingStr = Mid(workingStr, InStr(workingStr & Chr(34), Chr(34)) + 1)
Loop
startChr = InStr(workingStr & "[", "[")
workingStr = Mid(workingStr, startChr + 1)
subLen = InStr(workingStr, "]")
If CBool(subLen) Then
pointer = pointer + 1
outRRay(pointer) = Left(workingStr, subLen - 1)
workingStr = Mid(workingStr, subLen + 1)
End If
Loop Until Len(workingStr) < 1
ReDim Preserve outRRay(1 To Application.Max(pointer, 1))
End If
ExternalLinkWorkbookNames = outRRay
End Function

Function rrayStr(ByVal inputRRay As Variant, Optional Delimiter As String)
Dim xVal As Variant
If IsEmpty(inputRRay) Then Exit Function
If Delimiter = vbNullString Then Delimiter = " "
For Each xVal In inputRRay
rrayStr = rrayStr & Delimiter & xVal
Next xVal
rrayStr = Mid(rrayStr, Len(Delimiter) + 1)
End Function

[xluser2007]

mike, that works really well.

tested it several times and always finds the external linked workbooks:

E.g. I tested it on:


=IF(ISNA(MATCH(D11,'D:\VBATest\source files\[Specifications_Nov 06.xls]DEF'!$G$3:$G$51,0)),"",OFFSET('D:\VBATest\source files\[Specifications_Nov 06.xls]DEF'!$A$2,MATCH(D11,'D:\VBATest\source files\[Specifications_Nov 06.xls]DEF'!$G$3:$G$51,0),0))

And it pulled out:

Specifications_Nov 06.xls three times.

Is it possible, adapting this code further for this to extract separately the following parts:
'D:\VBATest\source files\ [i.e. FOLDER PATH]

Specifications_Nov 06.xls (this it already does without teh square brackets)

$G$3:$G$51 or '!$G$3:$G$51 (basically the next part fo the linked reference) [i.e. ADDRESS PATH]If these parts can be parsed (number 2 already can) then it is just a matter of concatenating and doing an Workbooks.open on the first part and second part and then an Application.goto on the last part and will take care of all EXTERNAL LINKS.

Then it is a matter of adapting your earlier approach to get the first level precedents for INTERNAL LINKS.

Any further help would really be appreciated on this. Really learning a lot from you in this process!

regards,

[xluser2007]

Bob, mike,

I've been thinking about this problem a lot more over the past couple of days. I agree with mike that reverse engineering Excel's built in formula parser (i.e. auidt toolbar) may be unnecessary work.

Also Bob, I have been researching regular expressions further (http://www.regular-expressions.info/), and I now see the importance of your words. It is pretty much the best possible string parsing language availible, though tough for a newb like me to use well at this stage!

So here is my two-prong strategy to 'best' attack the problem at hand:
Use the trace precedents toolbar button (using vba) to extract all possible formulae in the selected cell.
Need to understand how to do this in VBA

If all the above external links aren't identified then parse rigorously using RegExp, so that they are found.
Need to understand how to parse this using RegExp in VBA.
So here is why I expect this to work relatively easily compared to other methods.
if we extract the formulae from the audit toolbar, they can be of 4 different types:
An external link of a closed workbook
An external link of an already open workbook
An internal link in a different worksheet in the same workbook
An internal link in the same worksheet. The audit toolbar trace precedents will only return shortpaths e.g. '[Workbook2.xls]Sheet1!A1 to external links. So it does not on its own tell us in that example whether Workbook2.xls is open or closed.

If it is closed then the actual formula in the cell would read 'C:\Documents\test\[Workbook2.xls]Sheet1!A1 i.e. full folder path.

Or if Workbook2.xls is open then it would read as '[Workbook2.xls]Sheet1!A1 i.e. shortpath.

Either way, for these external links we can parse back the original formula using REGExp to test whether the reference is preceded by a \ before it ie. 'C:\Documents\test\[Workbook2.xls]Sheet1!A1, if it is not, it is an external link to an OPEN workbook, and we can go to it directly, else we can get parse the full folder and worksheet path.

Lastly if the formulae extracted from the audit toolbar don't have an apostrophe at the beginning, they are internal links and for the purpose of this exercise they are easy to go to (i.e. activate/ select) via the Userform.

Bob, mike Dave, could you please help me acheive this 2 pronged strategy, I have a method now which I have thought through (of course there may be holes), but for learning sake I would really like your help in coding the above.

Please guide me on the above. As with all your help above, I would appreciate it.

regards,

[xluser2007]

Ok, so I'm still not able to understand how to extract the links from trace precedents Goto menu.

Please find attached the picture below.

How do I extract
'[Book2]Testing Worksheet'!$B$1
[Book2]Sheet3!$B$1

from this window i.e. the 1st precedents from the active cell.

Could anyone please help me achieve this via VBA. It is the first part of my approach and I can't find this info anywhere else on the web.

Thanks,

[xluser2007]

Could anyone please let me know if the above is possible?

ie.. are we able to interact with the audit toolbar and extract the direct precedent list as shown in the picture. in the previous post.

Please let me know.

[Bob Phillips]

I haven't been following this thread in detail, or know what Mikle has given you, but have you looked at the DirectPrecedents/Dependents properties of the Range object?

[xluser2007]

Hi Bob,

Thanks for your reply.

If possible could you have alook at post #18. I wrote out a strategy in there that I would like to take to attack the problem. I would appreciate your insights.

I'm sorry if I'm verbose with my posts. I just like to be thorough so that other keen learners can follow in detail as well, and I fully explain myself.

I know that the long posts can be offputting at times:).

Mike has given me some great methods. He has somewhat reverse engineered the trace precedents in audit toolbar. It alsmost works, except if the external link is closed, his method does not capture any links at all. We discussed this in an earlier post.

[xluser2007]

I haven't been following this thread in detail, or know what Mikle has given you, but have you looked at the DirectPrecedents/Dependents properties of the Range object?

Thanks! Just tried the following:

Sub test()

Dim cel As Range

ActiveCell.DirectPrecedents.Select

For Each cel In Selection

Debug.Print cel.Address

Next

End Sub


and it printed out the internal links only in the activesheet. Good start, but still require the following:


An external link of a closed workbook
An external link of an already open workbook
An internal link in a different worksheet in the same workbookAny ideas how to acquire these without straight out RegExp parsing?

regards

[xluser2007]

Ok, some more good news.

Searched more for precedents and found the following code by Andy Pope of Ozgrid (and VBAX :)).

The link for this code is: http://www.ozgrid.com/forum/showthread.php?t=17028

And the code, pasted from this link is:

Sub FindPrecedents()
' written by Bill Manville
' With edits from PaulS
' this procedure finds the cells which are the direct precedents of the active cell
Dim rLast As Range, iLinkNum As Integer, iArrowNum As Integer
Dim stMsg As String
Dim bNewArrow As Boolean
Application. ScreenUpdating = False
ActiveCell.ShowPrecedents
Set rLast = ActiveCell
iArrowNum = 1
iLinkNum = 1
bNewArrow = True
Do
Do
Application.Goto rLast
On Error Resume Next
ActiveCell.NavigateArrow TowardPrecedent:=True, ArrowNumber:=iArrowNum, LinkNumber:=iLinkNum
If Err.Number > 0 Then Exit Do
On Error Goto 0
If rLast.Address(external:=True) = ActiveCell.Address(external:=True) Then Exit Do
bNewArrow = False
If rLast.Worksheet.Parent.Name = ActiveCell.Worksheet.Parent.Name Then
If rLast.Worksheet.Name = ActiveCell.Parent.Name Then
' local
stMsg = stMsg & vbNewLine & Selection.Address
Else
stMsg = stMsg & vbNewLine & "'" & Selection.Parent.Name & "'!" & Selection.Address
End If
Else
' external
stMsg = stMsg & vbNewLine & Selection.Address(external:=True)
End If
iLinkNum = iLinkNum + 1 ' try another link
Loop
If bNewArrow Then Exit Do
iLinkNum = 1
bNewArrow = True
iArrowNum = iArrowNum + 1 'try another arrow
Loop
rLast.Parent.ClearArrows
Application.Goto rLast
MsgBox "Precedents are" & stMsg
Exit Sub
End Sub
This finds
An external link of an already open workbook
An internal link in a different worksheet in the same workbook
An internal link in a different worksheet in the same workbookThe only thing remaining is:

An external link of a closed workbook, is this possible?Any VBgurus, this is the final piece of the puzzle, could anyone please help to solve it.

regards,

[xluser2007]

Ok, haven;t given up on this problem, though have not progressed as well as I would have liked.

Here is the code that I am using ("Test" is the man macro I run which calls the two subsequent macros) at the moment:

Option Explicit

Sub Test()

Call Find_Internal_Links_in_Activesheet_only

Call Find_External_Links_in_CLOSED_Workbooks_references_only

End Sub

Sub Find_Internal_Links_in_Activesheet_only()

Dim cel As Range

ActiveCell.DirectPrecedents.Select

For Each cel In Selection

MsgBox cel.Address

Next

Call test_find_external_links

End Sub

Sub Find_External_Links_in_CLOSED_Workbooks_references_only()

Dim cel_formula_text As String, ext_link As String
Dim cel_formula_length As Double, charac_position As Double
Dim startlink_ind As Boolean, letter As String

'initialise
cel_formula_text = Selection.Formula
cel_formula_length = Len(cel_formula_text)
startlink_ind = False
ext_link = ""

For charac_position = 1 To cel_formula_length Step 1
letter = Mid(cel_formula_text, charac_position, 1)
If startlink_ind Then
ext_link = ext_link & letter
End If

'picks up the ext_link using the "'" value
If letter = "'" And Not startlink_ind Then
startlink_ind = True
ext_link = "'"
ElseIf letter = "'" And startlink_ind Then
While (charac_position <= cel_formula_length) And (("A" <= letter And letter <= "Z") Or ("a" <= letter And letter <= "z") Or ("0" <= letter And letter <= "9") Or letter = "!" Or letter = "$" Or letter = "'" Or letter = ":")
ext_link = ext_link & letter

charac_position = charac_position + 1
letter = Mid(cel_formula_text, charac_position, 1)
Wend

MsgBox ext_link
startlink_ind = False

End If
Next charac_position

End Sub
So the Macro named "Find_Internal_Links_in_Activesheet_only" finds links in the activeworksheet of the activecell.

and

the macro named "Find_External_Links_in_CLOSED_Workbooks_references_only" finds links to CLOSED external workbook references.

I haven't been able to fault the macros in finding the specified types of links.

The only 2 types of links that aren't found with the above macros are:

An external link of an already open workbook
An internal link in a different worksheet in the same workbookI had thought that the code in the previous post found these, but als, it fails when there are closed workbooks in the activecell i.e. displays nothing in the Msgbox output.

Ok, so mike, Bob, could you please help to amend the above code to help isolate out the 2 types of links required.

I have tried hard but am not getting anywhere.

Any help appreciated.

regards,

[mikerickson]

Have you considered the path of using regular expressions to find cell references by the form of a formula.
1) Develop the rules needed to determine if a formula is well formed (eg. if "=A" and "=B" are well formed formulas, then "=A+B", "=A-B", "=A*B" and "=A/B" are also wff's)
2) Work backwards from those rules to determine the "atomic" portions of a specific formula.
3) Determine which of those are cell references (vs. constants).

[brettdj]

Have you considered the path of using regular expressions to find cell references by the form of a formula.
1) Develop the rules needed to determine if a formula is well formed (eg. if "=A" and "=B" are well formed formulas, then "=A+B", "=A-B", "=A*B" and "=A/B" are also wff's)
2) Work backwards from those rules to determine the "atomic" portions of a specific formula.
3) Determine which of those are cell references (vs. constants).see my earlier post on this.

cheers

dave

[Bob Phillips]

see my earlier post on this.

cheers

dave

Dave, can you check your PMs, Bob

[mikerickson]

I think this will do it. The sub RunMe will return the addresses of all precedents of the ActiveCell.

Open or closed workbook, named ranges are also returned.
After FindCellPrecedents is run, the collections are filled with addresses of all the cell's precedents.
The OtherWbRefs are in order that they appear in the formula. It also includes the precedents that are in closed workbooks.
SameWbOtherSheetRefs is also in order of the formula.

Someone with a scripting dictionary might want to change the coding in NextClosedWbRefStr.

I think this will do what you want.
Option Explicit
Public OtherWbRefs As Collection
Public ClosedWbRefs As Collection
Public SameWbOtherSheetRefs As Collection
Public SameWbSameSheetRefs As Collection
Public CountOfClosedWb As Long
Dim headerString As String

Sub RunMe()
Call FindCellPrecedents(ActiveCell)
End Sub

Sub FindCellPrecedents(homeCell As Range)
Dim i As Long, j As Long, pointer As Long
Dim maxReferences As Long
Dim outStr As String
Dim userInput As Long

If homeCell.HasFormula Then
Set OtherWbRefs = New Collection: CountOfClosedWb = 0
Set SameWbOtherSheetRefs = New Collection
Set SameWbSameSheetRefs = New Collection

Rem find closed precedents from formula string
Call FindClosedWbReferences(homeCell)

Rem find open precedents from navigate arrows
homeCell.Parent.ClearArrows
homeCell.ShowPrecedents
headerString = "in re: the formula in " & homeCell.Address(, , , True)
maxReferences = Int(Len(homeCell.Formula) / 3) + 1
On Error GoTo LoopOut:
For j = 1 To maxReferences
homeCell.NavigateArrow True, 1, j
If ActiveCell.Address(, , , True) = homeCell.Address(, , , True) Then
Rem closedRef
Call CategorizeReference("<ClosedBook>", homeCell)
Else
Call CategorizeReference(ActiveCell, homeCell)
End If
Next j
LoopOut:

On Error GoTo 0
For j = 2 To maxReferences
homeCell.NavigateArrow True, j, 1
If ActiveCell.Address(, , , True) = homeCell.Address(, , , True) Then Exit For
Call CategorizeReference(ActiveCell, homeCell)
Next j
homeCell.Parent.ClearArrows

Rem integrate ClosedWbRefs (from parsing) with OtherWbRefs (from navigation)
If ClosedWbRefs.Count <> CountOfClosedWb Then
If ClosedWbRefs.Count = 0 Then
MsgBox homeCell.Address(, , , True) & " contains a formula with no precedents."
Exit Sub
Else
MsgBox "string-" & ClosedWbRefs.Count & ":nav " & CountOfClosedWb
MsgBox "Methods find different # of closed precedents."
End
End If
End If

pointer = 1
For j = 1 To OtherWbRefs.Count
If OtherWbRefs(j) Like "<*" Then
OtherWbRefs.Add Item:=ClosedWbRefs(pointer), key:="closed" & CStr(pointer), after:=j
pointer = pointer + 1
OtherWbRefs.Remove j
End If
Next j

Rem present findings
outStr = homeCell.Address(, , , True) & " contains a formula with:"
outStr = outStr & vbCrLf & vbCrLf & CountOfClosedWb & " precedents in closed workbooks."
outStr = outStr & vbCr & (OtherWbRefs.Count - CountOfClosedWb) & " precedents in other workbooks that are open."
outStr = outStr & vbCr & SameWbOtherSheetRefs.Count & " precedents on other sheets in the same workbook."
outStr = outStr & vbCr & SameWbSameSheetRefs.Count & " precedents on the same sheet."
outStr = outStr & vbCrLf & vbCrLf & "YES - See details about Other Books."
outStr = outStr & vbCr & "NO - See details about The Active Book."
Do
userInput = MsgBox(prompt:=outStr, Title:=headerString, Buttons:=vbYesNoCancel + vbDefaultButton3)
Select Case userInput
Case Is = vbYes
MsgBox prompt:=OtherWbDetail(), Title:=headerString, Buttons:=vbOKOnly
Case Is = vbNo
MsgBox prompt:=SameWbDetail(), Title:=headerString, Buttons:=vbOKOnly
End Select
Loop Until userInput = vbCancel
Else
MsgBox homeCell.Address(, , , True) & vbCr & " does not contain a formula."
End If
End Sub

Sub CategorizeReference(Reference As Variant, Home As Range)
Rem assigns reference to the appropriate collection
If TypeName(Reference) = "String" Then
Rem string indicates reference to closed Wb
OtherWbRefs.Add Item:=Reference, key:=CStr(OtherWbRefs.Count)
CountOfClosedWb = CountOfClosedWb + 1
Else
If Home.Address(, , , True) = Reference.Address(, , , True) Then Exit Sub
If Home.Parent.Parent.Name = Reference.Parent.Parent.Name Then
Rem reference in same Wb
If Home.Parent.Name = Reference.Parent.Name Then
Rem sameWb sameSheet
SameWbSameSheetRefs.Add Item:=Reference.Address(, , , True), key:=CStr(SameWbSameSheetRefs.Count)
Else
Rem sameWb Other sheet
SameWbOtherSheetRefs.Add Item:=Reference.Address(, , , True), key:=CStr(SameWbOtherSheetRefs.Count)
End If
Else
Rem reference to other open Wb
OtherWbRefs.Add Item:=Reference.Address(, , , True), key:=CStr(OtherWbRefs.Count)
End If
End If
End Sub

Sub FindClosedWbReferences(inRange As Range)
Rem fills the collection with closed precedents parsed from the formula string
Dim testString As String, returnStr As String, remnantStr As String
testString = inRange.Formula
Set ClosedWbRefs = New Collection

Do
returnStr = NextClosedWbRefStr(testString, remnantStr)
ClosedWbRefs.Add Item:=returnStr, key:=CStr(ClosedWbRefs.Count)
testString = remnantStr
Loop Until returnStr = vbNullString

ClosedWbRefs.Remove ClosedWbRefs.Count
End Sub
Function NextClosedWbRefStr(FormulaString As String, Optional ByRef Remnant As String) As String
Dim workStr As String
Dim start As Long, interval As Long, del As Long
For start = 1 To Len(FormulaString)
For interval = 2 To Len(FormulaString) - start + 1
workStr = Mid(FormulaString, start, interval)
If workStr Like Chr(39) & "[!!]*'![$A-Z]*#" Then
If workStr Like Chr(39) & "[!!]*'!*[$1-9A-Z]#" Then
interval = interval - CLng(Mid(FormulaString, start + interval, 1) Like "#")
interval = interval - 3 * CLng(Mid(FormulaString, start + interval, 1) = ":")
interval = interval - CLng(Mid(FormulaString, start + interval, 1) Like "[$1-9A-Z]")
interval = interval - CLng(Mid(FormulaString, start + interval, 1) Like "[$1-9A-Z]")
interval = interval - CLng(Mid(FormulaString, start + interval, 1) Like "[$1-9A-Z]")
interval = interval - CLng(Mid(FormulaString, start + interval, 1) Like "[$1-9A-Z]")
NextClosedWbRefStr = Mid(FormulaString, start, interval)
Remnant = Mid(FormulaString, start + interval)
Exit Function
End If
End If
Next interval
Next start
End Function

Function OtherWbDetail() As String
Rem display routine
OtherWbDetail = OtherWbDetail & "There are " & OtherWbRefs.Count & " references to other workbooks. "
OtherWbDetail = OtherWbDetail & IIf(CBool(CountOfClosedWb), CountOfClosedWb & " are closed.", vbNullString)
OtherWbDetail = OtherWbDetail & vbCr & "They appear in the formula in this order:" & vbCrLf & vbCrLf
OtherWbDetail = OtherWbDetail & rrayStr(OtherWbRefs, vbCr)
End Function
Function SameWbDetail() As String
Rem display routine
SameWbDetail = SameWbDetail & "There are " & SameWbOtherSheetRefs.Count & " ref.s to other sheets in the same book."
SameWbDetail = SameWbDetail & vbCr & "They appear in this order, including duplications:" & vbCrLf & vbCrLf
SameWbDetail = SameWbDetail & rrayStr(SameWbOtherSheetRefs, vbCr)
SameWbDetail = SameWbDetail & vbCrLf & vbCrLf & "There are " & SameWbSameSheetRefs.Count & " precedents on the same sheet."
SameWbDetail = SameWbDetail & vbCr & "They are (out of order, duplicates not noted):" & vbCrLf & vbCrLf
SameWbDetail = SameWbDetail & rrayStr(SameWbSameSheetRefs, vbCr)
End Function
Function rrayStr(ByVal inputRRay As Variant, Optional Delimiter As String)
Rem display routine
Dim xVal As Variant
If IsEmpty(inputRRay) Then Exit Function
If Delimiter = vbNullString Then Delimiter = " "
For Each xVal In inputRRay
rrayStr = rrayStr & Delimiter & xVal
Next xVal
rrayStr = Mid(rrayStr, Len(Delimiter) + 1)
End Function

[xluser2007]

Hi mike,

Firstly, apologies for my delayed response, was busy with work yesterday so am responding with first opportunity today.

Secondly, before I discuss the awesome algorithm that you have supplied, can I just thank you and other contributors on VBAX who are helping me learn and develop through your superb and generous help :). On this steep learning curve, I really appreciate it.

The code you ahve sent me is brilliant, and have tested it for an externally linked workbook. To give details, I tested it on a linked workbook I posted here for a previous query to Bob titled C:\VBAX_Test_workbookforxld.xls. It has 3 worksheets ('Ext Links 1', 'Ext Links 2', 'Ext Links 3').

I tried the following testing (in 'Ext Links 1' worksheet):

An internal link in the same worksheet.

Cell 014 formula "=013"
Sub Run me output:
'[VBAX_Test_workbookforxld.xls]Ext Links 1'$0$13, as required.
FOUND CORRECTLY
An internal link in a different worksheet in the same workbook

Cell Q20 formula "='Ext Links 2'!F32"
Sub Run me output:
Msgbox OKonly style "Methods find different # of closed precedents"
FOUND INCORRECTLY i.e. no link found.
An external link of an already open workbook

Cell P14 formula "='[Test with Notes.xls]TestData'!D22"
Sub Run me output:
Msgbox OKonly style "Methods find different # of closed precedents"
FOUND INCORRECTLY i.e. no link found.
An external link of a closed workbook

Cell M9 formula "='C:\[sumif_countif.xls]Sheet1'!M8"
Sub Run me output:
'C:\[sumif_countif.xls]Sheet1'!M9, as required.
FOUND CORRECTLY
However sometimes when there is a single closed workbook link, the output errs and gives the same VBOkOnly Msgbox as with the p[revious test i.e. "Methods find different # of closed precedents", I can;t understand why it would do this for some cells and not others? When used in combination of open closed, internal links (i.e. a more realistic scenario e.g. cell formula is "=M8+D4+'C:\[sumif_countif.xls]Sheet1'!K9", the macro gives the same VBOkOnly Msgbox as above.

Did you find this when you tested it?

If not, could you please explain what I may be doing wrong?
If so, could you kindly help me to amend for it?Again thank you for your help and patience:). Despite my efforts on this problem, I realise its quite advanced but am learning lots in the process.

Please let me know on the above.

regards.

[mikerickson]

The posted routine confuses references to a worksheet with a space (eg.='My Sheet'!A1) with a reference to a closed workbook (='Macintosh HD:Users:merickson: Desktop:[Workbook3.xls]Sheet1'!$C14) (space inserted before "Desktop" to prevent emoticon :D )

("When did some nit-wit in a suit decide that spaces in sheet names were OK?", the old school grumbles.)

Changing this UDF will fix that.

The faulty result in the mixed case may be related to spaces in worksheet names. If so, this fix should include that as well.
The new routine correctly returned the precedents from
=Sheet2!B21+B21+'Macintosh HD:Users:merickson: Desktop:[Workbook2.xls]Sheet1'!$B$4+'Sh 3'!A3

ALSO: A remove-everything-between-double-quotes routine needs to be incorporated so that the text function
="xyz'[MyBook]mySheet'!A3abc" is not mis-read as a cell refernece.
I'll get on that when I get back from the post-event hot springs meeting.


Function NextClosedWbRefStr(ByVal formulaString As String, Optional ByRef Remnant As String) As String
Dim testStr As String
Dim startChr As Long
Dim subLen As Long
Dim i As Long
startChr = 0
Do
startChr = startChr + 1
subLen = 0
Do
subLen = subLen + 1
testStr = Mid(formulaString, startChr, subLen)
If testStr Like "'*'!*" Then
If testStr Like "'*]*'!*" Then
For i = 1 To 13
subLen = subLen - CBool(Mid(formulaString, startChr + subLen, 1) Like "[$:1-9A-Z]")
Next i
NextClosedWbRefStr = Mid(formulaString, startChr, subLen)
Remnant = Mid(formulaString, startChr + subLen)
Exit Function
Else
formulaString = Left(formulaString, startChr - 1) & Mid(formulaString, startChr + subLen)
startChr = 0
subLen = Len(formulaString) + 28
End If
End If
Loop Until Len(formulaString) < (subLen + startChr)
Loop Until Len(formulaString) < startChr
End FunctionThis function is the part of my code that is particularly suited to the use of Regular Expressions. (Parsing equations is the genesis of Regular Languages.) I wish my Mac supported them.

[xluser2007]

The posted routine confuses references to a worksheet with a space (eg.='My Sheet'!A1) with a reference to a closed workbook (='Macintosh HD:Users:merickson: Desktop:[Workbook3.xls]Sheet1'!$C14) (space inserted before "Desktop" to prevent emoticon :D )
Nice pick-up, may I ask how you realised this from my testing above?

The code works really well now mike :thumb.


ALSO: A remove-everything-between-double-quotes routine needs to be incorporated so that the text function
="xyz'[MyBook]mySheet'!A3abc" is not mis-read as a cell refernece.
This sounds interesting. I look forward to seeing your code for this and seeing a final solution to this interesting parsing problem. This problem is proving to be an enriching VBA experience, with all these conditions that keep popping up.


This function is the part of my code that is particularly suited to the use of Regular Expressions. (Parsing equations is the genesis of Regular Languages.) I wish my Mac supported them.

Since Bob and Dave mentioned this in an earlier post I have been intrigued by the pwer of RegExp. I feel I have only skimmed my understanding of its usefulness. The site I have been using is: http://www.regular-expressions.info/.

If I knew this better for use in VBA, I would love to adapt my initial attempt to using it. But other than Dave's great example list, there are not any great online tutorials for application in VBA, juts a case of trial-and-error to learn (which can be fun!)

BTW, I know my earlier code may is not as robust as yours, but for finding closed links in external workbooks, could you fault my code titled "Find_External_Links_in_CLOSED_Workbooks_references_only", I found that this bit worked quite well. If you can break it, is there a way to amend this code using RegExp to make it more rigorous, I'm just curious to hear your thoughts.

Well, thanks again and please let me know of the other Function and any changes.

regards,

[mikerickson]

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) '!testString Like " '*"!' " : Rem spaces added for clarityThe 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) '!testString Like " '*]*"!' " : Rem spaces added for clarity

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) '!
If testStr Like "'[![]*]*'!*" Then

In addition, the new function RemoveTextBetweenDoubleQuotes has been added.

These two routines should be replaced.Sub FindClosedWbReferences(inRange As Range)
Rem fills the collection with closed precedents parsed from the formula string
Dim testString As String, returnStr As String, remnantStr As String
testString = inRange.Formula
testString = RemoveTextInDoubleQuotes(testString): Rem new line
Set ClosedWbRefs = New Collection
Do
returnStr = NextClosedWbRefStr(testString, remnantStr)
ClosedWbRefs.Add Item:=returnStr, key:=CStr(ClosedWbRefs.Count)
testString = remnantStr
Loop Until returnStr = vbNullString

ClosedWbRefs.Remove ClosedWbRefs.Count
End Sub
Function NextClosedWbRefStr(ByVal formulaString As String, Optional ByRef Remnant As String) As String
Dim testStr As String
Dim startChr As Long
Dim subLen As Long
Dim i As Long
startChr = 0
Do
startChr = startChr + 1
subLen = 0
Do
subLen = subLen + 1
testStr = Mid(formulaString, startChr, subLen)
If testStr Like "'*'!*" Then
If testStr Like "'[![]*]*'!*" Then
For i = 1 To 13
subLen = subLen - CBool(Mid(formulaString, startChr + subLen, 1) Like "[$:1-9A-Z]")
Next i
NextClosedWbRefStr = Mid(formulaString, startChr, subLen)
Remnant = Mid(formulaString, startChr + subLen)
Exit Function
Else
formulaString = Left(formulaString, startChr - 1) & Mid(formulaString, startChr + subLen)
startChr = 0
subLen = Len(formulaString) + 28
End If
End If
Loop Until Len(formulaString) < (subLen + startChr)
Loop Until Len(formulaString) < startChr
End Function

And this new function added.
Function RemoveTextInDoubleQuotes(inString As String) As String
Dim firstDelimiter As Long, secondDelimiter As Long
Dim Delimiter As String: Delimiter = Chr(34)

RemoveTextInDoubleQuotes = inString
Do
firstDelimiter = InStr(RemoveTextInDoubleQuotes & Delimiter, Delimiter)
secondDelimiter = InStr(firstDelimiter + 1, RemoveTextInDoubleQuotes, Delimiter)
RemoveTextInDoubleQuotes = _
IIf(CBool(secondDelimiter), Left(RemoveTextInDoubleQuotes, firstDelimiter - 1), vbNullString) _
& Mid(RemoveTextInDoubleQuotes, secondDelimiter + 1)
Loop Until secondDelimiter = 0
End Function

All this string maniputlation can be improved. Windows supports better string handling features like Regular Expressions, Split, Join, Replace than Mac does.

I'm also wondering what this is for. Is there an end use for this or is it an intellectual exersize at the moment?

[xluser2007]

Hi mike,

That's a very elegant solution indeed. Thank you.


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 (http://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 (http://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 (http://www.thecodenet.com)
I see now. Good to know so I can pick-up some testing skills.


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.


I'm also wondering what this is for. Is there an end use for this or is it an intellectual exersize at the moment?
This will definetely have a use.

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.

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?Thanks again for your kind help :clap: 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.

[nikoskatraki]

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.


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.

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.


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?

Thanks again for your kind help :clap: 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.

hi all! I'm new to this forum. I've been googling for days to find something to help me understand precedents and create something that goes through them and allows the user to follow the one they like without having to use the mouse. The code posted here is the most comprehensive I've seen so far! Great job!
To be honest, I dont usually need to trace named ranges (they are obvious and happy to rely on name manager), but I just thought I'd mention it. Given that a named range does not necessarily point to a cell, it may be a bit harder to trace.
Thanks in advance,
Nikos