Microsoft KB Archive/107879

XL: Using Worksheet Functions in Visual Basic Procedures Article ID: Q107879 Revision Date: 13-SEP-1996

The information in this article applies to :

• Microsoft Excel for Windows version 5.0
• Microsoft Excel for Windows 95, version 7.0
• Microsoft Excel for the Macintosh, version 5.0

SUMMARY Microsoft Excel versions listed above offer two programming languages: Visual Basic and the Microsoft Excel version 4.0 macro language. Having two different programming languages can be confusing, especially when both languages have nearly identical functionality. Microsoft included both languages to ease the transition to Visual Basic. Both languages give you complete control of Microsoft Excel. However, you should start working with Visual Basic, because it is more flexible and powerful and will be the only programming language in future versions of Microsoft Excel. In Microsoft Excel versions 5.0 and later, you can combine Visual Basic and Microsoft Excel version 4.0-style macros: you can run your existing version 4.0 macros from Visual Basic procedures or, in cases where the same function is available in Visual Basic AND in the Microsoft Excel 4.0 macro language, you can specify whether you want to use the Visual Basic or Microsoft Excel 4.0 version of that function.

MORE INFORMATION Visual Basic and Microsoft Excel version 5.0 worksheet functions offer common functions such as the ABS() function. To make upgrading easier, Microsoft Excel lets you specify which version of a worksheet function you want to use in your Visual Basic procedure, either Visual Basic or Microsoft Excel. In most cases, there is no difference between the two versions of the same function.

Example To include the Visual Basic version of a worksheet function in your procedure, use the following syntax:

x = Abs(-1)

If you want to use the Microsoft Excel version of a worksheet function in your procedure, you need to precede the function name with the Application object name:

x = Application.Abs(-1)

To run a Microsoft Excel version 4.0-style macro from your Visual Basic procedure, use the Run method of the Application object. The Run method takes as its arguments the name and arguments of the macro you want to run:

Application.Run(Macro_name, Arg1, Arg2, …)

Two functions that are not the same in Visual Basic and the Microsoft Excel 4.0 macro language the LOG() function and the IF() function. Because IF is a reserved keyword in the Visual Basic language, you cannot use the Microsoft Excel IF() function in your procedures. You are not losing any functionality, though, because the Visual Basic keyword IF performs the same logical test as the Microsoft Excel IF() function. The Microsoft Excel LOG function returns the logarithm of a number to the base that you specify. The Visual Basic Log function returns the natural logarithm of a specified number. For more information about using worksheet functions and the Microsoft Excel 4.0 macro language in 5.0, see pages 297-299 of the “Visual Basic User’s Guide.”

KBCategory: kbusage KBSubcategory:

Additional reference words: 7.00 5.00

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.

Copyright Microsoft Corporation 1996.