Using Named Ranges in a Macro

Peg,
you can also highlight the text, copy and paste it into your favorite word processing app and then you can see everything.

Peg,

Sorry for the frustration. The only thing that is obscured by the ad is the word "vbYellow".

-Allen

Hi, Peg,

If you hover over the ad, there is an option to click on the X and then select "Ad covers the text" to remove the ad from the page. Take it easy.

I am trying to read a very helpful hint, but the advertisement covers up vital information. http://excelribbon.tips.net/T012612_Using_Named_Ranges_in_a_Macro.html?awt_l=KmaKF&awt_m=IgSYPjOAtMQF4X

I realize you are forced to display adds to be able to do this, but when the add completely negates any usefulness of the tip, it pisses me off enough to say screw this site; I'll find my information somewhere where I can read ALL of it

@Matt:

Range("range_name").Address

If you use the Intersect method you can use range names as argument.

Is it possible to determine the address of a named range?

I'd like to test a Workbook_SheetChange target against a list of named ranges.

Beware using the [] constuct in conjunction with line continuation " _".

I use a lot of Excel tables and have used [TableName[ColName]] instead of Range("TableName[ColName]") extensively.

I have no problems with the former construct on a single line of code but if I use it in long commands that need a line continuation, the position of the line continuation can move unpredictably causing compile errors. For example, a line continuation entered thus:

[TableName[ColName]], _
[TableName[Col2Name]]

will change to:

[TableName[ColName] _
],[TableName[Col2Name]]

and throw a missing end bracket error!

Another powerful feature with Named Ranges in macro coding is the ability to limit your access to cells within the range group.

For example say you had on your sheet cells A2:C15 named as MySales (or whatever). This named range has 14 rows and 3 columns. You can access any cell within this range using .Cell() in macro code.

...

TheValue = [MySales].Cells(RowNum, ColNum).Value

TheCellAddress = [MySales].Cells(RowNum, ColNum).Address

TheFormula = [MySales].Cells(RowNum, ColNum).Formula

...

Using [ ] within code is a syntax shortcut for using the Range() property. All functions and calls that Range() has can be accessed using [ ] as well.

TotalRows = [MySales].Rows.Count
TotalCols = [MySales].Columns.Count
ColNum = [AB23].Column

...

The RowNum and ColNum are variables (you can use whatever you want) and they are limited to the Named Range size ... in the above example, RowNum is 1 to 14 and ColNum is 1 to 3. Any value outside these values with raise an error.

Happy coding :-)

Using named ranges is a very powerful piece of knowledge.

You mentioned that Names are scoped to the workbook. It is also possible to scope them to specific worksheets. I have developed a method for those reports that need to be refreshed each month/week/quarter, using the prior period's workbook as a basis.

All areas that need to be cleared are named "DataArea1", "DataArea2" etc and scoped to the worksheet. This allows me to copy a worksheet for one department for use as another and I don't have to track how many data areas I have already named.

The code for clearing the old data is

For each Worksheet in TgtWkbk
for each Name in Worksheet.Names
If Left(Name, 8) = "DataArea" then
Name.refers.Range.ClearContent
' Sometimes it is ClearAll if formatting should be reverted
end if
Next Name
Next WorkSheet

Additional, if the number of rows or columns in the data area needs to be changed, as with a report that lists the work days of a given month or the names of the employees in the department, using Range or Row Delete/Insert within the range resets the size of the range referred to by the Name.

Paul, this is one of the most useful tips I have ever seen on this site! Thanks for that.

Another instance of using Square Braces.

Instead of using:
Workbooks("Book1.xls").Names("Account").RefersToRange.Interior.Color = vbYellow
This code can be used in place of the
[Account].Interior.Color = vbYellow
Much shorter to both write and read.

I have found that the following method for useing Names in a macro is much easier to use if you have more that one sheet to refer to.
The follow is the Name "Account" used in one worksheet and the Name "Balance" in another worksheet using Square Braces to refer to the names.

...
cbNewBalance = [Account] & " : " & [Balance]
...

This method can be used effectively throughout any Module or Form in a Project.