Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 35

Thread: Solved: Splitting all addresses in a formula

  1. #1
    VBAX Expert
    Joined
    Dec 2007
    Posts
    522
    Location

    Solved: Splitting all addresses in a formula

    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\[Book2]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):

    Internal Links
    i.e. links within the Activecell in Activeworkbook (C:\Documents\Test.xls)
    1. C:\Documents\[Test.xls]Sheet1'!$A$2
    2. C:\Documents\[Test.xls]Sheet1'!$B$2:$C$10
    3. C:\Documents\[Test.xls]Sheet1'!$D$2
    4. C:\Documents\[Test.xls]Sheet2'!A1
    5. C:\Documents\[Test.xls]Sheet3'!$A$1
    6. C:\Documents\[Test.xls]Sheet3'!$A$1

    External Links i.e. from External workbook sources in the Activecell in Activeworkbook (C:\Documents\Test.xls)
    1. 'C:\Documents and Settings\ExcelTestFiles\[Book2]Sheet1!$A$1
    2. 'C:\Documents and Settings\VBAX\[Book2]Sheet1!$B$1:$D$17
    3. 'C:\Important\Results\[Book2]Sheet2!A$1
    4. 'C:\TestFiles\[Book3]Sheet1!$A$1
    THUS, 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.
    Last edited by xluser2007; 05-03-2008 at 01:00 AM.

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    VBAX Expert
    Joined
    Dec 2007
    Posts
    522
    Location
    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 .

  4. #4
    Knowledge Base Approver VBAX Expert brettdj's Avatar
    Joined
    May 2004
    Location
    Melbourne
    Posts
    649
    Location
    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

  5. #5
    VBAX Expert
    Joined
    Dec 2007
    Posts
    522
    Location
    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,
    Last edited by xluser2007; 05-04-2008 at 12:32 AM.

  6. #6
    Mac Moderator VBAX Guru mikerickson's Avatar
    Joined
    May 2007
    Location
    Davis CA
    Posts
    2,778
    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

  7. #7
    VBAX Expert
    Joined
    Dec 2007
    Posts
    522
    Location
    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,

  8. #8
    VBAX Expert
    Joined
    Dec 2007
    Posts
    522
    Location
    Hi mike,

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

    I tested your code as follows.:
    1. Opened a workbook with valid external liks.
    2. Pasted all of your code into a Module 1.
    3. 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 ).

    regards

  9. #9
    Mac Moderator VBAX Guru mikerickson's Avatar
    Joined
    May 2007
    Location
    Davis CA
    Posts
    2,778
    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.
    Last edited by mikerickson; 05-04-2008 at 02:36 AM.

  10. #10
    VBAX Expert
    Joined
    Dec 2007
    Posts
    522
    Location
    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:

    [vba]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[/vba]

    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

  11. #11
    VBAX Expert
    Joined
    Dec 2007
    Posts
    522
    Location
    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.

    [vba]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[/vba]

    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

  12. #12
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Quote Originally Posted by xluser2007
    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)

    [vba]

    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
    [/vba]

    and another, an email validator using RegExp

    [vba]

    '-----------------------------------------------------------------
    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
    [/vba]

    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.
    Last edited by Bob Phillips; 05-04-2008 at 03:51 PM.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  13. #13
    Mac Moderator VBAX Guru mikerickson's Avatar
    Joined
    May 2007
    Location
    Davis CA
    Posts
    2,778
    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.
    Last edited by mikerickson; 05-04-2008 at 04:11 PM.

  14. #14
    VBAX Expert
    Joined
    Dec 2007
    Posts
    522
    Location
    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:

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

    [vba]ReDim outRRay(1 To Len(formulaString))[/vba]

    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,

  15. #15
    VBAX Expert
    Joined
    Dec 2007
    Posts
    522
    Location
    Quote Originally Posted by xld
    Here is a simple function that checks for invalid characters (such as in a filename)

    [vba]

    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
    [/vba]
    and another, an email validator using RegExp

    [vba]

    '-----------------------------------------------------------------
    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
    [/vba]
    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,

  16. #16
    Mac Moderator VBAX Guru mikerickson's Avatar
    Joined
    May 2007
    Location
    Davis CA
    Posts
    2,778
    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
    [VBA]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[/VBA]

  17. #17
    VBAX Expert
    Joined
    Dec 2007
    Posts
    522
    Location
    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:
    1. 'D:\VBATest\source files\ [i.e. FOLDER PATH]
    2. Specifications_Nov 06.xls (this it already does without teh square brackets)
    3. $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,



  18. #18
    VBAX Expert
    Joined
    Dec 2007
    Posts
    522
    Location
    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:
    1. 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
    2. 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,

  19. #19
    VBAX Expert
    Joined
    Dec 2007
    Posts
    522
    Location
    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,

  20. #20
    VBAX Expert
    Joined
    Dec 2007
    Posts
    522
    Location
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •