Excel VBA: Error Control in Dynamic Range Assignments (if(iserror(..))

Question

Hi, I've got a slightly complicated setup of data ranges, which arrive in Excel (Version 2003) by XML/Soap and are manipulated by VBA. The result is shown in multiple ListBoxes on various sheets. The main data sheet is codenamed "shData", the sheet name is "Data" (quite ingenuiously so far, hm?) and contains about 200 different ranges of data. One of the display pages is called Setup or shSetup. A Data range in Data could look like this:

ID    Last    First    Group           
1     Dwyer   Barb     A
2     King    Fu       A
3     Rea     Di A.    C

Let's say the range is named locally as "Data!Day0_Users".

Another range, say "Data!Day1_Users" could be just the header (if there is no data):

ID    Last    First    Group

These ranges get inserted to or deleted from and resized from within VBA, so I need a flexible referencing mechanism. Now, the ListBoxes can use a named range as the ListFillRange. For that, I define a local name in Setup, which references the one in data but uses an offset to exclude the header row: shSetup name "lbSomeListbox", refers to OFFSET(Data!Day0_Users, 1, , ROWS(Data!Day0_Users)-1) So far, so good. Obviously, when the range in Data is empty like in "Data!Day1_Users" in my example, such reference would generate an error. In such a case I would like to replace the reference with an empty array (which is defined as "Setup!EmptyRange5Col", which in turn is again an offset reference to Data!EmptyRange5Col to only return the "-" part.

Empty Empty   Empty    Empty
-     -       -        -

I have now tried to define my local range as a name using excels error control mechanism, but it does not work - the following should use the offset'ed data or replace it with my empty array.

=IF(ISERROR(OFFSET(Data!Day0_Users, 1, , ROWS(Data!Day0_Users)-1)), Setup!EmptyRange5Col, OFFSET(Data!Day0_Users, 1, , ROWS(Data!Day0_Users)-1))

If I use the same formula (I've also tried several other variations) on the worksheet itself using =ISREF(IF(IsError.....), it always evaluates to true; trying the ISERROR alone always evaluates false, all as would be expected. But as soon as I put it into the definition of the name the reference does not work, calls to shSetup.Names(...).RefersToRange produce runtime errors etc.

Does Excel have undocumented limitations on the use of formulas for names or am I being stupid? I've been looking at this for about 2 hours now and can't see the light. I'd really appreciate if you could push me in the right direction.

Thanks in advance, Stefan

Two observations. (1) OFFSET(Data!Day0_Users, 1, , ROWS(Data!Day0_Users)-1). The missing parameter, cols, is required. (2) The syntax for IF is IF(logical_test, [value_if_true], [value_if_false]). I have never tried using it to return a Range. Have you got this work in the way you wish? According to my experiments, in =OFFSET(reference, rows, cols, [height], [width]) height and width evaluate to one whatever values you provide. — Tony Dallimore
– Tony Dallimore, Commented Mar 13, 2012 at 20:15
Thanks, Tony. The missing parameter is strictly speaking correct, but it seems that Excel does not care that much, both versions work. I have now discovered that ISERROR() always evaluates to TRUE. However, ISREF() does exactly what I want even though I thought ISERROR would normally include #REF as well. Anyway, I'm going to answer my own question it seems. — ExternalUse
– ExternalUse, Commented Mar 13, 2012 at 21:46

ExternalUse · Accepted Answer · 2012-03-13 22:18:06Z

1

Sorry, I'd like to withdraw my question... Even though an invalid reference from OFFSET() would return #REF which is included in the check with ISERROR(), doing so does not work. This here works as expected for the ReferTo Part of my Named Range:

=IF(ISREF(OFFSET(Data!Day0_Users, 1, 0, ROWS(Data!Day0_Users)-1)), OFFSET(Data!Day0_Users, 1, 0, ROWS(Data!Day0_Users)-1), Setup!EmptyRange5Col)

Sorry for the question - I needed some sleep, I think.

answered Mar 13, 2012 at 22:18

ExternalUse

2,0752 gold badges26 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel VBA: Error Control in Dynamic Range Assignments (if(iserror(..))

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related