Use of array formula

  • Thread starter Thread starter hsPipe
  • Start date Start date
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.
 
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
 
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.
 
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]
 
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...
 
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...
 
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...
 
Back
Top