CodeModule.Find Method changes value of variables (Find Method VBA Add-In Object Model)

Question

I am trying to search the current VBProject for instances of procedure names. To do this, I am using the CodeModule.Find method (details here), which asks for:

A search term (I am supplying a string variable)
the start line (I am supplying a long variable)
the start column (I use column 1)
the end line (I am supplying a long variable), and
the end column (I use column 500).

For some reason, every time I call this method, it changes the value of both the st_line and en_line variables to different numbers. In the simple example I am providing, it doesn't seem like I need to use these numbers more than once, but in my application I do, and the method keeps changing them!

Code:

Sub Find_Method_Changes_Var_Values()

    Dim st_line As Long, en_line As Long
    Dim search_String As String
    Dim VBC As VBIDE.VBComponent

    search_String = "Sub"
    st_line = 5
    en_line = 100
    Set VBC = ThisWorkbook.VBProject.VBComponents("ThisWorkbook")

    ***If VBC.CodeModule.Find(search_String, st_line, 1, en_line, 500) = True Then
        MsgBox "Found our target."
    End If
End Sub

Once the line with three asterisks in front of it is run (whether or not it actually finds the string we're searching for), the values of st_line and en_line are both changed to 20.

What it seems like the method is doing is changing the st_line and en_line variables to the line where the method finds the first instance of the search term.

How can I both use this method and preserve my variable values?

After more research, I have found that if I do not include the st_line and en_line variables in the method, then they retain their values. — mattbierwirth, Aug 16 '17 at 18:57

score 2 · Accepted Answer · edited Aug 16 '17 at 21:26

From the documentation for this topic:

startline Required. A Long specifying the line at which you want to start the search; will be set to the line of the match if one is found. The first line is number 1.

It looks like VBC.CodeModule.Find is changing the startline. It affects the endline similarly:

endline Required. A Long specifying the last line of the match if one is found. The last line may be specified as 1.

You'll have to use another pair of variables set to the same values, so change:

st_line = 5
en_line = 100
Set VBC = ThisWorkbook.VBProject.VBComponents("ThisWorkbook")

***If VBC.CodeModule.Find(search_String, st_line, 1, en_line, 500) = True Then
    MsgBox "Found our target."
End If

to

st_line = 5
en_line = 100
' temporary variables
temp_st_line = st_line
temp_en_line = en_line
Set VBC = ThisWorkbook.VBProject.VBComponents("ThisWorkbook")

***If VBC.CodeModule.Find(search_String, temp_st_line, 1, temp_en_line, 500) = True Then
    MsgBox "Found our target."
End If

Source: https://msdn.microsoft.com/en-us/library/aa443952(v=vs.60).aspx

Ugh, how did I miss that? Thank you for actually interpreting the documentation! — mattbierwirth, Aug 16 '17 at 19:03

CodeModule.Find Method changes value of variables (Find Method VBA Add-In Object Model)

1 Answers1