This is in Excel 2003.
Using the Microsoft Visual Basic for Applications Extensibility Library 5.3
I am adding lines of code to a procedure.
The procedure is already there, so Sub XXX() and End Sub are there and just
adding lines to the body of the procedure.
Now I am trying to do this without causing a reset, that is without module
level and project level variables being lost and most importantly with a
running userform staying alive. I am not sure this is possible, but if you
do it manually
in the VBE it works OK, but I can't achieve this via the Extensibility
library. When the lines are added all is fine, but when the procedure ends
that adds the lines then the reset happens and the userform will disappear.
Below the code that adds the code lines after clearing any existing lines. I
haven't actually set a reference to the Extensibility library, so the VBE
objects are declared as Object.
Thanks for any advice, but my guess is that this is just not possible.

Sub InsertProcedureLines(strProject As String, _
strModule As String, _
strProcedure As String, _
arrCodeLines As Variant, _
Optional bFunction As Boolean)

Dim i As Long
Dim oProject As Object
Dim oCodeModule As Object
Dim lProcFirstLine As Long

ClearProcedureBody strProject, _
strModule, _
strProcedure, _
bFunction

lProcFirstLine = GetProcedureFirstLine(strProject, _
strModule, _
strProcedure)

Set oProject = Application.Workbooks(strProject).VBProject
Set oCodeModule = oProject.VBComponents(strModule).CodeModule

For i = 0 To UBound(arrCodeLines)
oCodeModule.InsertLines lProcFirstLine + 1 + i, arrCodeLines(i)
Next i

End Sub

Sub ClearProcedureBody(strProject As String, _
strModule As String, _
strProcedure As String, _
Optional bFunction As Boolean)

Dim i As Long
Dim oProject As Object
Dim oCodeModule As Object
Dim lProcFirstLine As Long
Dim lProcLastLine As Long

lProcFirstLine = GetProcedureFirstLine(strProject, _
strModule, _
strProcedure)

lProcLastLine = GetProcedureLastLine(strProject, _
strModule, _
strProcedure, _
bFunction)

'no lines between procedure start and end
If lProcLastLine - lProcFirstLine < 2 Then Exit Sub

Set oProject = Application.Workbooks(strProject).VBProject
Set oCodeModule = oProject.VBComponents(strModule).CodeModule

oCodeModule.DeleteLines lProcFirstLine + 1, (lProcLastLine -
lProcFirstLine) - 1

End Sub

Function GetProcedureFirstLine(strProject As String, _
strModule As String, _
strProcedure As String) As Long

Dim oProject As Object
Dim oCodeModule As Object

Set oProject = Application.Workbooks(strProject).VBProject
Set oCodeModule = oProject.VBComponents(strModule).CodeModule

GetProcedureFirstLine = oCodeModule.ProcBodyLine(strProcedure, 0)

End Function

Function GetProcedureLastLine(strProject As String, _
strModule As String, _
strProcedure As String, _
Optional bFunction As Boolean) As Long

Dim i As Long
Dim oProject As Object
Dim oCodeModule As Object
Dim lProcStartLine As Long
Dim lProcBodyStartLine As Long
Dim lProcCountLines As Long
Dim strProcEnd As String

Set oProject = Application.Workbooks(strProject).VBProject
Set oCodeModule = oProject.VBComponents(strModule).CodeModule

'includes blank lines before the procedure start
lProcStartLine = oCodeModule.ProcStartLine(strProcedure, 0)

'Line where actual Sub or Function starts
lProcBodyStartLine = oCodeModule.ProcBodyLine(strProcedure, 0)

'number of lines from lProcStartLine to end,
'including blank lines after End Sub or End Function
lProcCountLines = oCodeModule.ProcCountLines(strProcedure, 0)

If bFunction Then
strProcEnd = "End Function"
Else
strProcEnd = "End Sub"
End If

For i = lProcBodyStartLine + 1 To lProcStartLine + lProcCountLines - 1
If oCodeModule.Find(Target:=strProcEnd, _
StartLine:=i, StartColumn:=1, _
EndLine:=i, EndColumn:=255, _
WholeWord:=True, MatchCase:=True, _
PatternSearch:=False) Then
GetProcedureLastLine = i
Exit For
End If
Next i

End Function


RBS

It's pretty much a given that changes to code at runtime causes a reset
in VBA. My question to you is why do you need to modify code at
runtime? Perhaps if you more clearly explain what you're trying to
accomplish we can better offer suggestions.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

This has to do with a complex reporting application and this is to give the
user the option to
import code and run that as part of the report. One option is to add the
code to another workbook
that hasn't got public variables and hasn't got a userform running, but then
that code doesn't have
that easy access to the procedures in the main workbook.
It is not an essential thing, but it would be nice if it could be done.

RBS


"GS" wrote in message ...
It's pretty much a given that changes to code at runtime causes a reset in
VBA. My question to you is why do you need to modify code at runtime?
Perhaps if you more clearly explain what you're trying to accomplish we
can better offer suggestions.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

RB Smissaert pretended :
This has to do with a complex reporting application and this is to give the
user the option to
import code and run that as part of the report. One option is to add the code
to another workbook
that hasn't got public variables and hasn't got a userform running, but then
that code doesn't have
that easy access to the procedures in the main workbook.
It is not an essential thing, but it would be nice if it could be done.

RBS


"GS" wrote in message ...
It's pretty much a given that changes to code at runtime causes a reset in
VBA. My question to you is why do you need to modify code at runtime?
Perhaps if you more clearly explain what you're trying to accomplish we can
better offer suggestions.

-- Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion



Is there some reason why you can't include the code in the main
workbook, then use a variable to 'switch' it on based on user-selected
options?

Seems to me that if you're using code to write code then that code
already exists somewhere and so why not put it in the reporting
application and 'Call' it when needed. Where does the report display? I
assume in a worksheet -OR- a list control on the userform!

I find it strange that all possible options for an app's version
release aren't already coded for in the app. If options vary
version-to-version then maybe use a DLL to manage that?

Regardless of where the report displays, I fail to see why the report
can't be changed/updated after it's in place <IMHO.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Just believe me that this would be a useful option in this particular
situation.
I am aware there are different ways. I didn't really thing it could be done,
but
worth asking plus some code that other people may find useful.

RBS


"GS" wrote in message ...
RB Smissaert pretended :
This has to do with a complex reporting application and this is to give
the user the option to
import code and run that as part of the report. One option is to add the
code to another workbook
that hasn't got public variables and hasn't got a userform running, but
then that code doesn't have
that easy access to the procedures in the main workbook.
It is not an essential thing, but it would be nice if it could be done.

RBS


"GS" wrote in message
...
It's pretty much a given that changes to code at runtime causes a reset
in VBA. My question to you is why do you need to modify code at runtime?
Perhaps if you more clearly explain what you're trying to accomplish we
can better offer suggestions.

-- Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion



Is there some reason why you can't include the code in the main workbook,
then use a variable to 'switch' it on based on user-selected options?

Seems to me that if you're using code to write code then that code already
exists somewhere and so why not put it in the reporting application and
'Call' it when needed. Where does the report display? I assume in a
worksheet -OR- a list control on the userform!

I find it strange that all possible options for an app's version release
aren't already coded for in the app. If options vary version-to-version
then maybe use a DLL to manage that?

Regardless of where the report displays, I fail to see why the report
can't be changed/updated after it's in place <IMHO.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

RB Smissaert expressed precisely :
Just believe me that this would be a useful option in this particular
situation.
I am aware there are different ways. I didn't really thing it could be done,
but
worth asking plus some code that other people may find useful.

RBS

It would be useful in any situation, IMO, but is unfortunately not
doable the way you're going about it.

You might try using an object variable to load your form via the 'New'
keyword so it's only an instance of the original. Perhaps then you can
edit the code in the original and use it to replace the instance. Hide
all the screen activity so the user isn't aware that the instance form
was replaced.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion