Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 14 of 14

Thread: selecting range from different Worksheets ERROR

  1. #11
    Banned user
    Join Date
    May 2014
    Location
    Germany
    Posts
    1,336
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: selecting range from different Worksheets ERROR

    Quote Originally Posted by shg View Post
    From Help:


    In a worksheet module, it refers to an range on that worksheet.
    From Alan

    Quote Originally Posted by DocAElstein View Post
    ....... - so "Application" is the sort of "happening" going on at the time and this "happening" here in my example only sort of saw the wks reference, so sort of stuck with it. ).....
    Alan.


  2. #12
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    21,392
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    11 Thread(s)

    Default Re: selecting range from different Worksheets ERROR

    ....... - so "Application" is the sort of "happening" going on at the time and this "happening" here in my example only sort of saw the wks reference, so sort of stuck with it. ).....
    Alan.
    DE, I have no idea what that means.

  3. #13
    Banned user
    Join Date
    May 2014
    Location
    Germany
    Posts
    1,336
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: selecting range from different Worksheets ERROR

    SH G

    Quote Originally Posted by shg View Post
    DE, I have no idea what that means.
    I will do my best to explain...

    VBA is , i think intended to appear as much as possible like a OOP language,

    So-

    In a Class module (Worksheet Thisworkbook Userform Chart ( Older ** Macro Module ) ), things becomes a Public member of that Class. I do not really understand that but I think the general idea is that it “belongs” there so things made / referenced in it are from there – hence your point about the Worksheets Unqualified range reference going to that Worksheet )

    In a Normal Module, as Rory pointed out in the Link i gave, an unqualified Range call equates to Application.Range, not ActiveSheet.Range.
    It is not a Class Module, so the above Class stuff does not apply. I think you can think of it as a Code Module, maybe? - But maybe a ** newer VBA Code module so as not to confuse it with a earlier Macro Module thing ?!
    So, anyway, we construct code in it!
    ( _ ...or i do try anyway to ?!? )
    In constructing a typical code line you “Navigate” / go through / down the “Hierarchie.“ of the OOP chain .. ( But noting that as VBA is not really a true OOP you can “cheat” and go “up the Hierarchie.!?“
    http://www.mrexcel.com/forum/excel-q...ml#post3818458
    .....)


    VBA , we know , guesses often what you want, and at the start of many Code lines it adds something of the form
    Application.
    Or
    Excel.Application
    Or
    Application.Excel
    Or
    ( Non correct VBA OOP SomethingAfterApplication.SomethingElseAfterApplication.Application )
    Etc. etc.
    http://www.mrexcel.com/forum/excel-q...ml#post3818347

    VBA Knows you are in the Application of Excel, - or that is what it guesses in this case...I guesses

    So :

    _1 ) Effectively in a normal VBA Module you start, or somewhere along the lines you become, in the “Application”. That itself would usually be referring to the Active sheet. Then in any code line a “unqualified” “.Range” call will be referring to the Active sheet. Hence many people think that the “unqualified” “.Range” call in a normal module goes to the Active sheet. Usually it does. ( Just like usually VBA behaves like an OOP language ( and hence many people think it is ) )

    _2) In the link i gave i did a code line which effectively took the Application to a Worksheet. Later “down”the code chain i did an unqualified .Range reference and it stayed there, referencing the Worksheet and not the Active Sheet.

    _3) I have probably not got all the above quite right. I am just an ammeter, trying to learn. . But i think i am close. .........

    Hope that helps.

    Alan.de
    (Germany)

  4. #14
    Banned user
    Join Date
    May 2014
    Location
    Germany
    Posts
    1,336
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: selecting range from different Worksheets ERROR

    P.s. See also
    http://www.mrexcel.com/forum/excel-q...l?#post4332606

    Quote Originally Posted by DocAElstein View Post

    _ . This is suggesting
    _ a ) that somehow [] makes an unqualified range reference refer to the Application.Range as is the case in a normal module.
    http://www.mrexcel.com/forum/excel-q...ml#post4038308

    Range(“ “) in a Sheet Module attempts to find the Named Range in the Sheet of the sheet module in which the code is. This explains the first line which errors.
    _ b ) For a Sheet Module, this line
    Code:
    Names.Add Name:="w", RefersTo:=ws.Range("A1")
    Appears to set the “correct wanted” Range, but sets the “scope” to the sheet that the code is in. This somehow explains the second line which errors: - [] seems to ignore this “Scope” – It somehow “knows where to go.

    ( Note in passing if the Named range is set manually, then by default the Scope is set to the workbook ( as does the Names.Add by the code in a Normal module ) , in which case only Range("w").Value line errors for the case of the code in a different worksheet module, the “scope”, presumably in this case being valid for all sheets..(. somehow ) Or it is “ignored” )
    _...........................................

Some videos you may like

User Tag List

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
  •