I've written an XML web service to check the version of the VBA in an
Excel file against the version stored in the database. If the version
in the database is newer, the user is asked if they want to update
their file. If so, the web service returns the stored code from a
CLOB in the database and passes it to VBA as a string. After deleting
all of the lines of code in the appropriate VBA module, it writes the
new code back into the Code Module as follows:

ThisWorkbook.VBProject.VBComponents.item(sCodeName ).CodeModule.AddFromString
s

As it iterates through all of the appropriate forms, modules and
classes to update, I always get a "Microsoft Office Excel has
encountered a problem and needs to close" message...

This is working for SOME of my code. But failing consistently on
certain modules. I cannot make a distinction between the modules that
are working vs. the ones that are not.

Am I missing something? Has anyone seen anything like this? Anyone
have any ideas of what could be causing Excel to crash or want to see
more code?

Thank you in advance.

Does the code that performs the updates reside in the same module as the
code being inserted? In other words, is a module attempting to modify
itself? I would STRONGLY recommend against doing so. If you have the time,
you might consider moving the code that performs the updates out of the
VBProject and into another project, so that no part of a project is
attempting to modify any (other) part of the same project. A project should
only update another project, not itself.


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)





wrote in message
...
I've written an XML web service to check the version of the VBA in an
Excel file against the version stored in the database. If the version
in the database is newer, the user is asked if they want to update
their file. If so, the web service returns the stored code from a
CLOB in the database and passes it to VBA as a string. After deleting
all of the lines of code in the appropriate VBA module, it writes the
new code back into the Code Module as follows:

ThisWorkbook.VBProject.VBComponents.item(sCodeName ).CodeModule.AddFromString
s

As it iterates through all of the appropriate forms, modules and
classes to update, I always get a "Microsoft Office Excel has
encountered a problem and needs to close" message...

This is working for SOME of my code. But failing consistently on
certain modules. I cannot make a distinction between the modules that
are working vs. the ones that are not.

Am I missing something? Has anyone seen anything like this? Anyone
have any ideas of what could be causing Excel to crash or want to see
more code?

Thank you in advance.

The code that is performing the update does not reside in the same
module, but is part of the same VB project. This is really kind of
the intent as I essentially want users to be working with the latest
code without having to go somewhere else to get the update. I suppose
I could make the Update procedure an add-in but that would then
require my users to install something else and it defeats the purpose
to some extent.

As it's working right now, the code that is running the update is
completely untouched by the update itself. It's iterating through a
fixed list of modules, deleting all the lines in the CodeModule, then
adding the code back into this set list of modules via a string
returned from the XML web service. Again, this is working for some of
my modules and Excel is crashing on others. If I remove the modules
that cause Excel to crash, the process works. Would Excel crash
because there are references to classes or types I have defined or
could whitespace cause it? Would Excel crash because the code cannot
be compiled until all of the code is added back in? Speaking of
which, is there a way recompile the code once it's added back in?
Would Excel crash because there are cells in the workbook that are
calling functions from VBA that are deleted and then added back in?

Thank you for your help.

Matt

On May 19, 4:03*pm, "Chip Pearson" wrote:
Does the code that performs the updates reside in the same module as the
code being inserted? *In other words, is a module attempting to modify
itself? *I would STRONGLY recommend against doing so. *If you have the time,
you might consider moving the code that performs the updates out of the
VBProject and into another project, so that no part of a project is
attempting to modify any (other) part of the same project. A project should
only update another project, not itself.

--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
* * Excel Product Group
Pearson Software Consulting, LLCwww.cpearson.com
(email on web site)

wrote in message

...



I've written an XML web service to check the version of the VBA in an
Excel file against the version stored in the database. *If the version
in the database is newer, the user is asked if they want to update
their file. *If so, the web service returns the stored code from a
CLOB in the database and passes it to VBA as a string. *After deleting
all of the lines of code in the appropriate VBA module, it writes the
new code back into the Code Module as follows:

ThisWorkbook.VBProject.VBComponents.item(sCodeName ).CodeModule.AddFromStrin*g
s

As it iterates through all of the appropriate forms, modules and
classes to update, I always get a "Microsoft Office Excel has
encountered a problem and needs to close" message...

This is working for SOME of my code. *But failing consistently on
certain modules. *I cannot make a distinction between the modules that
are working vs. the ones that are not.

Am I missing something? *Has anyone seen anything like this? *Anyone
have any ideas of what could be causing Excel to crash or want to see
more code?

Thank you in advance.- Hide quoted text -

- Show quoted text -

Comments inline -

"Matt" wrote in message

The code that is performing the update does not reside in the same
module, but is part of the same VB project. This is really kind of
the intent as I essentially want users to be working with the latest
code without having to go somewhere else to get the update.

I'm sure there's a reason but how would an 'old' project modify itself with
the latest code. IOW where would it get the code from, presumably some other
source so why can't 'some other source' be a new project that updates the
old project.

I suppose
I could make the Update procedure an add-in but that would then
require my users to install something else and it defeats the purpose
to some extent.

You could have code in your old project that runs manually (button click) or
automatically (every x weeks) to get the update and triggers it to do its
thing.

As it's working right now, the code that is running the update is
completely untouched by the update itself. It's iterating through a
fixed list of modules, deleting all the lines in the CodeModule, then
adding the code back into this set list of modules via a string
returned from the XML web service. Again, this is working for some of
my modules and Excel is crashing on others. If I remove the modules
that cause Excel to crash, the process works.

Curiosity, is it writing to ordinary modules or code behind object modules
that causes your crash.

Would Excel crash
because there are references to classes or types I have defined or
could whitespace cause it?

Not sure but it would be worthwhile clearing any references in advance.

Would Excel crash because the code cannot
be compiled until all of the code is added back in? Speaking of
which, is there a way recompile the code once it's added back in?

Best avoid that altogether, indeed it might be your project compiling
inadvertantly while the code is running that is causing your Excel to crash.

If you wanted to re-compile some other project there is a cludgy way but
there's probably no need to recompile. As user runs code from different
modules those modules will compile, if it's an xls it willl get saved in its
latest compile state.

FWIW, programatically adding ActiveX worksheet controls in the same workbook
that's running the code will very likely crash Excel.

Would Excel crash because there are cells in the workbook that are
calling functions from VBA that are deleted and then added back in?

Probably not. If you are forcing a calculation during an update, which I
don't suppose your are, the worse that's likely to happen is a #NAME? error
in cells. Unless perhaps the function exists but is doing something
internally that fails during the update.

I think it would be well worth your while to pay head to the advice that
Chip Pearson gave you.

Regards,
Peter T


On May 19, 4:03 pm, "Chip Pearson" wrote:
Does the code that performs the updates reside in the same module as the
code being inserted? In other words, is a module attempting to modify
itself? I would STRONGLY recommend against doing so. If you have the time,
you might consider moving the code that performs the updates out of the
VBProject and into another project, so that no part of a project is
attempting to modify any (other) part of the same project. A project
should
only update another project, not itself.

--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLCwww.cpearson.com
(email on web site)

wrote in message

...



I've written an XML web service to check the version of the VBA in an
Excel file against the version stored in the database. If the version
in the database is newer, the user is asked if they want to update
their file. If so, the web service returns the stored code from a
CLOB in the database and passes it to VBA as a string. After deleting
all of the lines of code in the appropriate VBA module, it writes the
new code back into the Code Module as follows:


ThisWorkbook.VBProject.VBComponents.item(sCodeName ).CodeModule.AddFromStrin*
g
s

As it iterates through all of the appropriate forms, modules and
classes to update, I always get a "Microsoft Office Excel has
encountered a problem and needs to close" message...

This is working for SOME of my code. But failing consistently on
certain modules. I cannot make a distinction between the modules that
are working vs. the ones that are not.

Am I missing something? Has anyone seen anything like this? Anyone
have any ideas of what could be causing Excel to crash or want to see
more code?

Thank you in advance.- Hide quoted text -

- Show quoted text -

As far as I'm concerned, no good can come of programmatic manipulation of
code. One unmentioned issue is that if the modules are particularly large (
64k or so) they may become unstable.

You would do well to move the code into an add-in, and introduce a protocol
that replaces the old add-in by a newer one. Jan Karel Pieterse has some how
to examples on his web site (http://jkp-ads.com) that show how to get
started using add-ins and how to update them programmatically.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Peter T" <peter_t@discussions wrote in message
...
Comments inline -

"Matt" wrote in message

The code that is performing the update does not reside in the same
module, but is part of the same VB project. This is really kind of
the intent as I essentially want users to be working with the latest
code without having to go somewhere else to get the update.

I'm sure there's a reason but how would an 'old' project modify itself
with
the latest code. IOW where would it get the code from, presumably some
other
source so why can't 'some other source' be a new project that updates the
old project.

I suppose
I could make the Update procedure an add-in but that would then
require my users to install something else and it defeats the purpose
to some extent.

You could have code in your old project that runs manually (button click)
or
automatically (every x weeks) to get the update and triggers it to do its
thing.

As it's working right now, the code that is running the update is
completely untouched by the update itself. It's iterating through a
fixed list of modules, deleting all the lines in the CodeModule, then
adding the code back into this set list of modules via a string
returned from the XML web service. Again, this is working for some of
my modules and Excel is crashing on others. If I remove the modules
that cause Excel to crash, the process works.

Curiosity, is it writing to ordinary modules or code behind object modules
that causes your crash.

Would Excel crash
because there are references to classes or types I have defined or
could whitespace cause it?

Not sure but it would be worthwhile clearing any references in advance.

Would Excel crash because the code cannot
be compiled until all of the code is added back in? Speaking of
which, is there a way recompile the code once it's added back in?

Best avoid that altogether, indeed it might be your project compiling
inadvertantly while the code is running that is causing your Excel to
crash.

If you wanted to re-compile some other project there is a cludgy way but
there's probably no need to recompile. As user runs code from different
modules those modules will compile, if it's an xls it willl get saved in
its
latest compile state.

FWIW, programatically adding ActiveX worksheet controls in the same
workbook
that's running the code will very likely crash Excel.

Would Excel crash because there are cells in the workbook that are
calling functions from VBA that are deleted and then added back in?

Probably not. If you are forcing a calculation during an update, which I
don't suppose your are, the worse that's likely to happen is a #NAME?
error
in cells. Unless perhaps the function exists but is doing something
internally that fails during the update.

I think it would be well worth your while to pay head to the advice that
Chip Pearson gave you.

Regards,
Peter T


On May 19, 4:03 pm, "Chip Pearson" wrote:
Does the code that performs the updates reside in the same module as the
code being inserted? In other words, is a module attempting to modify
itself? I would STRONGLY recommend against doing so. If you have the
time,
you might consider moving the code that performs the updates out of the
VBProject and into another project, so that no part of a project is
attempting to modify any (other) part of the same project. A project
should
only update another project, not itself.

--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLCwww.cpearson.com
(email on web site)

wrote in message

...



I've written an XML web service to check the version of the VBA in an
Excel file against the version stored in the database. If the version
in the database is newer, the user is asked if they want to update
their file. If so, the web service returns the stored code from a
CLOB in the database and passes it to VBA as a string. After deleting
all of the lines of code in the appropriate VBA module, it writes the
new code back into the Code Module as follows:


ThisWorkbook.VBProject.VBComponents.item(sCodeName ).CodeModule.AddFromStrin*
g
s

As it iterates through all of the appropriate forms, modules and
classes to update, I always get a "Microsoft Office Excel has
encountered a problem and needs to close" message...

This is working for SOME of my code. But failing consistently on
certain modules. I cannot make a distinction between the modules that
are working vs. the ones that are not.

Am I missing something? Has anyone seen anything like this? Anyone
have any ideas of what could be causing Excel to crash or want to see
more code?

Thank you in advance.- Hide quoted text -

- Show quoted text -

First, thank you for your input.

As you suggested, I took Chip's advice and moved the entire update
process to its own Add-In. So now it's in its own VB Project,
updating the original. It's still crashing in exactly the same place
when updating the same module. Very frustrating.

My update process updates code behind the forms, modules and class
modules. The problem, surprisingly enough, happens when updating a
particular module.

I seem to be getting the feedback from the Microsoft MVPs that
updating code is a bad idea... but if that's the case, why are these
functions available in Excel? Why is there CodeModule.AddFromString
if it's so unstable that Microsoft is recommending against using it?

The concept here is to proactively update my users' functionality in
an Excel workbook that they have customized on their own. Their
changes will be to the worksheets. My changes are to the underlying
code. I don't want to lose their changes (which, of course, differ by
user), but I do want to push my changes into their files. What's most
frustrating here is that this is working at a 95% level... Some
modules are updating perfectly. And the ones that are not, actually
cause Excel to crash. There HAS to be a reason that this is so
unstable with certain modules since it crashes on the same ones every
time... but I don't see what it is.

Any other ideas?


On May 20, 4:51*pm, "Peter T" <peter_t@discussions wrote:
Comments inline -

"Matt" wrote in message
*The code that is performing the update does not reside in the same
*module, but is part of the same VB project. *This is really kind of
*the intent as I essentially want users to be working with the latest
*code without having to go somewhere else to get the update.

I'm sure there's a reason but how would an 'old' project modify itself with
the latest code. IOW where would it get the code from, presumably some other
source so why can't 'some other source' be a new project that updates the
old project.

*I suppose
*I could make the Update procedure an add-in but that would then
*require my users to install something else and it defeats the purpose
*to some extent.

You could have code in your old project that runs manually (button click) or
automatically (every x weeks) to get the update and triggers it to do its
thing.

*As it's working right now, the code that is running the update is
*completely untouched by the update itself. *It's iterating through a
*fixed list of modules, deleting all the lines in the CodeModule, then
*adding the code back into this set list of modules via a string
*returned from the XML web service. *Again, this is working for some of
*my modules and Excel is crashing on others. *If I remove the modules
*that cause Excel to crash, the process works.

Curiosity, is it writing to ordinary modules or code behind object modules
that causes your crash.

*Would Excel crash
*because there are references to classes or types I have defined or
*could whitespace cause it?

Not sure but it would be worthwhile clearing any references in advance.

*Would Excel crash because the code cannot
*be compiled until all of the code is added back in? *Speaking of
*which, is there a way recompile the code once it's added back in?

Best avoid that altogether, indeed it might be your project compiling
inadvertantly while the code is running that is causing your Excel to crash.

If you wanted to re-compile some other project there is a cludgy way but
there's probably no need to recompile. As user runs code from different
modules those modules will compile, if it's an xls it willl get saved in its
latest compile state.

FWIW, programatically adding ActiveX worksheet controls in the same workbook
that's running the code will very likely crash Excel.

*Would Excel crash because there are cells in the workbook that are
*calling functions from VBA that are deleted and then added back in?

Probably not. If you are forcing a calculation during an update, which I
don't suppose your are, the worse that's likely to happen is a #NAME? error
in cells. Unless perhaps the function exists but is doing something
internally that fails during the update.

I think it would be well worth your while to pay head to the advice that
Chip Pearson gave you.

Regards,
Peter T

On May 19, 4:03 pm, "Chip Pearson" wrote:



Does the code that performs the updates reside in the same module as the
code being inserted? In other words, is a module attempting to modify
itself? I would STRONGLY recommend against doing so. If you have the time,
you might consider moving the code that performs the updates out of the
VBProject and into another project, so that no part of a project is
attempting to modify any (other) part of the same project. A project
should
only update another project, not itself.

--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLCwww.cpearson.com
(email on web site)

wrote in message

...

I've written an XML web service to check the version of the VBA in an
Excel file against the version stored in the database. If the version
in the database is newer, the user is asked if they want to update
their file. If so, the web service returns the stored code from a
CLOB in the database and passes it to VBA as a string. After deleting
all of the lines of code in the appropriate VBA module, it writes the
new code back into the Code Module as follows:

ThisWorkbook.VBProject.VBComponents.item(sCodeName ).CodeModule.AddFromStrin**
g



s

As it iterates through all of the appropriate forms, modules and
classes to update, I always get a "Microsoft Office Excel has
encountered a problem and needs to close" message...

This is working for SOME of my code. But failing consistently on
certain modules. I cannot make a distinction between the modules that
are working vs. the ones that are not.

Am I missing something? Has anyone seen anything like this? Anyone
have any ideas of what could be causing Excel to crash or want to see
more code?

Thank you in advance.- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

I seem to be getting the feedback from the Microsoft MVPs that
updating code is a bad idea...

Indeed if code is updating self.

but if that's the case, why are these
functions available in Excel?

Some things might work fine writing code to "self" but I assume the methods
are primarily available for one project to update another.

...I moved the entire update
process to its own Add-In. So now it's in its own VB Project,
updating the original. It's still crashing in exactly the same place
when updating the same module.

Where is the code to start the update initiated from. If you have a small
function in the project tb updated that calls the dedicated update project,
in effect it's still updating itself and you have done nothing to solve the
problem.

If that's not the case and the update project is called independently,
hopefully there's something in particular that can be linked to the crash
and rectified. If the new code in the offending module is not too much post
as is, otherwise post anything vaguely suspicious.

Regards,
Peter T


"Matt" wrote in message
...
First, thank you for your input.

As you suggested, I took Chip's advice and moved the entire update
process to its own Add-In. So now it's in its own VB Project,
updating the original. It's still crashing in exactly the same place
when updating the same module. Very frustrating.

My update process updates code behind the forms, modules and class
modules. The problem, surprisingly enough, happens when updating a
particular module.

I seem to be getting the feedback from the Microsoft MVPs that
updating code is a bad idea... but if that's the case, why are these
functions available in Excel? Why is there CodeModule.AddFromString
if it's so unstable that Microsoft is recommending against using it?

The concept here is to proactively update my users' functionality in
an Excel workbook that they have customized on their own. Their
changes will be to the worksheets. My changes are to the underlying
code. I don't want to lose their changes (which, of course, differ by
user), but I do want to push my changes into their files. What's most
frustrating here is that this is working at a 95% level... Some
modules are updating perfectly. And the ones that are not, actually
cause Excel to crash. There HAS to be a reason that this is so
unstable with certain modules since it crashes on the same ones every
time... but I don't see what it is.

Any other ideas?


On May 20, 4:51 pm, "Peter T" <peter_t@discussions wrote:
Comments inline -

"Matt" wrote in message
The code that is performing the update does not reside in the same
module, but is part of the same VB project. This is really kind of
the intent as I essentially want users to be working with the latest
code without having to go somewhere else to get the update.

I'm sure there's a reason but how would an 'old' project modify itself
with
the latest code. IOW where would it get the code from, presumably some
other
source so why can't 'some other source' be a new project that updates the
old project.

I suppose
I could make the Update procedure an add-in but that would then
require my users to install something else and it defeats the purpose
to some extent.

You could have code in your old project that runs manually (button click)
or
automatically (every x weeks) to get the update and triggers it to do its
thing.

As it's working right now, the code that is running the update is
completely untouched by the update itself. It's iterating through a
fixed list of modules, deleting all the lines in the CodeModule, then
adding the code back into this set list of modules via a string
returned from the XML web service. Again, this is working for some of
my modules and Excel is crashing on others. If I remove the modules
that cause Excel to crash, the process works.

Curiosity, is it writing to ordinary modules or code behind object modules
that causes your crash.

Would Excel crash
because there are references to classes or types I have defined or
could whitespace cause it?

Not sure but it would be worthwhile clearing any references in advance.

Would Excel crash because the code cannot
be compiled until all of the code is added back in? Speaking of
which, is there a way recompile the code once it's added back in?

Best avoid that altogether, indeed it might be your project compiling
inadvertantly while the code is running that is causing your Excel to
crash.

If you wanted to re-compile some other project there is a cludgy way but
there's probably no need to recompile. As user runs code from different
modules those modules will compile, if it's an xls it willl get saved in
its
latest compile state.

FWIW, programatically adding ActiveX worksheet controls in the same
workbook
that's running the code will very likely crash Excel.

Would Excel crash because there are cells in the workbook that are
calling functions from VBA that are deleted and then added back in?

Probably not. If you are forcing a calculation during an update, which I
don't suppose your are, the worse that's likely to happen is a #NAME?
error
in cells. Unless perhaps the function exists but is doing something
internally that fails during the update.

I think it would be well worth your while to pay head to the advice that
Chip Pearson gave you.

Regards,
Peter T

On May 19, 4:03 pm, "Chip Pearson" wrote:



Does the code that performs the updates reside in the same module as the
code being inserted? In other words, is a module attempting to modify
itself? I would STRONGLY recommend against doing so. If you have the
time,
you might consider moving the code that performs the updates out of the
VBProject and into another project, so that no part of a project is
attempting to modify any (other) part of the same project. A project
should
only update another project, not itself.

--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLCwww.cpearson.com
(email on web site)

wrote in message

...

I've written an XML web service to check the version of the VBA in an
Excel file against the version stored in the database. If the version
in the database is newer, the user is asked if they want to update
their file. If so, the web service returns the stored code from a
CLOB in the database and passes it to VBA as a string. After deleting
all of the lines of code in the appropriate VBA module, it writes the
new code back into the Code Module as follows:


ThisWorkbook.VBProject.VBComponents.item(sCodeName ).CodeModule.AddFromStrin*
*
g



s

As it iterates through all of the appropriate forms, modules and
classes to update, I always get a "Microsoft Office Excel has
encountered a problem and needs to close" message...

This is working for SOME of my code. But failing consistently on
certain modules. I cannot make a distinction between the modules that
are working vs. the ones that are not.

Am I missing something? Has anyone seen anything like this? Anyone
have any ideas of what could be causing Excel to crash or want to see
more code?

Thank you in advance.- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

I was thinking along the same lines as far as where the call to update
the code initiated. And to ensure its independence, I created a menu
item that calls the Updater add-in when clicked. The Add-In is given
a reference to the workbook to updated when the workbook to update is
opened. So... the code that is being executed is not contained within
the updating workbook at all. Here's my updating code:

Menu calls:
Private Sub menCheckForUpdate()
If Not frmPostCode.gw_WorkbookToUpdateWorkbook Is Nothing Then
CheckCodeVersion frmPostCode.gw_WorkbookToUpdateWorkbook,
frmPostCode.gs_WorkbookToUpdateCODEVERSION
Else
MsgBox "WorkbookToUpdate file is not open."
End If
End Sub


Public Function CheckCodeVersion(ByRef WorkbookToUpdateWorkbook As
Workbook, ByVal s_WorkbookToUpdateCodeVersion As String) As Boolean
Dim sRemVersion As String
sRemVersion = WorkbookToUpdateAudit.wsm_GetLatestCodeVersion
If IsRemoteVersionLater(s_WorkbookToUpdateCodeVersion ,
sRemVersion) Then
'prompt user to update code
Dim sMsg As String, iBuild As Integer, sBuildDesc As String,
bUpdateable As Boolean
bUpdateable = True
sMsg = "An update to WorkbookToUpdate is available. This
update includes the following:" + vbCrLf + vbCrLf
If MajorVersionNumber(sRemVersion) =
MajorVersionNumber(s_WorkbookToUpdateCodeVersion) Then
For iBuild = BuildNumber(s_WorkbookToUpdateCodeVersion) +
1 To BuildNumber(sRemVersion)
sBuildDesc =
WorkbookToUpdateAudit.wsm_GetCodeDescription(Major VersionNumber(sRemVersion)
+ "." & iBuild)
If sBuildDesc < "" Then
sMsg = sMsg + vbCrLf + sBuildDesc
End If
bUpdateable = bUpdateable And
WorkbookToUpdateAudit.wsm_GetUpdateable(MajorVersi onNumber(sRemVersion)
+ "." & iBuild)
Next iBuild
Else
sMsg = "An update to WorkbookToUpdate is available. This
includes a major release to WorkbookToUpdate. Please see Internet for
a description of the changes." + vbCrLf + vbCrLf
bUpdateable = False
End If

If bUpdateable Then
sMsg = sMsg + vbCrLf + vbCrLf + "Would you like to update
your WorkbookToUpdate with these changes automatically?"

Dim iResp As Integer
iResp = MsgBox(sMsg, vbYesNo + vbInformation, "Update
WorkbookToUpdate?")
If iResp = vbYes Then
UnprotectVBProject WorkbookToUpdateWorkbook,
"T0w3rs456"
GetAllCode WorkbookToUpdateWorkbook, sRemVersion
MsgBox "Done updating."
End If
Else
sMsg = sMsg + "Your WorkbookToUpdate cannot be updated
automatically. If you would like to incorporate all of the functional
changes in the latest version of WorkbookToUpdate, please download it
from Internet and rebuild your WorkbookToUpdate."
MsgBox sMsg, vbInformation + vbOKOnly, "WorkbookToUpdate
update available on Internet."
End If
Else
CheckCodeVersion = True
End If
'MsgBox "Done checking version"
End Function

Public Sub GetAllCode(ByRef WorkbookToUpdateWorkbook As Workbook,
ByVal sCodeVersion As String)
Application.Cursor = xlWait
'Dim xlCalcMode As XlCalculation
'xlCalcMode = Application.Calculation
'Application.Calculation = xlCalculationManual

Dim l As Long
For l = 1 To pSC_Modules.ModuleCount
DeleteCode WorkbookToUpdateWorkbook, pSC_Modules.ModuleName(l)
AddCode WorkbookToUpdateWorkbook, sCodeVersion,
pSC_Modules.ModuleName(l)
DoEvents
Next l

'MsgBox "Done getting code. Turn calc mode back to what it was."

'Application.Calculation = xlCalcMode
Application.Cursor = xlDefault
End Sub

Private Sub DeleteCode(ByRef WorkbookToUpdateWorkbook As Workbook,
ByVal sCodeName As String)
Application.StatusBar = "Deleting " + sCodeName + "..."

WorkbookToUpdateWorkbook.VBProject.VBComponents.It em(sCodeName).CodeModule.DeleteLines
1,
WorkbookToUpdateWorkbook.VBProject.VBComponents.It em(sCodeName).CodeModule.CountOfLines
Application.StatusBar = ""
End Sub


Private Sub AddCode(ByRef WorkbookToUpdateWorkbook As Workbook, ByVal
sCodeVersion As String, ByVal sCodeName As String)
Application.StatusBar = "Adding " + sCodeName + "..."
'MsgBox "Adding " + sCodeName + "..."
Dim s As String
s = WorkbookToUpdateAudit.wsm_GetCode(sCodeVersion, sCodeName)

WorkbookToUpdateWorkbook.VBProject.VBComponents.It em(sCodeName).CodeModule.AddFromString
s
Application.StatusBar = ""
End Sub


I will show the code from the module that is failing in a separate
post.





On May 22, 4:56*am, "Peter T" <peter_t@discussions wrote:
I seem to be getting the feedback from the Microsoft MVPs that
updating code is a bad idea...

Indeed if code is updating self.

but if that's the case, why are these
functions available in Excel?

Some things might work fine writing code to "self" but I assume the methods
are primarily available for one project to update another.

...I *moved the entire update
process to its own Add-In. *So now it's in its own VB Project,
updating the original. *It's still crashing in exactly the same place
when updating the same module.

Where is the code to start the update initiated from. If you have a small
function in the project tb updated that calls the dedicated update project,
in effect it's still updating itself and you have done nothing to solve the
problem.

If that's not the case and the update project is called independently,
hopefully there's something in particular that can be linked to the crash
and rectified. If the new code in the offending module is not too much post
as is, otherwise post anything vaguely suspicious.

Regards,
Peter T

"Matt" wrote in message

...
First, thank you for your input.

As you suggested, I took Chip's advice and moved the entire update
process to its own Add-In. *So now it's in its own VB Project,
updating the original. *It's still crashing in exactly the same place
when updating the same module. *Very frustrating.

My update process updates code behind the forms, modules and class
modules. *The problem, surprisingly enough, happens when updating a
particular module.

I seem to be getting the feedback from the Microsoft MVPs that
updating code is a bad idea... but if that's the case, why are these
functions available in Excel? *Why is there CodeModule.AddFromString
if it's so unstable that Microsoft is recommending against using it?

The concept here is to proactively update my users' functionality in
an Excel workbook that they have customized on their own. *Their
changes will be to the worksheets. *My changes are to the underlying
code. *I don't want to lose their changes (which, of course, differ by
user), but I do want to push my changes into their files. *What's most
frustrating here is that this is working at a 95% level... *Some
modules are updating perfectly. *And the ones that are not, actually
cause Excel to crash. *There HAS to be a reason that this is so
unstable with certain modules since it crashes on the same ones every
time... but I don't see what it is.

Any other ideas?

On May 20, 4:51 pm, "Peter T" <peter_t@discussions wrote:



Comments inline -

"Matt" wrote in message
The code that is performing the update does not reside in the same
module, but is part of the same VB project. This is really kind of
the intent as I essentially want users to be working with the latest
code without having to go somewhere else to get the update.

I'm sure there's a reason but how would an 'old' project modify itself
with
the latest code. IOW where would it get the code from, presumably some
other
source so why can't 'some other source' be a new project that updates the
old project.

I suppose
I could make the Update procedure an add-in but that would then
require my users to install something else and it defeats the purpose
to some extent.

You could have code in your old project that runs manually (button click)
or
automatically (every x weeks) to get the update and triggers it to do its
thing.

As it's working right now, the code that is running the update is
completely untouched by the update itself. It's iterating through a
fixed list of modules, deleting all the lines in the CodeModule, then
adding the code back into this set list of modules via a string
returned from the XML web service. Again, this is working for some of
my modules and Excel is crashing on others. If I remove the modules
that cause Excel to crash, the process works.

Curiosity, is it writing to ordinary modules or code behind object modules
that causes your crash.

Would Excel crash
because there are references to classes or types I have defined or
could whitespace cause it?

Not sure but it would be worthwhile clearing any references in advance.

Would Excel crash because the code cannot
be compiled until all of the code is added back in? Speaking of
which, is there a way recompile the code once it's added back in?

Best avoid that altogether, indeed it might be your project compiling
inadvertantly while the code is running that is causing your Excel to
crash.

If you wanted to re-compile some other project there is a cludgy way but
there's probably no need to recompile. As user runs code from different
modules those modules will compile, if it's an xls it willl get saved in
its
latest compile state.

FWIW, programatically adding ActiveX worksheet controls in the same
workbook
that's running the code will very likely crash Excel.

Would Excel crash because there are cells in the workbook that are
calling functions from VBA that are deleted and then added back in?

Probably not. If you are forcing a calculation during an update, which I
don't suppose your are, the worse that's likely to happen is a #NAME?
error
in cells. Unless perhaps the function exists but is doing something
internally that fails during the update.

I think it would be well worth your while to pay head to the advice that
Chip Pearson gave you.

Regards,
Peter T

On May 19, 4:03 pm, "Chip Pearson" wrote:

Does the code that performs the updates reside in the same module as the
code being inserted? In other words, is a module attempting to modify
itself? I would STRONGLY recommend against doing so. If you have the
time,
you might consider moving the code that performs the updates out of the
VBProject and into another project, so that no part of a project is
attempting to modify any (other) part of the same project. A project
should
only update another project, not itself.

--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLCwww.cpearson.com
(email on web site)

wrote in message

....

I've written an XML web service to check the version of the VBA in an
Excel file against the version stored in the database. If the version
in the database is newer, the user is asked if they want to update
their file. If so, the web service returns the stored code from a
CLOB in the database and passes it to VBA as a string. After deleting
all of the lines of code in the appropriate VBA module, it writes the
new code back into the Code Module as follows:

ThisWorkbook.VBProject.VBComponents.item(sCodeName ).CodeModule.AddFromStrin**
*



g

s

As it iterates through all of the appropriate forms, modules and
classes to update, I always get a "Microsoft Office Excel has
encountered a problem and needs to close" message...

This is working for SOME of my code. But failing consistently on
certain modules. I cannot make a distinction between the modules that
are working vs. the ones that are not.

Am I missing something? Has anyone seen anything like this? Anyone
have any ideas of what could be causing Excel to crash or want to see
more code?

Thank you in advance.- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

OK so you are sure the project t.b. updated is not calling the code in
another project to do the update. Are you really sure, reason for asking is
I'm surprised it was not already 100% known for sure without having to go
and check, as it appears you had to do.

I'm afraid scan reading the code below does not give any clues. The updated
code in the adjacent post is 8-900 lines many of which are wrapped. It would
take a long time to reconstruct. And probably much longer to get a feel for
what it all does (out of context)

Some things to try -

Paste what you think is the correct code into the module in the project t.b.
updated.

Update a module in a dummy project (just the suspect module), using code to
do the update, is the new code as it should be.

Working with the actual project t.b. updated, put a break in the code after
it has done the 95% that works fine, then step through the rest with F8.

If the update code is "totally in a separate project, and not called from
the project t.b. updated, I sort of suspect the problem will relate to
something obvious. Just need to see it in the wood amongst the trees.

Regards,
Peter T


"Matt" wrote in message
...
I was thinking along the same lines as far as where the call to update
the code initiated. And to ensure its independence, I created a menu
item that calls the Updater add-in when clicked. The Add-In is given
a reference to the workbook to updated when the workbook to update is
opened. So... the code that is being executed is not contained within
the updating workbook at all. Here's my updating code:
<snip

Despite *knowing better* I also did a very similar thing to allow many users to easily receive code updates (In fact Chip Pearson I’d like to thank you for your well organized website, with a few wrappers, I used a lot of your vba module copy, delete code to get the job done). In my case it works great, but I did note a few issues and can point out an annoyance or two that may be related to your problems. Specifically your issue may be related to your use of class modules since (argh!) the class module name IS the class name (but in your post you don’t mention compile errors occur so it may not be the issue noted below). Here is what I did and issues I found …

So as mentioned in the thread, there are two obvious choices – push code out to users workbooks or allow them to suck it in – a.k.a. pull (again, like you, I chose the latter, but generally as you’ve heard, the former is recommended). What I wanted was to keep the source in virtually a single workbook <fooVbaSrc.xls (with revision history) and have the users workbook (upon Auto_open) look for this book, and if available, check the version control sheet comparing to the current const version_no., if newer optionally allow an upgrade. [BTW - What I did not like about the push model, was that while I could have, upon update selection, run the source wb and shut down the user wb allowing the upgrade to occur via a push, if more than a single user wb was in the directory (which was the case), I’d somehow have to pass the target to the source wb or allow all wb’s to be updated]. Upon selecting the update I chose to ‘scrub’ the workbook of all modules and copy all modules from the src book to the user target (I ‘scrub’ and copy with out regard to module names that way the set of updated module names does known or coded a priori – allowing any and all code to be modified). I found two primary issues.

Firstly, as you scrub the workbook of current code modules they won’t actually be deleted until the book is closed and even then the user is prompted by excel as it exits to ‘Save the changes’ - at which point anything other than ‘Yes’ will leave the ‘deleted modules’ (excel probably needs extra flags to indicate the delete from the scrub was already pending and should complete regardless of user input as the book closes – but I wouldn’t hold my breath for that:-). Also, as the updated modules are copied across, excel renames them from <foo to <foo1 to prevent a module name collision (actually I noted that it will properly and immediately delete modules that have not been used thus far - this may answer your question as to why some and not all modules cause you grief). For me this renaming is not an issue for standard modules. With this mechanism, if the user selects yes to the save msgbox upon exit, the wb is properly updated, if the user selects ‘no’ to the excel closure prompt, then the wb is reverted back to the previous rev (i.e. the foo1 modules are not saved – unless you did so in vba then you are in real trouble as both sets of modules will be preserved (<foo and <foo1 each with the same functions and subroutine names) and vba will throw a ambiguous module compiler exception). BTW when things go right the next time <foo is copied to the user book it works fine since there is no module name collision with <foo1 (so the workbook module names oscillate between <foo and ,<foo1 on each subsequent update). [so you can really see the Excel is straining to tell you not to do the pull method – but we press on...]

Related to the first issue is the second issue I encountered. This occurred when using MS Excels semi-useful classes (no inheritance, no polymorphism and no c’tor/d’tor make them essentially data encapsulation object of marginal organizational use). The problem here was that the module name IS the class name. So, noting issue #1 above when the class name changes to <foo1 the code will hit a vba compile error (the object of class foo can not be instantiated). *Luckily* as noted above the class module feature is so weak, I did not really mind switching back and avoiding it's use.

Hope this helps,

Regards,
Paul Schmitt