CODEWITHSUNDEEP
vbaexcel
Greedo
Greedo

Reputation: 5543

What is meant by a "Name" in Excel VBA

In the MSDN documentation, it says that the Application.Evaluate method takes a single parameter, which is called a Name and is of Variant datatype. The description of what Name means in this context is:

A formula or the name of the object, using the naming convention of Microsoft Excel. The length of the name must be less than or equal to 255 characters.

*emphasis my own

What exactly is meant by the Excel naming convention? What rules govern it and where are these documented?

In the Remarks section of the article, there are some examples of possible types of Name that can be used - but one type of that can be used in the method is

Defined names. You can specify any name in the language of the macro.

Which begs the question; what is a "name in the language of the macro" defined as?

Or to tie this more closely to the specific programming question I'm trying to solve; what can blah be in Application.Evaluate(blah) for the function to evaluate correctly?

Upvotes: 2

Views: 679

Answers (1)

Brandon Barney
Brandon Barney

Reputation: 2392

In Excel, a Name (in the context of ranges) can mean a few things:

A Defined Name

For example, if you select cell "A1" and in the top left corner of the screen change "A1" to "MyCellName" you now have a Named Range. This allows you to reference "MyCellName" instead of "A1" in formulas. This is particularly useful with constant input ranges, or when defining a name for a larger range.

Named Ranges Can Overlap

Say, for example's sake, you now highlight "A1-B3" and change the Name to "MyNamedRange". You, just as before, can use this name in formulas. You can still refer to "MyCellName" as well to refer to "A1", despite it being within "MyNamedRange".

Automatic Names

Ever created a Table or PivotTable? These, in essence, become names as well. They can be referred to by their names. For example, if you create a table, the default name is Table1. In a formula, you can refer to the entire table using Table1 (super useful with VLOOKUPS).

Default Names

The most common Names are plain ranges. "A1" is a name that points to the cell "A1". Same for every other cell. Even cells that have defined names can still be referred to by their range reference.

For Purposes of the Article

All the article is saying is that you can refer to a range using the standard methods of referring to a range. If I do Application.Evaluate "MyNamedCell" it will evaluate "A1" and if I do Application.Evaluate "A1" it will again evaluate "A1".

For evaluating variables, what is actually happening is Application.Evaluate is first resolving the range reference (figuring out what you mean by the parameter passed) and it is then operating on that object. As long as you are passing a valid Range reference that can be evaluated, it will evaluate it. This means you can put a bunch of range references into an array of arrays within a class that is the property of... see where I am going with this? Use whatever you want, but in the end it needs to know where the range is.

EDIT: Regarding your updated edit of 'What can the name be?', you need some string that can be passed in and evaluated. Pretty straightforward. 

Dim SomeFoo as Range
Set SomeFoo = ThisWorkbook.Sheets(1).Range

' Isnt what we need since evaluate wants a string
' NOTE: This will implicitly take the .Value property of SomeFoo and
' try to evaluate that. This is fine, but bad coding practice (assuming SomeFoo's value is the address)
Application.Evaluate(SomeFoo).Value = "SomeString"

' The address can be successfully evaluated as a string
Application.Evaluate(SomeFoo.Address).Value = "SomeString"

' If SomeFoo contains an address/name it can be successfully evaluated
Application.Evaluate(SomeFoo.Value).Value = "SomeString"

Upvotes: 6

Related Questions