Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 4 FirstFirst 1234 LastLast
Results 11 to 20 of 31

Thread: Can you play sound via VBA?

  1. #11
    Board Regular
    Join Date
    Mar 2002
    Posts
    88
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I would like to play sounds in my spreadsheet. The user chooses the wav files he wants, which are in the same directory as this application, and stores their filenames in cells A1 & A2. I want to play these wav sounds at certain occurances within my Vb application. Can you advise me how to do it ? I looked at the code below and didnt understand it all I have to admit. I was thinking of something a little easier ..or am I being dumb ?


    On 2002-05-01 02:08, Ivan F Moala wrote:
    Hi Tom
    Just to get you started.
    Why not record what you want eg...WRONG ANSWER, RIGHT ON etc....probably sound
    better coming from Mum
    Then just use this code to play the wave files you have recorded.


    Option Explicit

    Public Declare Function sndPlaySound Lib "winmm.dll" Alias "sndPlaySoundA" _
    (ByVal lpszSoundName As String, ByVal uFlags As Long) As Long

    Public Const SND_SYNC = &H0
    Public Const SND_ASYNC = &H1
    Public Const SND_NODEFAULT = &H2
    Public Const SND_MEMORY = &H4
    Public Const SND_LOOP = &H8
    Public Const SND_NOSTOP = &H10

    Dim SndFile As String
    Dim wFlags As Double
    Dim PlayIt

    Sub WAVPlay(File)
    Dim SoundName As String

    SoundName = File

    wFlags = SND_ASYNC Or SND_NODEFAULT

    PlayIt = sndPlaySound(SoundName, wFlags)

    End Sub

    Sub Wrong()
    SndFile = "C:MEDIAwrong.wav"

    If Dir(SndFile) = "" Then MsgBox "Sorry no File to Play!": End

    PlayIt = sndPlaySound(SndFile, SND_ASYNC)

    End Sub




    Tom....I have Kids would appreciate a copy


    Thanks

    Bill

  2. #12
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-08-22 02:44, Billm wrote:
    I would like to play sounds in my spreadsheet. The user chooses the wav files he wants, which are in the same directory as this application, and stores their filenames in cells A1 & A2. I want to play these wav sounds at certain occurances within my Vb application. Can you advise me how to do it ? I looked at the code below and didnt understand it all I have to admit. I was thinking of something a little easier ..or am I being dumb ?


    On 2002-05-01 02:08, Ivan F Moala wrote:
    Hi Tom
    Just to get you started.
    Why not record what you want eg...WRONG ANSWER, RIGHT ON etc....probably sound
    better coming from Mum
    Then just use this code to play the wave files you have recorded.


    Option Explicit

    Public Declare Function sndPlaySound Lib "winmm.dll" Alias "sndPlaySoundA" _
    (ByVal lpszSoundName As String, ByVal uFlags As Long) As Long

    Public Const SND_SYNC = &H0
    Public Const SND_ASYNC = &H1
    Public Const SND_NODEFAULT = &H2
    Public Const SND_MEMORY = &H4
    Public Const SND_LOOP = &H8
    Public Const SND_NOSTOP = &H10

    Dim SndFile As String
    Dim wFlags As Double
    Dim PlayIt

    Sub WAVPlay(File)
    Dim SoundName As String

    SoundName = File

    wFlags = SND_ASYNC Or SND_NODEFAULT

    PlayIt = sndPlaySound(SoundName, wFlags)

    End Sub

    Sub Wrong()
    SndFile = "C:MEDIAwrong.wav"

    If Dir(SndFile) = "" Then MsgBox "Sorry no File to Play!": End

    PlayIt = sndPlaySound(SndFile, SND_ASYNC)

    End Sub




    Tom....I have Kids would appreciate a copy


    Hi Billm
    1) At what occurances do you want to play the sound files.
    2) Are you familiar with coding
    If not, then do you know the steps you
    would be taking.
    3) Have you tried the code ??
    the SndFile = "C:MEDIAwrong.wav"
    variable is the key = Full path to
    the sound file you want to play.
    Kind Regards,
    Ivan F Moala From the City of Sails

  3. #13
    Board Regular
    Join Date
    Jul 2002
    Location
    Derbyshire, UK
    Posts
    1,605
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    hi

    I Can't help you with the sounds, but I think there is an example of a Multiplication Workbook on J. Walkenbach's site. This might save you some work.I have it on my daughter's computer and she thought it was great, it would be much better with sound.If you need it I can send you a copy.
    Why not submit the finished item for download as there seems to be a lot of interested parties ( me included)
    Good Luck

  4. #14
    Board Regular
    Join Date
    Mar 2002
    Posts
    88
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ivan, no I havent tried the code yet as I didnt know how to call it. I want to play just 2 sounds. eg when condition1 is met, play 1st sound, if condition 2 is met, play the 2nd sound within an existing routine. The 2 wav files are stored in the same directory as the spreadsheet and their filenames are stored in A1 and A2 cells. I suppose I should add an error trap so if the wav files are not there, then no errors occur and the proggie just carries on.

    I was thinking that there might be a simple 1-line VBA statement that can run the wav file for me, eg something like play "tune.wav" or play "A1" if A1 contains the wav filename.

    Alternatively, if I have to put the "play" code in a routine, something like your example, then how do I call it from within my routine so that it finds the correct wav file.

    Thanks
    Bill

  5. #15
    MrExcel MVP Joe Was's Avatar
    Join Date
    Feb 2002
    Location
    Central Florida, USA
    Posts
    7,539
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ivan,
    Does your Wav code work in Excel 2000 and XP?
    Do you need to set a reference in Excel?

    In your function do you need to set a name for "File" and what is the "wFlags."

    Thanks, Joe Was.
    JSW: Try and try again: "The way of the Coder!"

  6. #16
    New Member
    Join Date
    Aug 2002
    Posts
    43
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    IVAN,

    I don't understand these parts in your code:

    Public Declare Function sndPlaySound Lib "winmm.dll" Alias "sndPlaySoundA" _
    (ByVal lpszSoundName As String, ByVal uFlags As Long) As Long

    What is the meaning of the above, and what does it do?

    Then:
    Public Const SND_SYNC = &H0
    Public Const SND_ASYNC = &H1
    Public Const SND_NODEFAULT = &H2
    Public Const SND_MEMORY = &H4
    Public Const SND_LOOP = &H8
    Public Const SND_NOSTOP = &H10

    What does "&H0,&H1,&H2,&H4,&H8,&H10" means??

    Bear with me, Iam new to VBA. Appreciate your help.

    Excello...

  7. #17
    New Member
    Join Date
    Aug 2002
    Posts
    43
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    IVAN,

    I don't understand these parts in your code:

    Public Declare Function sndPlaySound Lib "winmm.dll" Alias "sndPlaySoundA" _
    (ByVal lpszSoundName As String, ByVal uFlags As Long) As Long

    What is the meaning of the above, and what does it do?

    Then:
    Public Const SND_SYNC = &H0
    Public Const SND_ASYNC = &H1
    Public Const SND_NODEFAULT = &H2
    Public Const SND_MEMORY = &H4
    Public Const SND_LOOP = &H8
    Public Const SND_NOSTOP = &H10

    What does "&H0,&H1,&H2,&H4,&H8,&H10" means??

    Bear with me, Iam new to VBA. Appreciate your help.

    Excello...

  8. #18
    Board Regular
    Join Date
    Mar 2002
    Posts
    88
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    an explanation of them would help me too
    Thanks

    Bill

  9. #19
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Bill & Excello

    Excello..No, thats OK. Even if you weren't new to VBA it may still look
    like giberish and if it didn't you may still not undertand it !
    Lets see if I can explain this properly?

    The Public Function >

    Public Declare Function sndPlaySound Lib "winmm.dll" Alias "sndPlaySoundA" _
    (ByVal lpszSoundName As String, ByVal uFlags As Long) As Long

    Is a function call [Look up "Function Statement" in the VBA Help files] to a Library file, in this case the Windows Multimedia Dynamic link
    library. Within this dll (Dynamic link library)
    There are various other functions (made in C / C++) that work
    at the lower level of programing, these calls or libraries of calls
    make up these DLLs, and are most commonly documented using C language
    syntax. To call these procedures from Visual Basic, you need to translate them into valid Declare statements and call them with the correct arguments.

    So bascially Dlls are libraries of procedures that applications can
    link to and use at run time rather than link to statically at compile time. These procedures are sometimes referred to as the Windows API, or application programming interface. You will see alot of these API calls
    on this site. An API is exactly as its acronym suggests, an interface.
    Just like the interface between your screen and yourself is the keyboard or mouse, so the undelying code just translates your actions
    to the screen....everything between these 2 is handled by Libray files
    that call other functions to perform actions. You use API while programing in VBA, you just don't realise it...the VBA just acts like
    a translator in converting your code to the approprate Op codes so that
    the underlying library files can perform some action/function.

    The plus side means that the libraries can be updated independently of the application, and many applications can share a single DLL.
    The down side is that if you get it wrong you may not recieve an error
    and/or your system will become unstable or crash causing you to reboot.
    Always good practive to constantly save when working with APIs.
    Also can be differcult to debug your program, infact you can't debug
    API calls in VBA.

    Have a look @ your System Dir under your Windows Dir and you will see all these files, so Windows is comprised of DLLs, and other applications call the procedures within these libraries to display windows and graphics, manage memory, or perform other tasks. There are
    numerous libraries and associated calls, APIs.

    Now to access these library files or APIs you need to how
    to declare a DLL procedure, you add a Declare statement to the Declarations section of the code window. By declaring the function,
    you tell VBA where it can find the function.

    If the procedure returns a value, write the declare as a Function:
    Using the winmm.dll call to playsoundA does return a value;
    A value of nonzero means the function call succeeded.
    Zero indicates the call failed.

    Declare Function publicname Lib "libname" [Alias "alias"] [([[ByVal] variable [As type] [,[ByVal] variable [As type]]...])] As Type

    If a procedure does not return a value, write the declare as a Sub:
    eg.
    Declare Sub publicname Lib "libname" [Alias "alias"] [([[ByVal] variable [As type] [,[ByVal] variable [As type]]...])]

    As per Std Sub Procedure set-up DLL procedures declared in standard
    modules are public by default and can be called from anywhere in your
    application. DLL procedures declared in any other type of module are
    private to that module, and you must identify them as such by preceding
    the declaration with the Private keyword. Procedure names are
    case-sensitive in 32-bit versions of Visual Basic. In previous, 16-bit
    versions, procedure names were not case-sensitive. (VB and Not VBA)

    Lets look @ the above Function;

    [Lib]
    Specifying the Library
    The Lib clause in the Declare statement tells Visual Basic where to find the .dll file that contains the procedure. When you're referencing one of the core Windows libraries (User32 [user interface functions],
    Kernel32 [operating system kernel functions], GDI32 [graphics device
    interface functions], and shell32.dll [Windows shell functions]), you
    don't need to include the file name extension:
    eg.
    Declare Function RegisterClass Lib "user32" Alias "RegisterClass" (Class As WNDCLASS) As Long

    For other DLLs, the Lib clause is a file specification that can include
    a path:
    Declare Function lzCopy Lib "c:windowslzexpand.dll" _(ByVal S As
    _ Integer, ByVal D As Integer) As Long

    If you do not specify a path for libname, Visual Basic will search for the file in the following order:
    1. Directory containing the .exe file
    2. Current directory
    3. Windows 32-bit system directory WindowsSystem32
    4. Windows directory Windows
    5. Path environment variable

    In most cases you will see API calls with No explicit path
    as you would use the core window library files which are located
    in your system dir.

    So in the above example we are Looking @ the winmm.dll

    sndPlaySound = The name by which Visual Basic will refer to the function everywhere else in your code. You can make this any
    name you want (as long as you use the proper Alias clause), but it's
    safest to make this the Actual name of the function in the Windows API

    [Alias]
    The Alias clause in the declare statement is needed to specify the
    correct character set. Windows API functions that contain strings actually exist in two formats: ANSI and Unicode.
    It is the A @ end that signifies this in "sndPlaySoundA"
    The Unicode version ends with the letter W.

    In the Windows header files, therefore, you'll get both ANSI and Unicode versions of each function that contains a string. have a look for winmm.dll and right click to select Quickview, you should get soemtyhing like this;

    Export table
    Ordinal | Entry Point | Name
    0004 00012d0 CloseDriver
    0005 0008db3 DefDriverProc

    etc....listing all the entry points or LP Long pointers to the
    various functions with in the library.

    Now for the Arguments and Types;
    (ByVal lpszSoundName As String, ByVal uFlags As Long) As Long

    lpszSoundName = a string that specifies the sound to play. This must
    be the Fullpathname. If this parameter is NULL, any currently playing waveform sound is stopped. ie To stop a currently playing sound then
    send the VBA Const VBNullString and NOT "" !!

    ByVal = when a value is passed ByVal, the actual value is passed directly to the function, and when passed ByRef, the address of the value is passed, so you may see something like LPWORD which is
    actually a Long Pointer to a memory address.

    As String = value passed is a string = the path name to the sound file

    ByVal uFlags As Long
    The function is expecting some Flags to be declared here as the data
    type Long (Look up data Type for Long) =
    Long (long integer) variables are stored as signed 32-bit (4-byte) numbers ranging in value from -2,147,483,648 to 2,147,483,647. The type-declaration character for Long is the ampersand (&).

    As for the Public Constants
    Public Const SND_SYNC = &H0
    Public Const SND_ASYNC = &H1
    Public Const SND_NODEFAULT = &H2
    Public Const SND_MEMORY = &H4
    Public Const SND_LOOP = &H8
    Public Const SND_NOSTOP = &H10

    What does "&H0,&H1,&H2,&H4,&H8,&H10" means??

    The function will recognise various Constants or flags that tell it
    how/what to do eg.
    Flags for playing the sound;

    SND_ASYNC
    The sound is played asynchronously and PlaySound returns immediately after beginning the sound. To terminate an asynchronously played waveform sound, call PlaySound with lpszSoundName set to VBNullString.
    SND_LOOP
    The sound plays repeatedly until PlaySound is called again with the lpszSoundName parameter set to VBNullString. You must also specify the
    SND_ASYNC flag to indicate an asynchronous sound event.
    SND_NODEFAULT
    No default sound event is used. If the sound cannot be found, PlaySound
    returns silently without playing the default sound.
    SND_NOSTOP
    The specified sound event will yield to another sound event that is
    already playing. If a sound cannot be played because the resource
    needed to generate that sound is busy playing another sound,the
    function immediately returns FALSE without playing the requested sound.
    If this flag is not specified, PlaySound attempts to stop the currently
    playing sound so that the device can be used to play the new sound.
    SND_NOWAIT
    If the driver is busy, return immediately without playing the sound.
    SND_SYNC
    Synchronous playback of a sound event. PlaySound returns after the
    sound event completes.

    The values; "&H0,&H1,&H2,&H4,&H8,&H10" are the Long Hex values of these
    constants eg.

    &[Long] H(Hex) 0 or &H0 = 0 Decimal
    &[Long] H(Hex) 10 or &H10 = 16 Decimal


    API calls are very useful when you require commands taht are not
    provided with VBA. By calling procedures in DLLs, you can access the thousands of procedures that form the backbone of the Microsoft Windows operating system.


    Option Explicit


    Declare Function sndPlaySound Lib "winmm.dll" Alias "sndPlaySoundA" _
    (ByVal lpszSoundName As String, ByVal uFlags As Long) As Long

    Public Const SND_SYNC = &H0
    Public Const SND_ASYNC = &H1
    Public Const SND_NODEFAULT = &H2
    Public Const SND_MEMORY = &H4
    Public Const SND_LOOP = &H8
    Public Const SND_NOSTOP = &H10

    Sub WAVStop()
    Call WAVPlay(vbNullString)
    End Sub


    Sub WAVLoop(File As String)
    Dim SoundName As String
    Dim wFlags As Long
    Dim x As Long

    SoundName = File
    wFlags = SND_ASYNC Or SND_LOOP
    x = sndPlaySound(SoundName, wFlags)
    If x = 0 Then MsgBox "Can't play " & File
    End Sub


    Sub WAVPlay(File As String)
    Dim SoundName As String
    Dim wFlags As Long
    Dim x As Long

    SoundName = File
    wFlags = SND_ASYNC Or SND_NODEFAULT
    x = sndPlaySound(SoundName, wFlags)
    If x = 0 Then MsgBox "Can't play " & File
    End Sub

    Sub Play()
    WAVPlay "C:windowsmediatada.wav"
    End Sub

    Sub PlayLoop()
    WAVLoop "C:windowsmediatada.wav"
    End Sub



    Kind Regards,
    Ivan F Moala From the City of Sails

  10. #20
    Board Regular
    Join Date
    Mar 2002
    Posts
    88
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ivan,
    that is an excellent explanation. Thankyou for your time and patience.
    Thanks

    Bill

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •