Problems with application.worksheetfunction.correl

[TFriis]

Using VBA, I populated a two dimensional array with values. I want use
various "rows" and "columns" of the array as inputs into an Excel
function. For example:

Dim arr As Variant
Dim answer as Double

arr = Range("A1:B10").Value
answer =
application.worksheetfunction.correl(arr(column1),arr(column2))

something that would have the same result as:
answer =
application.worksheetfunction.correl(range("A1:A10"),range("B1:B10"))

I'd prefer not to paste the array back to a sheet to run the
calculation.

I'd prefer not to populate one dimensional arrays based on the columns
of the two dimensional array.

Basically, I'm looking to easily address subsets/subranges of a multi-
dimensioned array.

 

[SteveM]

Your code looks all messed up. For one thing, you did not declare arr
as an Array or set its dimensions. The .Value line makes no sense
because the Value property maps to a single cell. The workbook Correl
function takes ranges of values. I'm not sure it would work with VB
arrays. I think you have to take out the VBA book first. BTW, see
the Option Base 1 global option if you are using arrays. BTW2, If you
intend on populating your arrays from ranges, why wouldn't you just
use the ranges directly in the function?

SteveM

 

[Jim Cone]

Application.Index(arr, 0, 1) 'returns the 1st column of the array
Application.Index(arr, 0, 2) 'returns the 2nd column of the array
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


"TFriis"wrote in message
Using VBA, I populated a two dimensional array with values. I want use
various "rows" and "columns" of the array as inputs into an Excel
function. For example:

Dim arr As Variant
Dim answer as Double

arr = Range("A1:B10").Value
answer =
application.worksheetfunction.correl(arr(column1),arr(column2))

something that would have the same result as:
answer =
application.worksheetfunction.correl(range("A1:A10"),range("B1:B10"))

I'd prefer not to paste the array back to a sheet to run the
calculation.

I'd prefer not to populate one dimensional arrays based on the columns
of the two dimensional array.

Basically, I'm looking to easily address subsets/subranges of a multi-
dimensioned array.

 

[TFriis]

Your code looks all messed up. For one thing, you did not declare arr
as an Array or set its dimensions. The .Value line makes no sense
because the Value property maps to a single cell. The workbook Correl
function takes ranges of values. I'm not sure it would work with VB
arrays. I think you have to take out the VBA book first. BTW, see
the Option Base 1 global option if you are using arrays. BTW2, If you
intend on populating your arrays from ranges, why wouldn't you just
use the ranges directly in the function?

SteveM









- Show quoted text -

It was quick psudo-code - the real code is much more complicated!

But the problem is, that the code being something like:

TempResultArray(rk, k) =
WorksheetFunction.Correl(Range(Sheets("Data").Range("A10").Cells(rk -
KorrTime + 1, 2 * i), Sheets("Data").Range("A10").Cells(rk, 2 * i)),
Range(Sheets("Data").Range("A10").Cells(rk - KorrTime + 1, j * 2),
Sheets("Data").Range("A10").Cells(rk, j * 2)))

This code loops through a lot af data in the sheets and calculates the
correlation - but I have made a lot of calculation changes - so I'd
prefere that I could use an Array with the same specifications. - If
that made any sense?