XL: "Invalid Data Format" with .XLA in Visual Basic Module

   Invalid Data Format

   Error in Loading DLL

Additional query words: 5.00 5.00a 5.00c 7.00 add in Addins libraries ---------------------------------------------------------------------- The information in this article applies to: - Microsoft Excel for Windows, versions 5.0, 5.0c - Microsoft Excel for Windows 95, versions 7.0, 7.0a - Microsoft Excel for the Macintosh, versions 5.0, 5.0a ---------------------------------------------------------------------- SYMPTOMS In Microsoft Excel, when you attempt to add a reference to an add-in (.XLA), you may receive the following error message: Invalid Data Format In Microsoft Excel for the Macintosh, you may see the following error message: Error in Loading DLL CAUSE You receive this error message if you attempt to add a reference to an add- in file that does not contain a Visual Basic module. If an add-in contains only a Microsoft Excel 4.0 macro (such as ANALYSIS.XLA or Analysis Functions on the Macintosh), it cannot be added as a reference. WORKAROUNDS If you want to use a Microsoft Excel version 4.0 macro function that is contained in a version 4.0 add-in (such as the Analysis ToolPak) in a Visual Basic procedure, do either of the following: - Use the Application.ExecuteExcel4Macro("string") method. The arguments for this method are the name and arguments of an Excel 4.0 macro function (without the equal sign). -or- - If the function you want to use is included in a version 5.0 add-in (such as the Analysis ToolPak - VBA), either choose the References command from the Tools menu while you are in the Visual Basic module or add the add-in that contains the functions. MORE INFORMATION The following examples show you how to use the two methods described above. These examples demonstrate how to create a macro that displays the ANOVA dialog box (an Analysis ToolPak macro). Application.ExecuteExcel4Macro("string") Method ----------------------------------------------- In order to run this macro, the version 4.0 add-in must be installed. To install an add-in, choose Add-Ins from the Tools menu. Microsoft provides programming examples 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 article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web: http://www.microsoft.com/supportnet/refguide/ - To use the function in the add-in, enter the following commands in a new module: Sub AnovaMacro1() Application.ExecuteExcel4Macro("ANOVA1?()") End Sub Choosing the References Command from the Tools Menu --------------------------------------------------- In order to run this macro, the Visual Basic version of the Analysis ToolPak must be among the add-ins available (to do this choose Add-Ins from the Tools menu). 1. In a new module, click References from the Tools menu. 2. Click the Browse button. Click the file ATPVBAEN.XLA, which is located in the EXCEL\LIBRARY\ANALYSIS directory, and then click OK. -or- In Microsoft Excel for the Macintosh, click the file Analysis ToolPak - VBA, which is located in the Microsoft Excel:Macro Library:Analysis Tools folder, and then click OK. 3. ATPVBAEN.XLA (or Analysis ToolPak - VBA) will now be displayed in the Available References list and its check box will be selected. Before you run the AnovaMacro2 macro, verify that the check box next to ATPVBAEN.XLA (or Analysis ToolPak - VBA) is selected 4. Enter the following code in the module: Sub AnovaMacro2() Anova1Q End Sub NOTE: When you use the second method, you use the same syntax as you would in a Microsoft Excel 4.0-style macro, with one exception: instead of a question mark, add a Q to the end of the name of the macro function. REFERENCES For more information about working with Microsoft Excel 4.0 macro functions in Microsoft Excel 5.0 or 7.0, choose Reference Information in the Help Contents window, and then choose Microsoft Excel Macro Functions Contents. In the Microsoft Excel Macro Functions Contents window, choose Visual Basic Equivalents for Functions and Commands and then choose More About Converting Microsoft Excel Version 4.0 Macros To Visual Basic procedures. Additional query words: 5.00 5.00a 5.00c 7.00 add in Addins libraries Keywords : kbcode kberrmsg Version : MACINTOSH:5.0,5.0a; WINDOWS:5.0,5.0c,7.0,7.0a Platform : MACINTOSH WINDOWS Issue type : kbprb Solution Type : kbworkaround 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: February 2, 1998 © 1998 Microsoft Corporation. All rights reserved. Terms of Use.