JenniferMurphy
Well-known Member
- Joined
- Jul 23, 2011
- Messages
- 2,532
- Office Version
- 365
- Platform
- Windows
I need to pass a range to a UDF. I would like to use named ranges. I may need to expand the range by adding rows or columns including at the ends of the range. But if I am not careful, rows or columns that are added at the ends of the range may or may not be included in the expanded range. My solution is to name the rows and columns that are just outside the range. But this presents a problem for the UDF, because the range is too large.
I have come up with two possible solutions. I would appreciate any comments or suggestions.
Here's the table. The columns have been assigned the names shown in Row 4. That is, Col D is named "ProdA". Similarly, the rows have been assigned the names shown in Col C.
<tbody>
</tbody>
Here's the UDF code. All it does is return the list of addresses of the cells in the range it was passed.
And here are the results.
<tbody>
</tbody>
In Row 5, I pass the UDF the literal range I want processed. This is the correct result.
In Row 6, I do the same, but using the Intersect operator.
In Row 7, I do it using the named rows and columns. This works, but has the problem mentioned above if I were to expand the range.
In Row 8, I use the names of the columns just outside the range. This includes those extra cells, which is not what I want.
In Row 9, I use the Offset function, which I didn't think would work, but it seems like it does. Are there any problems with this approach?
In Row 10, I use the optional pPlus2SW parameter to tell the UDF to skip the first and last cells in each row. This also works and seems simpler to me.
Does anyone have any comments?
I have come up with two possible solutions. I would appreciate any comments or suggestions.
Here's the table. The columns have been assigned the names shown in Row 4. That is, Col D is named "ProdA". Similarly, the rows have been assigned the names shown in Col C.
| R/C | C | D | E | F | G | H |
| 4 | Prod0 | ProdA | ProdB | ProdC | ProdD | ProdN |
| 5 | Row_1 | 1 | 2 | 3 | 4 | |
| 6 | Row_2 | 5 | 6 | 7 | 8 | |
| 7 | Row_3 | 9 | 10 | 11 | 12 |
<tbody>
</tbody>
Here's the UDF code. All it does is return the list of addresses of the cells in the range it was passed.
Code:
Function RangeTest(pRange As Range, Optional pPlus2SW As Boolean = False) As String
Dim RowBeg, RowEnd, ColBeg, ColEnd, i, j
RowBeg = 1: RowEnd = pRange.Rows.Count
ColBeg = 1: ColEnd = pRange.Columns.Count
If pPlus2SW Then
ColBeg = 2: ColEnd = ColEnd - 1
End If
RangeTest = ""
For i = RowBeg To RowEnd
For j = ColBeg To ColEnd
RangeTest = RangeTest & " " & pRange.Cells(i, j).Address
Next j
Next i
End FunctionAnd here are the results.
| R/C | K | L |
| 4 | Result | Formula |
| 5 | $D$5 $E$5 $F$5 $G$5 $D$6 $E$6 $F$6 $G$6 | K5: =rangetest(D5:G6) |
| 6 | $D$5 $E$5 $F$5 $G$5 $D$6 $E$6 $F$6 $G$6 | K6: =rangetest((D:D 5:5) : (G:G 6:6)) |
| 7 | $D$5 $E$5 $F$5 $G$5 $D$6 $E$6 $F$6 $G$6 | K7: =rangetest((ProdA Row_1) : (ProdD Row_2)) |
| 8 | $C$5 $D$5 $E$5 $F$5 $G$5 $H$5 $C$6 $D$6 $E$6 $F$6 $G$6 $H$6 | K8: =rangetest((Prod0 Row_1) : (ProdN Row_2)) |
| 9 | $D$5 $E$5 $F$5 $G$5 $D$6 $E$6 $F$6 $G$6 | K9: =rangetest((OFFSET(Prod0,0,1) Row_1) : (OFFSET(ProdN,0,-1) Row_2)) |
| 10 | $D$5 $E$5 $F$5 $G$5 $D$6 $E$6 $F$6 $G$6 | K10: =rangetest((Prod0 Row_1) : (ProdN Row_2),TRUE) |
<tbody>
</tbody>
In Row 5, I pass the UDF the literal range I want processed. This is the correct result.
In Row 6, I do the same, but using the Intersect operator.
In Row 7, I do it using the named rows and columns. This works, but has the problem mentioned above if I were to expand the range.
In Row 8, I use the names of the columns just outside the range. This includes those extra cells, which is not what I want.
In Row 9, I use the Offset function, which I didn't think would work, but it seems like it does. Are there any problems with this approach?
In Row 10, I use the optional pPlus2SW parameter to tell the UDF to skip the first and last cells in each row. This also works and seems simpler to me.
Does anyone have any comments?
