Excel: Macro Error Using TEXT.BOX() with Blank Parameters

• Microsoft Excel for Windows, version 4.0.

SUMMARY

The TEXT.BOX() macro function will cause a macro error in Microsoft Excel for Windows version 4.0 when used in the form

   TEXT.BOX(ADD_TEXT,OBJECT_ID_TEXT,,)

MORE INFORMATION

The TEXT.BOX() function replaces characters in a text box or button with the text specified by ADD_TEXT. The last two parameters are START_NUM and NUM_CHARS. START_NUM specifies the position number of the first character you want to replace in the text box and according to the "Microsoft Excel Function Reference," page 432, if omitted, it defaults to 1. NUM_CHARS is the number of characters you want to replace; if omitted, all the characters in the text box will be replaced.

If START_NUM and NUM_CHARS are left blank rather than omitted entirely, a macro error will occur when the function is evaluated. The function will work correctly if the last two commas are removed, that is: TEXT.BOX(ADD_TEXT,OBJECT_ID_TEXT).

In addition, the function will work incorrectly if the START_NUM is specified and NUM_CHARS is left blank with a comma inserted as a placeholder, for example, TEXT.BOX(ADD_TEXT,OBJECT_ID_TEXT,START_NUM,). Rather than replace all the characters in the text box as expected, the argument is interpreted as a zero which causes ADD_TEXT to be inserted at the position START_NUM. The function will work correctly in this instance if the last comma is removed.

STATUS

Microsoft has confirmed this to be a problem in Microsoft Excel version 4.0. This problem was corrected in version 4.0a of Microsoft Excel.

REFERENCES

"Microsoft Excel Function Reference," version 4.0, page 432