Macro Unexpectedly calls a Function

[Steve]

I can't figure this problem out and am getting quite frustrated and hope that
someone can HELP, PLEASE!!

My environment is: Windows XP Version 2002 Service Pack 2 and MS Office
Excel 2003 SP2. My workbook has three worksheets with many hyperlinks in
each worksheet.

I developed a user defined function (UDF) that exists in only the last
worksheet. The purpose of the UDF counts the cells within the last worksheet
based on the fill color. I wrote another program (ListHyplinks) to list the
hyperlinks within each worksheet. It cycles through each worksheet and lists
the hyperlinks in a “Hyperlinks†worksheet.

Here is the UDF function line, which is stored in a module named: ContColr.

Function CntClr(InRange As Range, ColorIndex As Long, Optional OfText As
Boolean = False) As Long

Here is part of the Hyperlink list program, which is stored in a module
named: Module2

Sub ListHyplinks()
Option Explicit
(((( There are various declaration statements that go here ))))
totwksnbr = Worksheets.Count
wrbkname = ActiveWorkbook.Name
Firstwks = True
icnt = 1

‘ Cycle through each worksheet
For wksnbr = 1 To totwksnbr
curwksname = Worksheet(wksnbr).Name
Answer = Msgbox(curwksname & “ – is the current worksheet.†_
& vbCr & vbCr & “Do you want to list the links?â€, vbYesNo, “List
Hyperlinksâ€)

If Answer = vbYes Then
If(Firstwks) Then
Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = “Hyperlinksâ€
Firstwks = False
Call WriteHeading
For Each h In ActiveSheet.Hyperlinks
Cells(icnt, 1) = h.address
icnt = icnt + 1
Next
(((( Additional statements go here ))))

When I run the ListHyplinks program control jumps to Function CntClr after
creating the "Hyperlinks" worksheet in the "If (Firstwks)" statement. It
cycles through that function for a bit and then returns. Then upon executing
the "Cells(icnt, 1) = h.address" statement control once jumps to the
Function CntClr again, cycles through and returns.

To investigate this phenomenon I performed the following debug process:

Scenario 1: Set debug breakpoint @ "If Answer = vbYes"
Ran the ListHyplinks program
Step to the "If (Firstwks) statement
"Hyperlinks" worksheet is created
Unexpectedly control then jumps to Function CntClr
I stop the program

Scenario 2: After completing Scenario 1
"Hyperlinks" worksheet still exists and is cleared out
I comment out the "If (Firstwks)" statement
Re-ran the ListHyplinks program using the same debug
breakpoint
Step through the program
Once it reaches "Cells(icnt, 1) = h.address" control jumps
to Function CntClr
I stop the program

Scenario 3: After completing Scenario 2
"Hyperlinks" worksheet still exists and is cleared out
Re-ran the ListHyplinks program using the same debug
breakpoint
The program runs as intended without control jumping to
Function CntClr????

Any assistance in resolving this issue would be greatly appreciated. Thank
you in advance for your time and efforts.

Steve

 

[Nigel]

Not so easy to debug remotely but if the function is embedded in your
worksheet cell(s), try turning off automatic calculation before running you
List Hyperlinks process. The act of creating a new sheet and adding data
will result in worksheet recalculations that will fire any embedded UDFs

Maybe the answer?


--

Regards,
Nigel
(e-mail address removed)

 

[sharon]

Steve,

It is a curious thing, but I posted a similar case than yours January, 8th.

I bed you have't fixed the problem yet... Application.EnableEvents has
nothing to do with this....(I admit it was my first point to check)

In fact trying to debug (in my case) has no sense since the flow is
'managed' by something apparently NOT LOGICAL. It goes to the UDF but instead
of doing all steps it cycles to just a few of them (several times) and return
to original position without reaching End Function statment.

I've been waiting an answer but I'm afraid this Forum is not what it use to
be... sadly for all.

I found a workaround which consists in replacing the formulas where UDF is
called with another name, say CntClr changes to KntClr, and when the UDF is
really needed giving back the real name in the formulas where it was replaced.

But I still wait for an answer, since I noticed this issue in many Excel VBA
projects.

HTH,

Sharon

 

[Steve]

Sharon,

You are right in that Application.EnableEvents did/does not correct the
problem, BUT turning off automatic calculation DID just the trick. Thank you
Nigel !!!!

At the beginning of my subroutine I turned off the automatic calculation:
Application.Calculation = xlCalculationManual

And just before exiting I turned it back on.
Application.Calculation = xlCalculationAutomatic

The program is now very fast.

By the way, I also ran into another condition that was/is equally
frustrating. The Function name cannot be the same name as the Module name.
When they are, the cells that contain the function get confused and report
NAME? error. But, when the Function and Module name are different there is
no ambiguity issues. However, this condition is not true for regular
Subroutine/Module names, don't know why, but that is what I've found to be
true. HTH ...

Steve

The module name of the

 

[sharon]

Hi Steve,

It works fine after setting calculation to Manual. I must admit I should
think of that possibility first of all, but my head was overheatted...

Thanks a lot.

BTW,
Should you rate Nigel's post?

Sharon,