"Invalid Data Format" with .XLA in Visual Basic Module (111726)

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.