Solved: Define Name from a function

[danlu]

Hi,

I would like to capture all cells that fullfill some criteria into a Range or variable (or something else appropriate) so that these cell references can be worked on by another formula by referencing this named range containing the cell references. Ex of other formulas that would use the defined range for some operation are formulas like SUM, COUNT or AVG.

One important thing about the cells that fullfill some criteria and thus should be included in the named range is that these cells may not be placed directly after each other and therefore the defined range could be a discontinous area. For ex it could consist of the cells A1,A2, B4, M1101, N10,N11,N12,P504.
Is this possible to have a named range defined by cells not placed in direct order after each other?

If this is possible, could a way to define this discontinous area be to:
First create a function (or procedure) which contains the logic for finding which cells meet some criteria defined in this function(procedure) and then
Secondly set the defined range equal to this function(procedure)'s output.

[mikerickson]

Something like this might work

Sub defineName()
Rem creates myNamedRange of all cells in column A than are less than 100
Dim myRange As Range
Dim oneCell As Range

Rem indentify range
With ThisWorkbook.Sheets("sheet1").Range("A:A")
For Each oneCell In Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
If IsNumeric(oneCell.Value) And Val(oneCell.Value) < 100 Then
If myRange Is Nothing Then Set myRange = oneCell
Set myRange = Application.Union(myRange, oneCell)
End If
Next oneCell
End With

Rem name the range
myRange.Name = "myNamedRange"
End Sub

[danlu]

Thanks a lot it works great. Just from curiousity: can you refer direcly to the variable myRange (from a worksheet)or do you need to explicitly designate a name to this variable (as I asked for above) to be able to refer to it when a user works in a worksheet?

[Bob Phillips]

You could perhaps define a UDF that returns a variant array that a normal Worksheet function such as SUM would act upon.

[mikerickson]

myRange is a range variable that (as currently scoped) can only be referred to by that particular routine.

However, the last thing that routine does is create a named range, myNamedRange, that can be referred to by spreadsheet formulae as well as other VB routines.

[danlu]

ok, so could you say that using a named range is the standard way of delivering values that a variable holds so a user (no VBA trained) user can work with this variable's value on a spreadsheet?

[mikerickson]

1) I don't plan for users (with or without VBA training) to enter anything but values into cells. Named ranges are tools for developers not users. (Yes, many folks wear more than one hat.)

2) Named ranges are one way to return a range from VB routines to the worksheet. As mentioned above, a UDF would be the other choice. A (range) UDF that takes no arguments might best be replaced by a Named range.