Sheet Range Object Item List Reorder

**Doc.AElstein** · 01-28-2015, 10:49 AM

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.

**natefarm** · 01-28-2015, 03:19 PM

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!

**Doc.AElstein** · 01-28-2015, 06:10 PM

Originally Posted by natefarm

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

****

Originally Posted by natefarm

......... Hope that helps!

... it sure did!! Thanks!"

**Doc.AElstein** · 02-01-2015, 06:30 PM

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

**Doc.AElstein** · 02-01-2015, 06:31 PM

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

**Doc.AElstein** · 02-01-2015, 06:33 PM

Code:

Please Login or Register  to view this content.

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

**Doc.AElstein** · 02-03-2015, 08:53 AM

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.

**Doc.AElstein** · 02-18-2015, 08:15 AM

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

**Doc.AElstein** · 02-18-2015, 08:16 AM

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.

**Doc.AElstein** · 08-07-2015, 09:50 AM

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.

Sheet Range Object Item List Reorder

LinkBack

Thread Tools

Rate This Thread

Display

Sheet Range Object Item List Reorder

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

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Similar Threads

UserForm: Search Item in List > Call Table associated with Item > Execute Command

change filter list menu from alphabetical order to order of original list

[SOLVED] Problems with dynamic named range - hyperlinks to trigger macro to change drop down list

using range.advancedfilter to copy data into a list object

Formula to Calculate 2nd Most Common Text Item in List with Date Range Constraints

Bookmarks

Bookmarks

Posting Permissions