User Defined Functions and Arrays

thopma2

New Member
Joined
Oct 28, 2010
Messages
9
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.
 

Some videos you may like

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
Joined
May 7, 2008
Messages
21,763
Office Version
2010
Platform
Windows
Maybe like this:
Code:
Function myLinEst(rX As Range, rY As Range) As Variant
    Dim sAdrX As String
    Dim sAdrY As String
    
    sAdrX = rX.Address(External:=True)
    sAdrY = rY.Address(External:=True)
    
    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
Joined
Oct 28, 2010
Messages
9
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
Joined
May 2, 2008
Messages
35,397
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
You're not returning anything as the function result...
 

thopma2

New Member
Joined
Oct 28, 2010
Messages
9

ADVERTISEMENT

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
Joined
May 2, 2008
Messages
35,397
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
You would use:
Code:
a = coefficients(1)
b = coefficients(2)
etc.
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,763
Office Version
2010
Platform
Windows

ADVERTISEMENT

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
    
    sAdrX = rX.Address(External:=True)
    sAdrY = rY.Address(External:=True)
    
    avdCoeff = Evaluate("linest(" & sAdrY & ", " & sAdrX & "^{1,2,3})")
    ' do what you wish with the coefficients ....
End Function
 

thopma2

New Member
Joined
Oct 28, 2010
Messages
9
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
Joined
May 2, 2008
Messages
35,397
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Code:
output = a
should be:
Code:
aggfactor = a
 

thopma2

New Member
Joined
Oct 28, 2010
Messages
9
You guys are awesome. Thanks Rory. I'm sure I'll be back with some more questions in a bit.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
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...
Top