troubles passing a discontinuous named range into a custom function

0
votes

I've been looking around stack overflow for an answer to this for longer than I care to admit now.

Here's what I have: In a worksheet I have a bunch of discontinuous cells which I need to check for the existence of specific text. I've created a simple function to do this and can do this easily when I define that range manually (in code).

However, when I procedurally create a named range (while doing other stuff) and then try passing in the named range, the function never executes.

I know that the named range is being properly created because I have auto-formatting on it and also I can reference the range with excel formula which accept discontinuous ranges (SUM and whatnot).

Here's the pertinent portions of my code: 

Function customProcess1(NamedRange As Range) As Long
For Each c in NamedRange.Cells
...
Next c
End Function

In Excel when I type the formula as "=customProcess1(A1:A2)" I get my number back after the function runs. When I type in "=customProcess1(NamedRange)" my function never even executes.

Again, I'm using the named range as defined already in the document. I can observe the name in the name manager, it references the appropriate cells, i can use the range in formula which accept non-continuous ranges, etc. I can't figure out how to get my working named range into my function.

When I put the formula as "=customProcess1("NamedRange")" the function executes, but since the named range is not ""NamedRange"" but is "NamedRange" it fails to set the object as Range (the object is not found). I've tried taking the named range as a string, but again, if I don't put the quotes around the name, it won't even run the function. So then I've tried passing in a string with the quotes and taking the quotes off inside the function, but this isn't exactly working well either.

In short, I just want to get my non-continuous named range in my custom function. Once I do that, everything is golden.

Anyone have any ideas? I'm not sure why this has been such a chore.

2
vbaexcel
Are all the cells in the named range on the same sheet? – Rory
I'm still not sure why it was happening, it works today (with no alterations). However, both of the answers below can work, so thanks for the help! – shagans

2 Answers

1
votes

I'm not sure why what you're trying doesn't work and don't really have time to research that part of it, but you could do the following:

Function customProcess1(NamedRange As String) As Long
Dim TheRange As Range
Set TheRange = Range(NamedRange)
For Each c in TheRange.Cells
...
Next c
End Function

Hope this helps.

1
votes

Adapting your UDF(), I coded:

Function customProcess1(NamedRange As Range) As Long
    For Each c In NamedRange.Cells
        customProcess1 = customProcess1 + c.Value
    Next c
End Function

I then assigned the name Mike to the cells B6,C8,D10 and placed values in these cells. I then placed the formula:

=customProcess1(Mike)

in a cell and got the following:

enter image description here

NOTE:

I did not use =customProcess("Mike")