Passing named range to UDF

[JenniferMurphy]

Finding that M$FT in its infinite wisdom chose not to provide a built-in IsDate function to go along with all of the other IsXxx functions, I decided to write my own. Here it is:

Function MyIsDate(ByVal cell As Variant) As Boolean
MyIsDate = IsDate(cell)
End Function

It works fine if I pass it a cell address or the scalar name of a cell, but not if I pass it a named range of the column the cell is in.

[TABLE="class: grid, width: 450"]
<tbody>[TR]
[TD="align: center"]R/C[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]11/12/19[/TD]
[TD="align: center"]TRUE[/TD]
[TD]D4: =myisdate(C4)[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]11/12/19[/TD]
[TD="align: center"]TRUE[/TD]
[TD]D5: =myisdate(temp)[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]11/12/19[/TD]
[TD="align: center"]FALSE[/TD]
[TD]D6: =myisdate(DateUseBy)[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]11/12/19[/TD]
[TD="align: center"]FALSE[/TD]
[TD]D7: =myisdate(+DateUseBy)[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]11/12/19[/TD]
[TD="align: center"]FALSE[/TD]
[TD]D8: =myisdate(0+DateUseBy)[/TD]
[/TR]
</tbody>[/TABLE]

Column C is named "DateUseBy".
Cell C5 is named "temp".

If I put stop on the UDF, the parameter "cell" shows up as "empty" in the last three calls (D6-D8).

What do I have to do to get Excel to pass the cell contents to the UDF for these calls?

 

[mikerickson]

What is the definition of the name DateUseBy?

Also you might add this to the start of the UDF

If Typename(cell) = "Range" then Set cell = cell.Cells(1,1)

 

[shg]

They didn't because they didn't need to.

Function JMIsDate(ByVal cell As Range) As Boolean
  JMIsDate = VarType(cell.Value) = vbDate
End Function

 

[JenniferMurphy]

What is the definition of the name DateUseBy?

Column C

Also you might add this to the start of the UDF

If Typename(cell) = "Range" then Set cell = cell.Cells(1,1)

When I add that code, I get the same results for the first 3 calls and a value error for the last 2.

 

[JenniferMurphy]

They didn't because they didn't need to.

If you mean they didn't need to because it can be done in VBA, then using that logic, there would be no reason for any built in functions. No?

Function JMIsDate(ByVal cell As Range) As Boolean
  JMIsDate = VarType(cell.Value) = vbDate
End Function

Using that code, I get the same result as with Mike addition: TRUE, TRUE, FALSE, #VALUE , #VALUE .

 

[Fluff]

If you pass a range of cells say C2:C10, what are you expecting as a result?
From your OP it looks like you are trying to check each cell individually, so why are you trying to pass a range of cells instead of just one?

 

[JenniferMurphy]

If you pass a range of cells say C2:C10, what are you expecting as a result?
From your OP it looks like you are trying to check each cell individually, so why are you trying to pass a range of cells instead of just one?

Yes, I am trying to check each cell individually. I do this all the time so that I can use a name, rather than an address. Most built-in function that take a cell address will take the one on the same row or column. If they balk, I just add the unary plus (+) and then it works. UDF parameters seem to be different.

 

[JenniferMurphy]

Here's an example using named columns to make formulas easier to read and less error prone.

[TABLE="class: grid, width: 720"]
<tbody>[TR]
[TD="align: center"]R/C[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]Price[/TD]
[TD="align: center"]S&H[/TD]
[TD="align: center"]Tax[/TD]
[TD="align: center"]Cost[/TD]
[TD="align: center"]Formula[/TD]
[TD="align: center"]Date[/TD]
[TD="align: center"]DoW[/TD]
[TD="align: center"]Formula[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: right"]$27.00[/TD]
[TD="align: right"]$1.25[/TD]
[TD="align: right"]$0.45[/TD]
[TD="align: right"]$28.70[/TD]
[TD]F5: =C5+D5+E5[/TD]
[TD="align: right"]2/04/19[/TD]
[TD]Mon[/TD]
[TD]I5: =TEXT(H6,"ddd")[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: right"]$27.00[/TD]
[TD="align: right"]$1.25[/TD]
[TD="align: right"]$0.45[/TD]
[TD="align: right"]$28.70[/TD]
[TD]F6: =Price+SandH+Tax[/TD]
[TD="align: right"]2/04/19[/TD]
[TD]Mon[/TD]
[TD]I6: =TEXT(Date,"ddd")[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: right"]$49.95[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$49.95[/TD]
[TD]F7: =C7+D7+E7[/TD]
[TD="align: right"]2/07/19[/TD]
[TD]Thu[/TD]
[TD]I7: =TEXT(H8,"ddd")[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: right"]$49.95[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$49.95[/TD]
[TD]F8: =Price+SandH+Tax[/TD]
[TD="align: right"]2/07/19[/TD]
[TD]Thu[/TD]
[TD]I8: =TEXT(Date,"ddd")[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: right"]$2.50[/TD]
[TD="align: right"]$2.50[/TD]
[TD="align: right"]$0.98[/TD]
[TD="align: right"]$5.98[/TD]
[TD]F9: =C9+D9+E9[/TD]
[TD="align: right"]2/12/19[/TD]
[TD]Tue[/TD]
[TD]I9: =TEXT(H10,"ddd")[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: right"]$2.50[/TD]
[TD="align: right"]$2.50[/TD]
[TD="align: right"]$0.98[/TD]
[TD="align: right"]$5.98[/TD]
[TD]F10: =Price+SandH+Tax[/TD]
[TD="align: right"]2/12/19[/TD]
[TD]Tue[/TD]
[TD]I10: =TEXT(Date,"ddd")[/TD]
[/TR]
</tbody>[/TABLE]

The columns have the same names as the column headers (Col C is named "Price", Col D is named "SandH", etc.).

Each pair of rows has identical data. The first one uses cell addresses. The second one uses the named ranges.

I find rows 6, 8, and 10 much easier to read and edit. I'd like to use this same mechanism for calls to UDFs.

 

[shg]

I think implied intersections are something you can't get at from UDFs -- like 3D ranges, like Excel only evaluating the target of the TRUE conditional of an IF formula.

For the construct you have, and alternative is named columns -- e.g., SandH as =RC4 ($D6 if defined from anywhere in row 6).

 

[JenniferMurphy]

I think implied intersections are something you can't get at from UDFs -- like 3D ranges, like Excel only evaluating the target of the TRUE conditional of an IF formula.

Is that what the formulas in Cols F & H are doing, implied intersections?

I guess I could do an explicit intersection, but then the formula gets complicated again.

I could swear that I found a way to do this some time ago, but I cannot find it now. Grrr...

For the construct you have, and alternative is named columns -- e.g., SandH as =RC4 ($D6 if defined from anywhere in row 6).

I don't understand what this means.