Help with Excel Function Call 1

[penndro]

Hi,

I could use some help in determining why my TREND formula in Vba brings a differnt result than the EXCEL trend formula. I use the same parameters:

(y,x, new x)

I use a High and Low threshold value to compare to the Payouts at those threshold levels:

Arg1(1) Arg2(1)
Arg1(2) Arg2(2)
UpAch/LowAch UpPay/LowPay
218,090,462.00 12,974.99
174,472,369.60 3,243.75

Performance 194,558,975.00
Performance % 89.2%
Actual Payout 7,725


Excel returns $7,725
Access Returns $8,451.80 (which i can recreate this amount if I added another row of variables to excel @ 0).

Here is my VBA Trend Function:

Function NewTrend(Payout1 As Double, Payout2 As Double, Achieve1 As Double, Achieve2 As Double, performance As Double) As Double
On Error GoTo Err_Trend
Dim objExcel As Excel.Application
Dim Arg1(2) As Double
Dim Arg2(2) As Double

Dim X As Integer
Dim result As Variant

Arg1(1) = Payout1
Arg1(2) = Payout2

Arg2(1) = Achieve1
Arg2(2) = Achieve2

Set objExcel = CreateObject("Excel.Application")
result = objExcel.WorksheetFunction.Trend(Arg1, Arg2, performance)
objExcel.Quit
Set objExcel = Nothing
NewTrend = result(1)

Exit_Trend:
Exit Function
Err_Trend:
MsgBox Err.Number & " " & Err.Description
Resume Exit_Trend
End Function


Any insight would be appreciated.

Thanks

 

[MajP]

http://office.microsoft.com/en-us/excel-help/trend-function-HP010062548.aspx

The third parameter "Const" is a boolean

Const is a logical value specifying whether to force the constant b to equal 0.
If const is TRUE or omitted, b is calculated normally.
If const is FALSE, b is set equal to 0 (zero), and the m-values are adjusted so that y = mx.

You appear to be passing in a double. My guess you are passing in something other that -1 and hence it assumes false. Therefore it is calculating b and adjusting the trend upward.

 

[penndro]

Thanks MajP,

I actually did read on that as i was trying to trouble shoot this. I figured since I was not passing anything as the const that it was defaulting to true.

So now I added that variable to my code but now I cant get any values returned; however, I do get a "13 Type Mismatch" error.

Can you Opine on this adjustment:

Function NewTrend(Payout1 As Double, Payout2 As Double, Achieve1 As Double, Achieve2 As Double, performance As Double) As Double
On Error GoTo Err_Trend

Dim objExcel As Excel.Application
Dim Arg1(2) As Double
Dim Arg2(2) As Double
Dim Constarg As Boolean
Constarg = -1

Dim X As Integer
Dim result As Variant

Arg1(1) = Payout1
Arg1(2) = Payout2
Debug.Print Payout1
Debug.Print Payout2


Arg2(1) = Achieve1
Arg2(2) = Achieve2
Debug.Print Achieve1
Debug.Print Achieve2

Set objExcel = CreateObject("Excel.Application")

result = objExcel.WorksheetFunction.Trend(Arg1, Arg2, performance, Constarg)
Debug.Print result

objExcel.Quit
Set objExcel = Nothing
NewTrend = result(1)

Exit_Trend:
Exit Function

 

[MajP]

I am Sorry. I am messing you up. Looks like you had it correct. There are four parameters not three, and the fourth is the constant. I miscounted.

If you omit it the constant or use True then your y intercept (b) is going to be calculated. "Best Fit"

If you put False then the Y intercept is going to be forced to 0. If when payout is 0 the achieved should be 0 then use false.

Now let me try to take a look and see why the values would be different. I will get back.

 

[MajP]

OK, Arrays are 0 based unless specified. So your arrays have three items not two.
arg1(0), arg1(1), and arg1(2). By default the value of arg1(0) is 0

so the solution
dim arg1(1) as double
and use (0) and (1)
or you can assign it as follows
dim arg1(1 to 2)
and use (1) and (2)

 

[penndro]

Thanks again MajP.

I adjusted the arrays but I am still getting th e Type 13 Error. Here is how I adjusted the code. Is this what you meant?

Function NewTrend(Payout1 As Double, Payout2 As Double, Achieve1 As Double, Achieve2 As Double, performance As Double) As Double
On Error GoTo Err_Trend

Dim objExcel As Excel.Application
Dim Arg1(1) As Double
Dim Arg2(1) As Double

Dim X As Integer
Dim result As Variant
Dim Constarg As Double

Arg1(0) = Payout1
Arg1(1) = Payout2
Debug.Print Payout1
Debug.Print Payout2


Arg2(0) = Achieve1
Arg2(1) = Achieve2
Debug.Print Achieve1
Debug.Print Achieve2

Set objExcel = CreateObject("Excel.Application")

result = objExcel.WorksheetFunction.Trend(Arg1, Arg2, performance)
Debug.Print result

objExcel.Quit
Set objExcel = Nothing
NewTrend = result(1)

Exit_Trend:
Exit Function
Err_Trend:
MsgBox Err.Number & " " & Err.Description


Resume Exit_Trend

End Function

 

[MajP]

Are you getting it when you call your function, or when your function calls the trend function. You can comment out the call to the worksheet function to test it.

You need to make sure your arguments match the parameter data types. I cannot see how you are calling the function. But since it looks like it is the same as how you previously had it, my guess is you are passing in something differently

ex:
public someFunction (x as integer) as double

if you pass that function a value that is something besides an integer you will get a type mismatch.

also if you do something like this

dim x as integer
x = somefunctionThatReturnsANonInteger

you will get the same error. Bottom line if you declare a variable of some data type and try to assign it a value not of that data type you will get the error.

my guess it is here:
(Payout1 As Double, Payout2 As Double, Achieve1 As Double, Achieve2 As Double, performance As Double)

or here
(Arg1, Arg2, performance)

I would have thought that performance would also have to be an array, but you claim that originally it worked so I guess not.

The arguments, known_y's, known_x's, and new_x's must be arrays or cell ranges with related dimensions.

 

[penndro]

Thanks again for your insight MajP.

This adjusted code is working perfectly now. It reads between the two points and returns the exact result as Excel! Awesome. I really appreciate your help.

Function NewTrend(Payout1 As Double, Payout2 As Double, Achieve1 As Double, Achieve2 As Double, performance As Double) As Double
On Error GoTo Err_Trend
Dim objExcel As Excel.Application
Dim Arg1(1) As Double
Dim Arg2(1) As Double

Dim X As Double
Dim result As Variant

Arg1(0) = Payout1
Arg1(1) = Payout2
Debug.Print Payout1
Debug.Print Payout2

Arg2(0) = Achieve1
Arg2(1) = Achieve2
Debug.Print Achieve1
Debug.Print Achieve2

Set objExcel = CreateObject("Excel.Application")
result = objExcel.WorksheetFunction.Trend(Arg1, Arg2, performance)
objExcel.Quit
Set objExcel = Nothing
NewTrend = result(1)
Debug.Print NewTrend

Exit_Trend:
Exit Function
Err_Trend:
MsgBox Err.Number & " " & Err.Description
Resume Exit_Trend
End Function

 

[MajP]

BTW this can be done a whole lot simpler

Public Function getPayout(Payout1 As Double, Payout2 As Double, Achieve1 As Double, Achieve2 As Double, performance As Double) As Double
  Dim m As Double
  Dim b As Double
  'm = y2-y1 / x2 - x1
  m = (Payout2 - Payout1) / (Achieve2 - Achieve1)
  'y = mx + b, pick one point
  'b = y(1) - mx(1)
  b = Payout1 - m * (Achieve1)
  getPayout = m * (performance) + b
End Function

Public Sub test()
 MsgBox getPayout(12974.99, 3243.75, 218090462, 174472369, 194558975)
End Sub