Microsoft KB Archive/111726

= "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 </li></ol>

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.

<div class="references_section">