"Find" inexplicably failing 1

[N1GHTEYES]

In code, the ".Find" method sometimes works and sometimes seems to fail for no reason I can see.

I have an add-in, for use in Excel, which several of my colleagues use and which I also use in several different locations. There is a problem in Excel (previously discussed in other threads) whereby it saves explicit path references to add-ins, so that if a user who references an add-in gives a copy of the workbook to another user who stores his copy of the same add-in in a different location then the workbook will not call his version of the add-in.

I have fixed this problem by writing a macro which is part of my add-in.

When run, the macro looks for all instances of a particular string which is a "signature" of the use of an explicit path for the add-in in question. It then parses the text to find the path to the add-in. It then does a search and replace, stripping the explicit path reference from all the cells which call the add-in functions. At this point, the new user's workbook will then identify the function call with its own version of the add-in.

That is all very well, and usually works fine. Except occasionally it does not seem to do so. Sometimes, it does not report finding any matches, even when they are clearly there.

I just ran and debugged the code in one of these cases and the "c" variable - the recipient of the find process - is listed as "nothing" or "error 2029" depending apparently on whether an instance of the search-for text is found.

Can anybody throw any light on why the behaviour of this code should vary, why the Find method sometimes seems to give these anomalous results, and if there is a solution to the problem?

Thanks in advance,

Tony

Here is the code:

Sub AL_Clear_AL_XL_Refs()
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Description:
'This sub finds and removes all the explicit path references to AL_XL_Tools.xla in the active workbook.
'
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'dim vars
Dim Pathfound As Boolean, searchingsheet
Dim AL_Path As String, stringstart As String
Dim wbk As Workbook
Dim wksht As Worksheet
Dim numshts As Long, shtnum As Long, funcpos As Long, startpos As Long
Dim c As Variant
Dim oldstate As Variant

'note old dislay state so as to be able to restore it later, then set it to false to avoid messages during operation
oldstate = Application.DisplayAlerts
Application.DisplayAlerts = False

'check all worksheets for an instance of an explicit reference to AL_XL_Tools (if there is one) and note the path
Set wbk = ActiveWorkbook
Pathfound = False
numshts = wbk.Worksheets.count
shtnum = 0
On Error Resume Next
While Not Pathfound And shtnum < numshts
    shtnum = shtnum + 1
    Set wksht = wbk.Worksheets(shtnum)
    searchingsheet = True
    While searchingsheet
        Set c = wksht.Cells.Find(What:="'!AL_", LookIn:=xlFormulas, LookAt:= _
                                    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
                                    , SearchFormat:=False)
        If Err.Number = 0 Then
            funcpos = InstrRev(c.Formula, "'!AL_")
            stringstart = Mid(c.Formula, 1, funcpos - 1)
            startpos = InstrRev(stringstart, "'")
            If startpos > 0 Then
                AL_Path = Mid(c.Formula, startpos, funcpos + 2 - startpos)
                Pathfound = True
                searchingsheet = False
            Else
                searchingsheet = True
            End If
        Else
            searchingsheet = False
            Err.Clear
        End If
    Wend
Wend

Err.Clear
On Error Resume Next

'find and remove all instances of the AL_XL_Tools path
If Pathfound Then
    shtnum = 0
    While shtnum < numshts
        shtnum = shtnum + 1
        wbk.Worksheets(shtnum).Cells.Replace What:=AL_Path, Replacement:="", LookAt:=xlPart, _
                        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                        ReplaceFormat:=False
        Err.Clear
    Wend
    MsgBox "All instances cleaned OK", vbOKOnly
Else

 

[strongm]

Goodness - that seems a bit ... overengineered.

Unless I've completely misunderstood (always a possibility), I think something akin to the following will achieve much the same result:

[blue]Sub Example()
    Dim aLinks As Variant
    Dim lp As Long
    Dim wbk As Workbook
    
    Set wbk = ActiveWorkbook
    
    aLinks = wbk.LinkSources(xlExcelLinks)
    If Not IsEmpty(aLinks) Then
        For lp = 1 To UBound(aLinks)
            If InStr(aLinks(lp), "AL_XL_Tools.xla") <> -1 Then
                wbk.ChangeLink aLinks(lp), "AL_XL_Tools.xla", Type:=xlExcelLinks
            End If
        Next
    End If
End Sub[/blue]

 

[N1GHTEYES]

Thanks strongm. As usual you have provided a much neater and more elegant solution and managed to teach me some new stuff at the same time. Have a star!

Overengineered? Well I do have a tendency to use concrete where cardboard will do...

Two quick questions
1 I've not used .linksources before (I did not even know it existed). I take it that what this is doing is finding any links which include the string "AL_XL_Tools.xla" and replacing them with just the string "AL_XL_Tools.xla"? Or is it telling excel that, in those cases, the link should be to the add-in it recognises by that name which is already listed as a reference. I think those two cases are subtly different.

2 Purely on the question of why the .find might work in some cases but not others - got any ideas? I realise I won't need this, assuming your code works (I'll try it shortly), but there might be other occasions when I want to be able to use the .find method and it would be nice to know why it sometimes does not seem to work.

Thanks again,

Tony

 

[strongm]

>I take it that what this is doing is finding any links

Sort of. Excel maintains a list of links. If your sheet or workbook has multiple links to something, Excel only need to keep one reference to that link. So, if you had loads and loads of cells containing references to your addin, linksouces would only contain 1 entry for it. Updating that entry in linksources causes all the indirect references (i.e all your sheet entries) to update at the same time.

>is it telling excel that, in those cases, the link should be to the add-in it recognises by that name which is already listed as a reference. I think those two cases are subtly different

They are indeed. And this code really implements the recognized add-in idea.

 

[N1GHTEYES]

OK, so I tried your code.

I had a workbook I'd created at home so the AL_XL_Tools.xla functions had an explicit path to where I keep it on my home machine. When I open it at work it therefore displays the NAME? error in the cells where the AL_XL functions are called.

When I ran your code, it came up with a dialogue box saying the links had not been updated because AL_XL_Tools had not be recalculated before last being saved. This is odd because there is nothing really in there which needs much calculation - it is primarily a set of UDFs and macros.

The dialogue gave me the option of continuing or cancelling - I continued. It then gave me a choice of worksheets within AL_XL_Tools.xla from which to take the values. This did not make a lot of sense to me to be honest, but I selected one and clicked OK.

It then whirred and clicked for a while and eventually finished.

When I looked at the workbook, the NAME? errors were still there, but now the explicit path was the correct path to the location on my work machine. I entered one of the cells where this was the case and hit <RTN> to see if that made it resolve the reference (as it sometimes does), but no joy - I still had the NAME? error.

I then tried running my code (as posted above) which then worked - i.e. I now got no problem with the .Find method.

So, in a round-about way, your code solved the problem, but the response of the system has left me a bit puzzled.

Tony
Tony

 

[strongm]

Hmm ...

 

[N1GHTEYES]

Any further thoughts?

On either:

1 why the links code caused the dialogs
2 how to avoid them
3 why it still gave a name error
4 why the .find method worked after application of the links code
5 why the .find method sometimes fails?

Tony

 

[yooneek]

From my limited exposure to udf's, you shouldn't need to tell excel which add-in to run the function from... you just type it in the into the cell the function you are calling.

(just started looking at UDF's with 2k7, which this works with)

 

[N1GHTEYES]

yooneek,

Yes and no. When you have an add-in referenced, if you type a udf from it into a cell, then no, you do not need to explicitly reference the add-in in the text of the cell. However, when Excel saves that workbook, it does save an explicit reference to the add-in, including its path. If you then give that workbook to somebody else who has a copy of the add-in, but which is located on a different path, the workbook fails to recognise that the function belongs to the local version of the add-in.

In that case, if you look at what is actually entered in a cell that calls a function from the first version of the add-in, the cell contains an explicit reference to the first add-in and its path.

One way of fixing the problem is to manually search for and remove the path and add-in name string from those cells. The workbook is then forced to look at the functions it has available to it, including those from add-ins, and it finds the one which matches in its locally referenced version, and thus it effectively links the function to its own version of the add-in. This process is a bit tedious however, and has to be done on each sheet of the workbook individually. Therefore, to make the whole thing a bit quicker and easier, I wrote a macro, attached to a toolbar button, which does exactly that - it searches every sheet of the workbook for references containing the name of the add-in in question, notes the path and name section of the string in the cell, and deletes them.

That process normally works find - I've been using it without a problem for ages - but I recently had a few cases where the "find" process within the code did not find the cells which it should have done. It therefore reported that no instances of the add-in were found when there actually were some. I checked manually to ensure that they could be found using the search string in the code, and Excel found them OK. This was weird and unexplained, so I posed the question here - why is the find method failing?

strongm, as usual (being a VBA god), suggested a much more elegant solution which used a property of which I'd been unaware, to find and edit the links the workbook was using instead. That process looks fine in principle, but had some issues in practice.

When I mentioned the above to strongm, the response was "Hmm...", which I took to mean he was thinking about it. But if anyone is reading this who knows why there may have been any of the problems I listed earlier in the thread with respect to strongm's suggestion, or with the Find process failing in the first place, I'd be very grateful for any suggestions.

Tony

 

[Gruuuu]

Was the AL_XL_Tools.xla file listed as one of the addins for your install of Excel at home?

 

[N1GHTEYES]

Gruuuu - sorry about the slow response, I've been a bit too tied up on other work to get back here for a few days.

AL_XL_Tools.xla is an add-in I wrote myself. As it happens, I do have it installed at home, but I also get the issue described above at work. We use a networked system, but each dept has its own areas. Those colleagues in my dept who use AL_XL_Tools.xla all refer to the same networked copy, so they can pass workbooks freely amongst themselves without a problem. However, some are from different departments, and they cannot see the copy of the add-in we use. These colleagues all use local copies, so when they open a workbook saved by me, they encounter the path reference problem - and ditto if I use one of theirs.

You are of course right in noting that I do also have this issue if I open at work a workbook I created at home.

Does that answer you question?

Tony