VBA: adding link anchor from the function

F

FlexMind

New Member
Joined
Apr 9, 2019
Messages
20
Hello!
Is it possible to add a link anchor from UDF?
I have a code:
Code: 
Function ActivateLinks(url As String)Dim linkAddr As String
linkAddr = "g5"
HyperLink url, linkAddr
End Function
Sub HyperLink(ByVal link As String, ByVal addr As String)
ActiveSheet.Hyperlinks.Add Anchor:=Range(addr), Address:=link
End Sub
Why does it not work?
Thank you in advance!
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Sort by date Sort by votes
I found some solution, but not works for me:
Code: 
[COLOR=#333333][FONT='inherit']The solution is much more complicated because worksheet and range object can not be access through UDF.[/FONT][/COLOR][COLOR=#333333][FONT='inherit']Actually, the hyperlink is created by calculate event with following solution.[/FONT][/COLOR]
[COLOR=#333333][FONT='inherit']You can try following steps[/FONT][/COLOR]
[COLOR=#333333][FONT='inherit']1. Insert a class module and name it "clsSht" and add following code:[/FONT][/COLOR]
[COLOR=#000088][FONT=inherit]Public[/FONT][/COLOR][COLOR=#000088][FONT=inherit]WithEvents[/FONT][/COLOR][COLOR=#000000][FONT=inherit] Sht [/FONT][/COLOR][COLOR=#000088][FONT=inherit]As[/FONT][/COLOR][COLOR=#000000][FONT=inherit] Worksheet
[/FONT][/COLOR][COLOR=#000088][FONT=inherit]Public[/FONT][/COLOR][COLOR=#000000][FONT=inherit] Rng [/FONT][/COLOR][COLOR=#000088][FONT=inherit]As[/FONT][/COLOR][COLOR=#000000][FONT=inherit] Range
[/FONT][/COLOR][COLOR=#000088][FONT=inherit]Public[/FONT][/COLOR][COLOR=#000000][FONT=inherit] myURL [/FONT][/COLOR][COLOR=#000088][FONT=inherit]As[/FONT][/COLOR][COLOR=#000088][FONT=inherit]String[/FONT][/COLOR][COLOR=#000000][FONT=inherit]

[/FONT][/COLOR][COLOR=#000088][FONT=inherit]Private[/FONT][/COLOR][COLOR=#000088][FONT=inherit]Sub[/FONT][/COLOR][COLOR=#000000][FONT=inherit] Sht_Calculate[/FONT][/COLOR][COLOR=#666600][FONT=inherit]()[/FONT][/COLOR][COLOR=#000000][FONT=inherit]
    [/FONT][/COLOR][COLOR=#000088][FONT=inherit]On[/FONT][/COLOR][COLOR=#000088][FONT=inherit]Error[/FONT][/COLOR][COLOR=#000088][FONT=inherit]Resume[/FONT][/COLOR][COLOR=#000088][FONT=inherit]Next[/FONT][/COLOR][COLOR=#000000][FONT=inherit]
    Sht[/FONT][/COLOR][COLOR=#666600][FONT=inherit].[/FONT][/COLOR][COLOR=#000000][FONT=inherit]Hyperlinks[/FONT][/COLOR][COLOR=#666600][FONT=inherit].[/FONT][/COLOR][COLOR=#000000][FONT=inherit]Add Rng[/FONT][/COLOR][COLOR=#666600][FONT=inherit],[/FONT][/COLOR][COLOR=#000000][FONT=inherit] myURL
[/FONT][/COLOR][COLOR=#000088][FONT=inherit]End[/FONT][/COLOR][COLOR=#000088][FONT=inherit]Sub[/FONT][/COLOR][COLOR=#333333][FONT='inherit']2. Insert a module and add following code:[/FONT][/COLOR]
[COLOR=#000088][FONT=inherit]Public[/FONT][/COLOR][COLOR=#000000][FONT=inherit] newLink [/FONT][/COLOR][COLOR=#000088][FONT=inherit]As[/FONT][/COLOR][COLOR=#000000][FONT=inherit] clsSht
[/FONT][/COLOR][COLOR=#000088][FONT=inherit]Function[/FONT][/COLOR][COLOR=#000000][FONT=inherit] getLink[/FONT][/COLOR][COLOR=#666600][FONT=inherit]([/FONT][/COLOR][COLOR=#000000][FONT=inherit]TS [/FONT][/COLOR][COLOR=#000088][FONT=inherit]As[/FONT][/COLOR][COLOR=#000088][FONT=inherit]String[/FONT][/COLOR][COLOR=#666600][FONT=inherit])[/FONT][/COLOR][COLOR=#000000][FONT=inherit]
    [/FONT][/COLOR][COLOR=#000088][FONT=inherit]Dim[/FONT][/COLOR][COLOR=#000000][FONT=inherit] myURL   [/FONT][/COLOR][COLOR=#000088][FONT=inherit]As[/FONT][/COLOR][COLOR=#000088][FONT=inherit]String[/FONT][/COLOR][COLOR=#000000][FONT=inherit]
    [/FONT][/COLOR][COLOR=#000088][FONT=inherit]Dim[/FONT][/COLOR][COLOR=#000000][FONT=inherit] Sht     [/FONT][/COLOR][COLOR=#000088][FONT=inherit]As[/FONT][/COLOR][COLOR=#000000][FONT=inherit] Worksheet
    [/FONT][/COLOR][COLOR=#000088][FONT=inherit]Dim[/FONT][/COLOR][COLOR=#000000][FONT=inherit] Rng     [/FONT][/COLOR][COLOR=#000088][FONT=inherit]As[/FONT][/COLOR][COLOR=#000000][FONT=inherit] Range
    [/FONT][/COLOR][COLOR=#880000][FONT=inherit]'do evaluations to get myURL[/FONT][/COLOR][COLOR=#000000][FONT=inherit]
    myURL [/FONT][/COLOR][COLOR=#666600][FONT=inherit]=[/FONT][/COLOR][COLOR=#008800][FONT=inherit]"http://www.google.com"[/FONT][/COLOR][COLOR=#000000][FONT=inherit]
    [/FONT][/COLOR][COLOR=#880000][FONT=inherit]'Add Hyperlink[/FONT][/COLOR][COLOR=#000000][FONT=inherit]
    [/FONT][/COLOR][COLOR=#000088][FONT=inherit]Set[/FONT][/COLOR][COLOR=#000000][FONT=inherit] newLink [/FONT][/COLOR][COLOR=#666600][FONT=inherit]=[/FONT][/COLOR][COLOR=#000088][FONT=inherit]New[/FONT][/COLOR][COLOR=#000000][FONT=inherit] clsSht
    [/FONT][/COLOR][COLOR=#000088][FONT=inherit]Set[/FONT][/COLOR][COLOR=#000000][FONT=inherit] newLink[/FONT][/COLOR][COLOR=#666600][FONT=inherit].[/FONT][/COLOR][COLOR=#000000][FONT=inherit]Rng [/FONT][/COLOR][COLOR=#666600][FONT=inherit]=[/FONT][/COLOR][COLOR=#000000][FONT=inherit] Application[/FONT][/COLOR][COLOR=#666600][FONT=inherit].[/FONT][/COLOR][COLOR=#000000][FONT=inherit]Caller
    [/FONT][/COLOR][COLOR=#000088][FONT=inherit]Set[/FONT][/COLOR][COLOR=#000000][FONT=inherit] newLink[/FONT][/COLOR][COLOR=#666600][FONT=inherit].[/FONT][/COLOR][COLOR=#000000][FONT=inherit]Sht [/FONT][/COLOR][COLOR=#666600][FONT=inherit]=[/FONT][/COLOR][COLOR=#000000][FONT=inherit] Application[/FONT][/COLOR][COLOR=#666600][FONT=inherit].[/FONT][/COLOR][COLOR=#000000][FONT=inherit]Caller[/FONT][/COLOR][COLOR=#666600][FONT=inherit].[/FONT][/COLOR][COLOR=#000000][FONT=inherit]Parent
    newLink[/FONT][/COLOR][COLOR=#666600][FONT=inherit].[/FONT][/COLOR][COLOR=#000000][FONT=inherit]myURL [/FONT][/COLOR][COLOR=#666600][FONT=inherit]=[/FONT][/COLOR][COLOR=#000000][FONT=inherit] myURL
    [/FONT][/COLOR][COLOR=#880000][FONT=inherit]'Show Display text[/FONT][/COLOR][COLOR=#000000][FONT=inherit]
    getLink [/FONT][/COLOR][COLOR=#666600][FONT=inherit]=[/FONT][/COLOR][COLOR=#008800][FONT=inherit]"Click Me"[/FONT][/COLOR][COLOR=#000000][FONT=inherit]
[/FONT][/COLOR][COLOR=#000088][FONT=inherit]End[/FONT][/COLOR][COLOR=#000088][FONT=inherit]Function[/FONT][/COLOR]
[COLOR=#333333][FONT='inherit']3. Try it[/FONT][/COLOR]
Maybe it is really impossible?
 
Upvote 0
I found some solution, but not works for me:
Code: 
The solution is much more complicated because worksheet and range object can not be access through UDF.Actually, the hyperlink is created by calculate event with following solution.
You can try following steps
Code: 
1. Insert a class module and name it "clsSht" and add following code:
Code: 
PublicWithEvents Sht As Worksheet
Public Rng As Range
Public myURL AsString

PrivateSub Sht_Calculate()
    OnErrorResumeNext
    Sht.Hyperlinks.Add Rng, myURL
EndSub
Code: 
2. Insert a module and add following code:
Code: 
Public newLink As clsSht
Function getLink(TS AsString)
    Dim myURL   AsString
    Dim Sht     As Worksheet
    Dim Rng     As Range
    'do evaluations to get myURL
    myURL ="http://www.google.com"
    'Add Hyperlink
    Set newLink =New clsSht
    Set newLink.Rng = Application.Caller
    Set newLink.Sht = Application.Caller.Parent
    newLink.myURL = myURL
    'Show Display text
    getLink ="Click Me"
EndFunction
Code: 
3. Try it
Maybe it is really impossible?
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
VBA to list all links (external links AND hyperlinks) together with the cells containing the links
Replies
2
Views
122
accountant606
A
A
  • Solved
Empty Folder Search...Need to Add Deeper Folder Levels to VBA Coding
Replies
4
Views
278
Micron
M
M
  • Question
Adding a link in email macro using a cell value
Replies
3
Views
168
KOKOSEK
KOKOSEK
C
  • Solved
VBA code to create a word document, link it to a cell and save the document using the cell value
Replies
3
Views
416
cmannp
C
A
  • Solved
3 way use button to run macro
Replies
2
Views
185
Aleytha
A

Forum statistics

Threads
1,216,810
Messages
6,132,829
Members
449,761
Latest member
AUSSW

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top