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

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

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

I have a search/replace ("Search Replace Code") example on my website which
will return you a line number with a little modification.



--
Rob van Gelder - http://www.vangelder.co.nz/excel


"RB Smissaert" wrote in message
...
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

Unlike most excel/vba functions, the arguments that Myrna shows are also for
output. Not only does the function return true or false, it also sets
values to the arguments which you can read with you variable

a sample home made function to illustrate

Sub Main
Dim i, j, k

i = 1
j = 2
k = 3
i = Myfunction(j, k)
msgbox i, j, k

End Sub

Function MyFunction( s, t)
s = s * 2
t = t * 2
Myfunction = s * t
End Function

Msbox show 24 4 6

so the function returns 3 values. One the normal way and 2 through the
argument list.


so your function would be

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 lStartRow As Long
Dim lCount As Long
Dim lEnd As Long
Dim lStartCol as Long
Dim vbMod As CodeModule

Set vbMod = VBComp.CodeModule

With vbMod
lStartRow = .ProcBodyLine(strProc, vbext_pk_Proc)
lCount = .ProcCountLines(strProc, vbext_pk_Proc)
lEnd = lStart + lCount
If .Find(strToFind, lStartRow, lStartCol , i, -1, False, False, False) =
True
GetLineNumberString = lStartRow
Else
GetLineNumberString = 0
End if
End With
End Function


--
Regards,
Tom Ogilvy


"RB Smissaert" wrote in message
...
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

Tom,

Thanks for that.
This is a new one for me. Very useful to know!

The msgbox in your example should be:
MsgBox i & " " & j & " " & k


RBS


"Tom Ogilvy" wrote in message
...
Unlike most excel/vba functions, the arguments that Myrna shows are also
for
output. Not only does the function return true or false, it also sets
values to the arguments which you can read with you variable

a sample home made function to illustrate

Sub Main
Dim i, j, k

i = 1
j = 2
k = 3
i = Myfunction(j, k)
msgbox i, j, k

End Sub

Function MyFunction( s, t)
s = s * 2
t = t * 2
Myfunction = s * t
End Function

Msbox show 24 4 6

so the function returns 3 values. One the normal way and 2 through the
argument list.


so your function would be

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 lStartRow As Long
Dim lCount As Long
Dim lEnd As Long
Dim lStartCol as Long
Dim vbMod As CodeModule

Set vbMod = VBComp.CodeModule

With vbMod
lStartRow = .ProcBodyLine(strProc, vbext_pk_Proc)
lCount = .ProcCountLines(strProc, vbext_pk_Proc)
lEnd = lStart + lCount
If .Find(strToFind, lStartRow, lStartCol , i, -1, False, False, False) =
True
GetLineNumberString = lStartRow
Else
GetLineNumberString = 0
End if
End With
End Function


--
Regards,
Tom Ogilvy


"RB Smissaert" wrote in message
...
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

I corrected it in my module, didn't copy the correction to the email.

Also, in your function

lEnd = lStart + lCount


lStart should be lStartRow

forgot to change that one.

--
Regards,
Tom Ogilvy



"RB Smissaert" wrote in message
...
Tom,

Thanks for that.
This is a new one for me. Very useful to know!

The msgbox in your example should be:
MsgBox i & " " & j & " " & k


RBS


"Tom Ogilvy" wrote in message
...
Unlike most excel/vba functions, the arguments that Myrna shows are also
for
output. Not only does the function return true or false, it also sets
values to the arguments which you can read with you variable

a sample home made function to illustrate

Sub Main
Dim i, j, k

i = 1
j = 2
k = 3
i = Myfunction(j, k)
msgbox i, j, k

End Sub

Function MyFunction( s, t)
s = s * 2
t = t * 2
Myfunction = s * t
End Function

Msbox show 24 4 6

so the function returns 3 values. One the normal way and 2 through the
argument list.


so your function would be

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 lStartRow As Long
Dim lCount As Long
Dim lEnd As Long
Dim lStartCol as Long
Dim vbMod As CodeModule

Set vbMod = VBComp.CodeModule

With vbMod
lStartRow = .ProcBodyLine(strProc, vbext_pk_Proc)
lCount = .ProcCountLines(strProc, vbext_pk_Proc)
lEnd = lStart + lCount
If .Find(strToFind, lStartRow, lStartCol , i, -1, False, False, False)
=
True
GetLineNumberString = lStartRow
Else
GetLineNumberString = 0
End if
End With
End Function


--
Regards,
Tom Ogilvy


"RB Smissaert" wrote in message
...
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

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

It shouldn't have been 'new' at the time you replied to Tom <g. I'm quite
sure I pointed out in my previous reply that the line number is returned in
the startline argument. And you used that information in writing the code
below: you set the return value to the value of StartRow.

In general, a procedure can always change the value of ANY of its arguments.
However, if the procedure header line says ByVal, the caller isn't able to see
the change that the procedure made.

What happens here is that when the procedure is called the actual value of the
argument is placed on the stack.

The procedure can change it, whether it's located on the stack or in main
memory. But if it's on the stack, but once the procedure has finished, the
stack pointer is repositioned, and the caller can't see the change because it
can't access that part of the stack.

If an argument is passed ByRef, and you write ByVal x in the call statement,
or you pass a constant for the argument, VB makes a temporary copy of x, or a
temporary copy of the constant, and passes that copy ByRef. In both of these
cases, you can't see any change that the procedure makes to the argument
because the temporary copy is erased.

On Sat, 9 Oct 2004 22:47:37 +0100, "RB Smissaert"
wrote:

Tom,

Thanks for that.
This is a new one for me. Very useful to know!

The msgbox in your example should be:
MsgBox i & " " & j & " " & k


RBS


"Tom Ogilvy" wrote in message
...
Unlike most excel/vba functions, the arguments that Myrna shows are also
for
output. Not only does the function return true or false, it also sets
values to the arguments which you can read with you variable

a sample home made function to illustrate

Sub Main
Dim i, j, k

i = 1
j = 2
k = 3
i = Myfunction(j, k)
msgbox i, j, k

End Sub

Function MyFunction( s, t)
s = s * 2
t = t * 2
Myfunction = s * t
End Function

Msbox show 24 4 6

so the function returns 3 values. One the normal way and 2 through the
argument list.


so your function would be

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 lStartRow As Long
Dim lCount As Long
Dim lEnd As Long
Dim lStartCol as Long
Dim vbMod As CodeModule

Set vbMod = VBComp.CodeModule

With vbMod
lStartRow = .ProcBodyLine(strProc, vbext_pk_Proc)
lCount = .ProcCountLines(strProc, vbext_pk_Proc)
lEnd = lStart + lCount
If .Find(strToFind, lStartRow, lStartCol , i, -1, False, False, False) =
True
GetLineNumberString = lStartRow
Else
GetLineNumberString = 0
End if
End With
End Function


--
Regards,
Tom Ogilvy


"RB Smissaert" wrote in message
...
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

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

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

Myrna,

It still can work if I do it like this:
Before the code gets imported the login username and password gets stored in
cells of a sheet of the add-in.
After the code has run the main form can then reload and the connection can
be re-made with these stored
variables. It is not perfect, but these Subs being stored as text files have
a few other advantages other than reducing
the size of the add-in.
One thing I have thought of is use VBScript files to run these reports, but
I presume this can't be done as they can't use
the variables and the connection set in the add-in.
The reason I needed the line number was to pick up the report description
from the imported code.
Thanks again for the assistance with this.

RBS


"Myrna Larson" wrote in message
...
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
m...
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