+ Reply to Thread
Results 1 to 28 of 28

Trying To Name A Range. What Am I Doing Wrong

  1. #1
    Forum Contributor
    Join Date
    10-25-2012
    Location
    Honolulu, Hawaii
    MS-Off Ver
    Excel 2013
    Posts
    128

    Trying To Name A Range. What Am I Doing Wrong

    Hello,

    Any help will be greatly appreciated !

    I am having problems naming a range; actually all I need is to name a cell.

    Here is the code I have

    [code]

    Sub NameCell()
    Dim wb As Workbook
    Dim rng As Range
    Dim ws As Worksheet

    Application.ScreenUpdating = False 'turn this off for the macro to run a little faster

    For Each ws In Sheets
    ws.Activate

    ActiveSheet.Range("M1").Name = "Me"


    'Range("M1").Name = "Me"
    'Range("M2").Name = "Me2"


    Next ws

    Application.ScreenUpdating = True

    End Sub

    [\code]

  2. #2
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Trying To Name A Range. What Am I Doing Wrong

    I don't believe that you can have duplicate names in the same book as a named range has Workbook scope.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  3. #3
    Valued Forum Contributor WasWodge's Avatar
    Join Date
    08-02-2010
    Location
    Hampshire,England
    MS-Off Ver
    Office 365 and Office 2010
    Posts
    882

    Re: Trying To Name A Range. What Am I Doing Wrong

    Try...

    Please Login or Register  to view this content.
    If my solution worked (or not) please let me know. If your question is answered then please remember to mark it solved

    Computers are like air conditioners. They work fine until you start opening windows. ~Author Unknown

  4. #4
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Trying To Name A Range. What Am I Doing Wrong

    @ WasWodge - your avatar reminds me of the old Mr. Bill cartoons - remember those?

  5. #5
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Trying To Name A Range. What Am I Doing Wrong

    This is because you need to replace ActiveSheet with ws inside the For Each loop.
    Also since you are using the same name, it will replace the previous named range.

    Try something like this....

    Please Login or Register  to view this content.
    PS. Also use proper code tags by highlighting your code and click the # icon in the formatting bar and if you do it manually you need to close the code tag like [/Code] instead of [\Code].
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  6. #6
    Valued Forum Contributor WasWodge's Avatar
    Join Date
    08-02-2010
    Location
    Hampshire,England
    MS-Off Ver
    Office 365 and Office 2010
    Posts
    882

    Re: Trying To Name A Range. What Am I Doing Wrong

    Quote Originally Posted by xladept View Post
    @ WasWodge - your avatar reminds me of the old Mr. Bill cartoons - remember those?
    I didn't but I do now.. and just watched Mr. Bill learns karate and mr bill safety tips (and yes it is my low level of humour ).

  7. #7
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Trying To Name A Range. What Am I Doing Wrong

    @ WasWodge - I just watched those two - really sick eh! (My Grandparents were Canadians)

  8. #8
    Forum Contributor
    Join Date
    10-25-2012
    Location
    Honolulu, Hawaii
    MS-Off Ver
    Excel 2013
    Posts
    128

    Re: Trying To Name A Range. What Am I Doing Wrong

    Hi Thankyou for your reply,

    It still does not work.

    Kindest Regards

  9. #9
    Forum Contributor
    Join Date
    10-25-2012
    Location
    Honolulu, Hawaii
    MS-Off Ver
    Excel 2013
    Posts
    128

    Re: Trying To Name A Range. What Am I Doing Wrong

    Hi Thankyou for your reply,


    It still does not work.

    Oh and Thankyou for bringing the code tag to my attention.


    Kindest Regards

  10. #10
    Valued Forum Contributor WasWodge's Avatar
    Join Date
    08-02-2010
    Location
    Hampshire,England
    MS-Off Ver
    Office 365 and Office 2010
    Posts
    882

    Re: Trying To Name A Range. What Am I Doing Wrong

    It still does not work.
    Doesn't tell us anything so can't help. Details on what happens, with which code and what appears in your name manager...

  11. #11
    Forum Contributor
    Join Date
    10-25-2012
    Location
    Honolulu, Hawaii
    MS-Off Ver
    Excel 2013
    Posts
    128

    Re: Trying To Name A Range. What Am I Doing Wrong

    I have figured it out. It works perfectly but I am confused on the LBound to UBound portion; I am assuming it is correct since there are no errors.

    Please Login or Register  to view this content.

  12. #12
    Forum Contributor
    Join Date
    10-25-2012
    Location
    Honolulu, Hawaii
    MS-Off Ver
    Excel 2013
    Posts
    128

    Re: Trying To Name A Range. What Am I Doing Wrong

    Select Code

    Sub AddHeaders()
    Dim Name1() As Variant
    Dim Name2() As Variant
    Dim Name3() As Variant
    Dim Name4() As Variant

    Dim ws As Worksheet
    Dim wb As Workbook

    Application.ScreenUpdating = False 'turn this off for the macro to run a little faster

    Set wb = ActiveWorkbook

    Name1() = Array("A")
    Name2() = Array("B")
    Name3() = Array("C")
    Name4() = Array("D")

    For Each ws In wb.Sheets
    With ws
    .Columns(2).Value = "" 'This will clear out column 2
    For i = LBound(Name1()) To UBound(Name4())


    .Cells(2, 2 + i).Value = Name1(i)
    .Cells(3, 2 + i).Value = Name2(i)
    .Cells(4, 2 + i).Value = Name3(i)
    .Cells(5, 2 + i).Value = Name4(i)
    Next i
    .Columns(2).Font.Bold = True
    End With
    Next ws

    Application.ScreenUpdating = True 'turn it back on


    End Sub
    Last edited by rmccain; 07-25-2016 at 10:58 AM.

  13. #13
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: Trying To Name A Range. What Am I Doing Wrong

    Post deleted.
    Last edited by sktneer; 07-24-2016 at 11:35 PM.

  14. #14
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Trying To Name A Range. What Am I Doing Wrong

    Quote Originally Posted by rmccain View Post
    I have figured it out. It works perfectly but I am confused on the LBound to UBound portion; I am assuming it is correct since there are no errors.......

    @ rmccain
    Hi rmccain
    I am not quite sure if you meant to do what you are doing.
    These are 4 seperate variables and they_....
    Name1()
    Name2()
    Name3()
    Name4()
    _...... all become single Element 1 Dimensional Arrays , pseudo: Array( 0 To 0 )

    So i is always equal to 0

    For example
    Name1(0)
    Has the string “A” which you gave it. You only “loop” therefore once.!!, as it follows that your Lower Bound and Upper Bound for all 4 of your variables are the same, 0 ( Internally generated Arrays start at indicie 0, not 1, in base 0 – so for more than one you would have 0, 1, 2, 3.... etc... - You have only one Element in each variable so it has indicie 0 )

    Alan
    Last edited by Doc.AElstein; 07-25-2016 at 04:50 AM.
    '_- Google first, like this _ site:ExcelForum.com Gamut
    Use Code Tags: Highlight code; click on the # icon above,
    Post screenshots COPYABLE to a Spredsheet; NOT IMAGES PLEASE
    http://www.excelforum.com/the-water-...ml#post4109080
    https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
    http://excelmatters.com/excel-forums/ ( Scrolll down to bottom )

  15. #15
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Trying To Name A Range. What Am I Doing Wrong

    Hi Orrin
    Here some Scope wonks!
    Quote Originally Posted by xladept View Post
    I don't believe that you can have duplicate names in the same book as a named range has Workbook scope.

    I think you can sort of have Duplicate names. The first Cell, for example in each Worksheet has the same Name property “A1”. That “A1” I think has then Worksheet Scope. But that somehow works a bit differently I think..I am not too sure about the "reserved by Bill gates Names"


    Anyways, The OP was doing a “shorthand” way I think to get a Workbooks scope. Here is a good Blog showing how you do it fully / properly, for both Workbooks and Worksheets scope
    http://www.thespreadsheetguru.com/bl...o-named-ranges

    I think sktneer’s should work. He is just adding something in each name to distinguish it.

    I think WasWodge is doing the full way of adding a Name Object and giving it a string Name.
    I think by .Parent.Parent applied to a Range, WasWodge goes two levels back up, first to Worksheet, then Workbook, so he is applying Workbook scope. I am not quite sure what WasWodge is doing with the name he gives it. I get confused a bit with Names and Reference strings... and I get confused with the .Name Object and the .Name string name...

    To get the string name of, for example, the first Cell if you had given it a “Name” then you would do this:
    Dim Nme As String
    Let Nme = Range("A1").Name.Name

    Here is a good code to step through. I tried to get all these things clear in my head:
    ( Posts 166- 168 of one of my appendix ... Lol.... ( take a big breath, Lol!! )
    http://www.excelforum.com/showthread...t=#post4404807
    One of the most important things I noticed there is that if you “give” a simple string name like “HiOrrinwsScope” under Worksheets scope, and then later look at what you are get returned for the given Name, then you will find that VBA Adds a bit to distinguish it to your Worksheet. Maybe that is along the lines of what WasWodge was doing.***


    Here below is a quick shortened code applied to you: ( Note in the quick demo for you here I have not done my usual careful explicit Range referencing, so be careful where the Implicit takes you... Lol.. )

    The bottom line, ( 150 ) returns you a slightly different name to that you gave it, as it has worksheets scope, so VBA adds a bit to distinguish the Worksheet. I think that may have been what WasWodge was doing by giving it at the outset its Full name***. ( But the extra ' bits always confuse me !! – sometimes you see them sometimes you don’t. !! ). I am confued then that he is somehow doing , maybe a Workbooks / worksheet scope mix up wonk???


    Please Login or Register  to view this content.
    Last edited by Doc.AElstein; 07-25-2016 at 04:51 AM.

  16. #16
    Valued Forum Contributor WasWodge's Avatar
    Join Date
    08-02-2010
    Location
    Hampshire,England
    MS-Off Ver
    Office 365 and Office 2010
    Posts
    882

    Re: Trying To Name A Range. What Am I Doing Wrong

    WasWodge goes two levels back up, first to Worksheet, then Workbook, so he is applying Workbook scope.
    Only on my phone so not posting any code but what the code does is change to WorkSheet scope .
    If you run the code and then go into Formulas - Name Manager you will see the setup (worksheet scope names don't appear in the Names dropdown).

    To use as a formula you would use indirect i.e. something like..
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    to get the value of the cell.

  17. #17
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Trying To Name A Range. What Am I Doing Wrong

    Hi WasWodge
    Thanks very much for the reply...
    Quote Originally Posted by WasWodge View Post
    ..... what the code does is change to WorkSheet scope .
    If you run the code and then go into Formulas - Name Manager you will see the setup (worksheet scope names don't appear in the Names dropdown).......
    That looks very interesting. But I have no idea how it is doing that? From what I read it looked like the
    sh.Range("M1").Parent.Parent.Names.Add
    was pseudo doing
    Workbooks.Names.Add
    so Workbooks scope Adding of a name
    But somehow you are tricking it into being Worksheets scope with the unusual way you are doing the
    RefersTo:=
    bit?

    Was just passing interest, maybe if you get some time later you could explain it a bit more
    But no Rush
    Thanks again
    Alan

    P.s
    Quote Originally Posted by WasWodge View Post
    .....
    To use as a formula you would use indirect i.e. something like..
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    to get the value of the cell.
    I am afraid that is a bit lost on me, I have heard of but never really used or understood INDIRECT stuff.

  18. #18
    Forum Contributor
    Join Date
    10-25-2012
    Location
    Honolulu, Hawaii
    MS-Off Ver
    Excel 2013
    Posts
    128

    Re: Trying To Name A Range. What Am I Doing Wrong

    Thankyou for your reply. I adapted another macro I had that placed headers in row 1, columns a,b,c. This is the only way I could get this to work and I will play with it a little bit more because I know that I somehow came across a loop hole. I know the names can be listed as a single, Array("name1", "name2", "name3") in relation to what column , i.e. G, I assign.

    It does work across several dozen worksheets no problem but I am still trying to figure out how to correctly list the column and row.

    Thankyou again

  19. #19
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Trying To Name A Range. What Am I Doing Wrong

    Hi rmccain
    Quote Originally Posted by rmccain View Post
    Thankyou for your reply. ... I know the names can be listed as a single, Array("name1", "name2", "name3") ... am still trying to figure out how to correctly list the column and row....Thankyou again
    You are welcome,
    Thanks for the feedback glad we could help
    _...............
    I do not fully follow what you are doing but just some quick info that my help:
    This thing
    = Array( )
    Is the VBA Array Function. It returns a Field of Elements of Type Variant as a 1 Dimensional Array. ( The Values of those Elements are based on what things you put in brackets, and you must separate each thing by a comer , :
    ( Thing1 , thing 2, ...... etc ).
    The thing can be just about anything. Hence the variable type of each Element has to be Variant, as only a Variant can contain any Type of thing.
    Generally a pair of " " quotes is used to tell VBA to expect a String. So you were telling VBA that you were putting simple character String types in )

    Hence you must, as you have been doing, declare ( Dim ) your variable as a dynamic ( not yet sized ) Array of Variant Types to “take” what the VBA Array method “chucks” at it.
    So
    Dim arr1() As Variant
    Let arr1()=Array("name1", "name2", "name3")

    This would not work_...
    Dim arr1() As String
    Let arr1()=Array("name1", "name2", "name3")
    _.. you have put String types in, but they are held within the Elements chucked back at you by the VBA Array( ) Method, which are Variant Types. You still have an Array fundamentally of Variant Types. ( Which may not be too efficient )


    Exactly what the empty brackets () does in something like this_....
    Arr () =
    _.... is not fully understood by anyone, ( not even Bill Gates – maybe he did know once, but forgot ) _....
    http://www.excelforum.com/excel-prog...acket-for.html
    _.... but approximately it tells VBA that what to the left of the () is expecting a Field of values ( Array ). Often people miss out the (). Sometimes this is intentional. Sometimes it is just carelessness.

    Strictly speaking a 1 Dimensional Array has no orientation. But in VBA there is a convention that VBA will accept a 1 Dimensional Array as a “pseudo” “ horizontal 2 Dimensional Array of 1 “row” "and, ( in this example 3 “columns” ). But many experts will debate this a bit....
    _............

    So you may have seen people use this to put out the headings in a row in a Worksheet. It is a very quick simple way to fill an Array in one go, and then paste out to a Worksheet. But it is not too efficient.
    _..........--

    You could do the same being a bit more precise with a 2 Dimensional Array.
    I expect you need to read up on some basic Array stuff. I will give you some good references later. ***

    First maybe this will help:

    I will write a short code for you.
    It will paste out your 3 names in a spreadsheet like this

    Using Excel 2007 32 bit
    Row\Col
    B
    C
    D
    E
    F
    9
    10
    name1 name2 name3
    11
    12
    name1 name2 name3
    13
    BracketWonk


    I will use two methods.

    Rem 1 VBA Array() Method
    As discussed above.

    Rem 2 A more basic Array Method
    I am much more explicit here. I start with a static ( fixed size ) Array. ( A 2 Dimensional Array with "pseudo"!!! "rows" and "columns" ) I know what types I will be filling, one by one, so can Declare ( Dim ) the types a bit more efficiently.
    ( !!! I say "pseudo" as an Array can have lots more than 2 Dimensions so "rows" and "columns" has no real meaning. But when interacting a 2 Dimensional Array with a worksheet, VBA accepts the first dimension as a row and the second dimension as a column when you "present" the Array to a Worksheet, so it is just a convenient way of us Humans "thinking" about it )
    _......

    I will try to write some explaining ‘Comments in the code.
    Best is to run the code in DeBug ( F8 ) mode and see step for step what is happening.

    Please Login or Register  to view this content.


    _..............................

    ***
    In this
    Thread you will find some good beginners You Tube Play lists.
    http://www.excelforum.com/showthread...00#post4272300

    These are a few good videos for learning basic Array stuff:
    https://www.youtube.com/watch?v=e47F...S5k4zsvnu2mkJC
    https://www.youtube.com/watch?v=h9FTX7TgkpM
    https://www.youtube.com/watch?v=t07y...2mkJC&index=19

    _....

    Alan
    Last edited by Doc.AElstein; 08-01-2016 at 02:21 AM.

  20. #20
    Forum Contributor
    Join Date
    10-25-2012
    Location
    Honolulu, Hawaii
    MS-Off Ver
    Excel 2013
    Posts
    128

    Re: Trying To Name A Range. What Am I Doing Wrong

    Aloha and THANKYOU SO MUCH for your reply ! I apologize for the delay and the reason is that I have been traveling with my laptop and for the past several days every time I typed in excelforum.com in my computer I was redirected to a fish company ?????

    I will try your suggestion, I am just happy I received a reply I have searched high and low to do this correctly.

    Kindest Regards !

  21. #21
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Trying To Name A Range. What Am I Doing Wrong

    Hi
    Quote Originally Posted by rmccain View Post
    Aloha and THANKYOU SO MUCH for your reply....
    Your welcome, glad we could help .....
    Quote Originally Posted by rmccain View Post
    .... for the past several days every time I typed in excelforum.com in my computer I was redirected to a fish company ?????....
    Ha Ha, Yep we have been discussing that....
    http://www.excelforum.com/the-water-...this-post.html
    _..... Excel Forum gets attacked frequently by Spammers, Hackers, .....problem with its poplarity


    Thanks for the feedback

    Alan

  22. #22
    Forum Contributor
    Join Date
    10-25-2012
    Location
    Honolulu, Hawaii
    MS-Off Ver
    Excel 2013
    Posts
    128

    Re: Trying To Name A Range. What Am I Doing Wrong

    Hi ,


    Just wanted to thankyou for all of your EXTENSIVE help. I had to leave town for an emergency and completely forgot about this. In between the chaos I have figured out the simplest code ever....I think:

    Please Login or Register  to view this content.
    Last edited by rmccain; 08-24-2016 at 02:00 AM. Reason: spelling error

  23. #23
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Trying To Name A Range. What Am I Doing Wrong. Range Referrencing. ;)

    Quote Originally Posted by rmccain View Post
    .... wanted to thankyou for all of your EXTENSIVE help. I had to leave town for an emergency and completely forgot about this. In between the chaos I have figured out the simplest code ever....I think:.....
    Hi rmccain,
    Thanks again for the Feedback, always welcome. Yep, real life stops the excel “fun” !
    Alan

    P.s.
    Just some small comments and notes on your code..

    _1 ) Referencing Workbooks, Worksheets info. Etc.
    _ 1a) Your code is probably going to work on the Active Workbook, which you probably want. But I would advise getting in the habit of Setting your Workbook to a specific one, even if it is the default Active Workbook that you want. ***** Relying on the Implicit default can catch you out. There are cases when the defaut will not be the “Active” one, for example *****

    _ 1b) And use at least one capital, like Wb when you Dim. – That will give a check that you write your variables correctly: - You see, if you always type later in the code in lower case, then VBA will change something like wb to Wb.
    So if you always use this approach, then if any variables you type in the code anywhere stay at lower case, you will have a check to see that you would have typed them a bit differently ( incorrectly) .
    _................

    _ -1) Option Explicit
    Even if you miss that ‘1b) check, then on attempting to run the code, the Option Explicit will cause any variables not yet declared ( Dim-ed ) to bring up a syntax error on compile.
    _................

    _2) “Activate.” ( Select ) etc..
    We Human’s tend to have to do this to “see” what we do. VBA does not have to “see”. As long as things are referenced properly / Explicitly, then VBA will “go to” or “work on” the correct thing, - Worksheets in this case.
    ‘2a can be left out as long as you include the ws. in ‘2b. Without the extra ws. then generally the Range("M2:M8") will refer to
    either
    ( the usual case ) the Active Worksheet for code in a normal Module,
    ( or *****
    the Worksheet of the Worksheet code module if you have the code in a Worksheet Code Module. ).
    _...................

    _ Application.ScreenUpdating = False )

    This is OK to do to speed things up a bit. BUT it may be important to turn it back on, and preferably that in such a way, ‘0 , with Error handling so that it is always “switched back on”. I am not sure if it is automatically switched back on. So if it is left “switched off” you may might find yourself looking at a “dead” screen after running the code, or possibly some other strange problem will come up later if it is left “turned off”

    -..

    Last minor point that you probably noticed anyway: You are trying to assign a range from M2 to M8 ( 7 Cells) with 6 values. So the last cell will be “filled” with an error warning I think

    _..............................................................
    _..............................................................

    Please Login or Register  to view this content.
    Last edited by Doc.AElstein; 08-24-2016 at 07:05 AM.

  24. #24
    Valued Forum Contributor WasWodge's Avatar
    Join Date
    08-02-2010
    Location
    Hampshire,England
    MS-Off Ver
    Office 365 and Office 2010
    Posts
    882

    Re: Trying To Name A Range. What Am I Doing Wrong. Range Referrencing. ;)

    Quote Originally Posted by Doc.AElstein View Post

    _ Application.ScreenUpdating = False )

    This is OK to do to speed things up a bit. BUT it may be important to turn it back on, and preferably that in such a way, ‘0 , with Error handling so that it is always “switched back on”. I am not sure if it is automatically switched back on. So if it is left “switched off” you may might find yourself looking at a “dead” screen after running the code, or possibly some other strange problem will come up later if it is left “turned off”

    Hi Doc, I know it is bit old but in the link below there is an article by Tom Urtis.
    The first point is one of the reasons why I always think it is safer just to automatically reset Application.ScreenUpdating back to True explicitly in the code.

    http://www.atlaspm.com/toms-tutorial...rue-heres-why/


    I think 2nd point is a bit mute though because if it errors out before you turn it back on in the code then you would still be in the same situation, so I don't really know what Tom is saying there unless you were to treat it as meaning don't use it at all.

    This is also partly why I keep a separate macro at work just to turn all the usual things back on (i.e. Calculation, Events, Screenupdating, Status bar and View Page Breaks).
    You will be amazed how often the few people who use VBA (mostly they record and adapt (or not )) leave one of the settings off or someone opens a workbook with Calculation set to manual and resets their other open workbooks.

  25. #25
    Valued Forum Contributor WasWodge's Avatar
    Join Date
    08-02-2010
    Location
    Hampshire,England
    MS-Off Ver
    Office 365 and Office 2010
    Posts
    882

    Re: Trying To Name A Range. What Am I Doing Wrong. Range Referrencing. ;)

    Quote Originally Posted by Doc.AElstein View Post

    _ Application.ScreenUpdating = False )

    This is OK to do to speed things up a bit. BUT it may be important to turn it back on, and preferably that in such a way, ‘0 , with Error handling so that it is always “switched back on”. I am not sure if it is automatically switched back on. So if it is left “switched off” you may might find yourself looking at a “dead” screen after running the code, or possibly some other strange problem will come up later if it is left “turned off”

    Hi Doc, I know it is bit old but in the link below there is an article by Tom Urtis.
    The first point is one of the reasons why I always think it is safer just to automatically reset Application.ScreenUpdating back to True explicitly in the code.

    http://www.atlaspm.com/toms-tutorial...rue-heres-why/


    I think 2nd point is a bit mute though because if it errors out before you turn it back on in the code then you would still be in the same situation, so I don't really know what Tom is saying there unless you were to treat it as meaning don't use it at all.

    This is also partly why I keep a separate macro at work just to turn all the usual things back on (i.e. Calculation, Events, Screenupdating, Status bar and View Page Breaks).
    You will be amazed how often the few people who use VBA (mostly they record and adapt (or not )) leave one of the settings off or someone opens a workbook with Calculation set to manual and accidently resets their other open workbooks.

  26. #26
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Trying To Name A Range. What Am I Doing Wrong. Range Referrencing. ;)

    Hi WasWedge
    Quote Originally Posted by WasWodge View Post
    .... in the link below there is an article by Tom Urtis.
    The first point is one of the reasons why I always think it is safer just to automatically reset Application.ScreenUpdating back to True explicitly in the code...
    I think 2nd point is a bit mute though because if it errors out before you turn it back on in the code then you would still be in the same situation, so I don't really know what Tom is saying there unless you were to treat it as meaning don't use it at all.
    . ...
    Thanks for picking up on my ...".....I am not sure if it is automatically switched back on.up. ..".......Appreciate it. I had done a bit of Googling ( or rather ixquick-ing ) and missed that Link and found no other
    ( BTW the Link you gave did not work but then I Googled based on your info and found what I expect is the one you meant. ..._.. ( strange my ixquick still never found it ?? ) )
    http://www.atlaspm.com/toms-tutorial...rue-heres-why/
    It can be helpful when someone gives the right link – there is so much info out there nowadays you can search ages and not hit the right info.
    _.........................

    _1 ) I agree with you on Point 1, but I note my last comments later... *****

    _2)
    The second part of the second point I did not quite understand. “Future versions of Excel will invariably support new, or stop supporting existing, VBA methods and properties, so it’s an unnecessary risk to not reset ScreenUpdating.” Unless he is just suggesting future versions may not mostly Reset to True, as is the case currently.
    The first point you did not understand I think I may understand. Or at least I have two possible explanations:
    Either:
    _2a) ) He says is “if it errors out”. Possibly there he is talking about using error handling as I did in my code in post #23, ( although I agree, he does not specifically say that. ). With such error handling the screen updating would be turned back , albeit not by yourself, but by the error handler. But I am guessing that may be what he meant. As he then goes on to say.. “interrupted before it completes”..... so......
    Or
    _2b) He means possibly turn it back on as soon as you have finished, in case it is “interrupted” before the code finishes. That is if it errors or rather maybe crashes later. - I would interpret “ interrupt” as in a crash ( I do not know if / how ScreenUpdating would be effected there- but probably using a macro to turn things back on as you do, would be wise in such a situation ) )

    I agree he is not clear on point 2). Or he is mixing up his points up a bit.

    _.....

    _3) I was confused with his third point initially. He does a demo like what I tried experimenting with. I see and saw all the results as code runs. I don’t / didn’t have to wait until the end. BUT I had been doing it all in Debug mode F8. I realise now that was my mistake. If you run the demos normally with Screen updating set to false , then you have to wait until the end, as he says. So seeing that demo helped me clear that up. ( I note he does not reset the screen updating to True in his demo !! So as often it appears screen updating is ( sometimes ( mostly) ) reset to true when the code Ends...

    But I think I have got the point now , based on his Blog and what you have said, that it is a good idea to do it anyway. ( I do not get the wild effect he mentions with dragging the message boxes. But message box positioning always seems a bit quirky with me – I play around a lot with Monitor settings and extra monitors etc., so that effects how and where they show )

    _....

    _4) Point 4 . Makes a lot of sense to reset things if you are sharing, unless you want to do a lot of extra documentation to inform the User exactly what you have done. (He touches on his first point 1) again here )

    _....**** A last general Point here. There is Another view point. One which says only do things if you need to. One argument for that being so that if you do it, you know you are doing it for a reason – That is because, or assumes, you know that it is in such a case needed. I have heard that argument for example with regard to Seting an Object to Nothing. I guess this is a very debatable point influenced by experience.
    _... my bottom line would be that I will probably use it ( That is to say always reset things I have set, even if I may not always need to. ) And / But I will then further dream and hope for a world where we all understand codes enough so that we only do such things when we need to, so that we are less “controlled by “ or dependant on computers !! lol :-) )



    Alan
    Last edited by Doc.AElstein; 08-25-2016 at 07:17 AM.

  27. #27
    Valued Forum Contributor WasWodge's Avatar
    Join Date
    08-02-2010
    Location
    Hampshire,England
    MS-Off Ver
    Office 365 and Office 2010
    Posts
    882

    Re: Trying To Name A Range. What Am I Doing Wrong. Range Referrencing. ;)

    ( BTW the Link you gave did not work but then I Googled based on your info and found what I expect is the one you meant. ..._.. ( strange my ixquick still never found it ?? ) )
    http://www.atlaspm.com/toms-tutorial...rue-heres-why/
    It can be helpful when someone gives the right link – there is so much info out there nowadays you can search ages and not hit the right info.
    _.........................
    The link was copied directly from the pages url this morning so no idea why it isn't working, other than bad copying/pasting (and not testing it but a poor defence is I was getting ready for work at the time))... I hope you didn't think I typed it by hand

    By the way on the link which isn't working if you just clicked the magnifying glass at the top then in the search box typed "screenupdating" it is the first article that appears (no need to have Googled it). There is also a search box at the bottom of the page.


    With respects to screenupdating, calculation etc. you should strictly speaking rather than automatically set things back to true record it's original state it was in at the start of the program running and then return it back to what ever the original state was at the end (and no I rarely do this either ).

    There is Another view point. One which says only do things if you need to
    .

    Ah you mean bodge it and hope you get no issues admittedly you learn what you can get away with (it doesn't make it right though and then you only have yourself to blame if it goes wrong... btw I have seen people have problems with UDF's when screenupdating wasn't turned back on, so unless you are 100% confident of what is going to happen....)

    ( I note he does not reset the screen updating to True in his demo !! So as often it appears screen updating is ( sometimes ( mostly) ) reset to true when the code Ends
    "Mostly" was the correct word and always if I remember correctly when you close and restart Excel.

    I have heard that argument for example with regard to Seting an Object to Nothing.
    Again "mostly" is applicable (and rarely (apparently) in modern versions of Excel). Although I must say I have seen more issues with Access rather than Excel.

    The second part of the second point I did not quite understand. “Future versions of Excel will invariably support new, or stop supporting existing, VBA methods and properties, so it’s an unnecessary risk to not reset ScreenUpdating.” Unless he is just suggesting future versions may not mostly Reset to True, as is the case currently.
    As Tom stated it wasn't necessary to reset it in early versions (too long ago for me to remember) and then they changed it and so there is nothing to say that they won't change it again.

    nuff said... feels like writing a book
    Last edited by WasWodge; 08-25-2016 at 10:15 AM.

  28. #28
    Forum Expert Doc.AElstein's Avatar
    Join Date
    05-23-2014
    Location
    '_- Germany >Outside Building things.... Mostly
    MS-Off Ver
    Office 2003 2007 2010 PC but Not mac. XP and Vista mostly, sometimes Win 7
    Posts
    3,618

    Re: Trying To Name A Range. What Am I Doing Wrong. Range Referrencing. ;)

    Hi WasWodge
    Thanks again
    for the reply, and helpful elaborations etc.. Appreciate it.
    _............


    URL’s are probably just something else that sometimes don’t work at EF, - all part of the “fun distinguishing EF characteristics” !!
    http://www.excelforum.com/showthread...t=#post4414683
    _....
    I should of twigged to the magnifying glass search – I am still a bit of a computer idiot ( very late starter )
    _.................

    _____________________________-

    Quote Originally Posted by WasWodge View Post
    ....
    With respects to screenupdating, calculation etc. you should strictly speaking rather than automatically set things back to true record it's original state it was in at the start of the program running and then return it back to what ever the original state was at the end (and no I rarely do this either ). ...
    I never thought of that !! ...A very good point actually ...

    I think...
    - being a bit of a Pedantic Bugger I should have twigged to that... Something else for me to annoy people with by including in all my codes.. A sort of Put back What Was Wodge Bodge..lol..
    I guess there is a simpler way than something like this, but I like it ??
    Please Login or Register  to view this content.
    _..................................

    The viewpoint of “ Another view point. One which says only do things if you need to … „ was not mine_ .. - .. It was given to me a few times , mostly by very talented people, ( Kyle the last I think )... ----
    http://www.excelforum.com/excel-prog...ml#post4446317 .... ---- their point being along the lines... “...not only adds unnecessary code, but also masks where it is actually necessary (it actually is in certain obscure scenarios)...” I can follow the reasoning. But it requires that you know exactly when everything is needed, as they do and I don’t. The argument does not address the “things changing with new versions or updates etc thing”... But I guess they would argue that every one should keep up to date and always use the most recent versions etc, as they can and I can’t... but .. enough.. maybe ..
    _.................

    Thanks again, especially for the Put What Was Bodge , wot , Was Wodge


    Alan.

    P.s. What’s wrong with writing a book ? ( apart from never having the time to do it )
    Last edited by Doc.AElstein; 08-29-2016 at 07:28 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] VLOOKUP, gives #N/A error (if Range lookup=0) or wrong data (if Range lookup=empty)
    By Ebalinska in forum Excel Formulas & Functions
    Replies: 19
    Last Post: 01-14-2016, 05:55 AM
  2. [SOLVED] Whats wrong with this .range statement???
    By klunker in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-02-2014, 03:15 PM
  3. What is wrong?? SUM values in a range with criterion
    By Cedric Wyckmans in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-07-2014, 11:10 AM
  4. Charting wrong range when duplicatnig tab
    By gtsinc in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 05-16-2013, 02:07 PM
  5. Code to select range and open a user form is opening the wrong range
    By rrbest in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-05-2010, 12:34 PM
  6. What is wrong with this range description
    By petca059 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-23-2010, 04:40 AM
  7. [SOLVED] Something wrong with my dynamic name range
    By cabybake in forum Excel General
    Replies: 0
    Last Post: 03-30-2006, 09:30 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1