Use of array formula

H

hsPipe

I would like to use the worksheetfunction.linEst function in excel VBA, but
need help on how to identify that the function returns an array rather than a
single value. Thank you.
 
B

Bob Umlas

By selecting several cells first, like 5 rows x 2 columns, for example.
Enter the LINEST function, press ctrl/shift/enter, see different results in
the cells.
There's not a way to inherently know if a function should be entered as an
array formula without using help (excel's or other folks' or newsgroups, or
trial & error --)
HTH
Bob Umlas
Excel MVP
 
H

hsPipe

Thanks, bob. However, my problem is I am trying to use the Linest function
in Excel VBA, and how to identify in the VBA code that the call to Linest is
an array formula.
 
C

Chip Pearson

You can store the array result of LINEST in a Variant (the single
Variant will contain an array of Doubles). You can put that array to a
range of cells or directly access the elements of the array:

Dim Res As Variant
Dim Dest As Range

Set Dest = Range("L1:M5")
Res = Application.WorksheetFunction.LinEst( _
Range("A2:A10"), Range("B2:B10"), True, True)
' put to worksheet
Dest = Res

' access array directly
Dim R As Long
Dim C As Long
For R = 1 To 5
For C = 1 To 2
Debug.Print Res(R, C)
Next C
Next R

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]
 
H

hsPipe

Thank you. That is what I am looking for.
One additional basic VBA question. I have stored the x and y data from the
spread sheet in variable arrays sXValue() and sYValue(), and I want to do
"Least Square Fitting" calculations using the LinEst function for different
ranges of x-y points within the sXValue and sYValue arrays. How do I achieve
that in Excel VBA?
--
Ho-Shu


Chip Pearson said:
You can store the array result of LINEST in a Variant (the single
Variant will contain an array of Doubles). You can put that array to a
range of cells or directly access the elements of the array:

Dim Res As Variant
Dim Dest As Range

Set Dest = Range("L1:M5")
Res = Application.WorksheetFunction.LinEst( _
Range("A2:A10"), Range("B2:B10"), True, True)
' put to worksheet
Dest = Res

' access array directly
Dim R As Long
Dim C As Long
For R = 1 To 5
For C = 1 To 2
Debug.Print Res(R, C)
Next C
Next R

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]




I would like to use the worksheetfunction.linEst function in excel VBA, but
need help on how to identify that the function returns an array rather than a
single value. Thank you.
Click to expand...
.
Click to expand...
 
M

Mike Middleton

Ho-Shu or hsPipe -

One way is to DIM two new arrays, write a loop to examine each point,
depending on your criteria add the old point to the new arrays, and learn
about REDIM.

- Mike
http://www.MikeMiddleton.com


Thank you. That is what I am looking for.
One additional basic VBA question. I have stored the x and y data from the
spread sheet in variable arrays sXValue() and sYValue(), and I want to do
"Least Square Fitting" calculations using the LinEst function for different
ranges of x-y points within the sXValue and sYValue arrays. How do I
achieve
that in Excel VBA?
--
Ho-Shu


Chip Pearson said:
You can store the array result of LINEST in a Variant (the single
Variant will contain an array of Doubles). You can put that array to a
range of cells or directly access the elements of the array:

Dim Res As Variant
Dim Dest As Range

Set Dest = Range("L1:M5")
Res = Application.WorksheetFunction.LinEst( _
Range("A2:A10"), Range("B2:B10"), True, True)
' put to worksheet
Dest = Res

' access array directly
Dim R As Long
Dim C As Long
For R = 1 To 5
For C = 1 To 2
Debug.Print Res(R, C)
Next C
Next R

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]




I would like to use the worksheetfunction.linEst function in excel VBA,
but
need help on how to identify that the function returns an array rather
than a
single value. Thank you.
Click to expand...
.
Click to expand...
 
H

hsPipe

Thank you. I was hoping there is a more elegant solutio, such as the use of
range for variable arrays?
--
Ho-Shu


Mike Middleton said:
Ho-Shu or hsPipe -

One way is to DIM two new arrays, write a loop to examine each point,
depending on your criteria add the old point to the new arrays, and learn
about REDIM.

- Mike
http://www.MikeMiddleton.com


Thank you. That is what I am looking for.
One additional basic VBA question. I have stored the x and y data from the
spread sheet in variable arrays sXValue() and sYValue(), and I want to do
"Least Square Fitting" calculations using the LinEst function for different
ranges of x-y points within the sXValue and sYValue arrays. How do I
achieve
that in Excel VBA?
--
Ho-Shu


Chip Pearson said:
You can store the array result of LINEST in a Variant (the single
Variant will contain an array of Doubles). You can put that array to a
range of cells or directly access the elements of the array:

Dim Res As Variant
Dim Dest As Range

Set Dest = Range("L1:M5")
Res = Application.WorksheetFunction.LinEst( _
Range("A2:A10"), Range("B2:B10"), True, True)
' put to worksheet
Dest = Res

' access array directly
Dim R As Long
Dim C As Long
For R = 1 To 5
For C = 1 To 2
Debug.Print Res(R, C)
Next C
Next R

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]




I would like to use the worksheetfunction.linEst function in excel VBA,
but
need help on how to identify that the function returns an array rather
than a
single value. Thank you.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Top