Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Edit VBA Code without opening the workbook
I have VBA event code in an EXCEL workbook that goes into a never-ending
loop. I know what the problem is -- it triggers its own event! -- but I don't know how to edit the errant code without opening the workbook. Is there a way to edit the code without opening the workbook? TIA, Phil |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Edit VBA Code without opening the workbook
Here is another option you might consider...
http://www.cpearson.com/excel/StartupErrors.aspx Mark Ivey "pwrichcreek" wrote in message ... I have VBA event code in an EXCEL workbook that goes into a never-ending loop. I know what the problem is -- it triggers its own event! -- but I don't know how to edit the errant code without opening the workbook. Is there a way to edit the code without opening the workbook? TIA, Phil |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Edit VBA Code without opening the workbook
Chris,
The discussion at cpearson.com is very useful and interesting and I think if I had pursued it further it might have gotten me to where I could edit the code at open time. I got a bit confused about exactly what he meant by 'safe mode'. A later reply (actually two of them) offered a suggestion that was pretty straightforward so I gave it a try and it got me to where I could edit the code. Thanks, Phil "Mark Ivey" wrote: Here is another option you might consider... http://www.cpearson.com/excel/StartupErrors.aspx Mark Ivey "pwrichcreek" wrote in message ... I have VBA event code in an EXCEL workbook that goes into a never-ending loop. I know what the problem is -- it triggers its own event! -- but I don't know how to edit the errant code without opening the workbook. Is there a way to edit the code without opening the workbook? TIA, Phil |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Edit VBA Code without opening the workbook
Hi there,
Try taking a look at the EnableEvents property. I use something like this to call before/after running code to optimize it slightly... Public Sub ToggleEvents(ByVal blnState As Boolean) 'Originally written by firefytr With Application .DisplayAlerts = blnState .EnableEvents = blnState .ScreenUpdating = blnState If blnState Then .CutCopyMode = False If blnState Then .StatusBar = False End With End Sub HTH -- Zack Barresse "pwrichcreek" wrote in message ... I have VBA event code in an EXCEL workbook that goes into a never-ending loop. I know what the problem is -- it triggers its own event! -- but I don't know how to edit the errant code without opening the workbook. Is there a way to edit the code without opening the workbook? TIA, Phil |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Edit VBA Code without opening the workbook
Thanks for your reply Zack, but it doesn't address the problem I am (was)
having. My problem was not a matter of trying to optimize the code; rather it had a recursive bug and I was unable to open the workbook without the code going into a never-ending loop. Phil "Zack Barresse" wrote: Hi there, Try taking a look at the EnableEvents property. I use something like this to call before/after running code to optimize it slightly... Public Sub ToggleEvents(ByVal blnState As Boolean) 'Originally written by firefytr With Application .DisplayAlerts = blnState .EnableEvents = blnState .ScreenUpdating = blnState If blnState Then .CutCopyMode = False If blnState Then .StatusBar = False End With End Sub HTH -- Zack Barresse "pwrichcreek" wrote in message ... I have VBA event code in an EXCEL workbook that goes into a never-ending loop. I know what the problem is -- it triggers its own event! -- but I don't know how to edit the errant code without opening the workbook. Is there a way to edit the code without opening the workbook? TIA, Phil |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Edit VBA Code without opening the workbook
Not really sure what you mean. If you were having problems with a
workbook_open event firing upon opening it via code, turning off the events will not trigger it, thus no recursive loop. Take this for example... Sub RunMeNow() Call ToggleEvents(False) Workbooks.Open ThisWorkbook.Path & "\Book1.xls" Call ToggleEvents(True) End Sub Public Sub ToggleEvents(ByVal blnState As Boolean) 'Originally written by firefytr With Application .DisplayAlerts = blnState .EnableEvents = blnState .ScreenUpdating = blnState If blnState Then .CutCopyMode = False If blnState Then .StatusBar = False End With End Sub Opening that particular workbook with the sub routine toggling the events off will not fire the workbook_open event in the target workbook. So if you want to supress that event, just toggle the EnableEvents property to false. No need to disable macros. Unless you are manually opening the workbook, then I may have mistaken your question in its origin. HTH -- Zack Barresse "pwrichcreek" wrote in message ... Thanks for your reply Zack, but it doesn't address the problem I am (was) having. My problem was not a matter of trying to optimize the code; rather it had a recursive bug and I was unable to open the workbook without the code going into a never-ending loop. Phil "Zack Barresse" wrote: Hi there, Try taking a look at the EnableEvents property. I use something like this to call before/after running code to optimize it slightly... Public Sub ToggleEvents(ByVal blnState As Boolean) 'Originally written by firefytr With Application .DisplayAlerts = blnState .EnableEvents = blnState .ScreenUpdating = blnState If blnState Then .CutCopyMode = False If blnState Then .StatusBar = False End With End Sub HTH -- Zack Barresse "pwrichcreek" wrote in message ... I have VBA event code in an EXCEL workbook that goes into a never-ending loop. I know what the problem is -- it triggers its own event! -- but I don't know how to edit the errant code without opening the workbook. Is there a way to edit the code without opening the workbook? TIA, Phil |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Edit VBA Code without opening the workbook
"pwrichcreek" wrote in message
... I have VBA event code in an EXCEL workbook that goes into a never-ending loop. I know what the problem is -- it triggers its own event! -- but I don't know how to edit the errant code without opening the workbook. Is there a way to edit the code without opening the workbook? Hold the SHIFT key when you open the workbook to prevent code from running. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Edit VBA Code without opening the workbook
Howard,
Many thanks for your reply. It did the trick. Then, the first time (after fixing the code) I opened the workbook WITHOUT holding the SHIFT key, it dawned on me that, if I just replied "disable" to the allow macros question, it probably would have worked as well. And, indeed, another poster replied after yours and made that very suggestion. Thanks, Phil "Howard Kaikow" wrote: "pwrichcreek" wrote in message ... I have VBA event code in an EXCEL workbook that goes into a never-ending loop. I know what the problem is -- it triggers its own event! -- but I don't know how to edit the errant code without opening the workbook. Is there a way to edit the code without opening the workbook? Hold the SHIFT key when you open the workbook to prevent code from running. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Edit VBA Code without opening the workbook
Open excel and set your Macro Security to Medium. When you open the workbook
with the code, select Disable Macros. You will be able to edit the code without the macros running. When you are done with the fix, save, close, reopen and Enable Macros. "pwrichcreek" wrote: I have VBA event code in an EXCEL workbook that goes into a never-ending loop. I know what the problem is -- it triggers its own event! -- but I don't know how to edit the errant code without opening the workbook. Is there a way to edit the code without opening the workbook? TIA, Phil |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Edit VBA Code without opening the workbook
Chris,
Thanks for your reply. Another poster suggested a different workaround, namely holding down the SHIFT key while doing the open. After trying the SHIFT key method and fixing the code, I went back and opened normally and replied DISABLE to the macros question and, sure enough, the file opened just fine and did not start executing the code. Thanks, Phil "Chris" wrote: Open excel and set your Macro Security to Medium. When you open the workbook with the code, select Disable Macros. You will be able to edit the code without the macros running. When you are done with the fix, save, close, reopen and Enable Macros. "pwrichcreek" wrote: I have VBA event code in an EXCEL workbook that goes into a never-ending loop. I know what the problem is -- it triggers its own event! -- but I don't know how to edit the errant code without opening the workbook. Is there a way to edit the code without opening the workbook? TIA, Phil |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
opening a link in a workbook cell to edit?? | New Users to Excel | |||
Error on opening more than one workbook containing the same code | Excel Programming | |||
run code on opening workbook and apply code to certain sheets | Excel Programming | |||
Performing code without opening the Workbook | Excel Programming | |||
Code for Opening a Closed Workbook | Excel Programming |