Using NAMED RANGES in VBA for Excel (97 SR-2)

Topic

New Reply

I am having trouble with some VBA code I created where I want to use named ranges and not absolute references. Can someone please look at the code below and let me know what changes I need to make in my code in order to replace the absolute references with named ranges? I have tried several methods and even followed examples in textbooks but I seem to be failing at something.

THANKS in advance to anyone who can help me.

Here is a snippet of code. You can see the range references within the code. Let’s assume I want to use range names such as NamedRange1, NamedRange2, etc. for the example.

Drew

Private Sub cmdOK_Click()
Dim iNumRows As Integer
Dim rngImportRange As Range
Dim rngStartingCell As Range
‘On Error Resume Next
Workbooks(“CP-Template.xls”).Worksheets(“Heat Treat”).Activate
Set rngStartingCell = Range(“H15″)

If cbHTLeafOneFirstOff.Value = True Then
Workbooks.Open FileName:=”c:temppfmeareference.xls”
Set rngImportRange = Range(“C7:k11”)
rngImportRange.Select
Worksheets(“data”).Range(“C7:K11”).Copy
Workbooks(“CP-Template.xls”).Worksheets(“Heat Treat”).Activate
Worksheets(“Heat Treat”).Range(“H15”).PasteSpecial Paste:=xlPasteValues
‘MsgBox rngImportRange.Rows.Count
iNumRows = rngImportRange.Rows.Count + 1
End If

Reply | Quote

Replies

What problem are you having? There should be no issue with replacing:

Set rngStartingCell = Range(“H15”)

with

Set rngStartingCell = Range(“RangeName1”)

so long as RangeName1 exists before that line of code is run. Set the range via

.Name = “RangeName1”

Reply | Quote

Your code:

Workbooks(“CP-Template.xls”).Worksheets(“Heat Treat”).Activate
Set rngStartingCell = Range(“H15”)

can be replaced by with (assuming H15 is e.g. called “MyRange”) :

Set rngStartingCell = Workbooks(“CP-Template.xls”).Worksheets(“Heat Treat”).[MyRange]

Note that you do not have to activate the workbook to create a reference to the named range or to do something with it:

MsgBox rngStartingCell.Value

Reply | Quote

Jan,

Thanks for the help. I can’t understand why the named range issue is not more clear in the documentation. You enclosed the range name in square brackets “[ ]” whereas I have never seen that kind of syntax in the textbooks or online help. That I find is maddening.

Anyway, I have made some strides in my coding, but I am sure I will be needing more help down the road.

THANKS!!

Drew

Reply | Quote

> …square brackets … I find is maddening

Want to say something about Unix…, but I won’t

In VBA, square brackets may be used as a substitute for the Evaluate method. They have an official name, but I’ve forgotten it : Jan Karl may help me out there. So, if you have a string variable and you want the value of A1 in it, you can use any of the following:

strA = Cells(1,1)
strA = Range("A1")
strA = Application.Evaluate("A1")
strA = [A1]

I like to think of it as the equivalent of the equal sign in Excel. In Excel, we write =Average(A1,A2), in VBA we can write sngAvg = [Average(A1,A2)]. It’s a very nice shortcut, but it has lots of overhead, so it’s best used spairingly and not at all in loops. HTH –Sam

Reply | Quote

Sammy,

Square brackets do not always return the same value as Application.Evaluate. Only for literals and not variables.

The following little example shows this

Dim StrA As String
    StrA = "A1"
    Debug.Print [StrA]
    Debug.Print Application.Evaluate(StrA)
    Debug.Print StrA

Only Application.Evaluate(StrA) returns the value contained in A1, the others just return the string “A1”.

Andrew

Reply | Quote

Good point but, not to be picky, [StrA] is the same as [“A1”], both of which return the string “A1”. Neither are the same as [A1] which returns the contents of cell A1. Now, what are those square brackets called?

Reply | Quote

I am very much open to correction, but as far as I know they are just called a shorthand for the Evaluate method, though I may have seen them refered to as Evaluate operators. I’m not aware of any other name for them in this context.

Andrew

Reply | Quote

Hi Sam,

From Excel 2002 VBA programmer’s reference:
(an excellent book!!!)

***Quote***

Evaluate
The Evaluate method can be used to calculate Excel worksheet formulas and generate references to Range objects. The normal syntax for the Evaluate method is as follows:

Evaluate(“Expression”)

There is also a short cut format you can use where you omit the quotes and place square brackets around the expression, as follows:

[Expression]

Expression can be any valid worksheet calculation, with or without the equal sign on the left, or it can be a reference to a range of cells. The worksheet calculations can include worksheet functions that are not made available to VBA through the WorksheetFunction object or they can be worksheet array formulas. You will find more information about the WorksheetFunction object later in this chapter.

For instance, the ISBLANK function, which you can use in your worksheet formulas, is not available to VBA through the WorksheetFunction object, because the VBA equivalent function IsEmpty provides the same functionality. All the same, you can use ISBLANK, if you need to. The following two examples are equivalent and return True if A1 is empty or False if A1 is not empty:

MsgBox Evaluate(“=ISBLANK(A1)”)

MsgBox [ISBLANK(A1)]

The advantage of the first technique is that you can generate the string value using code, which makes it very flexible. The second technique is shorter, but you can only change the expression by editing your code. The following procedure displays a True or False value to indicate whether the active cell is empty or not, and illustrates the flexibility of the first technique:

Sub IsActiveCellEmpty()
Dim stFunctionName As String, stCellReference As String
stFunctionName = “ISBLANK”
stCellReference = ActiveCell.Address
MsgBox Evaluate(stFunctionName & “(” & stCellReference & “)”)
End Sub

Note that you cannot evaluate an expression containing variables using the second technique.

The following two lines of code show you two ways you can use Evaluate to generate a reference to a Range object and assign a value to that object:

Evaluate(“A1”).Value = 10

[A1].Value = 10

***unquote***

Reply | Quote