Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple example of a range argument to a UDF?
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(B1:D1,B2:D2) 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple example of a range argument to a UDF?
You have two choices you can use Arrays or Range. You can move the data in
to an arrya then pass to a UDF. But I will stay with a UDF. Remember yo ucan 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 "Prof Wonmug" wrote: 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(B1:D1,B2:D2) 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple example of a range argument to a UDF?
"Prof Wonmug" wrote:
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(B1:D1,B2:D2) 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" wrote in message ... 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(B1:D1,B2:D2) 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple example of a range argument to a UDF?
On Sun, 3 May 2009 04:14:00 -0700, joel
wrote: 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,D6:D12) 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" wrote: 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(B1:D1,B2:D2) 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple example of a range argument to a UDF?
On Sun, 3 May 2009 09:28:23 -0700, "JoeU2004"
wrote: "Prof Wonmug" wrote: 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(B1:D1,B2:D2) 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple example of a range argument to a UDF?
"Prof Wonmug" wrote:
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 ----- "Prof Wonmug" wrote in message ... On Sun, 3 May 2009 09:28:23 -0700, "JoeU2004" wrote: "Prof Wonmug" wrote: 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(B1:D1,B2:D2) 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple example of a range argument to a UDF?
On Sun, 3 May 2009 14:18:27 -0700, "JoeU2004"
wrote: "Prof Wonmug" wrote: 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. 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 ----- "Prof Wonmug" wrote in message .. . On Sun, 3 May 2009 09:28:23 -0700, "JoeU2004" wrote: "Prof Wonmug" wrote: 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(B1:D1,B2:D2) 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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simple example of a range argument to a UDF?
"Prof Wonmug" wrote:
"JoeU2004" wrote: 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 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. 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? 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 ----- "Prof Wonmug" wrote in message ... On Sun, 3 May 2009 14:18:27 -0700, "JoeU2004" wrote: "Prof Wonmug" wrote: 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. 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 ----- "Prof Wonmug" wrote in message . .. On Sun, 3 May 2009 09:28:23 -0700, "JoeU2004" wrote: "Prof Wonmug" wrote: 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(B1:D1,B2:D2) 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
simple copy paste multiple range macro ARGUMENT ERROR | Excel Programming | |||
Difference function and argument for two simple amounts | Excel Discussion (Misc queries) | |||
Function (array argument, range argument, string argument) vba | Excel Programming | |||
Range as argument in function | Excel Programming | |||
Passing range as argument | Excel Programming |