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.

 

[thopma2]

If they will always be ranges, then pass them as ranges, and get the benefit of IntelliSense:

Function AggFactor(rXWav As Range, rYAbs As Range)
    Dim i As Long
 
    For i = 1 To rXWav.Rows.Count
        '...

I would urge you to acquire the habit of declaring all variables. Putting Option Explicit at the top of every module will enforce this.

In the VBE, Tools > Options, Editor, tick Require Variable Declaration

That was it! Done and done, thank you guys for your help. I have now successfully coded my first UDF in excel. It's a heady moment.

Function AggFactor(xWav As Range, yAbs As Range)
    Dim coefficients() As Variant
    Dim new_Ys() As Variant
    Dim Coeffs() As Variant
    Dim a As Double
    Dim b As Double
    Dim c As Double
    Dim d As Double
    Dim N As Long
    
    ReDim new_Ys(xWav.Rows.Count)
    ReDim Coeffs(xWav.Rows.Count)
                                            
    coefficients() = WorksheetFunction.LinEst(yAbs, Application.Power(xWav, Array(1, 2, 3)))
    a = coefficients(1)
    b = coefficients(2)
    c = coefficients(3)
    d = coefficients(4)
    
    For N = 1 To xWav.Rows.Count
        new_Ys(N) = (a * (xWav(N) ^ 3) + b * (xWav(N) ^ 2) + c * (xWav(N)) + d)
    Next N
    
    For N = 1 To xWav.Rows.Count
        If N = 1 Or N = xWav.Rows.Count Then
            Coeffs(N) = (1 / 2)
        Else
            Coeffs(N) = 1
        End If
    Next N
    
    
    AggFactor = 5 * WorksheetFunction.SumProduct(new_Ys, Coeffs)
End Function

Integrating 96 absorbance curves from a microplate just went from tedious to easy. Woot!