Knowledge Base

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

Article ID: 111726

Article Last Modified on 10/10/2006

APPLIES TO

Microsoft Excel 95a
Microsoft Excel 5.0c
Microsoft Excel 5.0a for Macintosh

This article was previously published under Q111726

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. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to 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 requirements.

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 XL

Keywords: kbcode kbdtacode kberrmsg kbprb KB111726