Hyperlink values

[S. Malik]

Hi,

I have got a column with certain values with each value pointing to a web
address. Now what I need done is, get the values in one columns and links in
other colums. For e.g: In A1, I have "Ben's Kitchen" hyperlinked to
"www.example.com", I want A1= Ben's Kitchen; B1=www.example.com

Any ideas or suggestions?

Thanks very much in advance.

S. Malik

 

[Gary''s Student]

We will use cols A, B, and C. With the hyperlink in col A, in B1 enter"
=A1 this gets the "friendly name"

In C1 enter:

=hyp(A1)

You must also install the following User Defined Function (UDF) to avoid the
#NAME error:

Function hyp(r As Range) As String
hyp = ""
If r.Hyperlinks.Count > 0 Then
hyp = r.Hyperlinks(1).Address
Exit Function
End If
If r.HasFormula Then
rf = r.Formula
dq = Chr(34)
If InStr(rf, dq) = 0 Then
Else
hyp = Split(r.Formula, dq)(1)
End If
End If
End Function

User Defined Functions (UDFs) are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it.

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the UDF from the Excel worksheet:

=hyp(A1)

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

or

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx
for specifics on UDFs

 

[S. Malik]

This was really very helpful Gary. Thanks very much. : )

 

[Gary''s Student]

You are very welcome!

 

[Mary-Anne]

Thank you Gary's student! Worked like a charm. Is ok to leave function in
workbook or is it better to remove?

 

[Mary-Anne]

I have to admit that I am clueless about macros. However, I followed all
your steps and the function/macro did exactly what I wanted. However..... I
want to remove the function/macro from the worksheet and my computer as it is
slowing the document down something terrible. I followed your steps and
deleted the function in VBE but everytime I open the document the question is
asked if i want to enable the macro. Help! Where is it lurking? How do I
fell the damn beast. It is no longer useful to me

 

[Mary-Anne]

OK, I’m stumped. I also admit to being absolutely clueless about
macros/functions. However, I followed your steps and it worked like a charm.
Not so trying to dispose of said function/macro. It is slowing my document
down terribly and every time I open the document there’s the question again:
Do you want to enable macro? It is obviously lurking somewhere in my
document … on my computer … Where?!!!! How do I find the beast and dispose
of it now that I no longer have a use for it?

 

[Dave Peterson]

Debra Dalgleish shares how to remove the remnants of macros:
http://contextures.com/xlfaqMac.html#NoMacros

Remember to convert those formulas (=hyp()) to values first, or you'll break
them.