Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
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):
When the named range 'INPTMOD_NMDRNG_ROWFILTER_
1004::Application-defined or object-defined error
The contents of the named range is as follows:
There are 12 areas to the range.
If I have only 11 areas then I get no error.
Attempts to resolve
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 ?
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
When the named range 'INPTMOD_NMDRNG_ROWFILTER_
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"
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 ?
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.
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.
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)
However as long as you know what worksheet all the cells are on the following workaround works.
As I said this is very limiting because you must know what cells the sheet is on.
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
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)
As I said this is very limiting because you must know what cells the sheet is on.
Annoyingly I put my 'workaround' into the live code and that too fails with the same non-descriptive-non-helpfu
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.
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.
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.
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)
On further inspection, it appears to relate to the length of the refersto string. If I rename the sheet to 'f' after adding the name, I can use the RefersToRange property.
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.
What duplicate ? This is the first time I have posted this issue, it only happened this morning.
If the length of the 'RefersTo' property is the issue then:
I have had much larger multi-area ranges before and never run into this sort of issue.
1.
Why can I print it to the debug window even though it is 'too long' to use2.
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.
This duplicate: http://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.
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.
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.
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.
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.
My suspicion would be the usual 255 characters. That is certainly the most you can use as an argument to the Range property.
I have written the below function which will compensate for this 'limitation'
I have tested this on a number of names with different length 'RefersTo' strings and it appears to work well.
Function rngGetRangeFromDefinedName(wkbTargetBook As Workbook, wksTargetSheetOfRanges As Worksheet, blnDefinedNameHasLocalScope As Boolean, strNameToUse As String) As Range
' This function compensates for Excel's limitation that the 'RefersTo' string poperty of a defined name can not be longer than 246 characters
' IMPORTANT: All cells must come from the same worksheet
Dim strOrigRefersTo As String
Dim strRefersToAreas() As String
Dim strCurRange As String
Dim intCurIdx As Integer
Dim intMaxIdx As Integer
Dim rngResult As Range
Dim rngCurRange As Range
On Error GoTo err_rngGetRangeFromDefinedName
' Get the full 'RefersTo' string from the named range
If blnDefinedNameHasLocalScope Then
strOrigRefersTo = wksTargetSheetOfRanges.Names(strNameToUse).RefersTo
Else
strOrigRefersTo = wkbTargetBook.Names(strNameToUse).RefersTo
End If
' Test to see if the 'RefersTo; string is too long
If Len(strOrigRefersTo) > 246 Then
' Break the refers to down into areas
strRefersToAreas = Split(strOrigRefersTo, ",")
intMaxIdx = UBound(strRefersToAreas)
' Cycle through creating ranges object for each range and combine
For intCurIdx = 0 To intMaxIdx
' Create the range object for the current part
strCurRange = strRefersToAreas(intCurIdx)
Set rngCurRange = wksTargetSheetOfRanges.Range(strCurRange)
' Join to the result range
If rngResult Is Nothing Then
Set rngResult = rngCurRange
Else
Set rngResult = Union(rngResult, rngCurRange)
End If
Next intCurIdx
' Return the result
Set rngGetRangeFromDefinedName = rngResult
Else
' Get the range directly from the name object
If blnDefinedNameHasLocalScope Then
Set rngGetRangeFromDefinedName = wksTargetSheetOfRanges.Names(strNameToUse).RefersToRange
Else
Set rngGetRangeFromDefinedName = wkbTargetBook.Names(strNameToUse).RefersToRange
End If
End If ' Len(strOrigRefersTo) > 246
Exit Function
err_rngGetRangeFromDefinedName:
' Do nothing - a 'Nothing' range returned
End Function
I have tested this on a number of names with different length 'RefersTo' strings and it appears to work well.
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.
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'
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.
Despite my initial belief it cannot be down to the literal length of the 'RefersTo' string as:
My live system builds up the named range from unions of several other ranges. The name is used to store which rows, within a certain range, are hidden and which are displayed.The exact makeup of this range can not be predicted since it is generated by a user's selections and is therefore quite random. This means that the 'RefersTo' string is not typed in and will consist of around 15 areas / several hundred rows or more..
I have therefore adjusted my code to try the 'RefersToRange' assignment first and only execute my union code if a 1004 error ('Application-defined or object-defined error') occurs. That seems best since there appears to be no definite test that can be done to anticipate the error.
when I have been using my code as part of the live system it failed when the string length was 242 (Less than i previously thought was the maximum).
I was trying again the workbook that I built for initial testing, to investigate this question, and found that the 'RefersToRange' now longer failed and in fact required a string of length between 304 and 320 before it failed
My live system builds up the named range from unions of several other ranges. The name is used to store which rows, within a certain range, are hidden and which are displayed.The exact makeup of this range can not be predicted since it is generated by a user's selections and is therefore quite random. This means that the 'RefersTo' string is not typed in and will consist of around 15 areas / several hundred rows or more..
I have therefore adjusted my code to try the 'RefersToRange' assignment first and only execute my union code if a 1004 error ('Application-defined or object-defined error') occurs. That seems best since there appears to be no definite test that can be done to anticipate the error.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial