Announcement

Collapse
No announcement yet.

Calling Named Ranges in Macro

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Calling Named Ranges in Macro



    Hi All,

    I have looked at many of the past posts on this topic, but none seem to solve my difficulty, or. more probable, I have'nt understood the answers.

    I have a workbook which contains 3 named cells, each of which I would like to access via a macro which updates their contents.

    I assumed that
    Code:
    Range(Name).Select
    would suffice, obviously incorrectly.


    How can I select these cells?

    I am sure someone can tell me where I am going wrong

    Robert

  • #2
    Re: Calling Named Ranges in Macro

    Robert,

    Very close. Try
    Code:
    Range("Name").Select
    Hope this helps.

    Regards,
    Batman.
    Regards,
    Batman.

    Comment


    • #3
      Re: Calling Named Ranges in Macro

      Hi Batman

      thanks for your input. I had tried that but received this error message

      "Compile error:

      Wrong number of arguments or invalid property assignment"

      Does there need to be a Dim statement so that VBA can recognize the fact that named ranges exist in the target workbook?

      Robert

      Comment


      • #4
        Re: Calling Named Ranges in Macro

        Robert,

        The fact that a particular range name does not exist within your workbook should not cause a compile error, which implies there is something fundamentally wrong with the structure of the VBA code.

        Declaring a variable is mandatory if you have used Option Explicit at the top of your code module, but if a variable didn't exist the compile error would say that it didn't exist.

        Could you perhaps post more of your code so I can have a better look at what might be wrong?

        Regards,
        Batman.
        Regards,
        Batman.

        Comment


        • #5
          Re: Calling Named Ranges in Macro

          Hi Batman,

          As the code works perfectly when the range is hard coded, I supect that the error lies within the format of the statement.

          I have, temporarily and unsatisfactorily, resolved the problem by using
          Code:
          Application.Goto ("Name")
          thanks again for your interest

          Robert

          Comment


          • #6
            Re: Calling Named Ranges in Macro

            Robert,

            Is Name a variable intended to hold the range name? If so, I misunderstood as I assumed that Name was just a representation of the fact that you would hard-code a name in that statement.

            If Name is a variable, yes it should be declared. If you have declared it as a range variable you would need to use
            Code:
            Range(Name.Address).Select
            otherwise, if you have declared it as a string you should be able to use
            Code:
            Range(Name).Select
            Hope this helps.

            Regards,
            Batman.
            Regards,
            Batman.

            Comment


            • #7
              Re: Calling Named Ranges in Macro

              Hi Batman

              The use of "Name" was intended to indicate every name that exists in the workbook, there are 6.

              Does that mean I would have to declare each name as a string, if so, how would VB recognize the range to which it referred.

              Robert

              Comment


              • #8
                Re: Calling Named Ranges in Macro

                Robert,

                The best way to work with your range names depends on a number of things - whether the names or number of ranges is likely to change over time, how often thet are referred to in your program, what you want to do with them, etc.

                Let's say your 6 ranges are named "MyRange1" to "MyRange6".

                If you only ever refer to each range once in the program, and the range names will never change, you can simply hard code the name in your program without declaring a variable, e.g.
                Code:
                Range("MyRange1").Select
                You can refer to the range in a similar way by holding the name of the range in a string variable, e.g.
                Code:
                Dim strRange1 As String
                
                strRange1 = "MyRange1"
                Range(strRange1).Select
                As an alternative to this, you could declare the variable with
                Code:
                Const strRange1 As String = "MyRange1"
                which removes the need for the separate statement to assign the value, but you can't change the value of that variable elsewhere in the program.

                You can also assign the range to a range variable, which is done and used as follows:
                Code:
                Dim rngRange1 As Range
                Dim strAddress As String
                
                Set rngRange1 = ActiveSheet.Range("MyRange1")
                strAddress = rngRange1.Address
                rngRange1.Select
                In this example rngRange1 becomes a reference to the range of cells itself, and can use the properties and methods of the cells. In the code below, you will see the code needed using the 3 methods explained above to set the value of a range:
                Code:
                ' Method 1
                ActiveSheet.Range("MyRange1").Value = 50
                ' Method 2
                ActiveSheet.Range(strRange1).Value = 50
                ' Method 3
                rngRange1.Value = 50
                Let me know if there's anything that's still not clear.

                Regards,
                Batman.
                Regards,
                Batman.

                Comment


                • #9
                  Re: Calling Named Ranges in Macro

                  Robert,

                  By the way, if you have created a range name in Excel, and you refer to it in VBA, VBA will recognise the name if you try to refer to it and will identify the address of the range from that.

                  To see how VBA will do this, go to the Visual Basic Editor, Immediate Pane (Ctrl + G) and type (with your workbook containing the names active)

                  ? ActiveWorkbook.Names(1).Name [Enter]
                  ? ActiveWorkbook.Names(1).Address [Enter]

                  Regards,
                  Batman.
                  Regards,
                  Batman.

                  Comment


                  • #10


                    Re: Calling Named Ranges in Macro

                    Thanks for that, Batman, itwas extremely informative.

                    I know you will smile disbelievingly when I tell you that Range("Name").Select, which I tried some hours ago, and which failed, has now deceided to work.

                    We certainly live ininteresting times, eh?

                    Regards

                    Robert

                    Comment

                    Working...
                    X