13

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?

  • 1
    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 '14 at 18:24
  • 2
    @techie007 not VBA. Just functions in large Excel spreadsheets. – Eliyahu May 8 '14 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 '14 at 18:29
  • @techie007 I'm using formulas. I'll press F9 after a bunch of work and have to wait. – Eliyahu May 8 '14 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 '14 at 18:55
21

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:

5

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

  • 2
    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 '14 at 20:41
  • 1
    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. – raddevus May 9 '14 at 14:57
1

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.

  • Welcome to Super User. This appears to be a comment on the other answers rather than a substantially different solution, which is what answer posts are intended for. With a little more rep, you will be able to post comments. – fixer1234 Mar 9 '17 at 21:18
0

The answer posted by @Ƭᴇcʜιᴇ007 is an excellent one with one exception that others have commented on: the Beep method relies on your onboard (and sometimes disabled) speaker within the computer and doesn't use your speakers, so you may hear nothing.

I'm posting a workaround based on that answer. If there were less content involved I would have posted a comment instead.

Here's what I use to play a sound with VBA on Windows. It uses your normal computer speakers instead of the onboard and often disabled speaker accessed with the Beep method.

Start with the answer by @Ƭᴇcʜιᴇ007. Just change Beep to this:

PlaySound sound_Up

You can change the sound by changing sound_Up to another item from the SoundType enumeration (list) in the code below such as sound_Complete or sound_Notify.

Option Explicit
' --------------------------------------------------
' --------------------------------------------------
'   REQUIRED DLL REFERENCE AND CONSTANTS
' --------------------------------------------------
' Reference to external procedure in DLL to play sounds

#If VBA7 Then
    ' 64-bit MS Office
    Private Declare PtrSafe Function sndPlaySound32bit Lib "winmm.dll" Alias "sndPlaySoundA" (ByVal lpszSoundName As String, ByVal uFlags As LongPtr) As LongPtr
#Else
    ' 32-bit MS Office
    Private Declare Function sndPlaySound32bit Lib "winmm.dll" Alias "sndPlaySoundA" (ByVal lpszSoundName As String, ByVal uFlags As LongPtr) As LongPtr
#End If

' --------------------------------------------------
' --------------------------------------------------
'   CONSTANTS
' --------------------------------------------------
'
Public Const SOUND_DO_NOT_WAIT          As String = "&H1"
Private Const SOUND_FILES_PATH          As String = "C:\Windows\Media\"

' --------------------------------------------------
' --------------------------------------------------
'   PUBLIC ENUMERATIONS
' --------------------------------------------------
'
Public Enum SoundType
    sound_None = 0
    sound_Up = 1
    sound_Down = 2
    sound_Attention = 3
    sound_Blocked = 4
    sound_Boop = 5
    sound_Complete = 6
    sound_Critical = 7
    sound_Ding = 8
    sound_Error = 9
    sound_Exclamation = 10
    sound_FastDown = 11
    sound_Information = 12
    sound_Notify = 13
    sound_PhoneRing = 14
    sound_RingOut = 15
    sound_Stop = 16
    sound_Tada = 17
    sound_Tone = 18
End Enum

' --------------------------------------------------
' --------------------------------------------------
'   PUBLIC METHODS
' --------------------------------------------------
'
Public Sub PlaySound(ByVal sound As SoundType)
' Plays a sound from a list (see the 'SoundType' Enum).  Code execution continues
'   while sound is playing.
    Dim soundFileName                   As String
    soundFileName = GetSoundFileName(sound)

    If Not soundFileName = vbNullString Then
        sndPlaySound32bit SOUND_FILES_PATH & soundFileName, SOUND_DO_NOT_WAIT
    End If
End Sub

' --------------------------------------------------
' --------------------------------------------------
'   PRIVATE DEPENDENCIES
' --------------------------------------------------
'
Private Function GetSoundFileName(ByVal soundToGet As SoundType) As String
' Returns a windows sound file name (not path) from a member of the custom
'   'SoundType' enumeration.

    Dim fName                           As String

    Select Case soundToGet
    Case sound_Up:          fName = "Speech On.wav"
    Case sound_Down:        fName = "Speech Off.wav"
    Case sound_Attention:   fName = "Windows Battery Low.wav"
    Case sound_Blocked:     fName = "Windows Pop-up Blocked.wav"
    Case sound_Boop:        fName = "Speech Misrecognition.wav"
    Case sound_Complete:    fName = "Windows Print complete.wav"
    Case sound_Critical:    fName = "Windows Critical Stop.wav"
    Case sound_Ding:        fName = "Windows Ding.wav"
    Case sound_Error:       fName = "Windows Error.wav"
    Case sound_Exclamation: fName = "Windows Exclamation.wav"
    Case sound_FastDown:    fName = "Speech Sleep.wav"
    Case sound_Information: fName = "Windows Information Bar.wav"
    Case sound_Notify:      fName = "notify.wav"
    Case sound_PhoneRing:   fName = "Windows Ringin.wav"
    Case sound_RingOut:     fName = "Windows Ringout.wav"
    Case sound_Stop:        fName = "chord.wav"
    Case sound_Tada:        fName = "tada.wav"
    Case sound_Tone:        fName = "Windows Balloon.wav"
    Case Else:              fName = vbNullString
    End Select

    GetSoundFileName = fName
End Function

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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