Excel can't evaluate Range Name

I have a complex formula used in 100's of range names. I use a macro to build the formula for me because it's too easy to screw it up when typing manually.

Excel can't evaluate the formula when using the Name->Define dialogue.
When I put my cursor in the RefersTo box, there are no marching ants around the range being defined. Names("myName").RefersToRange returns nothing in my VBE code.

But I can find no evidence that formulas in my workbook that refer to the range have any problem. "=Index(myName,4,1)" always gets the correct value. "=Rows(myName)" is always correct. So obviously Excel can and does evaluate the range correctly.

I'm trying to determine if that will cause any kind of problems because Excel can evaluate the formula sometimes but not at other times. I can't think of what to Google to find if there are any reports or studies dealing with this issue.

The formula defines a dynamic range from just 1 cell address so that the range can be cut and pasted to another location, and it's not volatile.

I don't think this will help, but here is the formula:

Please Login or Register  to view this content.

Any help with what to Google would be appreciated. Or if you can point to a knowledge base article, would be great.

This is not a named range, so nothing to evaluate.
It's a UDF; the only thing Excel has to do is find the UDF (not too complicated if put into a macromodule).

Without a UDF:

PHP Code: 

=INDEX($C:$C,ROW($C$18)+2,1):INDEX($C:$C,ROW($C$18)+2+CodesCount-1,1) 


This is the RefersTo for a range name. Name: myName, RefersTo: =INDEX(WC($C$18),ROW($C$18)+2,1):INDEX(WC($C$18),ROW($C$18)+2+CodesCount-1,1)

But I can't find a way in VBA to get it evaluated. It just returns nothing. And in the RefersTo box it doesn't put the marching ants around the range that it defines.

snb;
The reason I don't use =INDEX($C:$C,ROW($C$18)+2,1):INDEX($C:$C,ROW($C$18)+2+CodesCount-1,1) is that it can't be cut and pasted in a different cell. If I cut $C$18:$C$300 and paste it into $D$18:$D$300 the name's RefersTo would then read =INDEX($C:$C,ROW($D$18)+2,1):INDEX($C:$C,ROW($D$18)+2+CodesCount-1,1) which would return the cells in $C:$C, but the data has been moved into $D:$D

I see,

but what if using:

PHP Code: 

=INDEX(C:C,ROW($C$18)+2,1):INDEX(C:C,ROW($C$18)+2+CodesCount-1,1) 


combined with

Please Login or Register  to view this content.

I don't think your question about Myname can be answered without a posted workbook.

Personally, I think I'd use OFFSET for that. It may be volatile, but it's a lot simpler.

I agree that Offset is simpler. But the owner of the file is complaining that it's not as fast as it used to be and so I'm trying to get rid of all the volatile functions. At the same time it needs to be capable of cutting and pasting the range. He did that once in the past when the ranges were defined with "INDEX($C:$C:.....)" and it really screwed things up. He's promised never to do it again, but I don't want to take any chances that he hires someone else and someone forgets that they have to cut the entire column. I could probably design some way of preventing him from cutting part of a column, but if I can just get a non volatile dynamic range that can still be cut, then I don't have to worry about it.

The only reason this is a problem now is that I have a sub tool that searches for all the range names that contain a given cell, then goes looking for all the formulas in the workbook that use that range name. But with this formula, the sub can't determine what the range is to see if it intersects the given cell. I can probably write a sub that evaluates the RefersTo, but I'd rather not if I can avoid it.

snb;
It's not my file to post. But I can't figure out how it would help to see the file anyway. You can put the range name into your own workbook and see that VBA can't evaluate it and the Define Name dialogue can't evaluate it, but the workbook can.

I can't figure out what tst() is doing. I'm guessing that it's duplicating the data from one column into the column beside it, but I have no idea how that helps. And what if the user cuts from $C$18:$C$300 and pastes into $Z$68:$Z$350 with data in between the 2 columns? If I'm not understanding, please explain what it's supposed to be doing.

I did what you said and got the desired result ?

I can get the range reference using evaluate:

Please Login or Register  to view this content.

for example. It does sound to me as though either your workbook structure needs changing, or you need much tighter control over what the users can do, or you will spend your life patching over issues rather than curing them.

snb;
It looks like you don't have the problem I'm having. I don't get the marching ants.
I see you're using 2003, and I'm using 2002. I hope that's the difference. I recently had to fix this file when it got corrupted. This may indicate that it's still corrupted.

I'm curious to know if VBA can evaluate it. In the immediate window type

Please Login or Register  to view this content.

and see if it prints the address.

@snb,
It doesn't appear that you are using CodesCount in your name. If I use a literal value there, I get marching ants; using the name instead, I don't.

RS;
This is really weird.
When I "? Evaluate("myName").address", I get the address.
But when I "? activesheet.names("myName").RefersToRange.Address", I get an error 1004 "application or object defined error". I'm starting to think that my file is still corrupted.

Sometimes I do feel like I'm patching over issues. This particular workbook has been a pain because the user keeps wanting more and more capabilities and sometimes they conflict with what he has asked for in the past. Like when he said he wanted to be able to cut & paste at will, I changed all the dynamic names from "INDEX($C:$C....." to "OffSet(.....". Now he wants it to work faster. Up until now I haven't had any major problem giving him what he wants, but this one is causing me a big headache. But I'm now thinking the file's corrupted. He had been using this file for over 5 years when all of a sudden the sheet tabs disappeared. Now I'm guessing that whatever corrupted it is still there.

RS;
That's what I observed the first time I manually entered the formula. I had the marching ants right up until I entered "CodesCount". The name still worked in formulas in the workbook, just no ants while in the Define dialogue. I just assumed that there were too many UDFs and other range names in the formula for Excel to handle it. When I went to VBE and tried to "? ....RerfersToRange.Address" and got an error it seemed to confirm my thinking. But now that I see that "? Evaluate...." works, I'm more confused.

I don't think it's a sign of corruption since I get the same behaviour in a new workbook. I imagine the name is simply too complex for whatever method is used by the referstorange property, whereas evaluate calculates the formulas as it would on a sheet. That is a guess though.

I do have one good thing to say. My sub that searches for where a cell is being referenced was using .RefersToRange. I changed it to use Evaluate and it now finds the name with the complex formula. Thanks for pointing me to Evaluate.

I'm really glad to hear you get the same results I get.