?
Solved

Using Data Analysis Add-in (Excel) from VB6

Posted on 2009-02-19
10
Medium Priority
?
367 Views
Last Modified: 2012-05-06
Hi

I am trying to use the Regression analysis tool within the Data Analysis of Excel from VB6.

I have some data that is exported to Excel, and I am using the formulas of RSQ, Slope, INTCPT to get the answers I need, but I also want to add the additional ANOVA table created from the Regression Data Analysis.

I tried this code, but I get no result.

Any suggestions?
objApp.Run "ATPVBAEN.XLA!Regress", objSheet.Range("$b$1:$b" & high), _
        ActiveSheet.Range("a1:a" & high), False, False, , "", True, True, True, True, , False

Open in new window

0
Comment
Question by:Student_101
  • 7
  • 3
10 Comments
 

Author Comment

by:Student_101
ID: 23685435
Another thing I noticed is that,

when my worksheet becomes visible, the Data Analysis option is not there from the tools menu

But when I open a new spread sheet, it is present.

I have both Analysis Tool Pack/VBA enabled
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 23689766
If you are automating Excel, then by default the add-ins will not actually be loaded. You should be able to work around that by using code like that below:
 

With objApp.Addins("ATPVBAEN.XLA")
   .Installed = False
   .Installed = True
End With

Open in new window

0
 

Author Comment

by:Student_101
ID: 23692439
Hi Rorya,

so what you have sent me, is in addition to the code that I have rite?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 85

Expert Comment

by:Rory Archibald
ID: 23692477
Yes - it needs to go into your code before you try and run the add-in code.
0
 

Author Comment

by:Student_101
ID: 23692529
Ok,

I will verify the code and get back to go.

Thanks !
0
 

Author Comment

by:Student_101
ID: 23711261
Hi Rorya,

Sorry for getting back to you so late,

I tried it, and it doesnt work.

The data analysis add-in is also not present. ??
0
 

Author Comment

by:Student_101
ID: 23711286
The error I am getting is "subscript out of range"

This is my code.
With objApp.AddIns("ATPVBAEN.XLA")
   .Installed = False
   .Installed = True
   .Run "ATPVBAEN.XLA!Regress", objSheet.Range("$b$1:$b" & high), _
        objSheet.Range("a1:a" & high), False, False, , "", True, True, True, True, , False
   End With

Open in new window

0
 

Author Comment

by:Student_101
ID: 23711320
What I am trying to achieve is to get the regression analysis data,

so that I can get the standard errors for the slope and Y-intercept.

I have other data such as slope, RSQ, Y-int.

Is there a better way to find out the Standard error in the slope in Y-int?
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 1000 total points
ID: 23711398
Try changing the first line to:
 

With Application.AddIns("Analysis Toolpak - VBA")

Open in new window

0
 

Author Closing Comment

by:Student_101
ID: 31548955
Works Perfectly !

Bravo
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question