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]

Hey, Rory,

How come this generates a run-time error:

    With WorksheetFunction
        avdCoeff = .LinEst(rY, .Power(rX, Array(1, 2, 3)))
    End With

and this works:

    With Application
        avdCoeff = .LinEst(rY, .Power(rX, Array(1, 2, 3)))
    End With

 

[RoryA]

What does avdCoeff equal in the second case - an error value?

 

[shg]

With WorksheetFunction and OERN, there's no assignment made (it remains Empty) and you get a Type Mismatch error.

With Application it fills with the coefficients.

 

[RoryA]

It seems to be the Power part that's the issue. Not sure why, offhand. I'll have a think.

 

[thopma2]

So I'm getting an unexpected #VALUE! error. I traced it to the UBound() fxn I'm trying to use:

Function AggFactor(xWav As Variant, yAbs As Variant)
 
... 
 
For N = LBound(xWav) To UBound(xWav)
 
...

For some reason, LBound() and Ubound() don't seem to like the fact that I'm passing xWav to them. Even if I try something as simple as:

AggFactor = UBound(xWav)

This gives me a value error. Why can't I pass these arrays into the UBound() and LBound() functions?

 

[shg]

How are you calling this function?

 

[thopma2]

How are you calling this function?

Aren't UBound() and LBound() public functions built into VBA?

http://msdn.microsoft.com/en-us/library/95b8f22f(VS.90).aspx

 

[shg]

Yes, and they require that the argument be an array.

That's why I asked how you were calling the function -- meaning AggFactor, not L/UBound.

If xWav is a range, you can't do it in that fashion. If it can be either a range or a variant array, you need to test to find out which it is first.

 

[thopma2]

Yes, and require that the argument be an array.

That's why I asked how you were calling the function -- meaning AggFactor, not L/UBound.

Ahh, sorry. I pass xWav and yAbs as ranges of data. I can successfully pass xWav and yAbs into linest(), which require arrays...but the same data being passed into U/LBound() is giving me this error.

 

[shg]

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