Excel: VBA referencing named range based on string variable

Question

How do I reference/select a named range based on a string variable with the text being the name ranged name?

Example:

'Target is range variable which is set to cell that is double clicked.
'For this example lets say the cell value is "A1A"
Dim binName As String
binName = "B1" & Target(1).Value
MsgBox(binName) 'This displays "B1A1A" which is the name of the named range
Range(binName).Select 'I want to select the range with the name B1A1A

This gives me Run-time error '1004': Method 'Range' of object'_Worksheet' failed on the last line of the above code.

I understand that Range() is looking for an object, but I dont know how to refer call a range name from a string variable.

Thank in advanced for any help.

Resolution Edit: Per Davids recommendation below, I changed

Range(binName).Select

to

Application.Goto ThisWorkbook.Worksheets("B1").Range(binName) 'where "B1" is the worksheet name

Also, why do you want to `Select` anything? [Hint: You like 99.9% of the time shouldn't `Select` anything.](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) — David Zemens, Aug 07 '17 at 19:32
This error suggests that the Active worksheet does not contain the named range. If the range exists on another worksheet, this error should be expected. — David Zemens, Aug 07 '17 at 19:46
According to Name Manager, the scope of the range is Workbook. Select may not be the best option. What I want is to move the users view to this range. — Bow, Aug 07 '17 at 20:11

Brandon Barney · Answer 1 · 2017-08-07T19:41:13.313

0

Application.Evaluate(binName).Select

Application Evaluate will evaluate a string and resolve the reference. It accepts strings as arguments, and doesnt need to resolve to a range (for example, Application.Evaluate("10") would result in a return of 10).

For more information, check out the MSDN documentation: https://msdn.microsoft.com/en-us/vba/excel-vba/articles/application-evaluate-method-excel.

EDIT: Worth noting, this isnt properly error handled, so it could potentially return a Nothing reference, or an error, if the input string is not a valid named range.

Additionally, ThisWorkbook.Sheets("SomeRangeName").Select should work (and does work) just fine. It is more likely that there is something wrong with the name of your range.

edited Aug 07 '17 at 19:41

answered Aug 07 '17 at 19:32

Brandon Barney

2,382
1
9
18

I don't see any good reason to use `Evaluate` in this case, and it's probably more trouble than it's worth anyways. Assuming the string is valid and represents an actual `Name` in the active workbook, the problem is not in resolving the string -> string, it's more likely either the scope of the `Name` itself, or (related) which sheet is active at runtime. The use of `Target` suggests this is in a sheet's code module/event handler... – David Zemens Aug 07 '17 at 19:37
In all honesty, I just forgot all about using the String itself (since the OP was having an issue with it) and used `Evaluate` before realizing that chances are something is wrong with either the scope of the command, or the string itself. If I am not mistaken though, `Evaluate` should help with scope (since it is at the Application level), though I could be wrong. – Brandon Barney Aug 07 '17 at 19:40
Thank you both. The code is activated on one sheet, and the named range is referring to a location on another sheet, but, according to the Name Manager, the scope of the named range is Workbook. Is there something else I can be missing? – Bow Aug 07 '17 at 19:46
Evaluate won't be able to resolve the scope -- if "B1A1A" is on a different worksheet, then it will evaluate as a string literal and then you get an Object Required error :) – David Zemens Aug 07 '17 at 19:47
I tried the Evaluate() code you provided. It is giving the same error, so I believe that you are correct that something else is wrong. – Bow Aug 07 '17 at 19:53
@DavidZemens Good to know. After I said it I read the documentation where they qualify the range and I figured I was wrong :). – Brandon Barney Aug 07 '17 at 19:54
@Bow Try using a different name, and also fully qualify your range references. It would be best to do something like `ThisWorkbook.Worksheets("SomeSheetName").Range("SomeRangeName").Activate`. Try naming a cell "TestName" and then explicitly use that string. – Brandon Barney Aug 07 '17 at 19:56
@BrandonBarney I set binName = "TestRange", set a range name "TestRange", and qualified my range reference to: ThisWorkbook.Worksheets("B1").Range(binName).Activate where "B1" is the sheet name. I am now getting Run-time error '1004': Activate method of Range class failed. Thanks for the help by the way. – Bow Aug 07 '17 at 20:06
@Bow you named a worksheet "B1"? That error is likely because either the range doesn't exist, or the target sheet isn't active – Brandon Barney Aug 07 '17 at 22:02

score 0 · Accepted Answer · answered Aug 07 '17 at 20:22

It's possible to have a Workbook scoped range that is still explicitly assigned to a single worksheet, which I gather is what you want (Name refers to some range on some other worksheet, etc.).

If you're doing this as UI, rather than Select try using the following:

Application.GoTo Range(binName)

Or:

Application.GoTo [binName]

Tested, and seems to work even when a range is explicitly on another worksheet, Application.GoTo takes care of toggling to that sheet and selecting that range.

Thanks David! This worked to get me what I want. I did have to specify the sheet for this to work. Application.Goto ThisWorkbook.Worksheets("B1").Range(binName) — Bow, Aug 08 '17 at 00:24

Excel: VBA referencing named range based on string variable

2 Answers2