Avatar of AL_XResearch
AL_XResearch
Flag for United Kingdom of Great Britain and Northern Ireland asked on

Excel VBA: Named range can't be assigned to range object

Objective

To assign the rows from a named range to a range object so it can be processed.
The named range is created by another earlier function.

Issue

I have the following code (an abbreviated extract showing only the relevant code):

    Dim rngOrigDataRows As Range
Dim blnInputSheetRowsFiltered  as Boolean
    
    ' Init. var.
    blnInputSheetRowsFiltered = blnWorkbookNameExists(wkbInputModule, INPTMOD_NMDRNG_ROWFILTER_RSLTROWS)
    
    With wkbInputModule
    
        ' Get the origonal rowset to work on
        If blnInputSheetRowsFiltered Then
            Set rngOrigDataRows = .Names(INPTMOD_NMDRNG_ROWFILTER_RSLTROWS).RefersToRange
        Else
            Set rngOrigDataRows = .Names("InputSheetDataRows").RefersToRange
        End If

Open in new window


When the named range 'INPTMOD_NMDRNG_ROWFILTER_RSLTROWS' does exist the code that assigns it to a range fails with the following error:

1004::Application-defined or object-defined error

The contents of the named range is as follows:

wkbInputModule.Names(INPTMOD_NMDRNG_ROWFILTER_RSLTROWS).RefersTo = "=InputSheet!$35:$37,InputSheet!$41:$48,InputSheet!$52:$71,InputSheet!$73:$84,InputSheet!$87:$94,InputSheet!$97:$126,InputSheet!$129:$149,InputSheet!$153:$160,InputSheet!$164:$183,InputSheet!$185:$196,InputSheet!$199:$228,InputSheet!$231:$250"

Open in new window


There are 12 areas to the range.

 If I have only 11 areas then I get no error.

Attempts to resolve

I have even tried changing the 12th and final area to point to different rows but it doesn't help.
I have also tried ensuring that the 'InputSheet' worksheet is not protected (just in case) but this had no effect.
I created a new workbook in a new session of Excel and renamed on of the worksheets 'InputSheet' and used the above 'RefersTo' string to create a new range and the error still occurred.

Question

I have never come across this before. To my knowledge ranges can consist of more than 600 areas (no idea why you would want that) but that is far far more than I am using.

My VBA project uses literally hundreds of named ranges over about half-a-dozen workbooks and I have never hit this issue with large row multi-area selections.

What can this be and how can I work around it ?
Microsoft ExcelMicrosoft OfficeProgrammingVBA

Avatar of undefined
Last Comment
AL_XResearch

8/22/2022 - Mon
AL_XResearch

ASKER
I have tried the following test and found there was no error
1. Shortened the area list to exclude the 12th area
2. Assigned the 12th area address to a seperate named range
3. Assigned both of these named ranges to range objects
4. Used the 'Union' function to combine to the two ranges into a third
5. Printed out the 'RefersTo' string of the combined range - this was the 12 area range that I had originally

I then tried to set a new range to the combined range that I had created in step 4 above - no issue

I then created a new named range (using the '.names.add' syntax) and set it's 'RefersTo' argument equal to the combined range I created in step 4 above - no issue

I then created another new range and set it from the 'RefersToRange' property of the newly created named range - it failed with the same error.

It therefore seems that the issue is clearly with the 'RefersToRange' property for some reason.
AL_XResearch

ASKER
I have found a workaround - although not a satisfactory one.

The below code will cause the error (where 'Result' is the combination of my previously created ranges to return the original rowset)

Set rngCheck = wkbInputModule.Names("Result").RefersToRange

Open in new window


However as long as you know what worksheet all the cells are on the following workaround works.

Set rngCheck = ThisWorkbook.Worksheets("InputSheet").Range(ThisWorkbook.Names("Result").RefersTo)

Open in new window


As I said this is very limiting because you must know what cells the sheet is on.
AL_XResearch

ASKER
Annoyingly I put my 'workaround' into the live code and that too fails with the same non-descriptive-non-helpful error !
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Rory Archibald

Not sure why you've duplicated this question, or what you mean by "as long as you know what worksheet all the cells are on" but if you created a range that refers to cells on different sheets for some reason, you cannot assign it to a Range object in VBA.
AL_XResearch

ASKER
Rory: Thanks for the answers

That is what I meant in my post - that the worksheet.range object wouldn't work unless all the cells in the 'RefersTo' address string were on the same worksheet.

In any case my workaround, whilst working in a test book, doesn't work in my actual code. I can no more guess why the workaround doesn't work than the original code. Both should be valid.

I am not sure I know what you mean by 'Duplicated this question'.

This is critical to my code to resolve or find a workaround.
Rory Archibald

It's not just Worksheet.Range. Even if you created separate Range objects from each sheet, you couldn't Union them into one Range object. In VBA a Range object can only refer to cells on one sheet. Did you try what I posted in your duplicate? (I hadn't seen this then)
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
Rory Archibald

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
AL_XResearch

ASKER
Rory: yes we are both in agreement about the restrictions on a range object - just maybe phrasing it differently.

What duplicate ? This is the first time I have posted this issue, it only happened this morning.
AL_XResearch

ASKER
If the length of the 'RefersTo' property is the issue then:

1.

Why can I print it to the debug window even though it is 'too long' to use

2.

Why in the test environment with the same 'refersto' string does it allow me to create a worksheet range whereas in the live system it does not.
I have had much larger multi-area ranges before and never run into this sort of issue.
Rory Archibald

This duplicate: https://www.experts-exchange.com/questions/28699192/Excel-VBA-Named-range-can't-be-assigned-to-range-object.html

If it's not the length of the refersto, why would it work when shortening the sheet name? Your original fails for me in a new blank workbook.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
AL_XResearch

ASKER
Not sure how that duplciate was created - certainly not intentional.

I agree that RefersTo seems a likely candidate. I however didn't know until today that there was a limitation on RefersTo - I have always managed to create large named ranges without coming across this issue.

The 'orgional' code failed for me too in a blank workbook but my 'workaround' worked in a new blank book but failed in the live system - which I can't understand.
AL_XResearch

ASKER
Do you know what the actual length limit is on 'RefersTo' ?
AL_XResearch

ASKER
After some trial and error I have found the maximum length of the 'RefersTo' string (the length it can be before 'RefersToRange' will stop working) is 246.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Rory Archibald

My suspicion would be the usual 255 characters. That is certainly the most you can use as an argument to the Range property.
SOLUTION
AL_XResearch

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Rory Archibald

FWIW, your original was 241 characters and failed. I've succeeded with names with 249 characters. So I think there is something more complicated afoot, which may relate to the internal storage of the name.
AL_XResearch

ASKER
More than likely. I thought 246 was a rather random number. Until I have more definite facts though I will set this as the maximum limit for using 'RefersToRange'
Your help has saved me hundreds of hours of internet surfing.
fblack61
Rory Archibald

I have it working with 255 characters in the RefersTo. The Excel UI will not allow typing more than this in, which would tend to support it as a limit.
SOLUTION
AL_XResearch

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
AL_XResearch

ASKER
OK there is definitely some additional mysterious factor going on here. I got the same '' error today trying to use the 'RefersToRange. method on a defined name which has a 'RefersTo' string of :

"=InputSheet!$A:$U"

I changed the code to use my new function and there was no issue !
AL_XResearch

ASKER
Rory helped me on my journey to discover the cause of this and so deserves the points but my function provides a workaround to this issue although neither has actually explained or completely solved the issue.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.