CODEWITHSUNDEEP
vbaexcel
DrPepperholik
DrPepperholik

Reputation: 43

VBA sub for Worksheet_Calculate is not being triggered. What triggers this event?

I have a workbook and on one sheet it has the sub Worksheet_Calculate which does some formatting on the cells for the numbers, just sets the format for number of decimals to show depending on the value of that cell and another cell.

I have tried to debug the problem and turned on EnableEvents, opened my Workbook and it does its thing so far. I enter data and I expect the sub to be triggered and to hit my break point but I don't. I even click the "Calculate Now" and "Calculate Sheet" buttons, nothing. So what is supposed to fire off the Worksheet_Calculate sub? I need the workbook to do the formatting that is done in this sub, I don't really care where it does it so long as it does it. I even tried Application.EnableEvents = False at the beginning of the sub and Application.EnableEvents = True at the end of the sub, to no avail.

I don't know much about VBA or this Workbook as it's not something I created, I would have made a C# web application instead of the Excel spreadsheet but I digress.

Edit 1:

Here are the formulas/values for the cells in question to clarify things a bit. Range Q11:Q35 are all formatted to be number types. Their formula is as follows.

=IF(OR(O11="",P11=""),"",(O11/P11)*100)

The cells referenced above have the following formulas for O and P respectively.

=IF(OR(H11=""),"",INDEX(H11:L11,MATCH(9.99999999999999E+307,H11:L11))-M11)

=IF(AND(M11="",N11=""),"",N11-M11)

Upvotes: 1

Views: 3575

Answers (1)

steenbergh
steenbergh

Reputation: 1771

I've tried to use the MSDN Example found here: https://msdn.microsoft.com/en-us/library/office/ff838823.aspx

The example is nothing more than:

Private Sub Worksheet_Calculate()
    Columns("A:F").AutoFit
End Sub

It didn't trigger when I was just typing text on the sheet, but it did trigger when I input a formula: =1+1.

Make sure you're actually inputting something that Excel needs to calculate.

EDIT: As @gizlmeier mentioned in the comments, use Worksheet_Change if you want to execute code after something has been changed.

EDIT 2: To elaborate on when the Calculate is triggered:

  • When a formula is input that requires direct calculation
  • When a cell is modified that is referenced by a formula.

So, inputting this formula in column G:G does not trigger Calculate: =LEFT(Ax, 3). If we now input text or another static value into range A:A, Calculate is triggered because col G:G needs to be reworked.

Upvotes: 0

Related Questions