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.