Getting output from the TREND function using VBA


S

stanleykorn

The TREND function has the syntax TREND(known_y's,known_x's,new_x's,const).
In order to output the array of estimated y values on a spreadsheet, the
Excel Help entry specifies that a range must be selected beginning with the
cell in which the TREND function is entered and extending downward so that
the number of cells in the range is equal to the number of estimated y
values. The user is then instructed to press F2, and then press
CTRL+SHIFT+ENTER, causing the estimated y values to populate the selected
range. That method works fine for outputting the estimated y values on a
spreadsheet, but how do I output the estimated y values as an array within a
VBA subprogram?
 
Ad

Advertisements

P

Peter T

arrNewY = Application.WorksheetFunction.Trend(knownY, knownX, newXs, const)

knownY, knownX and newXs can refer to cells or arrays made in VBA

arrNewY is an array, even if newXs is a single value, with lower-bound of 1
and qty of elements same as newXs

Regards,
Peter T
 
S

stanleykorn

Peter,

I tried using that method. I entered the following statements in the
subprogram:

Dim ArrNewY(2 To 12) As Single
ArrNewY = Application.WorksheetFunction.Trend(E2:E12,A2:D12)

After entering the second statement, I got the following compile error:

Expected: list separator or )

with the colon between the E2 and E12 highlighted.

The same input to the TREND function worked perfectly fine when I used the
TREND function directly on the worksheet.
 
Ad

Advertisements

P

Peter T

change this
Dim ArrNewY(2 To 12) As Single
ArrNewY = Application.WorksheetFunction.Trend(E2:E12,A2:D12)
to

Dim ArrNewY as Variant ' it will become an array later
dim rngX as range, rngY as range
Dim newXs ' as Range or As Variant

Set rngX = actrivesheet.range("A2:D12")
Set rngY = actrivesheet.range("E2:E12")
Set newXs = some-range
or
newXs = array(1.5, 3.5,.....)

ArrNewY = Application.WorksheetFunction.Trend(rngY,rngX,newXs )

if the const is ommited the default is True

Regards,
Peter T
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top