Solved: Splitting all addresses in a formula

[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 )

("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 Function

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.

[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 )

Nice pick-up, may I ask how you realised this from my testing above?

The code works really well now mike .

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

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]

In addition, the new function RemoveTextBetweenDoubleQuotes has been added.

These two routines should be replaced.[VBA]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
[/VBA]
And this new function added.
[VBA]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
[/VBA]
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
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

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 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 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