VBA: Using defined Arrays/Ranges within Loop

Question

I am pretty new to VBA hence my current struggle and clumsy code. Within my code I am trying identify uncorrelated stocks for a study project. The idea is that my macro generates a correlation matrix, bounded the Stock Names and then loops through line by line to return the most "uncorrelated" stock for the underlying sample. However, I do not currently know whether there is just a referencing issue in my code or whether there is any fundamental issue. Any help would be much appreciated

Find below the relevant part of the script:

Sub Test()

Dim Names As Variant

Dim Ref As Variant

Set Names = Worksheets("Correlation Matrix").Range(Cells(1, 3), Cells(1, Stocks))

For i = 1 To Stocks

    Worksheets("Correlation Matrix").Activate

        Ref = Worksheets("Correlation Matrix").Range(Cells(i + 1, 3), Cells(i + 1, Stocks)).Value

    Worksheets("Main").Activate

        Worksheets("Main").Cells("O" & i + 4).FormulaArray = "=Index(" & Names.Address & ",MATCH(MIN(ABS(" & Ref.Address & " - 0),ABS(" & Names.Address & "- 0),0))"

Next i

End Sub

The "Correlation Matrix" worksheet looks like this:

           Disney        Microsoft     Apple         sp500 
Disney     1             0.764790855   0.737566223   0.832602399 
Microsoft  0.764790855   1             0.754724823   0.827980429
Apple      0.737566223   0.754724823   1             0.90982066
sp500      0.832602399   0.827980429   0.90982066    1

Hi Tomalak, Stocks is defined in an earlier section of the code. Its just the number of stocks within my sample — Maxime Mohnen
– Maxime Mohnen, Commented Apr 15, 2020 at 16:07
It would also help if you showed the layout of the "Correlation Matrix" worksheet. — Tomalak
– Tomalak, Commented Apr 15, 2020 at 16:09
Edit things like this into the question, just like I did right now! Stuff like this does not work well in the comment section. — Tomalak
– Tomalak, Commented Apr 15, 2020 at 16:16

Tomalak · Accepted Answer · 2020-04-15 17:12:51Z

Consider this approach:

In the correlation matrix, we only need to look at the numbers either "above" or "below" the diagonal line of "1"s. All other values are just a mirror image, looking at them would be a waste of time.

So we try to look at a triangle of coordinates:

        
   A (1)      B (2)         C (3)         D (4)         E (5)
1             Disney        Microsoft     Apple         sp500 
2  Disney     1             0.764790855   0.737566223   0.832602399 
3  Microsoft  0.764790855   1             0.754724823   0.827980429
4  Apple      0.737566223   0.754724823   1             0.90982066
5  sp500      0.832602399   0.827980429   0.90982066    1

Excel also counts columns from 1, that's what the numbers in the parenthesis are. The row-column coordinates we want to inspect could be the "lower" half, like this (the "x" marks the diagonal cells with value 1):

        
  A (1)      B (2)         C (3)         D (4)         E (5)
1
2            x
3            (3,2)         x
4            (4,2)         (4,3)         x
5            (5,2)         (5,3)         (5,4)         x

A nested loop can create those coordinate pairs. The loop runs for rows 3..5 and columns 2..4, respectively, but it excludes any columns outside of the "triangle":

Sub LeastCorrelated(NumStocks As Integer)
  Dim corrMatrix As Worksheet
  Dim minimumCell As Range, cell As Range
  Dim r As Integer, c As Integer
  Dim stock1 As String, stock2 As String

  Set corrMatrix = Worksheets("Correlation Matrix")
  Set minimumCell = corrMatrix.Cells(2, 2) ' the top-left cell containing "1"

  For r = 3 To NumStocks + 1
    For c = 2 To r - 1
      Set cell = corrMatrix.Cells(r, c)
      If cell.Value < minimumCell.Value Then Set minimumCell = cell
    Next
  Next

  minimumCell.Select
  stock1 = corrMatrix.Cells(1, minimumCell.Column).Value
  stock2 = corrMatrix.Cells(minimumCell.Row, 1).Value

  Debug.Print stock1 & " / " & stock2
End Sub

When called as

LeastCorrelated 4

this prints

Disney / Apple

and selects cell A4.

Scott Holtzman · Accepted Answer · 2020-04-15 16:07:08Z

0

Dimension the variables as Range objects. Variant is a catch all data type and does not have a .Name property.

See below. I also changed Names variable since .Names refers to named ranges.

Sub Test()

Dim theNames As Range
Dim Ref As Range

Set theNames = Worksheets("Correlation Matrix").Range(Cells(1, 3), Cells(1, Stocks))

For i = 1 To Stocks

    'Worksheets("Correlation Matrix").Activate - no need to activate sheet

    Set Ref = Worksheets("Correlation Matrix").Range(Cells(i + 1, 3), Cells(i + 1, Stocks)).Value

    'Worksheets("Main").Activate - no need to activate sheet

    Worksheets("Main").Cells("O" & i + 4).FormulaArray = "=Index(" & theNames.Address & ",MATCH(MIN(ABS(" & Ref.Address & " - 0),ABS(" & theNames.Address & "- 0),0))"

Next i

End Sub

answered Apr 15, 2020 at 16:07

Scott Holtzman

27.3k5 gold badges42 silver badges76 bronze badges

4 Comments

BigBen Over a year ago

Note that Set Ref = Worksheets("Correlation Matrix").Range(Cells(i + 1, 3), Cells(i + 1, Stocks)).Value... is an Range(Cells, Cells) issue.

Maxime Mohnen Over a year ago

Hi Scott, tried to implement the suggested changes, but it does not seem to work unfortunately

Scott Holtzman Over a year ago

@BigBen - hit the nail on the head that I missed in my original reply. Qualify the Cells to the worksheet object they belong too.

Maxime Mohnen Over a year ago

Thanks a lot for the suggested changes. Reran the macro and both Names ad Ref are correclty generated; the issue seems to be somewhere within my inserted arrayformula: Worksheets("Main").Cells("O" & i + 4).FormulaArray = "=Index(" & theNames.Address & ",MATCH(MIN(ABS(" & Ref.Address & " - 0),ABS(" & theNames.Address & "- 0),0))"

Collectives™ on Stack Overflow

VBA: Using defined Arrays/Ranges within Loop

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related