avoid reset after CodeModule InsertLines

  • Thread starter Thread starter RB Smissaert
  • Start date Start date
R

RB Smissaert

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
 
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
Click to expand...

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
 
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
Click to expand...

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
 
As an after thought, you could put the code in a standard module and
call it from your userform at runtime. Reset only occurs if changes are
made to executing code. A procedure in a standard module won't be
executing until you call it *after* the rewrite.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Reset only occurs if changes are made to executing code

That is not what I see.
I have the code that alters the VBE in a normal module. Code in the userform
(modeless userform)
calls the VBE altering code. The code that is altered is in another normal
module. This other module
has no other code at all and is not called. Still, the reset happens.
It looks the only way you can do this is to put the code to be altered in
another workbook.
That works kind of OK. Module level variables and global variables are still
still there after the code is
altered and while running the altered code. But when the execution of the
altered code is finished
then those variables are reset. The other workbook helps out here, because
it is not running that userform.
I suppose one possible solution would be to run the userform from another
workbook or from an ActiveX dll
but that would be way too much work for too little gain.

RBS
 
hi - I just registered to comment on this thread.

I have a similar request, but for Access rather than Excel. Believe me it would be very useful to be able to automate code manipulation. I have got exactly the same issue. Adding any code works, but resets the project. It obviously is doable because an add-on called MZ-tools does it - but I can't find a way to change any code without the project being reset.

further to this I found the MS Article

support dot microsoft dot com/kb/198637

so it looks like we can't avoid this problem.
 
Last edited:
Back
Top