Help! Excel won't open... "Illegal Operation" erro

[chaos]

Hi all,

I just finished writing two large macro formulas and then closed my workbook. Now, when I try to open it, I get a message saying Excel has performed an Illegal Operation. This is the error message shown when I click on details:


EXCEL caused an invalid page fault in
module EXCEL.EXE at 018f:3016083b.
Registers:
EAX=00000000 CS=018f EIP=3016083b EFLGS=00210246
EBX=0391216c SS=0197 ESP=0062cef0 EBP=0062cf4c
ECX=039122e4 DS=0197 ESI=039122e4 FS=0f97
EDX=00000000 ES=0197 EDI=00000000 GS=0000
Bytes at CS:EIP:
8b 08 ff 51 04 ff 75 f8 c7 45 ec 01 00 00 00 53
Stack dump:
00000000 00000000 0391216c 039b7f44 0062cf98 0062cf98 0231b840 00000009 d7053240 11cdce69 dd0077a7 573c1401 00000010 00000008 039b7f24 308e093b



The macros were really quite large. But they worked fine while the workbook was open.

In the past, I have got the Illegal Operation message while shutting down this workbook. But it always would open up again. Now it doesn't seem to want to.

My comuter is a PIII with 256 mg ram.

Your help solving this would be most appreciated.

Regards,

chaos

 

[Damon Ostrander]

Re: Help! Excel won't open... "Illegal Operation"

Hi chaos,

You mention "macro formulas." If you mean that you have written user-defined functions (UDFs) in VBA, and you believe that these may be at the root of the problem, I suggest you open the workbook with macros disabled. If you currently have macro security set to low, you will have to open Excel and set macro security to at least medium (Tools > Macro > Security...) to be able to disable macros when you open the problem workbook.

This might at least get you to where you can look at the code to try to figure out the cause of the problem. If you can't see it, you might try posting the code on this board.

Damon

 

[chaos]

Re: Help! Excel won't open... "Illegal Operation"

Hi Damon,

Thank you for the reply.

After a process of trial and error, I have discovered what the problem is, though not the reason for it. I will post a sample of the macro code below. But the problem does not appear to be with the code. As long as I manually (Tools/Macro/select macro/Run) activate the macro, everything is fine.

It's after I insert a command button that the problem arises. Here's what occurs. I create a command button. It works fine. I close the workbook. Then when I try to reopen the workbook, the error message appears and the workbook will not reopen.

The problem is limited to just the affected workbook. I can still open other workbooks that I have as well as new ones.

I have gone through Microsoft's help files on this sort of error message and can find nothing that addresses this particular problem. Fortunately, I had backed up the workbook and so am back on track.

However, it would like to know what the issue is in order to avoid it in future. What puzzles me is that I have half a dozen command buttons in the workbook already and they work okay. It is only when I attempt to use a command button with this macro code that the problem arises.

Here is a sample of the code. The purpose of the code is to track currency prices in relation to Bollinger band price levels. This sample is for one currency pair. It is repeated 12 times in the macro as I track 12 currency pairs.

Option Explicit
Sub EUR1_AutoDaily_Bollinger()

Dim rngSource As Range, rngFill As Range, lRow As Long

With Worksheets("EUR1")
lRow = .Range("z65536").End(xlUp).Row

'Moving average of last 20 entries
Set rngSource = .Range("aa26")
rngSource.FormulaR1C1 = "=average(R[0]c[-1]:R[-19]C[-1])"
Set rngFill = .Range("aa26:aa" & lRow)
rngSource.AutoFill Destination:=rngFill

'Close > Moving Average
Set rngSource = .Range("ab26")
rngSource.FormulaR1C1 = "=(R[0]C[-2]>R[0]C[-1])"
Set rngFill = .Range("ab26:ab" & lRow)
rngSource.AutoFill Destination:=rngFill

'Moving Average + StDev
Set rngSource = .Range("ac26")
rngSource.FormulaR1C1 = "=R[0]C[-2]+(2*STDEV(R[0]C[-3]:R[-19]C[-3]))"
Set rngFill = .Range("ac26:ac" & lRow)
rngSource.AutoFill Destination:=rngFill

'Close > Upper Band
Set rngSource = .Range("ad26")
rngSource.FormulaR1C1 = "=(R[0]C[-4]>R[0]C[-1])"
Set rngFill = .Range("ad26:ad" & lRow)
rngSource.AutoFill Destination:=rngFill

'Moving Average - StDev
Set rngSource = .Range("af26")
rngSource.FormulaR1C1 = "=R[0]C[-5]-(2*STDEV(R[0]C[-6]:R[-19]C[-6]))"
Set rngFill = .Range("af26:af" & lRow)
rngSource.AutoFill Destination:=rngFill

'Close < Upper Band
Set rngSource = .Range("ag26")
rngSource.FormulaR1C1 = "=(R[0]C[-7]<R[0]C[-1])"
Set rngFill = .Range("ag26:ag" & lRow)
rngSource.AutoFill Destination:=rngFill

End With

End Sub


Thanks in advance for your help,

chaos

 

[chaos]

Re: Help! Excel won't open... "Illegal Operation"

One more thing... after I create a comand button in my workbook for the problem macro, then close down and unsuccessfully try to reopen it, I notice that Excel creates about 2 1/2 columns worth of files in my temp directory, mostly emf files.

After going through this process numerous times while tying to figure out the macro problem, I realized Excel had created about 7 MB worth of files in my temp directory. :x