Edit VBA Code without opening the workbook

  • Thread starter Thread starter pwrichcreek
  • Start date Start date
P

pwrichcreek

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
 
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
 
pwrichcreek said:
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?
Click to expand...

Hold the SHIFT key when you open the workbook to prevent code from running.
 
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.
 
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
 
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
 
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,

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
 
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
 
Back
Top