Syntax in worksheet functions for individual values instead of ranges
 

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

Syntax in worksheet functions for individual values instead of ranges
 

On Sat, 8 Oct 2011 12:39:37 -0700 (PDT), REM wrote:

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)

Syntax in worksheet functions for individual values instead of ranges
 

On Sat, 8 Oct 2011 12:39:37 -0700 (PDT), REM wrote:

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)

Syntax in worksheet functions for individual values instead of ranges
 

"REM" wrote:
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.

REM wrote:
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))


REM wrote:
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))

Syntax in worksheet functions for individual values instead of ranges
 

I wrote:
[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).

Syntax in worksheet functions for individual values instead of ranges
 

PS.... I wrote:
[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

Syntax in worksheet functions for individual values instead of ranges
 

PS2.... I wrote:
"joeu2004" wrote in message
...
REM wrote:
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)

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.

Syntax in worksheet functions for individual values instead of ranges
 

On Oct 8, 9:44*pm, "joeu2004" wrote:
PS2.... *I wrote:
"joeu2004" wrote in message

...

REM wrote:
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)

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