Results 1 to 8 of 8

Dim variable as range syntax

This is a discussion on Dim variable as range syntax within the Excel Questions forums, part of the Question Forums category; Hi, I am having trouble understanding ranges in VBA . I have the following code where I am selecting cells ...

  1. #1
    MrExcel MVP parry's Avatar
    Join Date
    Aug 2002
    Location
    Wellington, New Zealand
    Posts
    3,355

    Default

    Hi, I am having trouble understanding ranges in VBA.

    I have the following code where I am selecting cells a1:a3 first by using a string to define the range then a range type. The string works fine but the range type comes up with runtime error 1004:'Method Range of Object'_Global Fail. Clicking on Help shows no topic grrrrr.

    Could someone please explain how you are supposed to declare then use range variables. Im stuck and searching VB help showed nothing useful.


    Code:
    Sub test()
    Dim rngtest As Range
    Dim strtest As String
    
    Set rngtest = Range("a1", "a3")
    strtest = "a1:a3"
    
    Range(strtest).Select ' works
    Range(rngtest).Select ' runtime error 1004
    
    End Sub

  2. #2
    Board Regular XL-Dennis's Avatar
    Join Date
    Jul 2002
    Location
    Ístersund, Sweden
    Posts
    1,922

    Default

    parry,

    Dim rntest as Range gives us access to rntest as a range-object.

    When we refer to this range-object we don┤t need to refer to it as a range, i e Range(rntest).


    Sub test()
    Dim rngtest As Range
    Dim strtest As String

    Set rngtest = Range("a1:a3")
    strtest = "a1:a3"

    Range(strtest).Select
    rngtest.Select
    End Sub


    Hopefully I has explained it in a understandable way.

    HTH,
    Dennis

    _________________
    "Windows was not able to find any keyboard. Press F1-button to try again or F2-button for cancel."

    [ This Message was edited by: XL-Dennis on 2002-09-01 04:54 ]

  3. #3
    MrExcel MVP parry's Avatar
    Join Date
    Aug 2002
    Location
    Wellington, New Zealand
    Posts
    3,355

    Default

    Thank you very much Dennis. Just to make sure I understand ...

    With the range defined as an object (does the "set" do that or the fact I used "as range"?) I could go rngtest.clear or rngtest.select etc. Cool.

    So the error came up because my code was really saying Range(Range("a1:a3")).select

    Please confirm my understanding


  4. #4
    Board Regular XL-Dennis's Avatar
    Join Date
    Jul 2002
    Location
    Ístersund, Sweden
    Posts
    1,922

    Default

    Parry,
    With the range defined as an object (does the "set" do that or the fact I used "as range"?) I could go rngtest.clear or rngtest.select etc. Cool.
    "As range" and You can use "Set" only with object type variables.

    So the error came up because my code was really saying Range(Range("a1:a3")).select
    Correct

    Kind regards,
    Dennis



    [ This Message was edited by: XL-Dennis on 2002-09-01 06:07 ]

  5. #5
    MrExcel MVP parry's Avatar
    Join Date
    Aug 2002
    Location
    Wellington, New Zealand
    Posts
    3,355

    Default

    Superb! Thanks very much Dennis, you have removed my frustrations with ranges so I can now start growing back my hair again

    Do you happen to know a site you could recommend that explains a bit more about what you can Dim. I used to think you could only "Dim" as a data type (single, boolean etc) until I saw people using "as range".

  6. #6
    Board Regular XL-Dennis's Avatar
    Join Date
    Jul 2002
    Location
    Ístersund, Sweden
    Posts
    1,922

    Default

    parry,

    I┤m not aware of websites that explicit deals with the Dim-statement.

    Have You explored the directhelp?
    (Highlight "Dim" and push the F1-button)

    If already done, the only good suggestion I can give is to search in the archieves for this board.
    Kind regards,
    Dennis

    .NET & Excel | 2nd edition PED | MVP

  7. #7
    MrExcel MVP Richie(UK)'s Avatar
    Join Date
    May 2002
    Location
    UK
    Posts
    3,329
    Richie

  8. #8
    MrExcel MVP parry's Avatar
    Join Date
    Aug 2002
    Location
    Wellington, New Zealand
    Posts
    3,355

    Default

    Cool thanks Richie.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com