Calling UDFs by Hyperlink


So you are creating a dashboard and you are in a deep thought , thinking what should I add to make it more interactive
You have added scroll bar, dropdowns to refresh your charts and all what you can do with the excel, but may time you
feel like there should be a mouse hover event on worksheets ,so that you can make it smoother ,more interactive and more
pleasant for your eyes
Here I am sharing a trick people can use this smartly
There are two way we can call a VBA Function From Worksheets .
1) . Direct Call like excel native function (writing your function follow by “=” on worksheets)
    =MyFucntion()
2). Calling a function with in Excel Native Function Hyperlink()
So here is all about calling a UDF by Excel Hyperlink() function. Here we go.
First of all create a function in VBA
1) . Go to to VBE : Press ALT+F11
2) Insert a Module : Press Alt + I + M
3) Write a Function there like this :
    Function ShowAddress(rngRange As Range)
        MsgBox rngRange.Address
    End Function
then switch to Excel and write a formula like :
    =HYPERLINK(ShowAddress(C28),””)
here I am passing a range to UDF to see the cell address in a massage box
when we do things like this we need to pass the cell address to UDF in which cell
Formula is being applied , like if I am putting formula in A1 then I will pass A1 in
UDF with in hyperlink
when you will apply this Function in Range A1 and fill down or fill right in some cells. Then
Move your mouse on that cells ., you will see the cell address in a massage box
Thank You for reading
Rajan verma

One Comment Add yours

  1. dwplum says:

    When would this ever be used?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s