Using worksheet function "TREND" in VBA and assigning returned values to VBA variables?
Carlos TREMBLAY
Bonjour,
I have the following problem:
In my VBA code, I would like to use the worksheet function TREND in my
VBA code and assign whatever the function returns to a scalar or an
array VBA variable.
When I use a worksheet function such as "Application.MIN" that returns a
scalar, it works as expected. It's only when I try to use functions such
as "Application.TREND" or "Application.LINEST" that return several
values at once that I have problems.
Below is the code that refuses to work:
'--------------------------------------------------
Function myTrend() ' NOT WORKING
Dim returnedValues As Variant
Dim myArray as variant
Dim myArg as String
myArg = "A1:D1,,5" 'hard-coded argument for testing purposes
returnedValues = Application.Trend(myArg)
myArray = Array(returnedValues) 'here I am trying to coerce
' the returnedValues into an array
myTrend = myArray(1) 'trying to return the first element of
'the array. I get #NAME? as a result...
End Function
'---------------------------------------------------
Now, if in a worksheet, I put the formula "=TREND(A1:D1,,5)" in a cell,
I get the correct answer. Why can't I retrieve that same return value
using VBA???
Thank you for any help you can offer, I have been breaking my head at
this problem for several days now.
Carlos Tremblay
tremblac/at/fonorola/dot/com
Systems Analyst
Fonorola Inc., Montreal, Quebec
Xingzeng Liu
Your code in calling trend is absolutely correct. If you delete the line
"myArray=array(...)" and direct access the elements of returnedValue, it
will work OK. I think you misuse the array function.
Here the returnedValue is already an array. When you use array function,
the nyArray becomes a array of array. If you like to use in your own
case, you can write in this way:
myTrend=myArray(0)(1)
use 0 or 1 in the first dimension depends if you use 1 0r 0 as your
array base.
Dana DeLouis
I was working the same problem a while ago with Linest.
Although I have not read it, I now believe that you can not put cell
references in the VB macro code of functions like Linest and trend. (any
confirmation from real experts?)
However, if you put actual numeric values in the VB macro, then it will
work though. I hope they fix this in the next release.
The only way I can get functions like Linest to work with VB is to actually
place the function in a cell with the proper cell references. Then you can
read the cells value and continue with the macro.
Although I am not an expert, here are some ideas.
1. Some functions require the use of
"Application.WorksheetFunction".Trend, although I have yet to figure out
when you must use Worksheetfunction. It looks like TREND does not require
it.
2. When entering array formulas, select the area and use .FormulaArray
The following should work:
Sub Macro3()
Dim myArg As String
myArg = "A1:D1,,5" 'hard-coded argument for testing purposes
Range("C5").select
Selection.FormulaArray = "=TREND(" & myArg & ")"
End Sub
---------
When pasting a formula into a cell, adding strings together can be
difficult to understand, and hard to debug. Therefore, I like to use the
Substitute function to make it easier.
Here, you just substitute the String address for y, and the number 5 for
num (or whatever.)
BasicFuncion = "=TREND(y,,num)"
myFunction = BasicFuncion
myRange = Range("A1:D1").address (or selection.address)
myFunction = SUBSTITUTE (myFunction ,"y", myRange)
myFunction = SUBSTITUTE (myFunction ,"num", 5)
Selection.FormulaArray = myFunction
Hope this gives you some ideas. Let me know if this helps.
Carlos TREMBLAY <car...@willy.cs.mcgill.ca> wrote in article
<60gvdj$a...@sifon.cc.mcgill.ca>...
> Bonjour,
>
> I have the following problem:
> In my VBA code, I would like to use the worksheet function TREND in my
> VBA code and assign whatever the function returns to a scalar or an
> array VBA variable.
>
> When I use a worksheet function such as "Application.MIN" that returns a
> scalar, it works as expected. It's only when I try to use functions such
> as "Application.TREND" or "Application.LINEST" that return several
> values at once that I have problems.
>
> Below is the code that refuses to work:
> '--------------------------------------------------
> Function myTrend() ' NOT WORKING
> Dim returnedValues As Variant
> Dim myArray as variant
> Dim myArg as String
> myArg = "A1:D1,,5" 'hard-coded argument for testing purposes
>
> returnedValues = Application.Trend(myArg)
>
> myArray = Array(returnedValues) 'here I am trying to coerce
> ' the returnedValues into an array
>
> myTrend = myArray(1) 'trying to return the first element of
> 'the array. I get #NAME? as a result...
>
> End Function
> '---------------------------------------------------
Carlos Tremblay
Hello Xingzeng and Dana,
I have finally solved my problem. The solution is to not use any direct
values as arguments of the TREND function in VBA but rather to reference
the values through range objects. So, in my original code, the part that
was causing the trouble was the last argument (the number 5). By putting
the number 5 in a cell and then referencing to that cell from the VBA
TREND function, everything worked as expected. So now I can call myTrend
like this in VBA code:
str1 = "A1:D1"
str2 = "A2:D2"
str3 = "E1"
result = myTrend(str1, str2, str3)
where myTrend is implemented in the following manner:
Function myTrend(arg1 as String, arg2 as String, arg3 as String)
Dim returnedValues As Variant
Dim range1 as Range
Dim range2 as Range
Dim range3 as Range
Set range1 = Range(arg1)
Set range2 = Range(arg2)
Set range3 = Range(arg3)
returnedValues = Application.Trend(range1, range2, range3)
myTrend = returnedValues(0) ' return the first element of
'the array.
End Function
----------------------------------------------
Thanks to both of you for your help. I am just discovering what you can
do with Excel and VBA and I must say that I am pretty amazed of how
quickly you can develop non-trivial applications with those tools.
- Carlos Tremblay
---------------------------------------------------------
Original messages follow:
Your code in calling trend is absolutely correct. If you delete the line
"myArray=array(...)" and direct access the elements of returnedValue, it
will work OK. I think you misuse the array function.
Here the returnedValue is already an array. When you use array function,
the nyArray becomes a array of array. If you like to use in your own
case, you can write in this way:
myTrend=myArray(0)(1)
use 0 or 1 in the first dimension depends if you use 1 0r 0 as your
array base.
----------------------------------------------------------