Use of the ON.DATA() Command in Excel

 Last reviewed: November 2, 1994
Article ID: Q58448

SUMMARY

When using the ON.DATA() command in Excel, you need to verify the following:

  1. Ignore Remote Requests must be unchecked in Excel (choose Workspace from the Options menu).

  2. The argument for Document_Text in the ON.DATA() function must refer to a sheet name that contains remote references and ONLY the sheet name, not including cell references.

  3. The DDE link must be to another application. ON.DATA() will not run if the link is to a second instance of Excel. Page 320 of the Excel Functions and Macro Guide states: 

            "ON.DATA() starts the macro specified whenever ANOTHER
         application sends new data to the document specified."

MORE INFORMATION

  1. You can ensure that the "Ignore Remote Requests" check box is unchecked by issuing the following macro statement before the ON.DATA() command: 

          =WORKSPACE(,,,,,,,false)

  2. Valid ON.DATA() commands would look like the following: 

          =ON.DATA("Sheet1.xls","Macro1.xlm!R1C1")
      =ON.DATA("Sheet1.xls","Macro1.xlm!Macro_name")

You may NOT reference any particular cells. The following would NOT be valid: 

   =ON.DATA("Sheet1.xls!$A$1","Macro1.xlm!R1C1")
   =ON.DATA("Sheet1.xls!Area1","Macro1.xlm!Macro_name")
The ON.DATA() command is used in Excel when you want a particular macro to run any time there is any type of update to a particular worksheet via DDE. The format of the formula is =ON.DATA("Document_Text","Macro_text"). If executed properly, the macro defined by Macro_text will run every time there is a DDE update to the worksheet specified by Document_Text.

KBCategory: kbother
KBSubcategory:

Additional reference words: noupd

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: November 2, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.