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 -