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:

Sub SpreadsheetRangeCaptureHyperlinkItemOrderBodged() '
Dim wks4 As Worksheet: Set wks4 = ThisWorkbook.Worksheets("CrazyHyperlinkItemOrderBodged") 'Give wks abbreviation Object, Method and Functions of Object Worksheet via enabled Intellisense suggestions after typing "." (Dot) after Object wks1

'Part 1a. The initial "Creating” of One Range Object to include all cells of interest.--------------------
Dim ObjectCapturedRange As Range 'This will be set to One Range Object. Only a Range object is seen so item types can be defined as Range Objects
Set ObjectCapturedRange = wks4.Range("A1").CurrentRegion 'Typical way to capture the Range in one go. Captured Range will be a grid which Inlcudes any cells "connected" to A1. SO IMPORTANT: Keep periphery of required range free. Alternative UsedRange would make a grid catching / Capturing any cell EVER used.

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

           = ObjectCapturedRange.Hyperlinks(ItemNumber).Address

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

Option Explicit
Sub SpreadsheetRangeCaptureHyperlinkItemOrderBodged2() '

Dim wks5 As Worksheet: Set wks5 = ThisWorkbook.Worksheets("CrazyHyperlinkItemOrderBodged2") 'Give wks abbreviation Object, Method and Functions of Object Worksheet via enabled Intellisense suggestions after typing "." (Dot) after Object wks1
'##### Change Sheet Reference above to suit your Sheet

'Part 1a. The initial "Creating" One Range Object to include all cells of interest.--------------------
Dim ObjectCapturedRange As Range 'This will be set to One Range Object. Only a Range object is seen so item types can be defined as Range Objects
Set ObjectCapturedRange = wks5.Range("B2").CurrentRegion 'Typical way to capture the Range in one go. Captured Range will be a grid which Inlcudes any cells "connected" to A1. SO IMPORTANT: Keep periphery of required range free. Alternative UsedRange would make a grid catching / Capturing any cell EVER used.
'##### Change B2 above to suit your start cell
Dim OutputTableRow As Long, OutputTableColumn As Long 'Bound Loop Count Variables for Output Table..  ......  http://www.mrexcel.com/forum/excel-questions/803662-byte-backward-loop-4.html
Dim LastRowTableOutput As Long: Let LastRowTableOutput = ObjectCapturedRange.Rows.Count 'We wish to loop below through items in the Object Range Capture Array and put coresponding determined..
Dim LastColumnTableOutput As Long: Let LastColumnTableOutput = ObjectCapturedRange.Columns.Count '... URL Strings in an output array, so we use this convenient Count Property to get the Dimensions then required for a Pre(RE)dimensioined Array

'Part 1b Array for URL Strings. Determined by looping through items in ObjectCapturedRange
Dim HyperlinkUrlValuesArray() As Variant 'This will be an  Array of Values but must be Dimensioned as variant as in the assigning below it will see a Range Object Initially from which the Hyperlink item  property returns the string values. (So Dimensioning to String will not work: jindon   http://www.excelforum.com/excel-programming-vba-macros/1058171-return-row-index-and-column-index-of-a-cell-in-a-range.html  )
ReDim HyperlinkUrlValuesArray(1 To LastRowTableOutput, 1 To LastColumnTableOutput) 'ReDim must be used as DIM only takes actual numbers, not variables. We have the info now to fill in our Array of RangeObjects by looping
Dim ItemNumber As Long: Let ItemNumber = 0 'In ObjectCapturedRange the hyperlinks are listed in a long sequential List. As we assign by looping for the outpüut Array URL string values we need a sequential count
    For OutputTableColumn = 1 To LastColumnTableOutput Step 1  'take each colunn (as the items are grouped in column sequentailly tacked on to eachother......
        For OutputTableRow = 1 To LastRowTableOutput Step 1  '... go along (down) each row, but miss out the first
        Let ItemNumber = ItemNumber + 1
            If OutputTableRow = 1 Then 'For the first Output table row we need the item at the end, that is to say, offset by the Captured range row number -1
            Let HyperlinkUrlValuesArray(OutputTableRow, OutputTableColumn) = ObjectCapturedRange.Hyperlinks(ItemNumber - 1 + LastRowTableOutput).Address
            Else 'For all other Output table rows the item we want is one item back
            Let HyperlinkUrlValuesArray(OutputTableRow, OutputTableColumn) = ObjectCapturedRange.Hyperlinks(ItemNumber - 1).Address
            End If
        Next OutputTableRow
    Next OutputTableColumn
'End Part 1-------------------------------------------------------------------------------------------

'Part 2 Output URL String Arrays To New range----------
Let wks5.Range("A17").Resize(LastRowTableOutput, LastColumnTableOutput).Value = HyperlinkUrlValuesArray() 'Neat way to output results in one go: resize the output start cell to the size of the Array with required output then equate that new range of values to that Array

'End Part 2---------------------------------