1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

The Beep

Discussion in 'VBA Macros' started by Eloise T, Jul 22, 2017.

  1. Eloise T

    Eloise T Active Member

    Messages:
    641
    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?
  2. shrivallabha

    shrivallabha Excel Ninja

    Messages:
    1,753
    Put some cooling time...
    Code (vb):

    Beep
    Application.Wait Now + TimeValue("0:00:01")
    Beep
    Application.Wait Now + TimeValue("0:00:01")
    Beep
     
    Eloise T and ThrottleWorks like this.
  3. Eloise T

    Eloise T Active Member

    Messages:
    641
    Thank you!
    ThrottleWorks likes this.
  4. dan_l

    dan_l Active Member

    Messages:
    724
    ahhh trying to drive your users insane.
  5. Eloise T

    Eloise T Active Member

    Messages:
    641
    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.
    ThrottleWorks likes this.
  6. dan_l

    dan_l Active Member

    Messages:
    724
    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.
    ThrottleWorks likes this.
  7. Eloise T

    Eloise T Active Member

    Messages:
    641
    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.
    ThrottleWorks likes this.
  8. Monty

    Monty Well-Known Member

    Messages:
    836
    You can also use....

    Speak....Which any text assigned to it.
    ThrottleWorks likes this.
  9. Monty

    Monty Well-Known Member

    Messages:
    836
    Code (vb):
    Sub Test()

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

    End Sub
    ThrottleWorks likes this.
  10. Eloise T

    Eloise T Active Member

    Messages:
    641
    I tried it out. Microsoft needs to improve the voice. :(
    ThrottleWorks likes this.
  11. Monty

    Monty Well-Known Member

    Messages:
    836
    Ya....But the only way to make Excel to speak.
    ThrottleWorks likes this.
  12. dan_l

    dan_l Active Member

    Messages:
    724
    Code (vb):

    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.
    ThrottleWorks likes this.
  13. Eloise T

    Eloise T Active Member

    Messages:
    641
  14. Marc L

    Marc L Excel Ninja

    Messages:
    3,175
    Hi !

    Sometimes I play music or voice from mp3 or wav file
    but for this kind of alert I use some musical beep :​
    Code (vb):
    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 !
  15. Eloise T

    Eloise T Active Member

    Messages:
    641
    upload_2017-7-24_21-54-59.png
  16. SirJB7

    SirJB7 Excel Rōnin

    Messages:
    8,894
    @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!
    ThrottleWorks and Marc L like this.
  17. YasserKhalil

    YasserKhalil Active Member

    Messages:
    740
    Try replace the declaration line with this
    Code (vb):
    #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
    SirJB7, ThrottleWorks and Marc L like this.
  18. Marc L

    Marc L Excel Ninja

    Messages:
    3,175


    As I avoid 64 bits Excel version …​
    ThrottleWorks likes this.
  19. Eloise T

    Eloise T Active Member

    Messages:
    641
    upload_2017-7-25_12-13-36.png

    I must have not put it in the correct place?
  20. YasserKhalil

    YasserKhalil Active Member

    Messages:
    740
    These lines are put in declaration part at the very top of the standard module
    Code (vb):
    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
  21. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,683
    Psst. Above will give error, since none of the variables are declared ;)

    Below worked on my 64-bit Excel.
    Code (vb):
    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 likes this.
  22. YasserKhalil

    YasserKhalil Active Member

    Messages:
    740
    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
  23. Chihiro

    Chihiro Well-Known Member

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

    YasserKhalil Active Member

    Messages:
    740
    Replace kBeep with Beep in the code
    Code (vb):
    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
    Monty likes this.
  25. Eloise T

    Eloise T Active Member

    Messages:
    641
    upload_2017-7-25_16-12-27.png

Share This Page