+ Reply to Thread
Results 1 to 10 of 10

Sheet Range Object Item List Reorder

  1. #1
    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

    Sheet Range Object Item List Reorder

    Title Changed 03.02.2015 by Alan from
    Re: Range Object Hyperlinks(Item) – List order Anomaly


    .......Original

    Range Object Hyperlinks(Item) – List order Anomaly

    Hi,
    . Hope this is in the right Sub Forum. (It may border between Basics and Normal or even Advanced)

    . I was recently handling large amounts of Hyperlinks using VBA.
    . I sometimes had a Spreadsheet Range where all cells contained Hyperlinks. As is Typical practice I “captured” this Range as a Range object. Here a typical exert from such a code:

    Please Login or Register  to view this content.

    . After doing this I can, for example, pick out the various (attributes? If that is the word??) from the item list of the Hyperlink “bit” inside the one big Range Object. For example, I was interested in obtaining the URL string. By inspection of the contents in the Watch Window shown for my Object”Captured”Range the URL string I wanted was seen to be one of the (“Attributes??) for each Hyperlink Item. A typical code exert then to obtain this would be of the form:

    Please Login or Register  to view this content.
    …. Where ItemNumber is a Whole Number variable


    . After many frustrating hours after getting weird mixed up order listings I identified that the Item list Order is somewot weird:

    . I attempt to demonstrate this graphically as best I can:

    . If this is a spreadsheet range of Hyperlinks:


    - A B C
    1 Link 1 Link 8
    2 Link 2 Link 9
    3 Link 3 Link 10
    4 Link 4 Link 11
    5 Link 5 Link 12
    6 Link 6 Link 13
    7 Link 7 Link 14
    8


    ..... then this is the corresponding order of the Items held within the Range Object:


    Item 7
    Item 1
    Item 2
    Item 3
    Item 4
    Item 5
    Item 6
    Item 14
    Item 8
    Item 9
    Item 10
    Item 11
    Item 12
    Item 13


    . Once I realized this I was able to “Bodge” my way around it.
    . But my 3 main Questions.

    . 1) Could experienced users confirm for me that this is (always) the case and point me in the direction of any literature confirming this.

    . 2) Is there some way to change the default order to something more sensible?


    . I ask these questions as I am very inexperienced and am somewot wary of using something which appears to behave in a very peculiar way. I am therefore not sure if it would always do it in this way.
    . I note for example that in the same Range object I may obtain the name of the link through the Property using something of the form
    . .Value2(Row , Column)
    . --- .. in this case the Values2() are held in an Array which looks identical both in size and order to the how the Spreadsheet Range A1 to B7 looks.

    (. 3) Just something minor I noticed in passing. In the Watch Window it appears that the number of items are Limited to 256. However, my final macro is working to date for over a thousand Hyperlinks. And Indeed in the Watch Window Values2 does not appear to be so limited. Can someone confirm that the limit is above 256? )


    . So basically I am looking for some clarity from someone who has more in depth knowledge of how VBA is working on how / why this peculiar order is used and if I can rely on it always being so...

    . Many Thanks
    Alan Elston.

    P.s. If it helps I enclose the file where I was investigating this strange Anomaly with a small sample of data.
    https://app.box.com/s/2ehgbdysv6uilc0g7z7jxi3t6xvw6njt
    . There are 5 sheets.
    . For each sheet there is one macro in each Sheet Module.
    . In the Final 2 Sheets I have a done a somewot crude “Bodge” to give me an Output URL string Array in the same order as the Captured Spreadsheet Hyperlink Range. – If BTW any Profi has a more professional solution for bringing out the URL strings in the correct order (for me “correct” here which would be the order correspond to the order of the Spreadsheet Range) then I would very much appreciate a copy of such a code.


    . The Final Code I wrote for example might be a start point for anyone requiring a quick solution to such a problem..
    . It will take this arbitrary range

    B
    C
    2
    Apple fresh Apfel
    3
    'Bierwurst' (coarse heat-treated sausage in bladder and smo "Cordon bleu" vom Schwein, bofrost
    4
    'Breslauer' Lyonaise "Peperonata" Paprikazubereitung Vogeley GV
    5
    'Gaisburger Marsch' (potatoes with beef) (1) "Pomona" Tomtenpüree-Konzentrat Vogeley GV


    and after running the macro “SpreadsheetRangeCaptureHyperlinkItemOrderBodged2()” it will give the outputted URL strings, in this form and correct order

    A
    B
    17
    http://www.ernaehrung.de/lebensmittel/en/F110100/Apple-fresh.php http://www.ernaehrung.de/lebensmittel/de/F110000/Apfel.php
    18
    http://www.ernaehrung.de/lebensmittel/en/W255400/%27Bierwurst%27-%28coarse-heat-treated-sausage-in-bladder-and-smo.php http://www.ernaehrung.de/lebensmittel/de/BOFRO1287/Cordon-bleu-vom-Schwein,-bofrost.php
    19
    http://www.ernaehrung.de/lebensmittel/en/W231100/%27Breslauer%27-Lyonaise.php http://www.ernaehrung.de/lebensmittel/de/VOGEL15805/Peperonata-Paprikazubereitung-Vogeley-GV.php
    20
    http://www.ernaehrung.de/lebensmittel/en/X468713/%27Gaisburger-Marsch%27-%28potatoes-with-beef%29-%281%29.php http://www.ernaehrung.de/lebensmittel/de/VOGEL15807/Pomona-Tomtenpueree-Konzentrat-Vogeley-GV.php


    Code:

    Please Login or Register  to view this content.
    Last edited by Doc.AElstein; 02-03-2015 at 08:13 AM.

  2. #2
    Valued Forum Contributor natefarm's Avatar
    Join Date
    04-22-2010
    Location
    Wichita, Kansas
    MS-Off Ver
    2016
    Posts
    1,018

    Re: Range Object Hyperlinks(Item) – List order Anomaly

    I will admit that my eyes went a little crossed looking at your lengthy post, so I didn't study it extensively, but it might help to make sure you understand that if your code goes through a sheet's (or range's) hyperlinks collection by item number, it will return them in the order they were added to the sheet. So if the 1st hyperlink added to the sheet was in A4, that will be Activesheet.hyperlinks(1). If the 2nd was added in A3, that will be Activesheet.hyperlinks(2), etc. However, if you loop through the range a cell at a time (rather than the full range), you would get the hyperlinks in the order in which you process the cells (cells(3,1).hyperlinks(1); cells(4,1).hyperlinks(1), etc.). Hope that helps!
    Acts 4:12
    Salvation is found in no one else, for there is no other name under heaven given to mankind by which we must be saved.

  3. #3
    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: Range Object Hyperlinks(Item) – List order Anomaly

    Quote Originally Posted by natefarm View Post
    ............. if your code goes through a sheet's (or range's) hyperlinks collection by item number, it will return them in the order they were added to the sheet. So if the 1st hyperlink added to the sheet was in A4, that will be Activesheet.hyperlinks(1). If the 2nd was added in A3, that will be Activesheet.hyperlinks(2), etc. However, if you loop through the range a cell at a time (rather than the full range), you would get the hyperlinks in the order in which you process the cells (cells(3,1).hyperlinks(1); cells(4,1).hyperlinks(1), etc.).......
    Hi natefarm,,
    . Very sorry for the long post. The problem was that by a very unlucky coincidence in the order I had pasted in with the numerous experimenting I did, there was a weird but apparently consistent "order" to the item list. So I went wildly off tracking down this “order” and went writing complicated “bodge” programs to always unravel this “order”.
    . Now having got your explanation and re-experimented I see that 90% of wot I wrote was rubbish. Sorry about that.
    . I seem to remember now that I encountered a similar phenomena after adding sheets where the Sheet.item again came out ,I think, similarly to be listed in the worksheets Object in the order that the sheets were added. I found that out by experimenting as well.
    . It never twigged to me that the pasting in of Hyperlinks would follow this rule. Especially as I was dealing with a range rather than the sheet. So I am very grateful for you putting me straight. I apologies for the eye strain caused by the waffling resulting from my ignorance.
    . I expect if these order Rules are written anywhere, then they are so well hidden that it is a case of virtually having to know the answer to know the correct search criteria in a search to find the answer (as appears to be a growing tendency with Microsoft stuff!)!!

    . Possibly this order based on when something was added could be a general rule of anything referred to as an item?

    . I bet this sort of thing catches people out often (I note also when pasting another list in from another sheet, it will be tacked on at the end of the list.. BUT maintains the order in which it was pasted into sheet from where it came!!!).

    . I hope that this Thread has added now something to be found when “Googling” for something like” VBA Item list order anomaly”….…thanks to your reply I think there is a good chance some other novices like me may be spared a few hours of unnecessary frustration.

    Thanks again.
    Alan Elston.

    P.s. I will leave the thread for a day or two as unsolved in case I or anyone else can contribute with something more such as where these “Rules” are written. But you have certainly come 99% close to solving this one for me.**** I will therefore mark it as solved in a few days if no other enlightenments come in.


    ****
    Quote Originally Posted by natefarm View Post
    ......... Hope that helps!
    ... it sure did!! Thanks!"

  4. #4
    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: Range Object Hyperlinks(Item) – List order Anomaly

    Hi,
    . I will mark the Thread here as Solved and add a final code..
    . I will only describe the code very briefly. Anyone interested in using a version “In anger” or has any other questions about it then please contact through this thread and I will be happy to help if I can.
    .
    . Basically as part of taking my project further I have written a program which amongst other things reorganizes the Item list.

    . I appreciate that the whole process is well “over the top” for the particular problem detailed in this Thread. Indeed the reorganizing of the item lit could be considered a by-product. I simply wanted to keep as many options open as possible as well as taking the code writing generally as a learning excessive..
    . Thanks go to, amongst other, Kyle and Jerry Sullivan in other Posts such as
    http://www.mrexcel.com/forum/excel-q...l?#post4059199
    for general help given on aspects of this code. (This code is quasi a “by-Product” from that Thread)
    .
    . The code basically places a 2 dimensional Spreadsheet Range of Range Objects into a Microsoft Scripting Runtime Dictionary. Each Range item is stored as a Range Object with it’s own unique Key. Simply for convenience the string key (ID) is taken as the .value of the cell and the actual item stored is the Cell as a Full Range Object in a Microsoft Scripting Runtime Dictionary

    . I choose the convention of going along the rows and then down the columns as this will give Dictionary item numbers (or “index”) that follow a similar convention to the spreadsheet convention for cells (The Cells property will accept one or two arguments. When using only one argument, it must be a number which is an index or “item number” from right to left then top to bottom.

    . To apply to a general range one must simply change the bit in Purple
    to a cell anywhere within the range of interest. (The sheet reference must also be changed appropriately)

    . At the end of the program, Part 4), the range is transferred to a new created sheet.- This part is just for demonstration purpose (and actually indirectly accesses the actual Spreadsheet ranges!)

    . In the practice parts of the Range Object could be accessed directly from the Microsoft Scripting Runtime Dictionary, as could be here the Hyperlinks, again but in the correct order.

    (. A check is made and appropriate action to warn of empty cells, but the use of the unique Microsoft Scripting Runtime Dictionary for checking for identical cell contents falls down, as the Dictionary item is a Range which in this case will always be unique as it is a different Cell (Range Object) each time. – But I am still a bit puzzled as to why I could not get this bit too work..).. )

    . The code as written would take the jumbled up listing order as initially described in this Thread of this range



    B
    C
    2
    Apple fresh
    Apfel
    3
    'Bierwurst' (coarse heat-treated sausage in bladder and smo
    Beef cooked
    4
    'Breslauer' Lyonaise
    "Peperonata" Paprikazubereitung Vogeley GV
    5
    'Gaisburger Marsch' (potatoes with beef) (1)
    "Pomona" Tomtenpüree-Konzentrat Vogeley GV
    6
    Beef cooked
    'Biene-Maja' Banane-Mandel Fruchtschnitte, Evers Naturkost
    7
    'Heaven and earth' (apples and pot.) with blood sausage (3)
    'Flip' Apfel-Birne Fruchtschnitte, Evers Naturkost
    8
    'Jägersoße' (thickened brown sauce with mushrooms) (5)
    'Maja-Willi-Flip' Multifrucht Fruchtschnitte, Evers Naturkost
    9


    … and paste to a new identical looking sheet but with the items in the order convention described above.

    ……Code in next post…..

  5. #5
    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: Range Object Hyperlinks(Item) – List order Anomaly

    Hi,
    . I will mark the Thread here as Solved and add a final code..
    . I will only describe the code very briefly. Anyone interested in using a version “In anger” or has any other questions about it then please contact through this thread and I will be happy to help if I can.
    .
    . Basically as part of taking my project further I have written a program which amongst other things reorganizes the Item list.

    . I appreciate that the whole process is well “over the top” for the particular problem detailed in this Thread. Indeed the reorganizing of the item lit could be considered a by-product. I simply wanted to keep as many options open as possible as well as taking the code writing generally as a learning excessive..
    . Thanks go to, amongst other, Kyle and Jerry Sullivan in other Posts such as
    http://www.mrexcel.com/forum/excel-q...l?#post4059199
    for general help given on aspects of this code. (This code is quasi a “by-Product” from that Thread)
    .
    . The code basically places a 2 dimensional Spreadsheet Range of Range Objects into a Microsoft Scripting Runtime Dictionary. Each Range item is stored as a Range Object with it’s own unique Key. Simply for convenience the string key (ID) is taken as the .value of the cell and the actual item stored is the Cell as a Full Range Object in a Microsoft Scripting Runtime Dictionary

    . I choose the convention of going along the rows and then down the columns as this will give Dictionary item numbers (or “index”) that follow a similar convention to the spreadsheet convention for cells (The Cells property will accept one or two arguments. When using only one argument, it must be a number which is an index or “item number” from right to left then top to bottom.

    . To apply to a general range one must simply change the bit in Purple
    to a cell anywhere within the range of interest. (The sheet reference must also be changed appropriately)

    . At the end of the program, Part 4), the range is transferred to a new created sheet.- This part is just for demonstration purpose (and actually indirectly accesses the actual Spreadsheet ranges!)

    . In the practice parts of the Range Object could be accessed directly from the Microsoft Scripting Runtime Dictionary, as could be here the Hyperlinks, again but in the correct order.

    (. A check is made and appropriate action to warn of empty cells, but the use of the unique Microsoft Scripting Runtime Dictionary for checking for identical cell contents falls down, as the Dictionary item is a Range which in this case will always be unique as it is a different Cell (Range Object) each time. – But I am still a bit puzzled as to why I could not get this bit too work..).. )

    . The code as written would take the jumbled up listing order as initially described in this Thread of this range



    B
    C
    2
    Apple fresh
    Apfel
    3
    'Bierwurst' (coarse heat-treated sausage in bladder and smo
    Beef cooked
    4
    'Breslauer' Lyonaise
    "Peperonata" Paprikazubereitung Vogeley GV
    5
    'Gaisburger Marsch' (potatoes with beef) (1)
    "Pomona" Tomtenpüree-Konzentrat Vogeley GV
    6
    Beef cooked
    'Biene-Maja' Banane-Mandel Fruchtschnitte, Evers Naturkost
    7
    'Heaven and earth' (apples and pot.) with blood sausage (3)
    'Flip' Apfel-Birne Fruchtschnitte, Evers Naturkost
    8
    'Jägersoße' (thickened brown sauce with mushrooms) (5)
    'Maja-Willi-Flip' Multifrucht Fruchtschnitte, Evers Naturkost
    9


    … and paste to a new identical looking sheet but with the items in the order convention described above.

    ……Code in next post…..

  6. #6
    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: Range Object Hyperlinks(Item) – List order Anomaly

    Code:



    Please Login or Register  to view this content.



    File (XL 2007 Range Referencing Passing Through Arrays.xlsm)
    https://app.box.com/s/2ehgbdysv6uilc0g7z7jxi3t6xvw6njt

  7. #7
    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: Range Object Hyperlinks(Item) – List order Anomaly

    Hi,
    . Just updating with a better code version developed here yesterday
    http://www.mrexcel.com/forum/excel-q...bjects.html?&&
    . Explanations in Post #10
    Alan


    Please Login or Register  to view this content.

  8. #8
    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: Range Object Hyperlinks(Item) – List order Anomaly

    Hi, Have no real Problem currently here, rather Just feeding back a couple of small bits of info :--
    (BTW I am currently using XL 2007)
    . 1). Just wanted to mention in passing that I noticed empirically that my hyperlink limit per Excel Spreadsheet was about 65530. I have not yet*** found specific written confirmation of this anywhere yet, but in searching I find many people have noticed similar limits. So I guess this is about the limit.
    . As a result of this limit I had a problem using my last code for reorganizing a row of approx 33928 Hyperlinks.. This is because the code as written copies the re-ordered item Range into an offset range of similar dimension. Hence I am effectively trying to have 67586 ( = 33928 x 2 ) Hyperlinks at once on the sheet which aint possible. So after item 31602 of the new resorted list, (=67586 – 33928 or ( 33928OriginalList + 31602NewSortedList = 65530 )) items ( rows in my current case), I only get values not hyperlinks put in the reordered range. I did a Simple mod to get the full 65530 available for the new offset Range Reordered List. That was simply to add a line after the offset paste to delete the Original source range list.
    . 2). I thought I was being clever and using the very last column in the spreadsheet as a temporary column (To assist getting Unique keys and / or highlighting Duplicate cells). I found in the practice that for large files this sometimes seemed to cause some weird resource issue errors when trying to delete Columns. Worth bearing in mind to consider sometimes using a spare column “just off screen” Maybe instead.

    . In next Post (# 9) a modified code reflecting the above..
    . P.s.1. I confirmed the limit using various simple copy paste variations manually and in code. So I do not think it is an Object or Microsoft Scripting Runtime Dictionary limit.
    ***. P.s.2. if anyone in the know, knows more about this limit then I am sure it would be an enrichment to this Thread and enlighten me / anyone else looking in the Future here..

    Alan
    '_- 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 )

  9. #9
    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: Range Object Hyperlinks(Item) – List order Anomaly

    Code: corresponding to last Post (….#8)
    Code: (Note as before Purple bits for notes on how to modify for your File)

    Please Login or Register  to view this content.

  10. #10
    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: Range Object Hyperlinks(Item) – List order Anomaly

    Hi,
    .. This is just a quick feedback, for my Future reference, if no one else’s...
    . This Thread was concerned with the Issue of item list number in VBA.
    . In particular how the Item order comes about.
    . Somewhere along the way the statement was given ( in the absence of finding any concrete documentation ) that....

    .. “ Item List number reflects the order in which things are out in “ . It certainly seemed to be the case for adding things into a spreadsheet. And a few experienced people have told ma that that is a general rule!!
    .
    . I just found along the way a discrepancy, so thought I would note it here.

    . ....
    . 1 ) If I Add a sheet to a workbook, that general rule does not seem too apply
    .
    . 2 ) If I move a sheet tab, manually dragging it, or with a code line ) similarly that general rule does not seem too

    Rather than labour the verbal Description of what happens, you can run or work through with F8 the first of the codes given below on a fresh Sheet ( Assuming you have XL2007 or XL 2010 which strats with having 3 sheets. )


    . The Bottom line is that you can manipulate the item number of Worksheets by
    . - ... where they are
    . rather than
    . - ... when you add them,
    . or
    . - ... by moving them ( manually or by code)
    .
    . At the end of the day , The item order for sheets is determined by what you see in the tab list at the bottom of the sheet , starting at item number 1 at the left and increasing as you go to the right.

    . As always, any Profi input, such as any documentation explaining how the item number is determined for different things would be welcome
    . But i expect I have hit on another one of those things that nobody knows for sure or ever thought to document..... ( At least the last code I give, necessary to reorganise the item lists for a sheet, is considerable simpler than that for the ( Hyperlink ) items in a sheet... that is to say for the simple example given here...I will probably post again with a more general code ....later.. ......)


    Alan.

    Demo Codes:

    Please Login or Register  to view this content.
    Last edited by Doc.AElstein; 08-07-2015 at 09:55 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. UserForm: Search Item in List > Call Table associated with Item > Execute Command
    By TexasAggie12 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-25-2014, 12:58 PM
  2. Replies: 0
    Last Post: 02-15-2014, 10:30 AM
  3. [SOLVED] Problems with dynamic named range - hyperlinks to trigger macro to change drop down list
    By hcyeap in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 02-04-2014, 10:45 AM
  4. using range.advancedfilter to copy data into a list object
    By mtnbiker98 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 05-28-2013, 02:58 PM
  5. Replies: 0
    Last Post: 09-05-2012, 10:11 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