Troubleshooting Macros that Use ON.DATA() in Excel

• Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 5.0
• Microsoft Excel for OS/2, versions 2.2, 3.0

SUMMARY

You may have a macro that uses the ON.DATA() function, but your macro halts every time it encounters this function. Even if you troubleshoot this line, this error may keep appearing. Check to ensure that you are disabling the ON.DATA() function before you make any changes to it. (Note: this is also true for version 5.0 macro sheets, but not modules.)

MORE INFORMATION

The ON.DATA(document_text,macro_text) function starts the macro specified by macro_text whenever another application sends new data to the document specified by document_text. Once called, the ON.DATA() function remains in effect until you turn it off (by calling ON.DATA() without the macro_text argument, or by turning off Excel).

As a result, even if you make what appears to be the appropriate corrections to the macro, these corrections are never noticed by the macro until the original ON.DATA() has been disabled.

Solution

Create a simple two-line macro using ON.DATA(document_text) and Return() in a separate area on the macro sheet and run this new macro between each test of the original macro.