# User Defined Functions and Arrays

#### thopma2

##### New Member
Two questions:

1) How can I pass two arrays into a user defined function?

2) How can I access single members of an array within a function?

I'm trying to write a function that will automate my data-work up. I want to pass two arrays to this function, a set of x values, and a set of y values.

Once I've passed those two arrays, I want my function to execute the linest() function with those arrays. Since I'm using a 3rd power polynomial, it will return a 4 member array with the coefficients. I want to then have my function use those coefficients to calulate other points on the best-fit line so I can estimate a definite integral across the range of x.

In the end, I only want one number. But I'm having trouble figuring out how to get started on this. A single experiment will take HOURS to work up manually, so building a custom function to do it is pretty vital. Please let me know if you need more information, and as this is my first post, please forgive any transgressions of posting procedure or etiquette.

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

#### shg

##### MrExcel MVP
Maybe like this:
Code:
``````Function myLinEst(rX As Range, rY As Range) As Variant
Dim sAdrX As String
Dim sAdrY As String

myLinEst = Evaluate("linest(" & sAdrY & ", " & sAdrX & "^{1,2,3})")
End Function``````
For example,
Code:
``````       A- -B-- C D E F G
1    1   10   1 2 3 4
2    2   26
3    3   58
4    4  112
5    5  194
6    6  310
7    7  466
8    8  668
9    9  922
10   10 1234``````
The array formula in D1:G1 is

=myLinEst(A1:A10, B1:B10)

#### thopma2

##### New Member
I'm guess I don't want to use Evaluate() to export any data to the spreadsheet. What I want is to pass the coefficients into a new array, so I can then go run calculations with them.

What I'm getting hung up on right now is the proper execution of the linest() function within my UDF. Right now, when I run the following mini-function, the only output I get is 0's. If I run the linest() function from inside the spreadsheet, I get the proper coefficients. Is there some kind of syntax I'm missing, or am I not outputting the coefficients() array properly?

Code:
``````Function AggFactor(xWav As Variant, yAbs As Variant)
Dim coefficients() As Variant

coefficients() = Application.LinEst(yAbs, Application.Power(xWav, Array(1, 2, 3)))

End Function``````

#### RoryA

##### MrExcel MVP, Moderator
You're not returning anything as the function result...

#### thopma2

##### New Member

Ok, I figured out my first problem. As you may be able to tell, 'newbie' is a fairly accurate description of my VBA coding level. I appreciate your responses so far.

This works:

Code:
``````Function AggFactor(xWav As Variant, yAbs As Variant) As Variant()
Dim coefficients() As Variant

coefficients() = WorksheetFunction.LinEst(yAbs, Application.Power(xWav, Array(1, 2, 3)))
AggFactor = coefficients()

End Function``````
If I crtl+shift+enter while highlighting 4 fields, I get the same 4 coefficients as I would if I were to just type in the linest() equation in a cell within the worksheet.

My next question is how do I assign the members of the coefficients() array to individual variables? i.e.
Code:
``````     a = coefficients(...first member of this array...)
b = coefficients(...second member of this array...)
etc.``````
Thanks for your help.

#### RoryA

##### MrExcel MVP, Moderator
You would use:
Code:
``````a = coefficients(1)
b = coefficients(2)``````
etc.

#### shg

##### MrExcel MVP

The use of Evaluate is a convenient shortcut unrelated to returning values to the worksheet.

Code:
``````Function x(rX As Range, rY As Range) As Variant
Dim sAdrX As String
Dim sAdrY As String
Dim avdCoeff As Variant

avdCoeff = Evaluate("linest(" & sAdrY & ", " & sAdrX & "^{1,2,3})")
' do what you wish with the coefficients ....
End Function``````

#### thopma2

##### New Member
I seem to be running into an issue with the assignment of the members of the coefficients() array:

Code:
``````Function AggFactor(xWav As Variant, yAbs As Variant)
Dim coefficients() As Variant
Dim a As Double

coefficients() = WorksheetFunction.LinEst(yAbs, Application.Power(xWav, Array(1, 2, 3)))
a = coefficients(1)

output = a

End Function``````
This returns the number 0 to my cell, which is not the value of the any of the coefficients in the array. The value of the first member of the array is actually -3.1E-7. What am I missing?

#### RoryA

##### MrExcel MVP, Moderator
Code:
``output = a``
should be:
Code:
``aggfactor = a``

#### thopma2

##### New Member
You guys are awesome. Thanks Rory. I'm sure I'll be back with some more questions in a bit.

1,106,419
Messages
5,511,244
Members
408,835
Latest member
janetjordan

### This Week's Hot Topics

• Turn fraction around
Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
• TIme Clock record reformatting to ???
Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
• TextBox Match
hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
• Using Large function based on Multiple Criteria
Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
• Can you check my code please
Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
• Combining 2 pivot tables into 1 chart
Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...