I'm writing an UDF for INTERCEPT and SLOPE functions that works with missing/invalid data. Simple enough to scrape the data ranges and then call the application functions INTERCEPT and SLOPE using only the valid data.
I often have to fit data that conforms to a power law. I hate having to create yet another column of data to transform the original values to logs and then fit these. So I do it inline by including the transformation in the function arguments. For example:
mypowerlawslope = SLOPE(log10(ydata), log10(xdata)) where xdata and ydata are ranges. Works nicely.
Problem is that when I pass transformed ranges to my UDF the function return result is #VALUE indicating something went wrong. However there seems to be no problem as the single cell level. For example-
Single Cell example:
On the worksheet:Function test(x As Variant) As Variant test = x End Function
Cell A1 has the value 10
factor = 10 (named cell)
=test(A1) returns 10 OK
=test(LOG10(A1)) returns 1 OK
=test(LOG10(10*A1)) returns 2 OK
=test(LOG10(factor*A1)) returns 2 OK
Array example:
Cells A1:A2 have the value 10, 100Function arrtest(x As Variant) As Variant arrtest = x(2) End Function
=arrtest(A1:A2) returns 100 OK
=arrtest(LOG10(A1:A2)) returns #VALUE FAIL (should be 2)
=arrtest(10*A1:A2) returns #VALUE FAIL (should be 3)
=arrtest(factor*A1:A2) returns #VALUE FAIL (should be 3)
So can anyone help me understand how to do this? How are arguments handled by vba functions, specifically when they are not simple cell references or ranges.
Thanks.