What's the RIGHT way to reference named cells in Excel 2013 VBA? (I know I'm messing this up)

Question

I have a cell (Ok there's a bunch but I'm just looking at one now) named "Classes". It's "C10" by Excel's grid notation.

My code works perfectly when I reference the cell as

Range("C10") = "Value"

But when I use

Classes = "Value"

It just does nothing.

So, what's the correct way to reference a named cell by its name?

score 36 · Accepted Answer · answered Jun 21 '13 at 05:40

36

You replace the address with the named range's name:

Range("Classes") = "Value"

answered Jun 21 '13 at 05:40

Jon Crowell

robotik · Answer 2 · 2017-09-17T02:36:58.433

26

You can use the square bracket shorthand for referencing ranges:

[Classes] = "Value"

So you can save on Range and "", and it also looks better (square brackets look a bit like a cell). You can also use [C10] = "Value"

edited Sep 17 '17 at 02:36

answered Oct 14 '14 at 19:11

robotik

3

TIL, I've never seen anyone do that in all the years of Excel – HackSlash Feb 26 '18 at 18:40
@HackSlash LOL. You need to get out more often. – Excel Hero May 02 '20 at 23:47
@ExcelHero, curious choice of words due to global pandemic. When I go out, I don't run in to people talking about Excel very often. – HackSlash May 04 '20 at 15:18
1

You misunderstand. Square bracket notation has been around since Joel hooked up VBA to Excel! Where have you been? – Excel Hero May 04 '20 at 15:51

2 Answers2