Correlation matrix in VBA

malkhazov

New Member
Joined
Jan 26, 2005
Messages
49
I am trying to write a macro that includes the "correlation" function from the data analysis toolpack.

I have data that is constantly updating in rows (the number of rows changes randomly) and columns (the number of columns changes randomly). I would like to write a macro that eliminates the manual work of having to use the corelation function and resize the rows and columns.

I need it to do the following:

1) resize the number of row
2) resize the number of columns
3) run the correlations

I used the record macro function and the only output I got was:

Application.Run "ATPVBAEN.XLA!Mcorrel", , , "C", False

I cant make any headway with this. I tried using MSFT help and searching this board.

--please advise
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
range name each array(or column) .lt us assume A1 is having a haeding and the data starts from A2 down
then insert-name-denfine
name the range as for e.g. array1(1 is useful because you can increment it in your macro ).
in the refers to window type

=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,1)

similarly array2 for column B data etc.

these are called dynamic range names and if you add or reduce the rows the range name will refer to expanded column

These naming ranges you do in the macro itself

for columns colatality create a variable in the macro e.g. j for the number of the last column e,.g
dim j as integer
j=range("a2").end(xltoright).column

so you can loop the correlations 1 to j

ultimately the correlation in vba will be something like this
range("H1").fomrula="=correl(array1,array2)"
will give the correlation between column A(1) and column B(2)
you have to have loops.

these are some of the ideas you can work on.
 
Upvote 0
I appreciate the try but this does not really address my biggest problem which is how to imbed the "correlation" feature from the data analysis toolpack. This feature allows you automatically build a correlation matrix without having to ever use the CORREL formula.

I think what you are proposing is to basically build my own correlation matrix feature which is something I want to avoid if possible. It seems like a lot of unnecessary work since Excel already has this feature build in.

At this point, I need to figure out if its possible to write a macro using the correlation matrix feature.

--thanks,
 
Upvote 0
tools-datanalysis-correlation
your input range is all the columns
choose suitable cell for out put range
choose "by columns"
check first row label
click ok
see whether what youwantl

see sample sheet below
Book1
ABCDEFGH
1asdfghj
27734289
382313101
46486436
54584939
61537341
791395410
821039836
95413511
1075101958
1134638910
12
13
14
15asdfghj
16a1
17s-0.57591
18d0.06184-0.008981
19f-0.275260.243231-0.3121
20g-0.268760.2462280.575437-0.087421
21h0.266367-0.194058.9E-17-0.46809-0.236841
22j0.2186810.0740850.4857160.1425230.4653680.1477131
23
Sheet1
 
Upvote 0
Right .....I have been using this exact feature in the exact way you described for a while but I have to constantly go through all the manual steps ....what I am looking for is to write VBA code that will do all those steps for me
 
Upvote 0
just create a macro following the steps. you will get something like this

Code:
Application.Run "ATPVBAEN.XLA!Mcorrel", ActiveSheet.Range("$A$1:$F$10"), _
        ActiveSheet.Range("$A$20"), "C", True
    Range("A20").Select

if you want to generalize it use something like this

Code:
Dim rng As Range
Set rng = Range([a1], [a1].End(xlDown).End(xlToRight))
     Application.Run "ATPVBAEN.XLA!Mcorrel", rng, _
        ActiveSheet.Range("$A$20"), "C", True
    Range("A20").Select
 
Upvote 0
When using the analysis toolpack to do something like this, does anyone know if you can switch off the overwriting message?

None of the below work!

Application.DisplayAlerts = False
Application.AlertBeforeOverwriting = False
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,952
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top