Excel hangs

Hi,

I have a simple test sub :

sub gsgs()
dim fred
fred = Range("F10").value
Range("f10").value = fred + 1
end sub

step through this code and fred gets set correctly, but the next line
totally hangs excel :-O. Needs a task manager kill app to get out of it.

Spreadsheet created in Excel 2003, same fault exists when run on PC
Excel2003, Mac Excel2003 and Win2000 Excel ???.

NB Its a Excel application with form, lookup ranges etc. - but the 'problem'
was isolated to the above simple sub.


Any ideas please ;-O.

Thanks

Graham

my guess :
you've a change event handler
causing an indefinite loop.

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Oxns wrote in <news:<[email protected]>

> sub gsgs()
> dim fred
> fred = Range("F10").value
> Range("f10").value = fred + 1
> end sub

Thanks for the response - that was about what I figured. This project is
bnot mine but my daughters for A Level exams ;-)). I am no excel programmer,
but have gone through the code in all modules and forms, and the only loops
in there are all 'foreach', and work on ranges of cells - only 4
subs/functions, plus some recorded macros :-O.

Also if it was in a loop, shouldn't the break key work in a code editor
??? - has no effect....

Must be something on the sheets however, as using the same code in a clean
book is fine :-O.

If it is a loop (which it looks like), how do I break into it ???.

Graham

"keepITcool" <[email protected]> wrote in message
news:[email protected]...
> my guess :
> you've a change event handler
> causing an indefinite loop.
>
> --
> keepITcool
> | www.XLsupport.com | keepITcool chello nl | amsterdam
>
>
> Oxns wrote in <news:<[email protected]>
>
>> sub gsgs()
>> dim fred
>> fred = Range("F10").value
>> Range("f10").value = fred + 1
>> end sub

Hmmmm,

Problem goes away if I turn OFF Autocalc. Then the sub runs and works
perfectly well, even going back to the sheet and pressing F9 - still doesn't
hang ;-O.

G.

"keepITcool" <[email protected]> wrote in message
news:[email protected]...
> my guess :
> you've a change event handler
> causing an indefinite loop.
>
> --
> keepITcool
> | www.XLsupport.com | keepITcool chello nl | amsterdam
>
>
> Oxns wrote in <news:<[email protected]>
>
>> sub gsgs()
>> dim fred
>> fred = Range("F10").value
>> Range("f10").value = fred + 1
>> end sub

Is F10 formatted as a Number?

Try Inserting a MsgBox to display Fred prior to
incrementing it. See if anything seems obvious there.

Good luck,

Krayten

*** Free account sponsored by SecureIX.com ***
*** Encrypt your Internet usage with a free VPN account from http://www.SecureIX.com ***

Hi

Based on my test, simply the code as you post did not seem to hang the
excel.
Commonly we can use Ctrl+Break to stop a macro executing.

Also I think you may try to post the Debug.print in the loop.
e.g.
For each ...

'Add the code line
Debug.Print "blabla..."
Next

If if the problem is occurred in the loop, a Debug.Print will keep print
out the text "blabla...."


Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

Peter,

Thanks for the reply.

Agreed that the code on its own does not fail :-O. It fails when in the full
Excel application.

If autocalc is turned on, I can step through the code until the point at
which the range is used to update the cell - then the app just hangs.

If I turn autocalc off it works just fine. Even works if I press F9 on the
spreadsheet - well I say works, nothing happens and it doesn't lock up - as
there is no change on the sheet, there is nothing to recalc.

So - the problem I have is that somehow autocalc appears to be running into
a loop :-O. How do I find out what and why ???.

The app is for my daughters exam coursework and I'm sure that she wouldn't
mind me sending it privately to you if this will help. The problem seems to
appear on most platforms :-O.

Thanks

regards

Graham



""Peter Huang" [MSFT]" wrote:

> Hi
>
> Based on my test, simply the code as you post did not seem to hang the
> excel.
> Commonly we can use Ctrl+Break to stop a macro executing.
>
> Also I think you may try to post the Debug.print in the loop.
> e.g.
> For each ...
>
> 'Add the code line
> Debug.Print "blabla..."
> Next
>
> If if the problem is occurred in the loop, a Debug.Print will keep print
> out the text "blabla...."
>
>
> Best regards,
>
> Peter Huang
> Microsoft Online Partner Support
>
> Get Secure! - www.microsoft.com/security
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>

> Agreed that the code on its own does not fail :-O. It fails when in the
full
> Excel application.
Is there more to this entire code routine, then, than just what you've
posted? If there's more code, than you need to look at where else you've
used F10 and see if you've got it pointing back on itself somewhere.

Also try defining fred as a certain type of variable and see if that helps.

Ed

"GrahamS" <[email protected]> wrote in message
news:[email protected]...
> Peter,
>
> Thanks for the reply.
>
> Agreed that the code on its own does not fail :-O. It fails when in the
full
> Excel application.
>
> If autocalc is turned on, I can step through the code until the point at
> which the range is used to update the cell - then the app just hangs.
>
> If I turn autocalc off it works just fine. Even works if I press F9 on the
> spreadsheet - well I say works, nothing happens and it doesn't lock up -
as
> there is no change on the sheet, there is nothing to recalc.
>
> So - the problem I have is that somehow autocalc appears to be running
into
> a loop :-O. How do I find out what and why ???.
>
> The app is for my daughters exam coursework and I'm sure that she wouldn't
> mind me sending it privately to you if this will help. The problem seems
to
> appear on most platforms :-O.
>
> Thanks
>
> regards
>
> Graham
>
>
>
> ""Peter Huang" [MSFT]" wrote:
>
> > Hi
> >
> > Based on my test, simply the code as you post did not seem to hang the
> > excel.
> > Commonly we can use Ctrl+Break to stop a macro executing.
> >
> > Also I think you may try to post the Debug.print in the loop.
> > e.g.
> > For each ...
> >
> > 'Add the code line
> > Debug.Print "blabla..."
> > Next
> >
> > If if the problem is occurred in the loop, a Debug.Print will keep print
> > out the text "blabla...."
> >
> >
> > Best regards,
> >
> > Peter Huang
> > Microsoft Online Partner Support
> >
> > Get Secure! - www.microsoft.com/security
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >

Ed,

Yup there is quite a lot more to the app - as described in the original post
:-O. There is however very little code. The variable fred is simply a test
variable, the cell F10 is another test cell (empty). The problem exists for
any cell being set from within the VBA code :-(.

Its not really a problem with the code as shown but with the whole app - in
that 'AutoCalc' seems to kick in when I update any cell from VBA - and then
proceeds to 'lock up'. If Control-Break worked I wouldn't have a problem, as
I could then step through the code :-).

Hit a reakpoint on that one line 'Range("xxx").Value = anything' - F8 to
step over the line and autocalc seems to kick in and bang - dead. Turn off
autocalc and its fine !!. Steps over the code, sets the cell - all good.

Its probably something stupid in one cell, but I have no means of finding
out what it is - no error displayed or any other clue :-(.

So - the maybe I should repost question as 'How to debug autocalc crashes'
???.

Thanks

G.


"Ed" wrote:

> > Agreed that the code on its own does not fail :-O. It fails when in the
> full
> > Excel application.
> Is there more to this entire code routine, then, than just what you've
> posted? If there's more code, than you need to look at where else you've
> used F10 and see if you've got it pointing back on itself somewhere.
>
> Also try defining fred as a certain type of variable and see if that helps.
>
> Ed
>
> "GrahamS" <[email protected]> wrote in message
> news:[email protected]...
> > Peter,
> >
> > Thanks for the reply.
> >
> > Agreed that the code on its own does not fail :-O. It fails when in the
> full
> > Excel application.
> >
> > If autocalc is turned on, I can step through the code until the point at
> > which the range is used to update the cell - then the app just hangs.
> >
> > If I turn autocalc off it works just fine. Even works if I press F9 on the
> > spreadsheet - well I say works, nothing happens and it doesn't lock up -
> as
> > there is no change on the sheet, there is nothing to recalc.
> >
> > So - the problem I have is that somehow autocalc appears to be running
> into
> > a loop :-O. How do I find out what and why ???.
> >
> > The app is for my daughters exam coursework and I'm sure that she wouldn't
> > mind me sending it privately to you if this will help. The problem seems
> to
> > appear on most platforms :-O.
> >
> > Thanks
> >
> > regards
> >
> > Graham
> >
> >
> >
> > ""Peter Huang" [MSFT]" wrote:
> >
> > > Hi
> > >
> > > Based on my test, simply the code as you post did not seem to hang the
> > > excel.
> > > Commonly we can use Ctrl+Break to stop a macro executing.
> > >
> > > Also I think you may try to post the Debug.print in the loop.
> > > e.g.
> > > For each ...
> > >
> > > 'Add the code line
> > > Debug.Print "blabla..."
> > > Next
> > >
> > > If if the problem is occurred in the loop, a Debug.Print will keep print
> > > out the text "blabla...."
> > >
> > >
> > > Best regards,
> > >
> > > Peter Huang
> > > Microsoft Online Partner Support
> > >
> > > Get Secure! - www.microsoft.com/security
> > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > >
> > >
>
>
>

Hi

To isolate the problem, I think we need to comment out the application and
code lines one by one until the problem is not occur.
Or reversely, we can create a new Excel Workbook, and add the code
blocks/lines into the new excel workbook one by one to see what is the
cause.
So that we can minimize the code that will reproduce the problem.

Also the autocalc will calculate the formula on the sheet automatically.
You may also try to check if there will be some loop reference or
calculation in the sheet's formula.
To isolate the problem, we can create a new workbook and add the "suspect"
formula one by one to see what is the "culprit".

BTW: Have you tried my last suggestion that add the Debug.Print in the Loop
to see when the problem occurred, if the Debug.Print has any output.


Best regards,

Peter Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.