Home |
Search |
Today's Posts |
|
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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. |
#2
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 - |
#3
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 - |
#4
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 - |
#5
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 - |
#6
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 - |
#7
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 - |
#8
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
this code crashes excel. How come? | Excel Programming | |||
VBA code crashes Excel 97 - Help please! | Excel Programming | |||
AddFromString Code - Excel crashes ... Why ? | Excel Programming | |||
Simple code crashes Excel | Excel Programming | |||
Code in ThisWorkbook crashes Excel | Excel Programming |