Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Location
    Ontario, Canada
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

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

    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

    <wherever>.Name = "RangeName1"
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    Star Lounger
    Join Date
    Feb 2001
    Location
    Ontario, Canada
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  5. #5
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    <img src=/w3timages/blackline.gif width=33% height=2>
    > ...square brackets ... I find is maddening
    <img src=/w3timages/blackline.gif width=33% height=2>
    Want to say something about Unix..., but I won't <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    In VBA, square brackets may be used as a substitute for the Evaluate method. They have an official name, but I've forgotten it <img src=/S/gramps.gif border=0 alt=gramps width=20 height=20>: 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:<pre>strA = Cells(1,1)
    strA = Range("A1")
    strA = Application.Evaluate("A1")
    strA = [A1]</pre>

    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
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

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

    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 <pre>Dim StrA As String
    StrA = "A1"
    Debug.Print [StrA]
    Debug.Print Application.Evaluate(StrA)
    Debug.Print StrA</pre>

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

    Andrew

  7. #7
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    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? <img src=/S/hmmn.gif border=0 alt=hmmn width=15 height=15>
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  8. #8
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

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

    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

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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***
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •