Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Extensibility library and dynamic code compilation
I have inherited a VB6 application that runs unattended and creates an Excel
2002 spreadsheet dynamically using the Excel library. The VB6 code then uses the VBA Extensibility library to access the created worksheet and then adds a "worksheet_calculate" event using mycodemodule.CreateEventProc("Calculate", "Worksheet") This "worksheet_calculate" event code is then populated by the VB6 code writing some VBA using mycodemodule.InsertLines lngTargetLine, strHeaderLine1 This works ok and we see that the VBA code works properly, but when the worksheet is opened by a user we find that we cannot change any of the data in the worksheet with calculation set to automatic - editing any cell will repeatably cause Excel to crash out. To change the worksheet values we either (a) switch calculation to manual, change the sheet and then put calculation back to automatic OR (b) we enter VBA and compile the worksheet using "Tools""Compile VBA Project". If we change the worksheet without making one of these changes then we find that Excel just crashes out. This appears to be an Excel bug. So, if we could find a "proper" way to get the VBA in the worksheet_calculate to be compiled we would not face this problem. As you will know, the VBE library has no inbuilt method to compile VBA (MakeCompiledFile and BuildFileName are only for VB6 code to create a .dll rather than compile VBA) and I am not happy about trying to implement a nasty send keys method. Has anyone encountered this and worked out a way to do this that does not use sendkeys??? Cheers, John -- www.alignment-systems.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Extensibility library and dynamic code compilation
John,
I've never had luck with code changing code - Excel often becomes unstable. My solution is to set up a workbook template with all the event code required, then copy it whenever I need it. Unless you are creating custom code dynamically, it may work for you as well.... HTH, Bernie MS Excel MVP "John.Greenan" wrote in message ... I have inherited a VB6 application that runs unattended and creates an Excel 2002 spreadsheet dynamically using the Excel library. The VB6 code then uses the VBA Extensibility library to access the created worksheet and then adds a "worksheet_calculate" event using mycodemodule.CreateEventProc("Calculate", "Worksheet") This "worksheet_calculate" event code is then populated by the VB6 code writing some VBA using mycodemodule.InsertLines lngTargetLine, strHeaderLine1 This works ok and we see that the VBA code works properly, but when the worksheet is opened by a user we find that we cannot change any of the data in the worksheet with calculation set to automatic - editing any cell will repeatably cause Excel to crash out. To change the worksheet values we either (a) switch calculation to manual, change the sheet and then put calculation back to automatic OR (b) we enter VBA and compile the worksheet using "Tools""Compile VBA Project". If we change the worksheet without making one of these changes then we find that Excel just crashes out. This appears to be an Excel bug. So, if we could find a "proper" way to get the VBA in the worksheet_calculate to be compiled we would not face this problem. As you will know, the VBE library has no inbuilt method to compile VBA (MakeCompiledFile and BuildFileName are only for VB6 code to create a .dll rather than compile VBA) and I am not happy about trying to implement a nasty send keys method. Has anyone encountered this and worked out a way to do this that does not use sendkeys??? Cheers, John -- www.alignment-systems.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Extensibility library and dynamic code compilation
I've never had luck with code changing code - Excel often becomes
unstable. That's interesting, because I've written tons of code that manipulates code in modules and Designer objects. The only problem that I've ever had is when code attempts to modify the module that contains the code doing the manipulation, i.e. a module modifying itself. That causes problems, but as long as I stay away from that module, I've had no problems. Maybe quirks, but not such that I would call them "problems" or that Excel became "unstable". -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com (email address is on the web site) "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... John, I've never had luck with code changing code - Excel often becomes unstable. My solution is to set up a workbook template with all the event code required, then copy it whenever I need it. Unless you are creating custom code dynamically, it may work for you as well.... HTH, Bernie MS Excel MVP "John.Greenan" wrote in message ... I have inherited a VB6 application that runs unattended and creates an Excel 2002 spreadsheet dynamically using the Excel library. The VB6 code then uses the VBA Extensibility library to access the created worksheet and then adds a "worksheet_calculate" event using mycodemodule.CreateEventProc("Calculate", "Worksheet") This "worksheet_calculate" event code is then populated by the VB6 code writing some VBA using mycodemodule.InsertLines lngTargetLine, strHeaderLine1 This works ok and we see that the VBA code works properly, but when the worksheet is opened by a user we find that we cannot change any of the data in the worksheet with calculation set to automatic - editing any cell will repeatably cause Excel to crash out. To change the worksheet values we either (a) switch calculation to manual, change the sheet and then put calculation back to automatic OR (b) we enter VBA and compile the worksheet using "Tools""Compile VBA Project". If we change the worksheet without making one of these changes then we find that Excel just crashes out. This appears to be an Excel bug. So, if we could find a "proper" way to get the VBA in the worksheet_calculate to be compiled we would not face this problem. As you will know, the VBE library has no inbuilt method to compile VBA (MakeCompiledFile and BuildFileName are only for VB6 code to create a .dll rather than compile VBA) and I am not happy about trying to implement a nasty send keys method. Has anyone encountered this and worked out a way to do this that does not use sendkeys??? Cheers, John -- www.alignment-systems.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Extensibility library and dynamic code compilation
Hi John,
I have also found adding code programmatically behind object modules can be fraught with problems, most especially if coding form thisworkbook to thisworkbook, but that I take it is not what you are doing. Some of your problems might depend on what you are doing (changing CodeName ?) and whether or not the VBE is open when you make the changes. Have you tried wb save/close/open, and/or flashing (programmatically) the VBE open/closed. Or maybe adding code to a sheet in a temporary wb and move the sheet to yours. To compile, ensuring the wb is active in the vbe Set oCtrl = xlApp.VBE.CommandBars.FindControl(ID:=578) oCtrl.Execute I think its 578 in all versions though perhaps not in XL-2007 msgbox oCtrl.caption Regards, Peter T "John.Greenan" wrote in message ... I have inherited a VB6 application that runs unattended and creates an Excel 2002 spreadsheet dynamically using the Excel library. The VB6 code then uses the VBA Extensibility library to access the created worksheet and then adds a "worksheet_calculate" event using mycodemodule.CreateEventProc("Calculate", "Worksheet") This "worksheet_calculate" event code is then populated by the VB6 code writing some VBA using mycodemodule.InsertLines lngTargetLine, strHeaderLine1 This works ok and we see that the VBA code works properly, but when the worksheet is opened by a user we find that we cannot change any of the data in the worksheet with calculation set to automatic - editing any cell will repeatably cause Excel to crash out. To change the worksheet values we either (a) switch calculation to manual, change the sheet and then put calculation back to automatic OR (b) we enter VBA and compile the worksheet using "Tools""Compile VBA Project". If we change the worksheet without making one of these changes then we find that Excel just crashes out. This appears to be an Excel bug. So, if we could find a "proper" way to get the VBA in the worksheet_calculate to be compiled we would not face this problem. As you will know, the VBE library has no inbuilt method to compile VBA (MakeCompiledFile and BuildFileName are only for VB6 code to create a .dll rather than compile VBA) and I am not happy about trying to implement a nasty send keys method. Has anyone encountered this and worked out a way to do this that does not use sendkeys??? Cheers, John -- www.alignment-systems.com |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Extensibility library and dynamic code compilation
Peter T - thanks for the pointer - that reduces the number of crashes but it
does not fix it. Strangely it seems to have a slightly different effect to manually running the compile VBA project button. -- www.alignment-systems.com "Peter T" wrote: Hi John, I have also found adding code programmatically behind object modules can be fraught with problems, most especially if coding form thisworkbook to thisworkbook, but that I take it is not what you are doing. Some of your problems might depend on what you are doing (changing CodeName ?) and whether or not the VBE is open when you make the changes. Have you tried wb save/close/open, and/or flashing (programmatically) the VBE open/closed. Or maybe adding code to a sheet in a temporary wb and move the sheet to yours. To compile, ensuring the wb is active in the vbe Set oCtrl = xlApp.VBE.CommandBars.FindControl(ID:=578) oCtrl.Execute I think its 578 in all versions though perhaps not in XL-2007 msgbox oCtrl.caption Regards, Peter T "John.Greenan" wrote in message ... I have inherited a VB6 application that runs unattended and creates an Excel 2002 spreadsheet dynamically using the Excel library. The VB6 code then uses the VBA Extensibility library to access the created worksheet and then adds a "worksheet_calculate" event using mycodemodule.CreateEventProc("Calculate", "Worksheet") This "worksheet_calculate" event code is then populated by the VB6 code writing some VBA using mycodemodule.InsertLines lngTargetLine, strHeaderLine1 This works ok and we see that the VBA code works properly, but when the worksheet is opened by a user we find that we cannot change any of the data in the worksheet with calculation set to automatic - editing any cell will repeatably cause Excel to crash out. To change the worksheet values we either (a) switch calculation to manual, change the sheet and then put calculation back to automatic OR (b) we enter VBA and compile the worksheet using "Tools""Compile VBA Project". If we change the worksheet without making one of these changes then we find that Excel just crashes out. This appears to be an Excel bug. So, if we could find a "proper" way to get the VBA in the worksheet_calculate to be compiled we would not face this problem. As you will know, the VBE library has no inbuilt method to compile VBA (MakeCompiledFile and BuildFileName are only for VB6 code to create a .dll rather than compile VBA) and I am not happy about trying to implement a nasty send keys method. Has anyone encountered this and worked out a way to do this that does not use sendkeys??? Cheers, John -- www.alignment-systems.com |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Extensibility library and dynamic code compilation
Sounds like things are moving in the right direction.
What "slightly different effect". Did you try the other 3 of the 4 pointers (if not already before the OP). Any object pointers in your main app that might be related, not released before moving on. A wild guess, only mention it as I have a something obscure along those lines at this very moment I can't track down, causing my Excel to crash on close. Regards, Peter T "John.Greenan" wrote in message ... Peter T - thanks for the pointer - that reduces the number of crashes but it does not fix it. Strangely it seems to have a slightly different effect to manually running the compile VBA project button. -- www.alignment-systems.com "Peter T" wrote: Hi John, I have also found adding code programmatically behind object modules can be fraught with problems, most especially if coding form thisworkbook to thisworkbook, but that I take it is not what you are doing. Some of your problems might depend on what you are doing (changing CodeName ?) and whether or not the VBE is open when you make the changes. Have you tried wb save/close/open, and/or flashing (programmatically) the VBE open/closed. Or maybe adding code to a sheet in a temporary wb and move the sheet to yours. To compile, ensuring the wb is active in the vbe Set oCtrl = xlApp.VBE.CommandBars.FindControl(ID:=578) oCtrl.Execute I think its 578 in all versions though perhaps not in XL-2007 msgbox oCtrl.caption Regards, Peter T "John.Greenan" wrote in message ... I have inherited a VB6 application that runs unattended and creates an Excel 2002 spreadsheet dynamically using the Excel library. The VB6 code then uses the VBA Extensibility library to access the created worksheet and then adds a "worksheet_calculate" event using mycodemodule.CreateEventProc("Calculate", "Worksheet") This "worksheet_calculate" event code is then populated by the VB6 code writing some VBA using mycodemodule.InsertLines lngTargetLine, strHeaderLine1 This works ok and we see that the VBA code works properly, but when the worksheet is opened by a user we find that we cannot change any of the data in the worksheet with calculation set to automatic - editing any cell will repeatably cause Excel to crash out. To change the worksheet values we either (a) switch calculation to manual, change the sheet and then put calculation back to automatic OR (b) we enter VBA and compile the worksheet using "Tools""Compile VBA Project". If we change the worksheet without making one of these changes then we find that Excel just crashes out. This appears to be an Excel bug. So, if we could find a "proper" way to get the VBA in the worksheet_calculate to be compiled we would not face this problem. As you will know, the VBE library has no inbuilt method to compile VBA (MakeCompiledFile and BuildFileName are only for VB6 code to create a ..dll rather than compile VBA) and I am not happy about trying to implement a nasty send keys method. Has anyone encountered this and worked out a way to do this that does not use sendkeys??? Cheers, John -- www.alignment-systems.com |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Extensibility library and dynamic code compilation
"slightly different effect" means that if I manually hit ALT+F11 and then
compile I seem to be able to changes values a few more times before Excel crashes. [my intial statement that manually doing this was crash-free was incorrect]. I always get rid of COM references (object pointers) by freeing/clearing and then "set mypointer = nothing" but thanks for the idea. I think that the problem I am now seeing is being caused by the high volume of real time data updates that are hitting the workbook. So, while I think the compilation problem is fixed we can still crash out Excel, it just takes a bit longer. And just for fun and games, Microsoft do not have the ability (as things stand) to replicate this bug, since they do not have any real time data feeds such as Reuters going into their development systems - at least according to David Gainer last time I spoke with him. I think this problem is caused by the external events that we pick up. Lots of real time data + worksheet_calculate = unstable ????? -- www.alignment-systems.com "Peter T" wrote: Sounds like things are moving in the right direction. What "slightly different effect". Did you try the other 3 of the 4 pointers (if not already before the OP). Any object pointers in your main app that might be related, not released before moving on. A wild guess, only mention it as I have a something obscure along those lines at this very moment I can't track down, causing my Excel to crash on close. Regards, Peter T "John.Greenan" wrote in message ... Peter T - thanks for the pointer - that reduces the number of crashes but it does not fix it. Strangely it seems to have a slightly different effect to manually running the compile VBA project button. -- www.alignment-systems.com "Peter T" wrote: Hi John, I have also found adding code programmatically behind object modules can be fraught with problems, most especially if coding form thisworkbook to thisworkbook, but that I take it is not what you are doing. Some of your problems might depend on what you are doing (changing CodeName ?) and whether or not the VBE is open when you make the changes. Have you tried wb save/close/open, and/or flashing (programmatically) the VBE open/closed. Or maybe adding code to a sheet in a temporary wb and move the sheet to yours. To compile, ensuring the wb is active in the vbe Set oCtrl = xlApp.VBE.CommandBars.FindControl(ID:=578) oCtrl.Execute I think its 578 in all versions though perhaps not in XL-2007 msgbox oCtrl.caption Regards, Peter T "John.Greenan" wrote in message ... I have inherited a VB6 application that runs unattended and creates an Excel 2002 spreadsheet dynamically using the Excel library. The VB6 code then uses the VBA Extensibility library to access the created worksheet and then adds a "worksheet_calculate" event using mycodemodule.CreateEventProc("Calculate", "Worksheet") This "worksheet_calculate" event code is then populated by the VB6 code writing some VBA using mycodemodule.InsertLines lngTargetLine, strHeaderLine1 This works ok and we see that the VBA code works properly, but when the worksheet is opened by a user we find that we cannot change any of the data in the worksheet with calculation set to automatic - editing any cell will repeatably cause Excel to crash out. To change the worksheet values we either (a) switch calculation to manual, change the sheet and then put calculation back to automatic OR (b) we enter VBA and compile the worksheet using "Tools""Compile VBA Project". If we change the worksheet without making one of these changes then we find that Excel just crashes out. This appears to be an Excel bug. So, if we could find a "proper" way to get the VBA in the worksheet_calculate to be compiled we would not face this problem. As you will know, the VBE library has no inbuilt method to compile VBA (MakeCompiledFile and BuildFileName are only for VB6 code to create a ..dll rather than compile VBA) and I am not happy about trying to implement a nasty send keys method. Has anyone encountered this and worked out a way to do this that does not use sendkeys??? Cheers, John -- www.alignment-systems.com |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Extensibility library and dynamic code compilation
I always get rid of COM references (object pointers) by freeing/clearing
and then "set mypointer = nothing" but thanks for the idea. Pretty sure I've done all that but suspect something circular is going on with my thing. Your live data feeds etc is way outside what I know about so this may seem naive - If, as you say, you think your problems are related to picking up external events, wouldn't disabling your events help until everything is ready to go again. Regards, Peter T "John.Greenan" wrote in message ... "slightly different effect" means that if I manually hit ALT+F11 and then compile I seem to be able to changes values a few more times before Excel crashes. [my intial statement that manually doing this was crash-free was incorrect]. I always get rid of COM references (object pointers) by freeing/clearing and then "set mypointer = nothing" but thanks for the idea. I think that the problem I am now seeing is being caused by the high volume of real time data updates that are hitting the workbook. So, while I think the compilation problem is fixed we can still crash out Excel, it just takes a bit longer. And just for fun and games, Microsoft do not have the ability (as things stand) to replicate this bug, since they do not have any real time data feeds such as Reuters going into their development systems - at least according to David Gainer last time I spoke with him. I think this problem is caused by the external events that we pick up. Lots of real time data + worksheet_calculate = unstable ????? -- www.alignment-systems.com "Peter T" wrote: Sounds like things are moving in the right direction. What "slightly different effect". Did you try the other 3 of the 4 pointers (if not already before the OP). Any object pointers in your main app that might be related, not released before moving on. A wild guess, only mention it as I have a something obscure along those lines at this very moment I can't track down, causing my Excel to crash on close. Regards, Peter T "John.Greenan" wrote in message ... Peter T - thanks for the pointer - that reduces the number of crashes but it does not fix it. Strangely it seems to have a slightly different effect to manually running the compile VBA project button. -- www.alignment-systems.com "Peter T" wrote: Hi John, I have also found adding code programmatically behind object modules can be fraught with problems, most especially if coding form thisworkbook to thisworkbook, but that I take it is not what you are doing. Some of your problems might depend on what you are doing (changing CodeName ?) and whether or not the VBE is open when you make the changes. Have you tried wb save/close/open, and/or flashing (programmatically) the VBE open/closed. Or maybe adding code to a sheet in a temporary wb and move the sheet to yours. To compile, ensuring the wb is active in the vbe Set oCtrl = xlApp.VBE.CommandBars.FindControl(ID:=578) oCtrl.Execute I think its 578 in all versions though perhaps not in XL-2007 msgbox oCtrl.caption Regards, Peter T "John.Greenan" wrote in message ... I have inherited a VB6 application that runs unattended and creates an Excel 2002 spreadsheet dynamically using the Excel library. The VB6 code then uses the VBA Extensibility library to access the created worksheet and then adds a "worksheet_calculate" event using mycodemodule.CreateEventProc("Calculate", "Worksheet") This "worksheet_calculate" event code is then populated by the VB6 code writing some VBA using mycodemodule.InsertLines lngTargetLine, strHeaderLine1 This works ok and we see that the VBA code works properly, but when the worksheet is opened by a user we find that we cannot change any of the data in the worksheet with calculation set to automatic - editing any cell will repeatably cause Excel to crash out. To change the worksheet values we either (a) switch calculation to manual, change the sheet and then put calculation back to automatic OR (b) we enter VBA and compile the worksheet using "Tools""Compile VBA Project". If we change the worksheet without making one of these changes then we find that Excel just crashes out. This appears to be an Excel bug. So, if we could find a "proper" way to get the VBA in the worksheet_calculate to be compiled we would not face this problem. As you will know, the VBE library has no inbuilt method to compile VBA (MakeCompiledFile and BuildFileName are only for VB6 code to create a ..dll rather than compile VBA) and I am not happy about trying to implement a nasty send keys method. Has anyone encountered this and worked out a way to do this that does not use sendkeys??? Cheers, John -- www.alignment-systems.com |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Extensibility library and dynamic code compilation
This wasn't well explained -
I have also found adding code programmatically behind object modules can be fraught with problems, most especially if coding form thisworkbook to thisworkbook, but that I take it is not what you are doing. I meant problems if the code that writes the changes is in the same project that's being written to, especially to sheet & wb modules. Peter T "Peter T" <peter_t@discussions wrote in message ... Hi John, I have also found adding code programmatically behind object modules can be fraught with problems, most especially if coding form thisworkbook to thisworkbook, but that I take it is not what you are doing. Some of your problems might depend on what you are doing (changing CodeName ?) and whether or not the VBE is open when you make the changes. Have you tried wb save/close/open, and/or flashing (programmatically) the VBE open/closed. Or maybe adding code to a sheet in a temporary wb and move the sheet to yours. To compile, ensuring the wb is active in the vbe Set oCtrl = xlApp.VBE.CommandBars.FindControl(ID:=578) oCtrl.Execute I think its 578 in all versions though perhaps not in XL-2007 msgbox oCtrl.caption Regards, Peter T "John.Greenan" wrote in message ... I have inherited a VB6 application that runs unattended and creates an Excel 2002 spreadsheet dynamically using the Excel library. The VB6 code then uses the VBA Extensibility library to access the created worksheet and then adds a "worksheet_calculate" event using mycodemodule.CreateEventProc("Calculate", "Worksheet") This "worksheet_calculate" event code is then populated by the VB6 code writing some VBA using mycodemodule.InsertLines lngTargetLine, strHeaderLine1 This works ok and we see that the VBA code works properly, but when the worksheet is opened by a user we find that we cannot change any of the data in the worksheet with calculation set to automatic - editing any cell will repeatably cause Excel to crash out. To change the worksheet values we either (a) switch calculation to manual, change the sheet and then put calculation back to automatic OR (b) we enter VBA and compile the worksheet using "Tools""Compile VBA Project". If we change the worksheet without making one of these changes then we find that Excel just crashes out. This appears to be an Excel bug. So, if we could find a "proper" way to get the VBA in the worksheet_calculate to be compiled we would not face this problem. As you will know, the VBE library has no inbuilt method to compile VBA (MakeCompiledFile and BuildFileName are only for VB6 code to create a ..dll rather than compile VBA) and I am not happy about trying to implement a nasty send keys method. Has anyone encountered this and worked out a way to do this that does not use sendkeys??? Cheers, John -- www.alignment-systems.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extensibility Library | Excel Programming | |||
VB Extensibility Library - 97 to 2002 | Excel Programming | |||
VBA extensibility library for deleting forms, modules, code etc... | Excel Programming | |||
VBA extensibility Library | Excel Programming | |||
VBA extensibility library for deleting forms, modules, code etc... | Excel Programming |