User Defined Functions and Arrays

[thopma2]

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.

 

[shg]

Maybe like this:

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,

       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]

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?

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]

You're not returning anything as the function result...

 

[thopma2]

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:

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.

     a = coefficients(...first member of this array...)
     b = coefficients(...second member of this array...)
     etc.

Thanks for your help.

 

[RoryA]

You would use:

a = coefficients(1)
b = coefficients(2)

etc.

 

[shg]

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

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]

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

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]

output = a

should be:

aggfactor = a

 

[thopma2]

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