VBIDE Error

[kpark91]

I've been trying to search text in VBA codepane and I came across this code from http://www.cpearson.com/excel/vbe.aspx

Code: Select all

    Sub SearchCodeModule()
        Dim VBProj As VBIDE.VBProject
        Dim VBComp As VBIDE.VBComponent
        Dim CodeMod As VBIDE.CodeModule
        Dim FindWhat As String
        Dim SL As Long ' start line
        Dim EL As Long ' end line
        Dim SC As Long ' start column
        Dim EC As Long ' end column
        Dim Found As Boolean
        
        Set VBProj = ActiveWorkbook.VBProject
        Set VBComp = VBProj.VBComponents("Module1")
        Set CodeMod = VBComp.CodeModule
        
        FindWhat = "findthis"
        
        With CodeMod
            SL = 1
            EL = .CountOfLines
            SC = 1
            EC = 255
            Found = .Find(target:=FindWhat, StartLine:=SL, StartColumn:=SC, _
                EndLine:=EL, EndColumn:=EC, _
                wholeword:=True, MatchCase:=False, patternsearch:=False)
            Do Until Found = False
                Debug.Print "Found at: Line: " & CStr(SL) & " Column: " & CStr(SC)
                EL = .CountOfLines
                SC = EC + 1
                EC = 255
                Found = .Find(target:=FindWhat, StartLine:=SL, StartColumn:=SC, _
                    EndLine:=EL, EndColumn:=EC, _
                    wholeword:=True, MatchCase:=False, patternsearch:=False)
            Loop
        End With
    End Sub

However, I was unable to run this code because of VBIDE error saying,
User-defined function not defined..

So, I asked a few people who eventually answered saying one file was not referenced 'well'. So I've tried like they suggested,
tools>references>Microsoft Visual Basic For Application Extensibility Library 5.3

I tried that method and it worked!
But easier method to fix this problem was running this code which does the same thing.

ThisWorkbook.VBProject.References.AddFromGuid _
GUID:="{0002E157-0000-0000-C000-000000000046}", _
Major:=5, Minor:=3

[HansV]

The error message "User-defined function not defined" usually means that there's a missing reference. The two most probable causes for this problem are:

• The reference has been set, but the corresponding library file is not available on the PC where the document/workbook/presentation/database is opened, or the library file has been installed in a different folder, or a different version of the library has been installed. If you select Tools | References... in the Visual Basic Editor, you'll see:
  
  MISSING: name of missing library
  
  among the selected references. The name provides a clue that tells you what is needed. Clear the check box of the missing reference, and look through the remaining references to see if you can find it (perhaps in a different version)
• You copied (or wrote) code that requires a non-standard reference but didn't set it. In this case, you'll have to find out which library file is needed for the code. A wbe search, for example using Google, may be helpful.

You only have to set a reference once (it will be stored with the document/workbook/presentation/database), so it's usually easier to use Tools | References... than to write code for it.

[Charlotte]

Direct reference to the VBIDE requires the extensibility library reference to be set. I would never consider using a GUID to generate the reference link easier than just setting the reference.