PDA

View Full Version : How Sheet formulae is called



frubeng
05-22-2009, 04:10 AM
Hi, I have a basic question about how the VB code is called in general.

I have in my Sheet1 tab:

Private Sub worksheet_change(ByVal Target As Range)
Application.EnableEvents = False
Call main
Application.EnableEvents = True
End Sub


and in my Module1 tab:
Sub main()
ActiveWorkbook.ActiveSheet.Cells(2, 7) = 0
sumtotal = 0
For i = 1 To Month(Now)
row2 = row2 + Row
Sum = 0
Row = Application.WorksheetFunction.Match("Total", Range(Cells(row2 + 1, 2), Cells(row2 + 200, 2)), 0)
If Not IsEmpty(ActiveWorkbook.ActiveSheet.Cells(Row + row2 - 1, 1)) Then
ActiveWorkbook.ActiveSheet.Cells(Row + row2, 1).EntireRow.Insert
Row = Row + 1
End If
'Summing all the comms for the monthly total
For j = 1 To Row - 3
Sum = Sum + ActiveWorkbook.ActiveSheet.Cells(row2 + j + 1, 4)
Next j
ActiveWorkbook.ActiveSheet.Cells(Row + row2, 4) = Sum
sumtotal = sumtotal + Sum
Next i
ActiveWorkbook.ActiveSheet.Cells(2, 7) = sumtotal
End Sub


When i originally wrote the code, it worked great, calling main as soon as a change was made byt the user. Then i did some more stuff on the workbook and it stopped calling main automatically (it would only work by going into the VB editor and pressing F5). What's happening here?

Also, on another workbook, I just have the regular

Private Sub Worksheet_calculate()
Call main
End Sub

And i have the same issue sometimes, that it would sometimes call it automatically and sometimes would have to be triggered.
Also, if it does run automatically, does it run every few seconds? (if so can the time interval be set??), or does it just runs as soon as memory is available?

I guess my questions are pretty basic, but i would appreciate some light!

Thank you!!

Bob Phillips
05-22-2009, 05:24 AM
Sounds to me that you have hit an error when events are turned off and so they didn't get turned on again. You need to reset events and add some error handling to your code.

mdmackillop
05-22-2009, 05:26 AM
It is possible that your code has errored, and you have exited the code without resetting events
Run this macro to reset events

Sub Enables()
Application.EnableEvents = True
End Sub

frubeng
05-22-2009, 05:32 AM
Great, that defintiely solves the problem!

I was also wondering if you had insight on this part:

Also, on another workbook, I just have the regular



Private Sub Worksheet_calculate()
Call main
End Sub


(http://www.thecodenet.com/)

And i have the same issue sometimes, that it would sometimes call it automatically and sometimes would have to be triggered.
Also, if it does run automatically, does it run every few seconds? (if so can the time interval be set??), or does it just runs as soon as memory is available?


Thank you so much for the help

mdmackillop
05-22-2009, 05:36 AM
Suggestions:
Always use Option Explicit and Dim your variables.
Use a With statement instead of repeated qualifying with ActiveWorkbook.ActiveSheet
Ensure all references are qualified.

Bob Phillips
05-22-2009, 06:57 AM
Error handling as I said before



Private Sub worksheet_change(ByVal Target As Range)
On Error Goto ws_exit:
Application.EnableEvents = False
Call main
ws_exit:
Application.EnableEvents = True
End Sub

frubeng
05-26-2009, 06:01 AM
So it seems that even with error handling, the program only works some of the time...

I have in Sheet1:

Sub Enables()
Application.EnableEvents = True
End Sub
Private Sub worksheet_change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
Call main
ws_exit:
Application.EnableEvents = True
End Sub


and in module1:


Sub main()
ActiveWorkbook.ActiveSheet.Cells(2, 7) = 0
sumtotal = 0
For i = 1 To Month(Now)
row2 = row2 + Row
Sum = 0
Row = Application.WorksheetFunction.Match("Total", Range(Cells(row2 + 1, 2), Cells(row2 + 200, 2)), 0)
If Not IsEmpty(ActiveWorkbook.ActiveSheet.Cells(Row + row2 - 1, 1)) Then
ActiveWorkbook.ActiveSheet.Cells(Row + row2, 1).EntireRow.Insert
Row = Row + 1
End If
'Summing all the comms for the monthly total
For j = 1 To Row - 3
Sum = Sum + ActiveWorkbook.ActiveSheet.Cells(row2 + j + 1, 4)
Next j
ActiveWorkbook.ActiveSheet.Cells(Row + row2, 4) = Sum
sumtotal = sumtotal + Sum
Next i
ActiveWorkbook.ActiveSheet.Cells(2, 7) = sumtotal
End Sub

the macro is not triggered when a change is made on the sheet. What is going wrong?

Thanks.

Bob Phillips
05-26-2009, 06:43 AM
Is the event macro in the worksheet code module? You said sometimes, does it work not at all, or some of the time?

frubeng
05-26-2009, 07:00 AM
Is the event macro in the worksheet code module? You said sometimes, does it work not at all, or some of the time?

The event macro is in the sheet, the Main macro is in the module.
It worked fine when I wrote the sheet, but since i have saved and closed and then re-opened, it doesn't get launched. (if i go into the code and press play, then it runs, but ti doesnt get triggered by the event code)

Bob Phillips
05-26-2009, 09:24 AM
I am struggling to see why, can you post the workbook?