Can I make Excel beep when a calculation is finished?

I have these really long calculations in Excel. I'd like to divert my attention from the screen and get something done at my desk, but be alerted for when the calculation is finished.

Any ideas?

Solution 1:

OK, something to try (instructions assume Excel 2007 or newer):

• Open your workbook.
• Save it as a macro-enabled SS (.XLSM).
• Hit Alt-F11 to open the VBA coding window.
• Right-click VBAProject (yourbook.xlsm), and pick Insert → Module.
• Open the newly created "Module1".

• In the module paste this snippet:

  Sub Auto_Open()
      Application.OnKey "{F9}", "F9Handler"
  End Sub
  

  This will cause the F9 key to be reassigned each time the SS is opened. When you hit F9 it will run F9Handler instead of the usual calculate.

• In the module, paste this snippet:

  Sub F9Handler()
      Application.Calculate
      Beep
  End Sub
  

  This is what will now be run when you hit F9. The first line runs the Application.Calculate (which is what F9 usually performs), and then Beeps.

• Save up everything.

• Close Excel.
• (Re)Open the XLSM you made.
• Profit. :)

Note: You'll probably have to enable macros and/or make the document a "trusted" document to ensure the macro(s) run as expected.

More info:

• Auto_Open
• Application.Calculate Method (Excel)
• Beep Function

Solution 2:

Just as the others mentioned, use the beep command in Visual Basic (VB):

Function beepNow()
Beep 
End Function

To get VB coding, you need to get the Developer tab available (see: Getting Developer tab visible)

So when you have a change in a cell or condition, it will beep. Example macro to call the VBS code above: =IF(C2<>C3,beepNow(),"")

Solution 3:

Alternatively to placing the module in the ss, you could put it in your Personal.xlsb. This would enable you to have the "beep" available to any ss. To run it, either modify the Quick Access Toolbar or insert a button/shape to click in your ss and assign the macro to it.