Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
    
How exactly are you doing the calculation? If it's in VBA, why not use VBA's "Beep" method? What have you tried already? –  Ƭᴇcʜιᴇ007 May 8 at 18:24
2  
@techie007 not VBA. Just functions in large Excel spreadsheets. –  Eliyahu May 8 at 18:24
1  
Still you can probably user the Beep function, especially if you SS is actually using "functions" to calculate instead of "formulas". But "how" you can use it depends greatly on how your spreadsheet works, which we don't know. Like are you just typing a value into a cell and then having to wait a really long time for it to finish, or do you hit a button to start the calculation after filling cells? IF you hit F11 to open the VBA editor, is there a bunch of code in there? –  Ƭᴇcʜιᴇ007 May 8 at 18:29
    
@techie007 I'm using formulas. I'll press F9 after a bunch of work and have to wait. –  Eliyahu May 8 at 18:30
    
You would have do your calculations another way. What you want isn't possible if all you are doing is telling Excel to perform the calculations on the open workbook –  Ramhound May 8 at 18:55

2 Answers 2

up vote 19 down vote accepted

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:

share|improve this answer

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(),"")

share|improve this answer
1  
Just be aware that if you're not currently using any VBA UDFs, introducing them can lead to a massive performance degradation (as, unlike normal functions, they run only on the main thread). Shouldn't be an issue with a single beep, but if you start sprinkling this everywhere you'll have issues. –  sapi May 8 at 20:41
    
Unfortunately this function uses your PC speaker -- not your sound card and speakers, but the internal speaker. FYI - In many companies the PC speaker has been removed or disabled so you will not hear anything. –  daylight May 9 at 14:57

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.