Check if cell is within a named range

Hello,

how can I check if a cell is in a specific named range? I only found the methods "HasCol" and "HasRow". But since named ranges can contain cells which are not adjacent or named ranges can be rectangles with missing cells, these 2 methods can give a false positive if used to check if a cell is within a specific named range.

Thanks in advance

In a general way, you really can't. This is because names are formulas, and they can not only contain cells not adjacent, they can contain anything.


For example, you could define the name:
"A1SquaredPlus1" = "=$A$1^2+1"

What are the cells of that name? Should HasCol return true for A1?  Or if you define the name "=Sum($A$1:$A$10) - $A$1"?  Or if you just define a name as "=7"? 

You might even use relative references in a name: In Excel, position yourself in cell A2, and add a name "Add1ToPrevious" with the definition "=Sheet1!A1+1". Then, position in say cell B5, and pull the name manager again. you'll see that the definition is now: "=Sheet1!B4+1". And if you write "=Add1ToPrevious" in B5, it will write the value of B4 + 1. in B6, it will write the value of B5 + 1.

For arbitrary names, you can't really do much more than to parse the RangeFormula and look for what you find. But in reality, while you can write whatever formula you want in a name (including as you said non adjacent ranges, but not limited to that), the true is that 99.99% of the time those are just rectangular ranges. This is why we offer HasRow and HasCol as convenience methods, so you don't have to parse the formula to figure out which cells (if any) are included in the name.

FlexCelReport will also only work with rectangular ranges, and internally it uses "HasRow", "HasCol". so if you are checking for FlexCel report compliance, the first thing to check is that those names are rectangular.
We can't really do much with a database name which is "=7". Not even non adjacent ranges make sense for a report. So just check if the "Top" property is less than 1, and if it is, report an invalid name.

A final note: While as said, almost any name you'll see will be rectangular, there is an exception that can be common. For "rows to repeat at top", and "columns to repeat at left" when printing, Excel uses a single non rectangular range. So you can have a "PrintTitles" that is: "=Sheet1!$A:$A,Sheet1!$1:$1". If you are checking for validity in FlexCel "names", check that the name isn't internal first. (or that it starts with "__", "", "I" or "II_"