Unable to get the correl property of the worksheet function class

[SBF12345]

I am having trouble with the line below

DistanceMeasure = (1 - Application.WorksheetFunction.Correl(Application.WorksheetFunction.Index(X, 0, 2), Application.WorksheetFunction.Index(TempSample, 0, 2))) * 100

X and TempSample are both arrays with an equal number of elements. Am I using "application.worksheetfunction.index" correctly? I am trying to return the entire column in the array, the second column.

Ideas?

 

[Norie]

The values in ArrTemp are numbers and there are no errors in the series.

Have you checked all the values in ArrTemp in the Locals Window?

 

[SBF12345]

when I run the test code against the worksheet correl function I get good numbers .35 and 65 for distance measure (1- correl) and the multiplier

 

[SBF12345]

When I open the locals window I see ArrTemp as Empty. Following the ArrTemp variant back a bit I look at NumObs and ii, which both contribute to the variant PriceData.

NumObs is valued at 2 at the line:

NumObs = UBound(X, 2)

which I'm not sure I understand. I would expect a 12 to be the NumObs value because the locals window says X is "variant/variant(1 to 12, 1 to 2)

Why is NumObs returning a 2?

 

[Norie]

UBound(X, 2) will return the upper bound of the 2nd dimension of X.

 

[SBF12345]

The locals window reads X as type variant/variant(1 to 12, 1 to 2), shouldn't it be returning a 12?

 

[SBF12345]

A is also returning a 2 on line

A = UBound(PriceData,2)

 

[SBF12345]

I rewrote the lines defining A and NumObs to (changing the 2 to a 1)

Dim A As Integer:                   A = UBound(PriceData, 1)     ' A is the population size and contains full set of test setsDim NumObs As Integer:              NumObs = UBound(X, 1)        ' NumObs is the sample size

why would I receive different values for A and NumObs when the array data is 2 columns wide by 12 rows (in the case of NumObs) and 2 columns by 2800 rows(in the case of A).

After running here the code gets hung up on line and reads "out of range"

TempSample(rr, 1) = PriceData(ii - rr + 1, 1)

I also tested the first two lines using:

A = UBound(PriceData,0)
NumObs = UBound(X,0)

When I tried this the locals window gave no values for A or NumObs

What it looks like is that the initial array defined by lines:

Dim PriceData As Variant:           PriceData = Worksheets(DataSht).Range(SampleRange)

and

Dim X As Variant:                   X = Worksheets(DataSht).Range(DataRange)

isn't recognizing a second dimension despite the locals window reading "variant/variant(1 to a number, 1 to 2)"

 

[Norie]

The locals window reads X as type variant/variant(1 to 12, 1 to 2), shouldn't it be returning a 12?

No, the first dimension (1 to 12) of X has a lower bound of 1 and upper bound of 12, the second dimension (1 to 2) of X has a lower bound of 1 and upper bound of 12.

UBound(X, 2) returns the upper bound of the second dimension of X.

The second argument of UBound determines the dimension for which you want the upper bound.

 

[SBF12345]

the second dimension (1 to 2) of X has a lower bound of 1 and upper bound of 12.

Is there a type-o in this line? because when I run the code with Ubound(X,2) and Ubound(PriceData,2) I receive NumObs = 2 and A = 2. When I run the code with UBound(X,1) and UBound(PriceData,1) it returns A = 2801 and NumObs = 12. It looks like the UBound function is returning not the number of elements in the second dimension but the upper bound of the number of columns in X or PriceData.

 

[Norie]

Yes there is a typo, it should read like this.

the second dimension (1 to 2) of X has a lower bound of 1 and upper bound of 2.

Note, UBound does not return the no of elements in an array.