Microsoft KB Archive/109208

= Microsoft Knowledge Base =

XL: Function Subroutines Can Show Message Boxes
Last reviewed: March 27, 1997

Article ID: Q109208

5.00 5.00c 7.00 | 5.00

WINDOWS        | MACINTOSH kbusage

The information in this article applies to:


 * Microsoft Excel for Windows, versions 5.0, 5.0c
 * Microsoft Excel for Windows 95, version 7.0
 * Microsoft Excel for the Macintosh, version 5.0

SUMMARY
In Microsoft Excel versions 5.x and 7.0, you can use the MsgBox function to display a message box in a Visual Basic function macro. The following Visual Basic procedure demonstrates one way to do this.

NOTE: In earlier versions of Microsoft Excel it is not possible to display a message box from a macro sheet.

Visual Basic Code Example
Microsoft provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. This Visual Basic procedure is provided 'as is' and Microsoft does not guarantee that it can be used in all situations. Microsoft does not support modifications of this procedure to suit customer requirements for a particular purpose. Note that a line that is preceded by an apostrophe introduces a comment in the code--comments are provided to explain what the code is doing at a particular point in the procedure. Note also that an underscore character (_) indicates that code continues from one line to the next. You can type lines that contain this character as one logical line or you can divide the lines of code and include the line continuation character. For more information about Visual Basic for Applications programming style, see the "Programming Style in This Manual" section in the "Document Conventions" section of the "Visual Basic User's Guide."

The following Visual Basic function subroutine illustrates one way to display a message box in a function.

  In a new Visual Basic module in a new workbook, enter the following subroutine: '-- 'This function accepts two arguments: X, an integer, and Y, an integer. 'It returns an integer. 

Function Test(X As Integer, Y As Integer) As Integer

'Multiply X by Y to get the value of Test. Test = X * Y  'Shows the value of Test in a message box. MsgBox "The result is " & Test & "." End Function

'-- In a new worksheet in the same workbook, enter the following formulas:

A1: =Test(4,5) A2: =Test(3,6) A3: =Test(20,20) After you enter each formula, a message box will be displayed. The messages shown are the following:

The result is 20. The result is 18. The result is 200. NOTE: If your Visual Basic function subroutine, includes the line

Application.Volatile a message box will be shown for each cell that uses the subroutine EVERY TIME YOU RECALCULATE THE WORKSHEET. For example, if you insert the Application.Volatile line into your function subroutine and then enter 500 formulas that use that subroutine, each time you recalculate the worksheet, you will receive 500 message boxes. In this case, there is no way to prevent the display of the message boxes as the worksheet is recalculated.

For this reason, it is recommended that you not use the MsgBox function and Application.Volatile in the same function subroutine.