Syntax in worksheet functions for individual values instead of ranges

[REM]

I am trying to use the TREND function in a macro but cannot work out
the syntax. I only need a single value result and can get the
following to put the correct value into the active cell:
ActiveCell.FormulaR1C1 = "=TREND({5;10},{10;20},15)"

But dont know how to evaluate this to a variable, without using a
cell.
Ideally I would like the numbers to be variables also.

Any help gratefully received.

Rod

 

[Ron Rosenfeld]

I am trying to use the TREND function in a macro but cannot work out
the syntax. I only need a single value result and can get the
following to put the correct value into the active cell:
ActiveCell.FormulaR1C1 = "=TREND({5;10},{10;20},15)"

But dont know how to evaluate this to a variable, without using a
cell.
Ideally I would like the numbers to be variables also.

Any help gratefully received.

Rod

I'm not sure exactly what you want, but perhaps:

Dim v As Variant
v = Evaluate("=TREND({5;10},{10;20},15)")
Debug.Print v(1)

 

[Ron Rosenfeld]

I am trying to use the TREND function in a macro but cannot work out
the syntax. I only need a single value result and can get the
following to put the correct value into the active cell:
ActiveCell.FormulaR1C1 = "=TREND({5;10},{10;20},15)"

But dont know how to evaluate this to a variable, without using a
cell.
Ideally I would like the numbers to be variables also.

Any help gratefully received.

Rod

Another approach:

Dim v As Variant
v = Application.WorksheetFunction.Trend(Array(5, 10), Array(10, 20), Array(15))
Debug.Print v(1)

 

[joeu2004]

I am trying to use the TREND function in a macro but cannot
work out the syntax. [....]
ActiveCell.FormulaR1C1 = "=TREND({5;10},{10;20},15)"

Works fine for me in XL2003 with VBA 6.5.

That is, when an cell is indeed selected. I wonder if you selected another
object (e.g. a chart) at the time you executed that line in the macro.

dont know how to evaluate this to a variable,
without using a cell.

y = Evaluate("=TREND({5;10},{10;20},15)")

or [1]:

y = WorksheetFunction.Forecast(15, Array(5,10), Array(10,20))

Ideally I would like the numbers to be variables also.

knownY = Array(5,10)
knownX = Array(10,20)
x = 15
y = WorksheetFunction.Forecast(x,knownY,knownX)

-----
[1] I don't know why one of the following does not work. I don't care
because I would use FORECAST for this purpose instead of TREND anyway.

y = WorksheetFunction.Trend(Array(5,10), Array(10,20), 15)
y = WorksheetFunction.Trend(Array(5,10), Array(10,20), Array15))

 

[joeu2004]

[1] I don't know why one of the following does not work. I don't care
because I would use FORECAST for this purpose instead of TREND anyway.

y = WorksheetFunction.Trend(Array(5,10), Array(10,20), 15)
y = WorksheetFunction.Trend(Array(5,10), Array(10,20), Array15))

Aha! Looking at Ron's response, I know now why they did not work.

The first does not work because apparently WorksheetFunction.Trend requires
that the 3rd parameter be an array. I had guessed as much, which is why I
tried the second form.

The second form actually did work. I did not realize that the error I got
was not on that statement, but on my next statement: Msgbox y.

That did not work because apparently WorksheetFunction.Trend returns an
array. So I should have written Msgbox y(1), as Ron did.

All the more reason to use FORECAST and WorksheetFunction.Forecast for this
purpose (returning a single value).

 

[joeu2004]

[1] I don't know why one of the following does not work. I don't care
because I would use FORECAST for this purpose instead of TREND anyway.

y = WorksheetFunction.Trend(Array(5,10), Array(10,20), 15)
y = WorksheetFunction.Trend(Array(5,10), Array(10,20), Array15))

Aha! Looking at Ron's response, I know now why they did not work. [....]
The second form actually did work. I did not realize that the error I got
was not on that statement, but on my next statement: Msgbox y.

Likewise, if you use:

y = Evaluate("=TREND({5;10},{10;20},15)")

you need to reference y(1) for the single value.

Again, I would prefer FORECAST to return a single value, to wit:

y = Evaluate("=FORECAST(15,{5;10},{10;20})")
MsgBox y

 

[joeu2004]

PS2.... I wrote:

knownY = Array(5,10)
knownX = Array(10,20)
x = 15
y = WorksheetFunction.Forecast(x,knownY,knownX)

Or:

y = WorksheetFunction.Forecast(x,Array(y1,y2),Array(x1,x2))
MsgBox y

If you still cannot figure it, provide the relevant VBA context --
declaration of variables and code fragment showing how those variables are
set up.

 

[REM]

PS2....  I wrote:




Or:

y = WorksheetFunction.Forecast(x,Array(y1,y2),Array(x1,x2))
MsgBox y

If you still cannot figure it, provide the relevant VBA context --  
declaration of variables and code fragment showing how those variables are
set up.

These options work for me.
Many thanks to all.
Rod