UDF: Formula()

Post date: May 14, 2013 6:59:04 PM

Create an incredibly simple, yet useful UDF (User Defined Function).

Hyper link extraction

Below is a post from a forum I frequent:

"I need help with a task. I have a column in excel, that has hyperlinks and under the hyperlink it has an e-mail address that I need to pull...in to a new column....any help would be appresiated. I try Mid, left, right formula...but no luck."

I'm assuming the poster wants to pull the e-mail address from an XL formula like this:

=HYPERLINK("Craig_Hatmaker@MyMail.com", "Craig's Fictitous email")

It seemed obvious that all the poster needed was a little string manipulation of the formula. Sounds simple. But wait - what is the XL function to display a cell's formula? I couldn't think of anything and the function wizard provided no help. After Googling for answers I concluded no such function exists in XL. But a solution could be quickly made from VBA. Here is an example:

http://en.kioskea.net/forum/affich-85224-excel-formula-display-in-cell

The following is based on their solution. I modified their solution slightly for personal preference.

NOTE! I have no error checking here. I recommend adding error handling to all routines. For more on error handling see: https://www.dropbox.com/s/98ipp2lcvr2u8vo/Error_Handling.pdf?dl=0

Public Function Formula(oCell As Range) As String

Formula = oCell.Formula

End Function

Once the above code is added to a standard VBA module, XL gains a new UDF. Here is an example use:

=Formula(A4)

Now all that does is display cell A4's formula. To complete the poster's request, we need to include our UDF in a formula to extract the link from something like:

=SUBSTITUTE(MID(Formula(A4),12, FIND(",",Formula(A4))-12),"""","")

For more on extending XL by creating UDFs see:

https://www.dropbox.com/s/u9dcgursm9wof9p/User%20Defined%20Function%20-%20Eval.pdf?dl=0