Results 1 to 7 of 7

Correlation matrix in VBA

This is a discussion on Correlation matrix in VBA within the Excel Questions forums, part of the Question Forums category; I am trying to write a macro that includes the "correlation" function from the data analysis toolpack. I have data ...

  1. #1
    New Member
    Join Date
    Jan 2005
    Posts
    49

    Default Correlation matrix in VBA

    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

  2. #2
    Board Regular
    Join Date
    Aug 2005
    Posts
    4,571

    Default

    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.
    I am not an expert. So better solutions may be available
    MinE WINDOWS 7 AND excel 2007(compatbililty mode)
    venkat1926(at)gmail(dot)com
    preferably do not send private messages in the newsgroup reply to newsgroup

  3. #3
    New Member
    Join Date
    Jan 2005
    Posts
    49

    Default

    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,

  4. #4
    Board Regular
    Join Date
    Aug 2005
    Posts
    4,571

    Default

    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
    =

    A
    B
    C
    D
    E
    F
    G
    H
    1
    asdfghj*
    2
    7734289*
    3
    82313101*
    4
    6486436*
    5
    4584939*
    6
    1537341*
    7
    91395410*
    8
    21039836*
    9
    5413511*
    10
    75101958*
    11
    34638910*
    12
    ********
    13
    ********
    14
    ********
    15
    *asdfghj
    16
    a1******
    17
    s-0.57591*****
    18
    d0.06184-0.008981****
    19
    f-0.275260.243231-0.3121***
    20
    g-0.268760.2462280.575437-0.087421**
    21
    h0.266367-0.194058.9E-17-0.46809-0.236841*
    22
    j0.2186810.0740850.4857160.1425230.4653680.1477131
    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.
    I am not an expert. So better solutions may be available
    MinE WINDOWS 7 AND excel 2007(compatbililty mode)
    venkat1926(at)gmail(dot)com
    preferably do not send private messages in the newsgroup reply to newsgroup

  5. #5
    New Member
    Join Date
    Jan 2005
    Posts
    49

    Default

    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

  6. #6
    Board Regular
    Join Date
    Aug 2005
    Posts
    4,571

    Default

    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
    I am not an expert. So better solutions may be available
    MinE WINDOWS 7 AND excel 2007(compatbililty mode)
    venkat1926(at)gmail(dot)com
    preferably do not send private messages in the newsgroup reply to newsgroup

  7. #7
    Board Regular
    Join Date
    Aug 2007
    Location
    London
    Posts
    50

    Default Re: Correlation matrix in VBA

    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

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com