Correlation matrix in VBA

[malkhazov]

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

[venkat1926]

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.

[malkhazov]

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,

[venkat1926]

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

******** ******************** ************************************************************************>

Microsoft Excel - Book1 ___Running: xl2002 XP : OS = Windows XP
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
A1 =
	A	B	C	D	E	F	G	H
1	a	s	d	f	g	h	j	*
2	7	7	3	4	2	8	9	*
3	8	2	3	1	3	10	1	*
4	6	4	8	6	4	3	6	*
5	4	5	8	4	9	3	9	*
6	1	5	3	7	3	4	1	*
7	9	1	3	9	5	4	10	*
8	2	10	3	9	8	3	6	*
9	5	4	1	3	5	1	1	*
10	7	5	10	1	9	5	8	*
11	3	4	6	3	8	9	10	*
12	*	*	*	*	*	*	*	*
13	*	*	*	*	*	*	*	*
14	*	*	*	*	*	*	*	*
15	*	a	s	d	f	g	h	j
16	a	1	*	*	*	*	*	*
17	s	-0.5759	1	*	*	*	*	*
18	d	0.06184	-0.00898	1	*	*	*	*
19	f	-0.27526	0.243231	-0.312	1	*	*	*
20	g	-0.26876	0.246228	0.575437	-0.08742	1	*	*
21	h	0.266367	-0.19405	8.9E-17	-0.46809	-0.23684	1	*
22	j	0.218681	0.074085	0.485716	0.142523	0.465368	0.147713	1
23	*	*	*	*	*	*	*	*
Sheet1 *

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

[malkhazov]

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

[venkat1926]

just create a macro following the steps. you will get something like this

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

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

[Excellor_From_London]

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