"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.