Passing named range to UDF

JenniferMurphy

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
2,687
Office Version
  1. 365
Platform
  1. Windows
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:

Code: 
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?
 
Sort by date Sort by votes
From any cell in row 6, Name Manager > New, SandH Refers to: =$D6

Then proceed as before.
 
Last edited:
Upvote 0

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
shg said:
From any cell in row 6, Name Manager > New, SandH Refers to: =$D6

Then proceed as before.
Click to expand...

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.

[TABLE="class: grid, width: 600"]
<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]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]11/01/19[/TD]
[TD="align: center"]TRUE[/TD]
[TD]D4: =myisdate(C4)[/TD]
[TD="align: right"]43770[/TD]
[TD]F4: =DateUseBy[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]11/02/19[/TD]
[TD="align: center"]TRUE[/TD]
[TD]D5: =myisdate(temp)[/TD]
[TD="align: right"]43771[/TD]
[TD]F5: =DateUseBy[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]11/03/19[/TD]
[TD="align: center"]TRUE[/TD]
[TD]D6: =myisdate(DateUseBy)[/TD]
[TD="align: right"]43772[/TD]
[TD]F6: =DateUseBy[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]11/04/19[/TD]
[TD="align: center"]#VALUE![/TD]
[TD]D7: =myisdate(+DateUseBy)[/TD]
[TD="align: right"]43773[/TD]
[TD]F7: =DateUseBy[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]11/05/19[/TD]
[TD="align: center"]#VALUE![/TD]
[TD]D8: =myisdate(0+DateUseBy)[/TD]
[TD="align: right"]43774[/TD]
[TD]F8: =DateUseBy[/TD]
[/TR]
</tbody>[/TABLE]

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?
 
Upvote 0
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.
 
Upvote 0
shg said:
From any cell in row 6, Name Manager > New, SandH Refers to: =$D6

Then proceed as before.
Click to expand...
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).
[TABLE="class: grid, width: 800"]
<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"]5[/TD]
[TD="align: right"]Weights[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]Weighted[/TD]
[TD]
[/TD]
[TD="align: center"]Weighted[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: right"]Features[/TD]
[TD="align: center"]Speed[/TD]
[TD="align: center"]Waste[/TD]
[TD="align: center"]Quiet[/TD]
[TD="align: center"]Rating[/TD]
[TD="align: center"]Formula[/TD]
[TD="align: center"]Rating[/TD]
[TD="align: center"]Formula[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: right"]Prod A[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]6.6667[/TD]
[TD]G7: =wtdrtg(Ratings,Weights)[/TD]
[TD="align: center"]6.6667[/TD]
[TD]I7: =wtdrtg(C7:G7, C$5:G$5)[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: right"]Prod B[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]6.6667[/TD]
[TD]G8: =wtdrtg(Ratings,Weights)[/TD]
[TD="align: center"]6.6667[/TD]
[TD]I8: =wtdrtg(C8:G8, C$5:G$5)[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: right"]Prod C[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6.6667[/TD]
[TD]G9: =wtdrtg(Ratings,Weights)[/TD]
[TD="align: center"]6.6667[/TD]
[TD]I9: =wtdrtg(C9:G9, C$5:G$5)[/TD]
[/TR]
</tbody>[/TABLE]

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.

[TABLE="class: grid, width: 600"]
<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]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]Weights[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Weighted[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: right"]Features[/TD]
[TD="align: center"]Speed[/TD]
[TD="align: center"]Waste[/TD]
[TD="align: center"]Quiet[/TD]
[TD="align: center"]Rating[/TD]
[TD="align: center"]Formula[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: right"]Prod A[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]4.9375[/TD]
[TD]G7: =wtdrtg(Ratings,Weights)[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: right"]Prod B[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]6.6250[/TD]
[TD]G8: =wtdrtg(Ratings,Weights)[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: right"]Prod C[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]7.5000[/TD]
[TD]G9: =wtdrtg(Ratings,Weights)[/TD]
[/TR]
</tbody>[/TABLE]

Now product C is the clear winner.

Here's the code:

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.
 
Upvote 0
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.
Click to expand...

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

Code: 
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
 
Last edited:
Upvote 0
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:

Code: 
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))
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

SimonHughes
  • Solved
Text in cells but no text visible
Replies
6
Views
206
SimonHughes
SimonHughes
D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
570
smozgur
smozgur
MarkSELA
  • Question Question
Multiple choice question generator using randomly selected Excel ranges
Replies
3
Views
342
MarkSELA
MarkSELA
M
  • Question Question
Copied sheet code to new file and lost some functionality....not sure how/why
Replies
5
Views
90
MeaclH
M
F
  • Solved
Selecting range based on another cell last row (VBA)
2
Replies
16
Views
563
feni1388
F

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top