0

I have created a very simple modal form with two buttons: one just closes the form, and the other calls Application.RTD.RefreshData. In Excel 2003 this all works just fine, but in Excel 2010 the Application.RTD.RefreshData crashes with

Run-time error '1004': Application-defined or object-defined error

If I create the form as modeless everything works fine in both Excel 2003 and Excel 2010.

For what it's worth, this is all the code behind the form:

Private Sub RefreshDataButton_Click()
  Application.RTD.RefreshData
End Sub

Private Sub CloseButton_Click()
  Call Unload(Me)
End Sub

And I invoke the form using this code in my sheet:

Private Sub CommandButton1_Click()
  Dim form As UserForm1
  Set form = New UserForm1
  form.Show vbModal
End Sub

I've scoured Google and SO for any information on this to no avail. Is it a known issue with Excel 2010, or am I really not allowed to call Application.RTD.RefreshData from a modal form?

EDIT: I should add that as far as I can tell there are no RTD servers in my Excel session.

EDIT2: Further investigation: the error does not occur in Excel 2003 or 2007; it does occur in 2010 and 2013.

atkins
  • 1,963
  • 14
  • 27

1 Answers1

1

In a normal RTD server, you use the excel callback to call UpdateNotify(), and then Excel will decide when to call RefreshData() based on the throttle settings and how "busy" it currently is. The definition of "busy" normally includes things like formula recalculations, saving the sheet, and having a modal dialog open.

Excel protects itself from external components when it considers itself busy by throwing an exception. This makes sense, as they don't want their application becoming unstable or locking up because of a greedy addin or macro. Essentially all calls you put in your code which access any part of the Excel object model (including your call to RefreshData()) can throw an exception if Excel decides it is "busy". I suspect that in later versions of Excel they have tightened their definition of "busy", which is why you are seeing this disparity with the modal dialog.

Franchesca
  • 1,453
  • 17
  • 32