Simple example of a range argument to a UDF?

[Prof Wonmug]

Can someone show me a simple example of how to pass a range of cells
to a UDF and then how to address them in the UDF?

Suppose I have two rows of paired parameters (X and Y)

A B C D
1 X X1 X2 X3
2 Y Y1 Y2 Y3

I need to write a regression function that will operate on the pairs
from column B to successive columns to the right.

I understand that I can pass a range to a UDF something like this:

A B C D
1 X X1 X2 X3
2 Y Y1 Y2 Y3
3 f(x,y) =F(B1:B1,B2:B2) =F(B1:C1,B2:C2) =F(B11,B22)

Suppose I wanted to compute the sum of the product pairs (X1*Y1),
(X1*Y1 + X2*Y2), (X1*Y1 + X2*Y2 + X3*Y3), ...

What would the VBA code look like? My function is a lot more
complicated than that and involves looping, but if I had the code for
this simple sum of products, I could adapt it.

Thanks

 

[JoeU2004]

I understand that I can pass a range to a UDF something like this:
[....]
f(x,y) =F(B1:B1,B2:B2) =F(B1:C1,B2:C2) =F(B11,B22)

Suppose I wanted to compute the sum of the product pairs (X1*Y1),
(X1*Y1 + X2*Y2), (X1*Y1 + X2*Y2 + X3*Y3), ...

One way....

Option Explicit

Function mysumprod(x As Range, y As Range)
Dim r As Long, c As Long, i As Long
r = x.Rows.Count
c = x.Columns.Count
If r <> 1 Or y.Rows.Count <> r Or y.Columns.Count <> c Then
mysumprod = CVErr(xlErrNA)
Exit Function
End If
For i = 1 To c
mysumprod = mysumprod + x.Cells(1, i) * y.Cells(1, i)
Next i
End Function

Notes:

1. Option Explicit is optional. It requires that you declare all variables;
generally a good practice, especially when you are new to VBA programming.

2. The function returns a variant (implicitly typed) so that we can return
error if the shape of the ranges do not match.

3. The function assumes that each range is a row. It could be adapted to
work with columns and with matrices.


----- original message -----

 

[Prof Wonmug]

You have two choices you can use Arrays or Range. You can move the data in
to an array then pass to a UDF. But I will stay with a UDF. Remember you
can only return one value from a UDF or return an Array function. The
problem with writing you array function is you must specifically specify the
number of cells returned (standard function will return the szie
automatically). An Array function is when you put curly brackets around the
function on the worksheet.

Now here is your answer. You can use Range, Cells, Columns, Rows just like
any worksheet Range.

=MyUDF(B6:H8)


Function MyUDF(Target as Range)

FirstCell = Target.Range("A1") 'this is location B6
LastCell = Target.Cells(8,7) 'this is h8

end Function

Thanks, Joel. I'll have to study that code. I don't understand the
syntax and I got some odd results trying to run it.

In the meantime, could you post a version that does some work on each
cell in the range so I can see how the addressing works?

It would be tremendously helpful if you could post a UDF that takes 2
arguments, X and Y, both 1xN, and calculates this series:

MyUdf = X1*Y1^1 + X2*Y2^2 + X3*Y3^3 ... + XN*YN^N

The function call would be

=MyUDF(B6:B12,D612)

Both ranges will be 1xN. N will vary from 1 to 5 or 6.

Just in case you have nothing better to do today, ;-), I would love to
compare a "range function" solution with an "array function" solution.
This function returns a single value.

Thanks. I'll buy you a virtual drink (or even lunch). ;-)

 

[Prof Wonmug]

I understand that I can pass a range to a UDF something like this:
[....]
f(x,y) =F(B1:B1,B2:B2) =F(B1:C1,B2:C2) =F(B11,B22)

Suppose I wanted to compute the sum of the product pairs (X1*Y1),
(X1*Y1 + X2*Y2), (X1*Y1 + X2*Y2 + X3*Y3), ...

One way....

Option Explicit

Function mysumprod(x As Range, y As Range)
Dim r As Long, c As Long, i As Long
r = x.Rows.Count
c = x.Columns.Count
If r <> 1 Or y.Rows.Count <> r Or y.Columns.Count <> c Then
mysumprod = CVErr(xlErrNA)
Exit Function
End If
For i = 1 To c
mysumprod = mysumprod + x.Cells(1, i) * y.Cells(1, i)
Next i
End Function

That works perfectly. Thank you very much.

The only part I don't understand is the x1ErrNA argument to the CVErr
function.

 

[JoeU2004]

The only part I don't understand is the x1ErrNA
argument to the CVErr function.

That's "ex ell ErrNA", not "ex one ErrNA". It's a standard constant. And
you can probably ignore it, for your purposes.

If you enter "cverr function" (without quotes) into the VBA Help search
field, you should find links to the CVErr function and Error Values help
pages. The latter shows you the standard constants.

CVErr can be used to return standard and non-standard error codes from
variant functions. But note that Excel treats any non-standard error code
as a #VALUE! error.


----- original message -----

I understand that I can pass a range to a UDF something like this:
[....]
f(x,y) =F(B1:B1,B2:B2) =F(B1:C1,B2:C2) =F(B11,B22)

Suppose I wanted to compute the sum of the product pairs (X1*Y1),
(X1*Y1 + X2*Y2), (X1*Y1 + X2*Y2 + X3*Y3), ...

One way....

Option Explicit

Function mysumprod(x As Range, y As Range)
Dim r As Long, c As Long, i As Long
r = x.Rows.Count
c = x.Columns.Count
If r <> 1 Or y.Rows.Count <> r Or y.Columns.Count <> c Then
mysumprod = CVErr(xlErrNA)
Exit Function
End If
For i = 1 To c
mysumprod = mysumprod + x.Cells(1, i) * y.Cells(1, i)
Next i
End Function

That works perfectly. Thank you very much.

The only part I don't understand is the x1ErrNA argument to the CVErr
function.

 

[Prof Wonmug]

That's "ex ell ErrNA", not "ex one ErrNA". It's a standard constant. And
you can probably ignore it, for your purposes.
Aha!

If you enter "cverr function" (without quotes) into the VBA Help search
field, you should find links to the CVErr function and Error Values help
pages. The latter shows you the standard constants.

The help is minimal. It doesn't even include a link to the standard
codes, let alone any information on how to create my own.

A search for xlcverror turned up the stardard codes:

Name Value Description
xlErrDiv0 2007 Error number: 2007
xlErrNA 2042 Error number: 2042
xlErrName 2029 Error number: 2029
xlErrNull 2000 Error number: 2000
xlErrNum 2036 Error number: 2036
xlErrRef 2023 Error number: 2023
xlErrValue 2015 Error number: 2015

The third column is particularly enlightening. ;-)

CVErr can be used to return standard and non-standard error codes from
variant functions. But note that Excel treats any non-standard error code
as a #VALUE! error.

So, I can define my own error codes, but Excel will ignore them?

----- original message -----

I understand that I can pass a range to a UDF something like this:
[....]
f(x,y) =F(B1:B1,B2:B2) =F(B1:C1,B2:C2) =F(B11,B22)

Suppose I wanted to compute the sum of the product pairs (X1*Y1),
(X1*Y1 + X2*Y2), (X1*Y1 + X2*Y2 + X3*Y3), ...

One way....

Option Explicit

Function mysumprod(x As Range, y As Range)
Dim r As Long, c As Long, i As Long
r = x.Rows.Count
c = x.Columns.Count
If r <> 1 Or y.Rows.Count <> r Or y.Columns.Count <> c Then
mysumprod = CVErr(xlErrNA)
Exit Function
End If
For i = 1 To c
mysumprod = mysumprod + x.Cells(1, i) * y.Cells(1, i)
Next i
End Function

That works perfectly. Thank you very much.

The only part I don't understand is the x1ErrNA argument to the CVErr
function.

 

[JoeU2004]

The help is minimal. It doesn't even include
a link to the standard codes

That's true of the link to CVErr help :-(.

But I also mentioned the link to the Error Values help, which does indeed
show the standard constants in my revision of Excel (2003). In my revision
of Excel, searching for "cverr function" (without quotes) shows both links.

So, I can define my own error codes, but Excel will ignore them?

Depends on what you mean by "ignore". I wrote: "Excel treats any
non-standard error code as a #VALUE! error". It's treated as an error, not
ignored; but Excel does not differentiate the non-standard error codes.

Again, that's for my revision of Excel 2003. And of course, you could
simply try it and answer the question for yourself.


----- original message -----

That's "ex ell ErrNA", not "ex one ErrNA". It's a standard constant. And
you can probably ignore it, for your purposes.
Aha!

If you enter "cverr function" (without quotes) into the VBA Help search
field, you should find links to the CVErr function and Error Values help
pages. The latter shows you the standard constants.

The help is minimal. It doesn't even include a link to the standard
codes, let alone any information on how to create my own.

A search for xlcverror turned up the stardard codes:

Name Value Description
xlErrDiv0 2007 Error number: 2007
xlErrNA 2042 Error number: 2042
xlErrName 2029 Error number: 2029
xlErrNull 2000 Error number: 2000
xlErrNum 2036 Error number: 2036
xlErrRef 2023 Error number: 2023
xlErrValue 2015 Error number: 2015

The third column is particularly enlightening. ;-)

CVErr can be used to return standard and non-standard error codes from
variant functions. But note that Excel treats any non-standard error code
as a #VALUE! error.

So, I can define my own error codes, but Excel will ignore them?

----- original message -----

I understand that I can pass a range to a UDF something like this:
[....]
f(x,y) =F(B1:B1,B2:B2) =F(B1:C1,B2:C2) =F(B11,B22)

Suppose I wanted to compute the sum of the product pairs (X1*Y1),
(X1*Y1 + X2*Y2), (X1*Y1 + X2*Y2 + X3*Y3), ...

One way....

Option Explicit

Function mysumprod(x As Range, y As Range)
Dim r As Long, c As Long, i As Long
r = x.Rows.Count
c = x.Columns.Count
If r <> 1 Or y.Rows.Count <> r Or y.Columns.Count <> c Then
mysumprod = CVErr(xlErrNA)
Exit Function
End If
For i = 1 To c
mysumprod = mysumprod + x.Cells(1, i) * y.Cells(1, i)
Next i
End Function

That works perfectly. Thank you very much.

The only part I don't understand is the x1ErrNA argument to the CVErr
function.