Consulting

Results 1 to 20 of 20

Thread: Find text in Excel workbook

  1. #1
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location

    Find text in Excel workbook

    There is a bug in the code designed to write some code to a sheet module in this procedure. (Excel 2000). Suggestions/cures?
    MD

  2. #2
    VBAX Mentor CBrine's Avatar
    Joined
    Jun 2004
    Location
    Toronto, Canada
    Posts
    387
    Location
    mdmackillop,

    Found your problem. Your code was working fine. It's the code you were writing to the vba editor that was causing excel to crash. If you try to add code that the editor chokes on, it crashes excel. Take a look at the new attached file.
    The most difficult errors to resolve are the one's you know you didn't make.


  3. #3
    Moderator VBAX Master Tommy's Avatar
    Joined
    May 2004
    Location
    Houston, TX
    Posts
    1,184
    Location
    I knew I shold have tried to run the code that was added. LOL

    CBrine - Good catch

    Later
    Tommy
    aka
    dragontooth

  4. #4
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Hi Cbrine,
    Still the same problem!
    Can you tell me, if you let the MsgBox Pause run, Is FindWord visible as one of the sheet tabs?, With me it is not.
    MD

  5. #5
    VBAX Mentor CBrine's Avatar
    Joined
    Jun 2004
    Location
    Toronto, Canada
    Posts
    387
    Location
    Occurences of:door
    LocationCell TextData1!A21Revised cost of internal doors and screensData1!A22Saving on standard doorsData1!A23Addition to glazed doors, cupboard doors and glazed screensData1!A26Window/External Doors: Investigate alternative suppliersData1!A8567 ? 68. Allow the provisional sum of ?750 for supply of door bell and bell push (terms strictly nett)Data1!A8758 ? 59. Allow the provisional sum of ?750 for supply of door bell and bell push (terms strictly nett)MoreData!A21Revised cost of internal doors and screensMoreData!A22Saving on standard doorsMoreData!A23Addition to glazed doors, cupboard doors and glazed screensMoreData!A26Window/External Doors: Investigate alternative suppliersMoreData!A8567 ? 68. Allow the provisional sum of ?750 for supply of door bell and bell push (terms strictly nett)MoreData!A8758 ? 59. Allow the provisional sum of ?750 for supply of door bell and bell push (terms strictly nett)MoreData!E21Revised cost of internal doors and screens



    MoreData!E22Saving on standard doorsMoreData!E23Addition to glazed doors, cupboard doors and glazed screensMoreData!E26Window/External Doors: Investigate alternative suppliersMoreData!E8567 ? 68. Allow the provisional sum of ?750 for supply of door bell andMoreData!E8758 ? 59. Allow the provisional sum of ?750 for supply of door bell and


    Here's the response I get on running with the default value "door". It appears on Worksheet "FindWord"
    Edit: That's uglier then I thought it would be.
    Also, I did setup the pause msgbox to run. No Problems.

    What happens when you run it?
    The most difficult errors to resolve are the one's you know you didn't make.


  6. #6
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    It all works except adding the code to the sheet. Here's a simplified version.

  7. #7
    Knowledge Base Approver
    The King of Overkill! VBAX Master
    Joined
    Jul 2004
    Location
    Rochester, NY
    Posts
    1,727
    Location
    Still no errors here MD, I'm baffled

  8. #8
    VBAX Mentor CBrine's Avatar
    Joined
    Jun 2004
    Location
    Toronto, Canada
    Posts
    387
    Location
    MD,
    Did you setup the reference for the vba extensibilty lib?
    The most difficult errors to resolve are the one's you know you didn't make.


  9. #9
    Moderator VBAX Master Tommy's Avatar
    Joined
    May 2004
    Location
    Houston, TX
    Posts
    1,184
    Location
    I think I have it. I think the problem is that Excel doesn't actually "add" the CodeModule to the collection until the codepane is displayed for the "new" sheet. In other words when you are stepping through the code it is added, when you are using the macro it is not.

    I revised the code to this and I got no more errors, no GPF's no excel crashing.
    Which by the way I was getting a lot of before.

    Sub AddSheetCode()
       Dim strCode As String
       Dim FWord As String
       Dim WB As Workbook
       Dim Sh
       Dim I As Integer
       Set WB = ActiveWorkbook
       strCode = "Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)" & vbCr _
            & "If Sh.Name = " & Chr(34) & "FindWord" & Chr(34) & " Then" & vbCr _
            & "If Target.Address = " & Chr(34) & "$B$1" & Chr(34) & " Then" & vbCr _
            & "FindAll Target.Text, " & Chr(34) & "False" & Chr(34) & vbCr _
            & "Cells(1,2).Select" & vbCr _
            & "End if" & vbCr _
            & "End if" & vbCr _
            & "End Sub"
        Debug.Print strCode
        FWord = "ThisWorkbook"
        For I = 1 To WB.VBProject.VBComponents.Count
            If WB.VBProject.VBComponents.Item(I).Name = FWord Then
                Exit For
            End If
        Next
        If Not WB.VBProject.VBComponents.Item(I).CodeModule Is Nothing Then
            If Not WB.VBProject.VBComponents.Item(I).CodeModule.Find("Workbook_SheetChange", 1, 1, 100, 100) Then
                WB.VBProject.VBComponents.Item(I).CodeModule.AddFromString (strCode)
            End If
        End If
        Set WB = Nothing
    End Sub

  10. #10
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Thanks Tommy and all others who rendered assistance. Final version has been posted as a KB entry. I've still to puzzle out exactly what's going on, but its working!

  11. #11
    Site Admin
    Urban Myth
    VBAX Guru
    Joined
    May 2004
    Location
    Oregon, United States
    Posts
    4,940
    Location
    Quote Originally Posted by Tommy
    ... no GPF's ...
    Hey Tommy, just trying to follow this great thread. What are GPF's?

  12. #12
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Hi Zack,
    General Protection Faults (the things that make your PC crash).
    That's all I know about them!
    MD

  13. #13
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Hi Tommy,
    I was running the code again, and failed to get the FindWord page to add. Investigation showed that the error on Sheets(FindWord").Select was -2147352565 , not 9 as per usual. This can be added to the routine, but I wondered if you had any ideas?
    MD

  14. #14
    Moderator VBAX Master Tommy's Avatar
    Joined
    May 2004
    Location
    Houston, TX
    Posts
    1,184
    Location
    Hi Zack,

    To go along with MD, that is how I tell I have got to pay more attention to what I am doing . It could mean that I stepped on some system memory (OS).

    MD
    This is what MS says about the error.
    -2147352565 (8002000B) Invalid index.
    http://support.microsoft.com/default...b;en-us;186063

    It is an automation error. Were you making this into an add-in? If so it may have gotten confused as to which workbook you were working on, just a guess. Did you change versions? I have a copy 2003 to test and maybe duplicate the error. If I can duplicate the error I (hopefully) can fix it, but I need to know how to duplicate.

    Later

  15. #15
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Hi Tommy,
    I've amended my code to if Err <> 0 (as suggested by DRJ), so this avoids the issue. However, on using the procedure at work, I notice it doesn't handle sheet names with spaces. Can you return the KB item to WIP so I can add a couple of quotation marks
    Thanks
    MD

  16. #16
    Moderator VBAX Master Tommy's Avatar
    Joined
    May 2004
    Location
    Houston, TX
    Posts
    1,184
    Location
    Hi MD,
    I changed the KB to WIP.
    I would suggest 2 things in the Err <> 0 part, this is just me and to my knowledge is not necessary. Err.Number <> 0 this is because I just like to know what I am checking for, once the Err.Number <> 0 then Err.Clear I prefer to clear it if it is there, the only reason is I have spent hours looking for an error that I knew I had trapped, come to find out it was someplace else entirely and was left over.

    Later

  17. #17
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    As follows?

    If Err.Number <> 0 Then 
             'error occured so clear it
            Err.Clear 
            Sheets.Add.Name = "FindWord" 
            Sheets("FindWord").Move After:=Sheets(Sheets.Count) 
             'Run macro to add code to ThisWorkbook
            AddSheetCode 
        End If

  18. #18
    Moderator VBAX Master Tommy's Avatar
    Joined
    May 2004
    Location
    Houston, TX
    Posts
    1,184
    Location
    Yes, but once again this just my opinion.

  19. #19
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    I agree with the logic, especially where an error is anticipated as in this case.

  20. #20
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    A "curiosity".
    Entering * as the search term lists the contents of all the used cells in the workbook. Although why anyone would want to do this I don't know.
    MD

Posting Permissions

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