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.

R/C	C	D	E
4	11/12/19	TRUE	D4: =myisdate(C4)
5	11/12/19	TRUE	D5: =myisdate(temp)
6	11/12/19	FALSE	D6: =myisdate(DateUseBy)
7	11/12/19	FALSE	D7: =myisdate(+DateUseBy)
8	11/12/19	FALSE	D8: =myisdate(0+DateUseBy)

<tbody>
</tbody>

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?

 

[shg]

From any cell in row 6, Name Manager > New, SandH Refers to: =$D6

Then proceed as before.

 

[JenniferMurphy]

From any cell in row 6, Name Manager > New, SandH Refers to: =$D6

Then proceed as before.

Wow!!! It's like magic. Going back to my original table, I selected a cell in row 4, then called the Name Manager and defined DateUseBy = $C4. The formula in D6 now works.

R/C	C	D	E	F	G
4	11/01/19	TRUE	D4: =myisdate(C4)	43770	F4: =DateUseBy
5	11/02/19	TRUE	D5: =myisdate(temp)	43771	F5: =DateUseBy
6	11/03/19	TRUE	D6: =myisdate(DateUseBy)	43772	F6: =DateUseBy
7	11/04/19	#VALUE!	D7: =myisdate(+DateUseBy)	43773	F7: =DateUseBy
8	11/05/19	#VALUE!	D8: =myisdate(0+DateUseBy)	43774	F8: =DateUseBy

<tbody>
</tbody>

But how it is working? Is it relative addressing like with conditional formatting? Since we made the column absolute and the row relative, will any reference to DateUseBy access a cell in Col C in the same row?

Where is this documented? What do I search for to read more?

Thank you!!!

PS: I see that the formulas in D7 & D8 still fail. Can I not do arithmetic on data so accessed or did the arithmetic make into something other than a date?

 

[RoryA]

Did you change your code to pass a Range argument? That's the only reason I can see to get a #VALUE ! error with those formulas.

 

[JenniferMurphy]

From any cell in row 6, Name Manager > New, SandH Refers to: =$D6

Then proceed as before.

Shg,

This tip has saved me more time and frustration than I can imagine. Thank you so much.

Before you gave me this, I was using all kinds of convoluted and error-prone methods to pass a simple dynamically variable range to a UDF. Now, with one simple definition, everything works and it is easy to understand.

Here's a simple version of my latest implementation. The WtdRtg function calculates a weighted rating for products. Each product is given a rating (0-10) on each of several features. The features are then given relative weights.

In this table, we compare three products (A, B, & C) on three features (Speed, Waste, & Quiet). In this example, they are all given equal weight (5). Since their ratings all add up to the same value (20), they all get the same weighted average (6.6667).

R/C	C	D	E	F	G	H	I	J
5	Weights	5	5	5	Weighted		Weighted
6	Features	Speed	Waste	Quiet	Rating	Formula	Rating	Formula
7	Prod A	3	8	9	6.6667	G7: =wtdrtg(Ratings,Weights)	6.6667	I7: =wtdrtg(C7:G7, C$5:G$5)
8	Prod B	6	8	6	6.6667	G8: =wtdrtg(Ratings,Weights)	6.6667	I8: =wtdrtg(C8:G8, C$5:G$5)
9	Prod C	8	7	5	6.6667	G9: =wtdrtg(Ratings,Weights)	6.6667	I9: =wtdrtg(C9:G9, C$5:G$5)

<tbody>
</tbody>

The formula in G as shown in H, uses the named ranges as you suggested.

• Ratings is defined as [$Cr:$Gr]. The columns are static. The rows are dynamic. They will assume the value of the row where it is referenced. So the call in G8 will pass [$C$8:$G$8].
• Weights is defined as [$C5:$G$5]. Both the rows and columns are static, so will remain the same regardless of where they are called from.

The formula in I as shows in J passes the same ranges, but is less readable.

Both ranges include the columns before and after the data columns (D:F). This allows me to add columns for new features without having to redefine the ranges. The function knows this and will process cells 2 through N-1. (See code below.)

Now suppose we care more about Speed and less about Quiet. We can change the weights and get different weighted averages.

R/C	C	D	E	F	G	H
5	Weights	10	5	1	Weighted
6	Features	Speed	Waste	Quiet	Rating	Formula
7	Prod A	3	8	9	4.9375	G7: =wtdrtg(Ratings,Weights)
8	Prod B	6	8	6	6.6250	G8: =wtdrtg(Ratings,Weights)
9	Prod C	8	7	5	7.5000	G9: =wtdrtg(Ratings,Weights)

<tbody>
</tbody>

Now product C is the clear winner.

Here's the code:

'===============================================================================================
'                               Test Variable Range

' This function tests two ranges, Ratings & Weights.
' The range is defined to include the column before and the column after the target range.
' See the workbook for details.

'     Change Log
' 04/16/19  Created based on information from shg on Mr. Excel, thread [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1086964]#1086964[/URL] .
'===============================================================================================
Public Function WtdRtg(Ratings As Range, Weights As Range) As Double

Dim Col1 As Long          'The first row
Dim Coln As Long          'The last row
Dim NumCols As Long       'The number
Dim iCol As Integer       'Column loop index
Dim NextRtg As Double     'Temp variable for next rating in range
Dim NextWt As Double      'Temp variable for next weight in range
Dim SumWtdRtgs As Double  'Tally the weighted ratings
Dim SumWts As Double      'Tally the weights

NumCols = Ratings.Columns.Count 'The number of columns
Col1 = 2                        'Start with the second column
Coln = NumCols - 1              'End with the next to last column

SumWtdRtgs = 0              'Initialize the sum of the weighted ratings
SumWts = 0                  'Initialize the sum of the weights
For iCol = Col1 To Coln     'Loop through columns
  NextRtg = Ratings(1, iCol)  'Get the next rating
  NextWt = Weights(1, iCol)  'Get the next weight
  SumWtdRtgs = SumWtdRtgs + (NextRtg * NextWt)
  SumWts = SumWts + NextWt
Next iCol

If SumWts = 0 Then            'If the sum of the weights = 0
  WtdRtg = 0                    'Avoid divide by zero
Else                          'If OK,
  WtdRtg = SumWtdRtgs / SumWts  'Calculate the weighted rating
End If

End Function

Again, thank you Shg. This has made my life much easier.

PS: I would welcome any comments or suggestions for improving this code.

 

[shg]

Both ranges include the columns before and after the data columns (D:F). This allows me to add columns for new features without having to redefine the ranges.

I would define

Weights (while in any cell) Refers to: =INDEX(Sheet1!$5:$5, COLUMN(Sheet1!$C$5) + 1):INDEX(Sheet1!$5:$5, COLUMN(Sheet1!$G$5) - 1)

Ratings (while in row 7) Refers to: =INDEX(Sheet1!myRow, COLUMN(Sheet1!$C7) + 1):INDEX(Sheet1!myRow, COLUMN(Sheet1!$G7) - 1)

... and code as

Public Function WtdRtg(rRtg As Range, rWgt As Range) As Variant
  Dim dSumW         As Double   ' sum of rWgt
  Dim dSumP         As Double   ' sumproduct of rRtg and rWgt
  Dim i             As Long     ' row index  to rRtg and rWgt
  Dim j             As Long     ' col index  to rRtg and rWgt

  If rRtg.Rows.Count <> rWgt.Rows.Count Or _
     rRtg.Columns.Count <> rWgt.Columns.Count Then
    WtdRtg = CVErr(xlErrValue)

  Else
    For i = 1 To rRtg.Rows.Count
      For j = 1 To rRtg.Columns.Count
        dSumW = dSumW + rWgt(i, j).Value
        dSumP = dSumP + rWgt(i, j).Value * rRtg(i, j).Value
      Next j
    Next i

    If dSumW = 0# Then
      WtdRtg = CVErr(xlErrDiv0)
    Else
      WtdRtg = dSumP / dSumW
    End If
  End If
End Function

 

[JenniferMurphy]

I would define

. . .

I'll have to study this a bit. Thanks.

 

[shg]

I think we covered this in a different thread, but if not ...

Select any cell in row 1 and define myRow with sheet scope as =Sheet1!1:1

Or you could run this:

Sub AddSomeNames()
  Dim wks           As Worksheet

  With ActiveWorkbook
    .Add Name:="conBig", RefersTo:="=9E+307 + 8.97693134862315E+307 + 6E+292"
    .Add Name:="conZzz", RefersTo:="=rept(""z"", 255)"

    For Each wks In .Worksheets
      AddWksNames wks
    Next wks
  End With
End Sub

Sub AddWksNames(Optional wks As Worksheet)
  If wks Is Nothing Then Set wks = ActiveSheet

  With wks.Names
    .Add Name:="Me", RefersToR1C1:="=rc", Visible:=False
    .Add Name:="myCol", RefersToR1C1:="=c", Visible:=False
    .Add Name:="myRow", RefersToR1C1:="=r", Visible:=False

    .Add Name:="relN", RefersToR1C1:="=r[-1]c", Visible:=False
    .Add Name:="relE", RefersToR1C1:="=rc[1]", Visible:=False
    .Add Name:="relS", RefersToR1C1:="=r[+1]c", Visible:=False
    .Add Name:="relW", RefersToR1C1:="=rc[-1]", Visible:=False
  End With
End Sub

conBig refers to the biggest number a Double can contain (e.g., row containing last number: =MATCH(conBig, A:A))

conZzz refers to a big string of z's (e.g., row containing last text: =MATCH(conZzz, A:A))

Me refers to the cell in which is appears (e.g., =ROWS(A$1:Me))

myRow/myCol refers the the row/column in which it appears (e.g., as previously shown)

relN/E/S/W refer to the cell above/right/below/left of the one in which it appears (e.g., =SUM(A$1:relN))