If you modify the code by adding a module or removing it, the project has to
be re-compiled. This process clears all variables.

You can't run code from a text file. It has to be compiled, and to compile it,
it has to be present as a module in the project.

I think you must just accept the size of the add-in.

On Sat, 9 Oct 2004 23:41:23 +0100, "RB Smissaert"
wrote:

Myrna,

Thanks for making this clear.
My idea was the following:
I have a large add-in, now over 3 Mb.
There are a number of large Subs that run reports.
I thought what I could do is put this subs in a text file.
Open the text file to a module, run the code and clear the module after.
This does work, but I have just found one major drawback to this.
When the Sub is cleared I loose my variables and my ADO connection to the
Interbase database. This would mean that after runing these reports the main
form
would have to be reloaded, and all the variables and the ADO connection
would have
to be re-done.
I suppose this is not possible, but it would be nice if code could be run
from textfiles.
Any suggestions here?

RBS


"Myrna Larson" wrote in message
.. .
Yes, the return type of the *function* is boolean. But it DOES return a
2nd
value in one of the arguments, namely startline. So startline serves as
both
and input and an output.

I see you are searching in only one procedure. Why? If you know the
procedure
name, I don't see what is to be added by determining the line number, but
then
I haven't seen your entire code ....

Please let me know if the following works. It searches the ENTIRE module
for
the string. Once you have the line number, you can use ProcOfLine to find
out
which procedure "owns" that line.

You didn't use VBProj in your code, and the procedure name shouldn't be
necessary, so I removed those as arguments. OTOH, maybe part of the
problem is
that it should be

set vbMod = VBProj.VBComp.CodeModule

(I haven't worked with these functions lately, so I'm a bit "rusty".)


Function GetLineNumberString(ByRef VBComp As VBComponent, _
ByVal strToFind As String) As Long
Dim lStart As Long
Dim vbMod As CodeModule

Set vbMod = VBComp.CodeModule
lStart = 1
If vbMod.Find(strToFind, lStart, 1, -1, -1) Then
GetLineNumberString = lStart
Else
GetLineNumberString = 0
End If
End Function

On Sat, 9 Oct 2004 20:51:03 +0100, "RB Smissaert"
wrote:

Myrna,

As far as I can see the Find method of the VBE library only returns TRUE
or
FALSE.

I have just made a function that loops through the lines. It works, but I
would be interested
if there was a direct way to get the line number where string was found
first.


Function GetLineNumberString(ByRef vbProj As VBProject, _
ByRef VBComp As VBComponent, _
ByVal strProc As String, _
ByVal strToFind As String) As Long

Dim i As Long
Dim lStart As Long
Dim lCount As Long
Dim lEnd As Long
Dim vbMod As CodeModule

Set vbMod = VBComp.CodeModule

With vbMod
lStart = .ProcBodyLine(strProc, vbext_pk_Proc)
lCount = .ProcCountLines(strProc, vbext_pk_Proc)
lEnd = lStart + lCount
For i = lStart To lEnd
If .Find(strToFind, i, 1, i, -1, False, False, False) = True
Then
GetLineNumberString = i
Exit Function
End If
Next
End With

GetLineNumberString = 0

End Function

Sub linetest()

MsgBox GetLineNumberString(ThisWorkbook.VBProject, _

ThisWorkbook.VBProject.VBComponents("SubsFromText" ),
_
"UnlinkedDrugsStatsPivot", _
"pivot table")
End Sub


RBS


"Myrna Larson" wrote in message
...
How are you searching for the string? If it's via the Find method in the
VBIDE
library, you find the following in Help.

Note the comments re the StartLine argument, specifically that if the
string
is found, the argument is set to the line number where it was found.

Is that what you need?

"Find Method (VBA Add-In Object Model)

Searches the active module for a specified string.

Syntax

object.Find(target, startline, startcol, endline, endcol [, wholeword]
[,
matchcase] [, patternsearch]) As Boolean

object Required. An object expression that evaluates to an object in the
Applies To list.

target Required. A String containing the text or pattern you want to
find.

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.

startcol Required. A Long specifying the column at which you want to
start
the
search; will be set to the column containing the match if one is found.
The
first column is 1.

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

endcol Required. A Long specifying the last line of the match if one is
found.
The last column may be designated as -1.

wholeword Optional. A Boolean value specifying whether to only match
whole
words. If True, only matches whole words. False is the default.

matchcase Optional. A Boolean value specifying whether to match case. If
True,
the search is case sensitive. False is the default.

patternsearch Optional. A Boolean value specifying whether or not the
target
string is a regular expression pattern. If True, the target string is a
regular expression pattern. False is the default.


On Sat, 9 Oct 2004 19:22:20 +0100, "RB Smissaert"
wrote:

How do I programmatically get the linenumber of the first line in a code
module where a specified string is found?
I can see if a string was found or not, but there seems to be no way to
get
the line number, other
than running a loop and checking a number of lines.

RBS