The Beep

Eloise T · Jul 22, 2017

I want to add "Beep" at the end of a macro so that I hear 3 distinct beeps so I know it's done.

If I put:

Beep
Beep
Beep

I only hear 1 Beep because the 3 Beeps happen so quickly. What can I add to the code to slow down the beeping so I hear 3 distinct beeps?

shrivallabha · Jul 22, 2017

Put some cooling time...

Code:

Beep
Application.Wait Now + TimeValue("0:00:01")
Beep
Application.Wait Now + TimeValue("0:00:01")
Beep

Eloise T · Jul 22, 2017

Thank you!

dan_l · Jul 23, 2017

ahhh trying to drive your users insane.

Eloise T · Jul 23, 2017

dan_l said:
ahhh trying to drive your users insane.

I am the user. The calculating takes so long I am able to leave my desk and do something else in the room until I hear its melodious BEEPs calling me back to the grind stone.

dan_l · Jul 24, 2017

Ah. I use an email for that. Just at the end of a long routine I throw on a quick email that says "your stuff is done". It also works for any constituent users you may have who insist being notified of it too.

Eloise T · Jul 24, 2017

dan_l said:
Ah. I use an email for that. Just at the end of a long routine I throw on a quick email that says "your stuff is done". It also works for any constituent users you may have who insist being notified of it too.

Please reply with the code that does that. It's probably a bit more than I need at the moment, but it may come in handy in the future.

Monty · Jul 24, 2017

You can also use....

Speak....Which any text assigned to it.

Monty · Jul 24, 2017

Code:

Sub Test()

Application.Speech.Speak ("Eloise You job is completed,..Please Review")

End Sub

Eloise T · Jul 24, 2017

Monty said:

I tried it out. Microsoft needs to improve the voice.

Monty · Jul 24, 2017

Ya....But the only way to make Excel to speak.

dan_l · Jul 24, 2017

Eloise T said:
Please reply with the code that does that. It's probably a bit more than I need at the moment, but it may come in handy in the future.

Code:

Sub EmailNote()
   
  Dim sDistroList As String
   
   
  Set OutApp = CreateObject("Outlook.Application")
  Set OutMail = OutApp.CreateItem(0)
   
  sDistroList = whoever@whereever.com
   
  With OutMail
  .SentOnBehalfOfName = ""
  .To = sDistroList
  .bcc = ""
  .cc = ""
  .Subject = "Project Updated"
   
  .body = "Project UPdated"


  .display
  .send

  End With
  Set OutMail = Nothing
  Set OutApp = Nothing
   


End Sub

This is pretty generic code to do it. In practice, I have this built up in a class so at various points I can have something like

dim cUpdateMessage as new cEmailNotification

cUpdateMessage.quicksend "email@address.com", "project updated" just to be quick.

Eloise T · Jul 24, 2017

Thanks.

Marc L · Jul 25, 2017

Hi !

Sometimes I play music or voice from mp3 or wav file
but for this kind of alert I use some musical beep :

Code:

Private Declare Function kBeep Lib "kernel32" Alias "Beep" (ByVal Frq&, ByVal Dur&) As Boolean

Sub kBeepDemo()
    FD = [{392,494,588,740,880,740,880;200,100,200,100,400,100,900}]
    For L& = 1 To UBound(FD, 2):  kBeep FD(1, L), FD(2, L):  Next
End Sub

Do you like it ? So thanks to click on bottom right Like !

Eloise T · Jul 25, 2017

Marc L said:
Hi !

Sometimes I play music or voice from mp3 or wav file
but for this kind of alert I use some musical beep :

Code:

Private Declare Function kBeep Lib "kernel32" Alias "Beep" (ByVal Frq&, ByVal Dur&) As Boolean Sub kBeepDemo() FD = [{392,494,588,740,880,740,880;200,100,200,100,400,100,900}] For L& = 1 To UBound(FD, 2): kBeep FD(1, L), FD(2, L): Next End Sub

Do you like it ? So thanks to click on bottom right Like

I received the following error:

SirJB7 · Jul 25, 2017

@Marc L
Hi, buddy!
Maybe you can use Option Explicit and define variables once in a blue moon...
For a couple of coldfusioned Carlsberg update it for both x86 & x64?
Regards!

YasserKhalil · Jul 25, 2017

Try replace the declaration line with this

Code:

#If VBA7 Then
    Private Declare PtrSafe Function kBeep Lib "kernel32" (ByVal dwFreq As Long, ByVal dwDuration As Long) As Long
#Else
    Private Declare Function kBeep Lib "kernel32" (ByVal Frq&, ByVal Dur&) As Boolean
#End If

Marc L · Jul 25, 2017

As I avoid 64 bits Excel version …

Eloise T · Jul 25, 2017

YasserKhalil said:

Try replace the declaration line with this

Code:

#If VBA7 Then
    Private Declare PtrSafe Function kBeep Lib "kernel32" (ByVal dwFreq As Long, ByVal dwDuration As Long) As Long
#Else
    Private Declare Function kBeep Lib "kernel32" (ByVal Frq&, ByVal Dur&) As Boolean
#End If

I must have not put it in the correct place?

YasserKhalil · Jul 25, 2017

These lines are put in declaration part at the very top of the standard module

Code:

Option Explicit

#If VBA7 Then
    Private Declare PtrSafe Function kBeep Lib "kernel32" (ByVal dwFreq As Long, ByVal dwDuration As Long) As Long
#Else
    Private Declare Function kBeep Lib "kernel32" (ByVal Frq&, ByVal Dur&) As Boolean
#End If

Sub kBeepDemo()
    FD = [{392,494,588,740,880,740,880;200,100,200,100,400,100,900}]
    For L& = 1 To UBound(FD, 2):  kBeep FD(1, L), FD(2, L):  Next
End Sub

Chihiro · Jul 25, 2017

Psst. Above will give error, since none of the variables are declared

Below worked on my 64-bit Excel.

Code:

Option Explicit
#If VBA7 Then
    Private Declare PtrSafe Function kBeep Lib "kernel32" Alias "Beep" (ByVal dwFreq As Long, ByVal dwDuration As Long) As Boolean
#Else
    Private Declare Function kBeep Lib "kernel32" (ByVal Frq&, ByVal Dur&) As Boolean
#End If

Sub kBeepDemo()
Dim FD, L As Long
    FD = [{392,494,588,740,880,740,880;200,100,200,100,400,100,900}]
    For L& = 1 To UBound(FD, 2)
        kBeep FD(1, L), FD(2, L)
    Next
End Sub

YasserKhalil · Jul 25, 2017

Thanks a lot Mr. Chihro for this remark .. In fact I put this Option Explicit because it appears at the very top of the module and I needed to show Eloise that the code will be put below that statement "Option Explicit" .. but you are right as variables are not declared .. Thanks a lot

Chihiro · Jul 25, 2017

I forgot to mention, without 'Alias "Beep"' portion, code gave me error "can't find DLL entry point kBeep in kernel32".

YasserKhalil · Jul 25, 2017

Replace kBeep with Beep in the code

Code:

Option Explicit
#If VBA7 Then
    Private Declare PtrSafe Function Beep Lib "kernel32" Alias "Beep" (ByVal dwFreq As Long, ByVal dwDuration As Long) As Boolean
#Else
    Private Declare Function Beep Lib "kernel32" (ByVal Frq&, ByVal Dur&) As Boolean
#End If

Sub kBeepDemo()
Dim FD, L As Long
    FD = [{392,494,588,740,880,740,880;200,100,200,100,400,100,900}]
    For L& = 1 To UBound(FD, 2)
        Beep FD(1, L), FD(2, L)
    Next
End Sub

Eloise T · Jul 25, 2017

YasserKhalil said:

Replace kBeep with Beep in the code

Code:

Option Explicit
#If VBA7 Then
    Private Declare PtrSafe Function Beep Lib "kernel32" Alias "Beep" (ByVal dwFreq As Long, ByVal dwDuration As Long) As Boolean
#Else
    Private Declare Function Beep Lib "kernel32" (ByVal Frq&, ByVal Dur&) As Boolean
#End If

Sub kBeepDemo()
Dim FD, L As Long
    FD = [{392,494,588,740,880,740,880;200,100,200,100,400,100,900}]
    For L& = 1 To UBound(FD, 2)
        Beep FD(1, L), FD(2, L)
    Next
End Sub

The Beep

Active Member

Excel Ninja

Active Member

Active Member

Active Member

Active Member

Active Member

Well-Known Member

Well-Known Member

Active Member

Well-Known Member

Active Member

Active Member

Excel Ninja

Active Member

Excel Rōnin

Well-Known Member

Excel Ninja

Active Member

Well-Known Member

Excel Ninja

Well-Known Member

Excel Ninja

Well-Known Member

Active Member