Macro to run Descriptive Statistics

Question

Good morning,

I am trying to create a macro to run Descriptive Statistics from the Data Analysis Tool-pack

The add-in is active in the workbook.

The code I used is:

Application.Run "ATPVBAEN.XLAM!Descr", , , "C", False, True, 1, 1, 95

I receive the error message:

Run time error '1004' Method 'Run' of object'-Application' failed

When I de-bug

I get either

Cant' execute code in break mode or 'C:\Users\Al\Desktop\ATPVBAEN.XLAM cannot be found

How do I do this?

Thanks,

Al

Answer 1

You need to enable red zone Add-in to run it though VBA...

Edit - Also - After adding it, record a macro through VBA to confirm if  VBA statement is allright as when I record, it gives following statement which means that Input Range which A1:A8 is missing in your statement.

     Application.Run "ATPVBAEN.XLAM!Descr", ActiveSheet.Range("$A$1:$A$8"), "" _

        , "C", False, True, 1, 1, 95

Answer 2

Thank you sir for replying to my question but even after adding the input range I am still getting the error message 'C:\Users\Al\Desktop\ATPVBAEN.XLAM cannot be found.

I do have the Analysis Toolpak enabled.

I did record my macro as follows:

I clicked on Analysis Toolpak and selected Descriptive Statistics

I entered the information in the dialog box and filled in the information.

I received the results. Everything was fine during the recording and execution.

But when I try to run the macro again, even after adding the range location I still get the error 'C:\Users\Al\Desktop\ATPVBAEN.XLAM cannot be found.

Any thoughts?

Respectfully,

Al

Answer 3

I was talking about "Analysis Toolpack - VBA" not "Analysis Toolpack"..Both should be active..

Can you post the screenshot of your active add-ins?

Answer 4

Hello Mr. Verma,

Thank you for your continuing support.

Here is the screenshot you requested as well as an image of the Descriptive Statistics dialog-box with my inputs.

I hope this helps,

Sincerely,

Al

Answer 5

Try disabling both Add-ins and enabling them again. Restart Excel and try again.