Office XP: runtime error '50290' Application Defined or object defined error

[Nico]

Hi,
I use a VBA add-in to write into a Excel worksheet. This add-in uses a
dll to get Real Time data from a server.
Everything works perfectly on Office 97 and office 2000.
I have an issue with OfficeXP: sometime (yes, it does not happen all
the time...), when the VBA add-in tries to write into an excel sheet,
I get the following error:
runtime error '50290' Application Defined or object defined error

The line where it breaks is:
Wb.Worksheets("Sheet1").Cells(RowNbr,ColNbr).Value = myValue

myValue is an Integer, less than 100
Wb is the workbook object
RowNbr and ColNbr are Integer (their value is less than 10)

I need help. I have looked in many newsgroups but I cannot find what
is the issue. I have a deleverable on Wed this week...

Thanks.

[Nicolas]

[Siew Moi Khor [MS]]

Hi,
More information would be helpful--one line of code with the values doesn't
tell much. When coding in Excel, the current state of the app when you try
to execute an instruction has a big impact on what it does (and whether it
will actually succeed).

So if it works in 97 & 2000, but not in XP, what difference do you see when
it tries to do this? Does Excel still have focus? Is the worksheet you're
trying to write to visible and active? If not, perhaps calling
Wb.Worksheets("Sheet1").Activate before trying to write into it would help.

Also, there are several objects in that line. You could try making it
easier to debug by breaking it up into several different lines, like this:

Dim rngCell as Excel.Range
Set Ws = Wb.Worksheets(1)
Ws.Activate
Set rngCell = Ws.Cells(RowNbr, ColNbr)
rngCell.Value=myValue

And then see which line it fails on to help narrow the problem down.

Siew Moi
---
This posting is provided 'AS IS' with no warranties, and confers no rights.
Sample code subject to http://www.microsoft.com/info/cpyright.htm

[Nicolas]

Hi Siew,

Thanks for your reply. To reply to your questions:
- Does Excel still have focus?
--> No, everytime I get this error, Excel does not
have the focus.

- Is the worksheet you're trying to write to visible and
active?
--> The worksheet is NOT active. The worksheet is
visible (visible = true).

Unfortunately, I cannot make the sheet active nor focus
on the Excel application: The VBA macro gets real time
data from an ActiveX DLL and writes thedata into the
Excel sheet. It MUST work when the application does not
have the focus (for example when the user is using Word
or Outlook) and also when the worksheet is not active
because the user may be working on sheet2 while sheet1 is
updated.

I tried to debug it but unsuccessfully. What I noticed
from the Watch is that I can get the value of the cell
Wb.Worksheets(1).Cells(RowNbr, ColNbr).Value but I cannot
write to the cell when I get this error 50290.

As I said, I have no issue with Office 97 and 2000
What should I do to make it work Office XP?

Thank you for your help,

Nicolas.

>.
>

[Siew Moi Khor [MS]]

Hi Nicolas,
Instead of using Cells, try using Range instead to get around this problem.

[Tom Purton]

We experienced a similar problem with an Excel 2002 sheet with some
DDE links running under Windows XP : 50290 would occur intermittently
when a timer event ran that tried to read/write to the sheet. As
there seems to have been no official response from Microsoft anywhere
on the boards I've copied below an exchange of mails with Microsoft
Technical Support relating to this (earliest ones first, first one
from Microsoft):

> This error is caused by an intentional design change in Excel, which was done
> in order to address several serious reentrancy issues that can occur when
> Excel is in an outgoing COM call and a dialog is displayed. To work around
> the change, you can do one of two things:
>
> 1) Before Excel makes the long running COM call that produces the dialog, set
> the "DisplayAlerts" property of Excel's Application object to "False". This
> will keep Excel from displaying the Server Busy dialog in the first place and
> avoid the issue.
>
> 2) When the Server Busy Dialog blocks a COM call, you can catch the error,
> use the FindWindow API to locate the dialog box, and use the PostMessage API
> to tell it to close.
>
> ExcelBusyHandler:
>
> 'If the object model is disabled...
>
> If (Err.Number = 50290) Then
>
> 'And we're not stuck in an error loop...
>
> busyCount = busyCount + 1
>
> If (busyCount < 5) Then
>
> 'try to find the Dialog window
>
> Dim busyDialogWindow As Long
>
> busyDialogWindow = FindWindow(vbNullString, "Microsoft Excel")
>
> If (busyDialogWindow <> 0) Then
>
> 'Send it a WM_CLOSE message and clear the error flag
>
> PostMessage busyDialogWindow, WM_CLOSE, 0, 0
>
> Err.Clear
>
> End If
>
> 'Try the call that failed again
>
> Resume
>
> End If
>
> End If

James

Thank you for the response. I don't quite follow the logic of some of
this: please could you confirm the following:-

The response seems to be saying that the problem occurs when a) Excel
is in an outgoing COM call, *and* b) a dialog box is displayed. The
error has never occurred in this application *when* a dialog box is
displayed - obviously we get the standard run-time error dialog when
50290 occurs if Break On All Errors is set to true.

If by the "Server Busy dialog" you mean the standard error dialog box
displayed when error 50290 occurs, this makes more sense, although the
error description for 50290 is just "Application-defined or
object-defined error.", not "Server Busy". Please could you clarify
whether or not the Server Busy dialog box is the standard run-time
error dialog box that is displayed when there is no error-handling and
50290 occurs, and if not where the Server Busy dialog comes into this.

Does Application.DisplayAlerts stop error 50290 from being triggered
at all? I have always understood DisplayAlerts to relate to specific
alerts rather than as a means of switching off error-handling for
certain errors.

In our code, although there are DDE links in the sheet the error does
not seem to be directly related to the DDE cells. Rather, it occurs
when the sheet is trying to read/write back to the spreadsheet
(changing the value or font colour of a cell, for example).

> Hi Tom -
>
> The error code you have reported is returned when Excel refused access to the
> object model.
>
> This occurs EITHER when a dialogue box is displayed or when excel is busy
> performing certain tasks (such as using COM for instance). There are 2
> seperate parts to the information my colleague provided you with.
>
> 1 - Reducing the number of dialogues being displayed using
> Application.DisplayAlerts to stop the object model being locked
>
> 2 - Using the code sample provided to make a number of attempts to close the
> dialogue window which has been displayed and try to connect again.
>
> I suspect that part of the problem is that the realtime addin you have is not
> intended to work with Excel 2002 and it's more strict security behaviour.
> Excel now has it's own real-time functions and you may be better off in the
> long run looking at using this functionality rather than the addin you
> currently use. For more information see the following Knowledge Base
> article...
>
> 289150 HOW TO: Set Up and Use the RTD Function in Excel 2002
> http://support.microsoft.com/?id=289150
>
> Best Regards
> James Snell
>
> Microsoft Product Support.

James

Thank you for the reply. This is slightly clearer now, but I'd just
like to confirm that this has never occurred when a dialog box is
being displayed. I'll assume the 'Server busy' dialog box in the
previous response does not relate to 50290 in general but was a
specific response to a previous query.

I would be using RTD if I could, but unfortunately the third-party
application providing prices does not yet use RTD.

At present we are trapping 50290 when it occurs and resuming at the
line that fell over - this has been working okay so far, but would you
be able to tell me the average length of time that the error persists
for, and whether there might be any better ways of handling it.

Thanks
Tom

> Hi Tom -
>
> Really the lockout depends on how busy the application is and what is locking
> it out. Some functions will engage a longer lockout than others... So
> really it is just a case of waiting for the lockout ot be released. I don't
> see that most lockouts would last more than 20 / 30 seconds, but that's only
> a very rough guide.
>
> Trapping the error and waiting is by far the best way of dealing with the
> issue. The only pitfall to avoid is that there is not a build up of calls
> waiting to update the same information / cells and everything should be fine.
>
> Best Regards
> James Snell
> Microsoft Product Support.