Function behaviour, can someone explain please?

[Ron]

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

 

[Jim Thomlinson]

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]

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...

Hi Jim,

Thanks for that.

I tried going 'private' with the function, which was in it's own module
with nothing else.

I then tried to run this code from a different module in the same
workbook...

Sub CopyCols()
Range("f2:h21").Select
Selection.Copy
Sheets("WEB QUERY").Select
Range("k2").Select
Selection.PasteSpecial paste:=xlPasteValuesAndNumberFormats
End Sub

The function was called at the Selection.PasteSpecial line.

Again the function looped seemingly infinitely, so I put in a counter in
the function and added the two extra lines to the above code...

Sub CopyCols()
testcount = 0
Range("f2:h21").Select
Selection.Copy
Sheets("WEB QUERY").Select
Range("k2").Select
Selection.PasteSpecial paste:=xlPasteValuesAndNumberFormats
MsgBox "there were " & testcount & " loops this time"
End Sub

On running this code again and again I got 467 loops then 263 loops and
then curiously got zero loops time after time.

I'm trying to reproduce the loops to see if I can pin down what triggers
the calling but am unable to at the minute.

I'll post on here if I come up with any sort of answer.

Is it possible to put the function code in the worksheet module to where it
pertains, so that it doesn't stray outside? Maybe on a Worksheet_Change
event?

Ron