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

  • 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

  • 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.

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

  • 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

  • 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.

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

  • 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

  • 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.

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

  • 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.

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

  • 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

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!