Hi everybody,

Has anybody ever experienced Excel hanging when using the function
Offset in a formula?

I've a UserForm to fill out an Excel sheet. I use ControlSources to
copy the entered data to the sheet. In some cells I keep flags to
determine what the user filled in. I use the worksheet to do some
calculations on these flags. That works just fine.. until I use the
function Offset in the calculation. While running my VBA App when I
enter anywhere some data in the UserForm Excel stops responding. To be
precise: on the moment I'm leaving a TextBox by clicking somewhere
else.
I tried several things solving this problem: VBA cleaner and even
rebuilding the whole UserForm. I tried the VBA App on different
computers: same problem. I made a test program. Strange enough every
thing works fine with the test program.
Do you have any idea? Maybe a hint where I should delve into this
problem.

Many thanks in advance,

Bart
XPSP2
Office 2003SP2

Do you have any On Calculate event code in your program? Offset is a volatile
function maning that you could be geting into an infinite loop if you have
event code triggered on calculation...
--
HTH...

Jim Thomlinson


"Bart" wrote:

Hi everybody,

Has anybody ever experienced Excel hanging when using the function
Offset in a formula?

I've a UserForm to fill out an Excel sheet. I use ControlSources to
copy the entered data to the sheet. In some cells I keep flags to
determine what the user filled in. I use the worksheet to do some
calculations on these flags. That works just fine.. until I use the
function Offset in the calculation. While running my VBA App when I
enter anywhere some data in the UserForm Excel stops responding. To be
precise: on the moment I'm leaving a TextBox by clicking somewhere
else.
I tried several things solving this problem: VBA cleaner and even
rebuilding the whole UserForm. I tried the VBA App on different
computers: same problem. I made a test program. Strange enough every
thing works fine with the test program.
Do you have any idea? Maybe a hint where I should delve into this
problem.

Many thanks in advance,

Bart
XPSP2
Office 2003SP2

Thanks Jim Thomlinson for replying,

No I don't use the event "Private Sub object_Calculate()".
It looks suspiciously like an endless loop: Excel uses 99% of my CPU
when it hangs. I have to use the Task Manager to break of Excel.

Bart

I was wondering if you had
Private Sub Worksheet_Calculate()

End Sub
or
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)

End Sub

Depending on what you are up to you could end up with a recursive call of
these procedures. That being said have you tried stepping through your code
with F8 to see what the actual flow of execution is?
--
HTH...

Jim Thomlinson


"Bart" wrote:

Thanks Jim Thomlinson for replying,

No I don't use the event "Private Sub object_Calculate()".
It looks suspiciously like an endless loop: Excel uses 99% of my CPU
when it hangs. I have to use the Task Manager to break of Excel.

Bart

On May 29, 6:36 pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com wrote:
I was wondering if you had
Private Sub Worksheet_Calculate()

End Sub
or
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)

End Sub

Depending on what you are up to you could end up with a recursive call of
these procedures. That being said have you tried stepping through your code
with F8 to see what the actual flow of execution is?
--
HTH...

Jim Thomlinson



"Bart" wrote:
Thanks Jim Thomlinson for replying,

No I don't use the event "Private Sub object_Calculate()".
It looks suspiciously like an endless loop: Excel uses 99% of my CPU
when it hangs. I have to use the Task Manager to break of Excel.

Bart- Hide quoted text -

- Show quoted text -

I tried using step by step. I can start the application. So there
aren't any errors in the Initialize Event. Though I put a Breakpoint
on the last statement of this Event and Stepped (F8) from this point.
I did this on purpose to keep the Debug Mode when using the UI.
I tested several things. In whatever kind of way I write data to the
Worksheet, Excel starts to hang. An example is:

Activesheet.Range("A1").Value = Me.TextBox1.Value

After executing this statement the next line is marked yellow, but
it's the end of the Excel execution session as well: I've to shut down
Excel manually.
When I remove the formula containing the Offset function, no errors
occur!
The App only reads from the Sheet on Events initiated by the user
using the User Interface and at Initializing the UserForm.

Bart

Step by step I've tried.
I can start the application. So there aren't any errors in the
Initialize Event. Though I put a Breakpoint on the last statement of
this Event and Stepped (F8) from this point. I did this on purpose to
keep the Debug Mode when using the UI.
I tested several things. In whatever kind of way I write data to the
Worksheet, Excel starts to hang. An example is:

Activesheet.Range("A1").Value = Me.TextBox1.Value

After executing this statement the next line is marked yellow, but
it's the end of the Excel execution session as well: I've to shut down
Excel manually.
When I remove the formula containing the Offset function, no errors
occur!
The App only reads from the sheet on Events initiated by the user
using the User Interface and at initializing the UserForm.

Bart

Try turning calculation off while the procedure is running and then turn it
back on at the end perhaps...

Application.Calculation = xlCalculationManual
'your code here
Application.Calculation = xlCalculationAutomatic

--
HTH...

Jim Thomlinson


"Bart" wrote:

Step by step I've tried.
I can start the application. So there aren't any errors in the
Initialize Event. Though I put a Breakpoint on the last statement of
this Event and Stepped (F8) from this point. I did this on purpose to
keep the Debug Mode when using the UI.
I tested several things. In whatever kind of way I write data to the
Worksheet, Excel starts to hang. An example is:

Activesheet.Range("A1").Value = Me.TextBox1.Value

After executing this statement the next line is marked yellow, but
it's the end of the Excel execution session as well: I've to shut down
Excel manually.
When I remove the formula containing the Offset function, no errors
occur!
The App only reads from the sheet on Events initiated by the user
using the User Interface and at initializing the UserForm.

Bart