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

ID: Q111726

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

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 professionals 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/support/supportnet/overview/overview.asp

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 click Add-Ins on the Tools menu).

In a new module, click References on the Tools menu.

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.

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

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.00a 5.00c 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