Sleeper: Passing transformed arguments to UDF

[ChrisF]

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:


Function test(x As Variant) As Variant
test = x
End Function

On the worksheet:
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:


Function arrtest(x As Variant) As Variant
arrtest = x(2)
End Function


Cells A1:A2 have the value 10, 100
=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.

[Bob Phillips]

I am confused as to why you think 3 & 4 should be 3, looks like 1000 to me, but try


Function arrtest(x As Variant) As Variant
arrtest = Application.Transpose(x)(2)
End Function

[snb]

Function arrtest(x As Variant) As Variant
arrtest = Array(10 * x(1, 1), 100 * x(2, 1))
End Function

Sub snb()
sn = arrtest([A1:A2].Value)
MsgBox sn(0) & vbTab & sn(1)
End Sub

[ChrisF]

@xld

I am confused as to why you think 3 & 4 should be 3, looks like 1000 to me
you're right of course, cut & paste laziness on my part

@snb

I'm sorry. I'm struggling to see how what your showing me resolves the problem. (maybe I haven't had enough coffee ...). Perhaps I haven't explained my question well enough. Allow me to elaborate.

If I have x data 4, 16 in cells A1:A2 and y data 6, 12 in cells B1:B2. This data fits a powerlaw y=3x^0.5.

To get the power (0.5) I simply type in a cell =SLOPE(LOG10(B1:B2),LOG10(A1:A2))
To get the coefficient I type in a cell =10^INTERCEPT(LOG10(B1:B2),LOG10(A1:A2))

Note that I can perform the log10 transformation inline so to speak in the calls to functions slope and intercept. This eliminates the need to create columns of transformed data first.

So if I have my own functions MYSLOPE and MYINTERCEPT I'd like to simply enter cell formulae as follows:

=MYSLOPE(LOG10(B1:B2),LOG10(A1:A2)) and
=10^MYINTERCEPT(LOG10(B1:B2),LOG10(A1:A2))

and have my udfs work with the transformed data. Is that possible?

Alternatively, is there any way I can examine the input data and establish what transformation is being applied at the time my function is called?

hope someone can help.

[Bob Phillips]

@xld

you're right of course, cut & paste laziness on my part

SO did you try my suggestion?


@snb

I'm sorry. I'm struggling to see how what your showing me resolves the problem.

That is often the case.

[ChrisF]

@xld

SO did you try my suggestion?
At first, no. Couldn't see how it helped. At your prompting, yes, perhaps I missed something. Unfortunately it failed.

To be clear on what failed:
A1:A2 contain 10, 100
entered your function in a module.
in cell B1 entered =arrtest(LOG10(A1:A2))
returned result was #VALUE (should be 2)

The failure seems to stem from the LOG10 transformation included in the function arguments. How to handle this situation is precisely the problem I'm trying to resolve.


Can I trap the transformation on entry to the function?


That is often the case.

snb's solution is a function that takes the untransformed data and transforms it within the function. I have no problem doing that. The problem comes when I want to pass an expression to the function. Passing the expression LOG10(a1:a2) to the native SLOPE and INTERCEPT functions works fine but fails when I pass the expression to a UDF. I don't understand why.

[Bob Phillips]

LOG10 would not not normally take a range, so you have to array-enter the formula that calls the UDF.

[ChrisF]

:banghead: So it was the lack of transposition AND forgetting to enter as an array formula that was the problem. Works beautifully now. :biggrin:

I really appreciate your patience. I'll summarize below for anyone else who might read this thread.



' key thing here is the Transpose function
Function arrtest(x As Variant) As Variant
arrtest = Application.Transpose(x)(2)
End Function

Cells A1:A2 have the value 10, 100
"factor" is a named cell containing the value 10

Cell B1 contains the "array formula" i.e. CTRL-SHIFT-ENTER after entering the formula in the cell. This puts the {} braces around the formula

{=arrtest(A1:A2)} returns 100
{=arrtest(LOG10(A1:A2))} returns 2
{=arrtest(10*A1:A2)} returns 1000
{=arrtest(factor*A1:A2)} returns 1000


PS. in the vba code how did you get the line between function and end function to indent? Tabs and spaces seem to get stripped.

[mikerickson]

The array that is passed is not 1 dimensional. Perhaps

Function arrtest(x As Variant) As Variant
arrtest = x(2,1)
End Function

[snb]

@Mike

Correct.
or

Function arrtest(x) As Variant
arrtest = Application.Index(x, 2)
End Function

[mikerickson]

Actualy, one can use code like this to pass either a range or a variant array to a sub.

Function Foo (myArg as Variant) As Variant
Dim oneElement as Variant
For each oneElement in myArg
rem process each element for example
Foo = Foo & CStr(oneElement)
Next oneElement
End Sub