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?
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?
OK, something to try (instructions assume Excel 2007 or newer):
.XLSM
).yourbook.xlsm
), and pick Insert → Module.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.
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:
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(),"")
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.
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