Break points will be ignored in Excel

[Winfried Kastner]

Hello,

I have one big problem because I cannot use the break point
functionality in Excel for whatever reasons

I have a big Excel workbook (size about 18 MB, around 30 worksheets,
around used 150.000 formulas), my PC has 4 GB memory and I use Windows
XP SP3 and Excel 2003 SP3 with all patches.

In around 17 worksheets I have to check changed cell values against
some plausibility rules. In those 17 worksheets I use the
"Worksheet_Change" event that looks as follows:

Private Sub Worksheet_Change(ByVal Target As Range)

' Call of the sub for further centralized processing of the
"Worksheet_Change" event.
Call Check_Worksheet_Change(ActiveSheet.Name, Target)

End Sub


To avoid duplicate code I call a central sub "Check_Worksheet_Change"
with the active worksheet name and the changed and to be checked cell
as parameters for further processing. The parameter "Target" is always
one single cell.


This central sub "Check_Worksheet_Change" looks as follows:

Sub Check_Worksheet_Change(ByVal wksName As String, ByVal Target As
Range)

Call InitVariables

' here follows several code to execute the checks etc.

end sub


In order to check my code I want to set a break point to the row "Call
InitVariables". What happens is that that breakpoint will be ignored
completely. If you insert a "MsgBox" command or a "Debug.Print Time"
before or after the "Call InitVariables" you see that all will be
executed and displayed but the set break point will be ignored
completely. This of course is very bad when you want respectively have
to debug your program.

This always happens when I already set another break point in the
"Worksheet_Change" event.

In my Excel worksheet this mailfunction of the use of a break point
only happens only for one specific column but this is the most
important one. The cells in this column have all a conditional
formatting that looks as follows: "Condition1 is "Formula is"
"=HasCellAFormula('cell')" with 'cell' contents like R6 or R18. This
is an own written tiny function that returns whether True or False
whether this cell contains a formula or not. The VBA code for this
function is "HasCellAFormula = cell.HasFormula" with cell as parameter
that shall be checked. But that should not be the reason.

When I do a change in those worksheets in cells of other columns
beside column "R" all works as it should, i.e. the code stops at
defined break point.


Now my main question: Has somebody any idea why Excel ignores this
break point? Are there any conditions or known errors when a break
point will be ignored? I googled the web but I found no hit. If you
have any idea please let me know.

Thank you in advance.
Winfried

 

[Gord Dibben]

First of all, calling a sub from a worksheet_change event in order to
avoid duplicate code in each sheet is not necessary.

You can put the event code once in Thisworkbook module..............

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)

'event code goes here and will run in whichever sheet is active

End Sub

The breakpoint part I don't understand.............I have never tried
to figure those out<g>


Gord