Solved: Worksheet_Calculate event

[jwise]

The code below is my Worksheet_Calculate event code. It doesn't work, but it is being entered MANY times instead of just once. Here are some details:

The worksheet in question holds ONLY date control information. There are probably about 100 cells with various forms of dates or related data. All this data is derived from a single cell (B2) which is a year (like 2007). Most of the other fields are "formula" driven from this field. Three or four columns of these derived dates/data are not able to be formula-driven, so I wrote VBA subs to calculate them. This requires three subs. These subs work as expected.

Thus the code merely calls these three subroutines. But it is being entered many times, and I get "stack overflow" from VBA. I only want the event to be entered once when B2 is updated, or even better, after all the recalculations are done when B2 is changed.

Sub Worksheet_Disable() 'Calculate

Call WeekMonth

Call PayPrdMo

Call StartEndRow

End Sub


When the code was being tested, I changed the name to stop the "stack overflow" problem. There are several worksheets in the workbook, and the three subs only need to be called when this worksheet is updated.

I am concerned about what might happen IF the event were to trigger the Worksheet_Calculate subroutine BEFORE all these date fields are updated since the subs do look at these derived fields.

TIA

[mvidas]

Hi jwise,

You could try putting "application.enableevents = false" at the beginning of it and then =true at the end, see if that prevents the unnecessary calls.

Otherwise, why can't you use the _Change event which allows you to check specific cells being changed?

[jwise]

Thanks Matt. Your suggestion worked, and I expected it to work.

I thought I could use this as a learning experience, so after I tested the enable/disable code in the _Calculate code, I tried the change event code. It seems to loop as well (the 3 subs make/alter about 70 cell changes). Each of the three subs has a MsgBox statement which identifies themselves. This was a left-over from a previous problem.

Here is the Worksheet_Change code:

Sub Worksheet_Change(ByVal B2 As Range)

Call WeekMonth

Call PayPrdMo

Call StartEndRow

End Sub


The only cell I change is B2. My guess is that I did not properly identify B2, or perhaps the code is supposed to check and exit if it is not B2. There is no indication of this in the help files, but I also admit that I did not fully understand the example cited.

I did not try to add the disable/enable events to this code which does seem logical given the problem. Your suggestion implied that this exit would not need this, or did I misunderstand?

There is also the possibility that the Worksheet_Calculate event is still being invoked. I changed its name to "Worksheet_Disable()" believing that there is no event named as such and I could restore it easily. Did I err again?

TIA

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B2" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Call WeekMonth

Call PayPrdMo

Call StartEndRow
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

[jwise]

Thank you XLD. Your code does work. I did have one problem. Did you leave out the label "ws_exit"? I added that after the "End IF" and the code worked as expected.

Now that I have two working examples, I have my immediate problem solved, but I am still in a quandry as to which of the events I SHOULD have used. Both pieces of code are very similar. I am trying to say that I hoped to learn when to use the _Calculate event and the _Change event, but I can not tell from this vantage point. They both seem equal. My intuition tells me that one of these is the correct place for the code.

I also freely admit that I do not really understand what your code is doing.

1. Why the "On Error" statement? What sort of errors are you trying to trap?
2. What does the "IF" statement compare? My intuition says that it is checking to see if a range is sent to the event code. This does not make sense. Obviously, the caller is some sort of canned routine. How would it know the particular range the event code is interested in? Is this exit called for each cell that changes? If it is being called on a "if the cell is changed" basis, then the subroutines (WeekMonth, PayPrdMo, StartEndRow) might get called BEFORE the fields they examine are updated. This would mess up the results big time.
3. What does "With Target" mean?

Thanks again for the code, and the coding lesson. I'm learning.

[Bob Phillips]

Thank you XLD. Your code does work. I did have one problem. Did you leave out the label &quot;ws_exit&quot;? I added that after the &quot;End IF&quot; and the code worked as expected. No, look again at my code, it is there exactly where you are saying you inserted it.

Now that I have two working examples, I have my immediate problem solved, but I am still in a quandry as to which of the events I SHOULD have used. Both pieces of code are very similar. I am trying to say that I hoped to learn when to use the _Calculate event and the _Change event, but I can not tell from this vantage point. They both seem equal. My intuition tells me that one of these is the correct place for the code. Change is usually the better event to use, as it is more controlled, only firing when you make the change, and the cell changed is passed to the event as a a parameter. Calculate will fire every time the sheet recalculates, and you have to apply it to all and every cell you are monitoring. But sometimes change doesn't fire, as in DDE links, so you have to use calculate.
I also freely admit that I do not really understand what your code is doing.


1. Why the &quot;On Error&quot; statement? What sort of errors are you trying to trap? Any errors, why differentiate?
2. What does the &quot;IF&quot; statement compare? My intuition says that it is checking to see if a range is sent to the event code. This does not make sense. Obviously, the caller is some sort of canned routine. How would it know the particular range the event code is interested in? Is this exit called for each cell that changes? If it is being called on a &quot;if the cell is changed&quot; basis, then the subroutines (WeekMonth, PayPrdMo, StartEndRow) might get called BEFORE the fields they examine are updated. This would mess up the results big time.
The IF test is checking to see whether the range of cells being changed (which can be just one) is within the range of cells that you want to monitor. Excel controls passing the range being changed.
3. What does &quot;With Target&quot; mean?
With ... allows you to refer to an object once. and all dot objects or properties thereafter (such as .Value) will refer to objects or properties of that object. It is more efficient and makes for easier reading in long code

[jwise]

Thanks again for answering the questions.

I think I must have made a mistake when I copied your code originally. After I looked at it, I used the standard "copy to the clipboard" method of putting your code in the _Change event. So I must have made a snafu. Sorry. I should have checked the event code against the VBAX listing. I also bet that I left out the "enable" after the label. This would explain another small anomaly I encountered. I'll check for this ASAP.

Thanks for the explanation on the choice of events. Did you learn this via the "school of hard knocks", or is this documented somewhere? I am searching for the RIGHT way to learn this stuff.

[tstom]

Just for your information. If you do need to respond to a calc, here is a method that works well...


Private Sub Worksheet_Calculate()
On Error GoTo Err_Worksheet_Calculate
Application.EnableEvents = False

'Me.Calculate
'or
'Application.Calculate
'or
'Application.CalculateFull
'or
Range("B2").Calculate

'your code will run here, once, after all
'calculations have completed...

Err_Worksheet_Calculate:
Application.EnableEvents = True
End Sub

[jwise]

Thank you for the example code. Your code does raise some new questions!

1. Apparently, the _Calculate event is raised for EVERY CELL when a worksheet is recalculated. The event code itself LIMITS its execution to only the desired situations. Said another way, "Range("B2").Calculate essentially makes the code following the statement executed only in the appropriate situation. All other situations (for example, a change in cell "B11") are ignored. Is this correct?

2. I would assume that there are other options besides "Calculate" that I could put on the "Range("B2").Calculate" statement. Is this correct, and if so, where does one get a list of these additional options?

3. If I leave out the "Application.EnableEvents = False" and the latter "True", I am assuming I could get in a loop. Is this correct?

4. I also assume that Excel must pass through ALL its recalculations and then start calling this event. Otherwise, I would not be guaranteed that all updates have been applied. This is crucial to the logic because the three subroutines modify columns of data (around 70+ cells) based on the contents of other formula-dependent cells. Is this correct?

Thanks again for your contribution to my education.

[tstom]

1. Apparently, the _Calculate event is raised for EVERY CELL when a worksheet is recalculated.

The answer is no. I know that much. But I do not really have much of a clue as to how Excel goes about calculating

The event code itself LIMITS its execution to only the desired situations.
Said another way, "Range("B2").Calculate essentially makes the code following the statement executed only in the appropriate situation. All other situations (for example, a change in cell "B11") are ignored. Is this correct?

My logical assumption is that all direct and indirect precedents and dependants related to the range being calculated are evaluated. That is just an educated guess... :)

2. I would assume that there are other options besides "Calculate" that I could put on the "Range("B2").Calculate" statement. Is this correct, and if so, where does one get a list of these additional options?

Place you cursor on the word "Range" in your VBA editor and press F1. You can see a list of the properties and methods that belong to the range object.

3. If I leave out the "Application.EnableEvents = False" and the latter "True", I am assuming I could get in a loop. Is this correct?

No. Not unless your code brings about more calculations. The event is not recursive but it may be called many, many times. The example I gave you simply allows you to force a calculation to update all of your values and then run your code.

4. I also assume that Excel must pass through ALL its recalculations and then start calling this event. Otherwise, I would not be guaranteed that all updates have been applied. This is crucial to the logic because the three subroutines modify columns of data (around 70+ cells) based on the contents of other formula-dependent cells. Is this correct?

In office 2003 this is true. That was not my experience in previous versions. So I guess my example might be redundant in Office 2003...

[jwise]

It is a little embarrassing, but none-the-less true, I did not know about the F1 key on the keyword. I'll give that a whirl.

I understand why some people like their language-intelligent editor, but give me SPF any day. Perhaps that is only because I used it 25+ years on the mainframe. Many times I copy my code and use notepad... that's how much VBE annoys me. I'm also a fanatic about column alignment and comment alignment, and VBE makes that difficult.

Thanks for the explanations. I've read them all but not much sunk in my thick skull on the first pass. I will read your answers again until I understand.

Thanks again for the explanations and code sample.

[johnske]

... I'm also a fanatic about column alignment and comment alignment, and VBE makes that difficult...if you're referring to code in the VBE window there, download and try Smart Indenter (http://www.bmsltd.ie/indenter/IndentPage.asp), it's very configurable and should suit almost anyone :)

[jwise]

I appreciate your suggestion. I have downloaded the code, and will install it later today.

[Bob Phillips]

jwise

Smart Indenter is a good product, but totally unnecessary IMO. For one thing, it is after the evnt, whereas you really need the indentation as you are coding to help the logic flow.

If you indent as you go, using tab, it is very easy to get into the habit of good code style/formatting.

You can even select a block of code and use Tab to indent every row, or Shift-Tab to unindent every row.

I have the same obsession with lining up as you, but I do it all in VBA, very easily.

[jwise]

I really appreciate your suggestion. Thanks for sharing your experience.

Your code snippets, and often complete routines, certainly do demonstrate organized and uncluttered code. IMO, code is easier to write than to read, especially when it is someone else's, or if it has been several years since I last saw it. So I take great care that my code is "easy to follow". I would not say that my VBA code has reached that point, especially in light of the several tricky loops that I have found necessary to solve some problems. This will be a maintenance nightmare to me in the future. Hopefully, I'll be able to improve this logic before too long, and rewrite some of it.

I have a natural distaste for the tab key. My mainframe prejudice predisposes me to like fixed length records, where you can space things as you wish and ANYBODY's editor will display them properly. The OOP world complicates this immensely with its "A.B.C" mentality, and the corresponding lengthy names for the elements. The "bad part" of short labels or parameters is that they are cryptic in nature, and some people abuse this (label01, label02, etc.). I usually included a "dictionary" as comments in my code, and I listed variable names and labels, and tried to explain their origin and meaning. The ultimate goal was that the reader would be able to make a better guess at the true meaning of the cryptic label or variable name, or look for it in the dictionary.

Perhaps the tab key is the answer. I take all code snippets (and routines) I get from VBAX and put them in notepad. I save them in a file and print them. When I'm coding, I look through those pages to get ideas, suggestions, or often answers. This has helped me tremendously. I will have to experiment with the tab key and my "notepad" code because I want all my code available outside of VBA. It is so much easier to back-up, transport, and manipulate outside of VBA.

Thanks again for your suggestion. It'll take a while for me to evaluate this idea.

[mvidas]

I have a different indenting style than most people here (at least much different than the vba code tags give), so it always take a little time to get it to look the way i want it. Writing code though is nice, I just set the Tab Width to 1 in the VBE and check the Auto Indent checkbox. As for copied code from here, tab/shifttab on blocks of lines helps to get it to look a little better, as does replacing 4 spaces with 1 space, then a couple minor changes (select case blocks, etc) and it looks ok. I never had much luck with smart indenter, never really gave it a chance though (i also like notepad/notepad2, though the VBE is often preferred for me because of the Auto List Members option and the auto capitalization

[johnske]

Surely there's enough options here to cater for everyone?

[Bob Phillips]

Where is the option that says do it as you enter the code?