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 -