Syntax in worksheet functions for individual values instead of ranges

R

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
 
R

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)
 
R

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)
 
J

joeu2004

REM said:
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))
 
J

joeu2004

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

joeu2004

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

joeu2004

PS2.... I wrote:
joeu2004 said:
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.
 
R

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
 

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