Solved: Passing Arrays into Functions problem

[geode]

Can someone tell me what's wrong with the code below? What I wanted is to pass two columns (sigma1 and sigma3) as parameters into a function and get the Slope and Y-Intercept. I am really confused about passing arrays into function. Thanks.


Function SLOPEPQ(sigma1 As Variant, sigma3 As Variant) As Long

Dim ctr As Integer
Dim PVALUE, QVALUE As Long
PVALUE = 0.5 * (sigma1 + sigma3)
QVALUE = 0.5 * (sigma1 - sigma3)
For ctr = LBound(arr1) To UBound(arr1)
PVALUE(ctr) = 0.5 * (sigma1(ctr) + sigma3(ctr))
QVALUE(ctr) = 0.5 * (sigma1(ctr) - sigma3(ctr))
Next ctr

SLOPEPQ = Application.LinEst(PVALUE, QVALUE)
End Function

[Bob Phillips]

You are not handling sigma1 and sigma3 as arrays, but single scalars. And where is arr1 coming from?

[geode]

You are not handling sigma1 and sigma3 as arrays, but single scalars. And where is arr1 coming from?

thanks xld. arr1 should be changed to sigma1. So how do I fix this? Any suggestions?

[Kenneth Hobs]

Please give us an example with numbers.

You Dimmed your PVALUE as a variant but QVALUE as Long. If sigma1 is known_y's and sigma3 is known_x's as ranges, then how do you expect to add the two?

For the example data from help of:
1 0

9 4

5 2

7 3

Linest as you detailed, for this data, returns a value of 2 which is the slope.

To answer your question, when working with arrays in the functions that expect a certain data orientation, one trick is to use Application.Transpose. Depending on the data layout, you may need a double Application.Transpose.

If sigma1 and sigma3 are ranges, you should dim them as Range in the input parameters rather than Variant.

[geode]

OK, Here is an example. I have these in Excel 2010:

sigma1 sigma3 PVALUE QVALUE 0.325 26.7 13.5125 13.1875 0.65 29 14.825 14.175 1.3 32.3 16.8 15.5






The input parameters are sigma1 and sigma3. PValue is (sigma1 +sigma3)/2 and QValue is (sigma1=sigma3)/2. I need to make a function to get the Slope and Y-Intercept from PValue and QValue. Of course, I do not want to show PValue and QValue and just want to get the slope and y-intercept.

Can someone fix that code for me? Sorry I am a newbie and I need to know how arrays are passed into functions.

Please give us an example with numbers.

You Dimmed your PVALUE as a variant but QVALUE as Long. If sigma1 is known_y's and sigma3 is known_x's as ranges, then how do you expect to add the two?

For the example data from help of:
1 0

9 4

5 2

7 3

Linest as you detailed, for this data, returns a value of 2 which is the slope.

To answer your question, when working with arrays in the functions that expect a certain data orientation, one trick is to use Application.Transpose. Depending on the data layout, you may need a double Application.Transpose.

If sigma1 and sigma3 are ranges, you should dim them as Range in the input parameters rather than Variant.

[Paul_Hossler]

I'm just wondering why there's the intermediate PVALUE and QVALUE

If all you want is the slope and intercept from y = mx + b, can't you just get them directly?

[vba]
Option Explicit
Function SLOPEPQ(Y_Range As Variant, X_Range As Variant) As Variant

SLOPEPQ = Application.LinEst(Y_Range, X_Range)

End Function

Sub test()
Dim v As Variant
v = SLOPEPQ(Range("b1:b4"), Range("a1:a4"))
With ActiveSheet
MsgBox "Slope = " & v(1)
MsgBox "Coeff = " & v(2)
End With
End Sub
[/vba]

Paul

[geode]

Hi Paul,

Yes you are right, I could have just get the slope and intercept directly but I have to do a lot of intermediary calculations between sigma1 and sigma3. The ones I presented are just simplified. I would just want to get an example on how it is done. I just need to have to type the function and select the parameters by clicking. By doing this, it eliminates the unnecessary clutter so that it only shows the result and the given.

What you did was not what I wanted since the range selection is fixed. I also wanted the result to be shown in the cell where the function is and not in a msgbox.

I'm just wondering why there's the intermediate PVALUE and QVALUE

If all you want is the slope and intercept from y = mx + b, can't you just get them directly?

[vba]
Option Explicit
Function SLOPEPQ(Y_Range As Variant, X_Range As Variant) As Variant

SLOPEPQ = Application.LinEst(Y_Range, X_Range)

End Function

Sub test()
Dim v As Variant
v = SLOPEPQ(Range("b1:b4"), Range("a1:a4"))
With ActiveSheet
MsgBox "Slope = " & v(1)
MsgBox "Coeff = " & v(2)
End With
End Sub
[/vba]
Paul

[Kenneth Hobs]

You can easily modify this to return the first or second element of the array to make your UDF.
[VBA]Sub Test_LinEst()
Dim a() As Variant, b() As Variant
a() = fLinEst(Range("A3:A7"), Range("B3:B7"))
b() = fLinEstPQ(Range("A3:A7"), Range("B3:B7"))
MsgBox "LinEst Slope: " & vbTab & a(1) & vbLf & _
"LinEst Y-Int: " & vbTab & a(2) & vbLf & _
"LinEstPQ Slope: " & vbTab & b(1) & vbLf & _
"LinEstPQ Y-Int: " & vbTab & b(2) & vbLf
End Sub

Function fLinEstPQ(sigma1 As Range, sigma3 As Range) As Variant
Dim ctr As Integer
Dim PVALUE() As Variant, QVALUE As Variant
ReDim PVALUE(1 To sigma1.Cells.Count)
ReDim QVALUE(1 To sigma1.Cells.Count)
For ctr = 1 To sigma1.Cells.Count
PVALUE(ctr) = 0.5 * (sigma1(ctr) + sigma3(ctr))
QVALUE(ctr) = 0.5 * (sigma1(ctr) - sigma3(ctr))
Next ctr
fLinEstPQ = Application.LinEst _
(WorksheetFunction.Transpose(WorksheetFunction.Transpose(PVALUE)), _
WorksheetFunction.Transpose(WorksheetFunction.Transpose(QVALUE)))
End Function

Function fLinEst(Y_Range As Range, X_Range As Range) As Variant
fLinEst = Application.LinEst(Y_Range, X_Range)
End Function
[/VBA]

[geode]

THANKS HEAPS KENNETH!!! This is what I needed. Thank you also to others who gave feedbacks.