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.

1. 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.

KBCategory: kbusage KBSubcategory: Additional reference words: 5.00 5.00c 7.00 Keywords : kbusage Version : 5.00 5.00c 7.00 | 5.00 Platform : MACINTOSH WINDOWS 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: March 27, 1997 ©1997 Microsoft Corporation. All rights reserved. Legal Notices.