All functions and sub procedures are by default public scope. If you want to
keep something from being called outside of a given module or sheet you
should declare it private. Good coding practice is to declare everything
private, unless it has to be public. The format should be:

Private MyFunctions(byval rngMyRange as range) as Variant

end function

With functions you should also declare what it is returning. In this case a
variant. By default functions return variants unless otherwise specified.

Without being able to see your other code I can not tell you why this would
be called unexpectedly. Try making the switch to private (if this function is
ont intended to be called outside of this sheet or modue) and let me know how
it goes.

HTH...

"Ron" wrote:

> Hi Guys,
>
> I have a custom function that finds a hyperlink/s in a cell.
> It wasn't written by me but sourced on the web somewhere, unknown
> origin.
>
> Here is the code...
>
> Function TextLink(cellT As Range, Optional defVal As Variant)
>
> If (cellT.Range("A1").Hyperlinks.Count <> 1) Then
> TextLink = defVal
> Else
> TextLink = cellT.Range("A1").Hyperlinks(1).Address
> End If
> End Function
>
>
> This works fine, however what it does do is seems to get called by
> totally unrelated code in other worksheets in the same workbook. I
> don't know why, and I would like to know why.
>
> I have rewrote the code with this...
>
> Function TextLink(cellT As Range, Optional defVal As Variant)
>
> If (cellT.Offset(0, 0).Hyperlinks.Count <> 1) Then
> TextLink = defVal
> Else
> TextLink = cellT.Offset(0, 0).Hyperlinks(1).Address
> End If
> End Function
>
> This also works but doesn't (after testing) seem to get called by other
> seperate code in other worksheets in the same workbook.
>
> Now in the first code the cellT range is A2 and the function is sat in
> B2. I'm extracting the first 55 characters of the hyperlink. I'm using
> this..
>
> =LEFT(TextLink(A2,""),55)
>
> Works ok. Returns an empty string if there's no hyperlink.
>
> The problem as I see it is the .Range("A1") part of the original
> function. Is it open to be called by any sheet at any time if A1 is
> selected?
>
> It isn't a problem while the code runs at normal speed, but when I'm
> stepping through to debug I'm getting this function being called and
> insisting on looping through itself possibly hundreds of times before I
> get sick and have to end the debug.
>
> I'm quite happy at the rate I'm learning VBA, but I'm getting stuck on
> functions and their behaviour more and more often.
>
> Can anyone shed any light on this please?
>
> Thanks guys,
>
> Ron
>