Creating a VBA Range object from Excel HYPERLINK workbook function
 

I am trying to create a VBA macro that will chart some data points,
both measured data and limit data.

The measured data is listed in a range on another worksheet of the same
workbook that is hyperlinked using the workbook function 'HYPERLINK',
and I need to retrieve the contents of the hyperlink (which contains
full path and filename information) as a range object to pass on the
relevant columns to the chart, but I have not found an elegant way to
do this.

In a message from 1999, <news:en#jj3ni#GA.263@cppssbbsa03, Chip Pearson wrote that:

[...]
if you are using the =HYPERLINK() worksheet
function. In that case, use

TheAddress = Mid(Range("A1").Formula, 13, InStr(1,
Range("A1").Formula, ",") - 14)

to get the address of the hyperlink.

<http://groups.google.com/group/microsoft.public.excel.programming/msg/6fec646182dd90a4

I am, however, hoping that there is a more elegant solution available
than extracting the range information from the hyperlink in this way,
and I hope that you will be able to point me in the right direction.

--
Troels Forchhammer
TP -- Verification Services


The idea of being *paid* to govern is terribly
middle-class :-)
- Igenlode on AFH-P

Creating a VBA Range object from Excel HYPERLINK workbook function
 

On May 10, 1:46 pm, Troels Forchhammer
wrote:
I am trying to create a VBA macro that will chart some data points,
both measured data and limit data.

The measured data is listed in a range on another worksheet of the same
workbook that is hyperlinked using the workbook function 'HYPERLINK',
and I need to retrieve the contents of the hyperlink (which contains
full path and filename information) as a range object to pass on the
relevant columns to the chart, but I have not found an elegant way to
do this.

In a message from 1999, <news:en#jj3ni#GA.263@cppssbbsa03, Chip Pearson wrote that:



[...]
if you are using the =HYPERLINK() worksheet
function. In that case, use

TheAddress = Mid(Range("A1").Formula, 13, InStr(1,
Range("A1").Formula, ",") - 14)

to get the address of the hyperlink.

<http://groups.google.com/group/microsoft.public.excel.programming/msg...

I am, however, hoping that there is a more elegant solution available
than extracting the range information from the hyperlink in this way,
and I hope that you will be able to point me in the right direction.

--
Troels Forchhammer
TP -- Verification Services


The idea of being *paid* to govern is terribly
middle-class :-)
- Igenlode on AFH-P

Hello Troels,

The easiest way in VBA is to use the SubAddress property of the
Hyperlink. This property returns the Worksheet name and Range in this
format as a string: "Sheet!A1:A100". Hyperlinks can only be referenced
by their index number in the collection, which is 1 based, or by using
the displayed text.

For example, you have one Hyperlink on the "Sheet 1" which displays
"Data Group 2". You can get the worksheet and range by either method:
RangeAdress = ActiveSheet.Hyperlinks(1).SubAddress
or
RangeAddress = ActiveSheet.Hyperlinks("Data Group 2").SubAddress

Sincerely,
Leith Ross