WAVs and API difficulty

  • Thread starter Thread starter smudge
  • Start date Start date
S

smudge

I use an add-in that provides my excel worksheet with live streamin
data. As this changes all of my calculations ament accordingly.

I wish to use audio or WAV files to warn and indicate when certai
conditions are met. I have some experience with macros but I am unabl
to get the following code to work:

'Playing WAV-files using VBA in Microsoft Excel
(http://www.exceltip.com/st/Playing_WAV-files_using_VBA_in_Microsoft_Excel/460.html


Or

'Playing sound notes using VBA in Microsoft Excel
(http://www.exceltip.com/st/Playing_sound_notes_using_VBA_in_Microsoft_Excel/461.html)

Any method of playing an audio file would be wonderful. My difficultie
are:

- Do I paste the code into a macro?

- How do I 'Call' the sound when a logical condition (e.g. Cell
A1:A5>=10) is met?

- Will this run automatically, or will I have to run a macro to see i
the condition is met to play the audio?

- Any tips on avoiding a loop sound if a condition doesn't change for
minutes? Perhaps a play once when condition is met, and everytim
thereafter as the live data updates if the condition is still met, e.g
LESS than stated value, so as data is streamed:-

A1:A5 = 10 = Play audiofile1 Once. (First Stream Update)
A1:A5 = 11 = Play audiofile2 Once. (Second Stream Update)
A1:A5 = 08 = Less than 10 so no audio sounded. (Third Stream Update)

Many thanks in advance
 
Thanks for your swift reply. That link is very useful, but where exactl
do I place the code?

I open Myanalysisworkbook.xls, do I hiy alt+F11 (visual Basic editor
or alyt+shift+F11 (Script Editor)?

I don't know where the code needs to be placed! I only imagine it save
this code in the current work-book and not on an externally reference
vbs or equivalent
 
That's great and working! A twist on a theme tho, how would I midif
this code to play one alarm (True) and another (False)?

'Windows API function declaration
Private Declare Function PlaySound Lib "winmm.dll" _
Alias "PlaySoundA" (ByVal lpszName As String, _
ByVal hModule As Long, ByVal dwFlags As Long) As Long


Function Alarm(Cell, Condition)
Dim WAVFile As String
Const SND_ASYNC = &H1
Const SND_FILENAME = &H20000
On Error GoTo ErrHandler
If Evaluate(Cell.Value & Condition) Then
WAVFile = ThisWorkbook.Path & "\sound.wav" 'Edit thi
statement
Call PlaySound(WAVFile, 0&, SND_ASYNC Or SND_FILENAME)
Alarm = True
Exit Function
End If
ErrHandler:
Alarm = False
End Function

Essentially, I wish to build a library of sounds, one for eac
condition, e.g.

A1 = '10' Alarm 01
A1 = '09' Alarm 02 etc..:confused
 
Hi
change the Alarm function as follows (I also included two parameters
for your sound file names / The second one as optional):

Function Alarm(Cell, Condition As String, sound_true_file As String,
Optional sound_false_file As String) As Boolean
Dim WAVFile As String
Const SND_ASYNC = &H1
Const SND_FILENAME = &H20000
On Error GoTo ErrHandler
If Evaluate(Cell.Value & Condition) Then
WAVFile = sound_true_file
Call PlaySound(WAVFile, 0&, SND_ASYNC Or SND_FILENAME)
Alarm = True
ElseIf sound_false_file <> "" Then
WAVFile = sound_false_file
Call PlaySound(WAVFile, 0&, SND_ASYNC Or SND_FILENAME)
Alarm = False
Else
Alarm = False
End If
Exit Function

ErrHandler:
Alarm = False
End Function

Usage examples:
=alarm(A1,"=20","C:\winnt\media\chimes.wav")
(plays only sound if formula evaluates tu TRUE)
=alarm(A1,"=20","C:\winnt\media\chimes.wav","C:\winnt\media\chord.wav")
plays different sounds for TRUE and FALSE
 
Took a little bit of playing around but I got successful results
thankyou very much!

Quite new to this section of Excel (the Visual Basic stuff, modules an
the like) I imagine I can create functions such as your examples to m
hearts content?

Would you have any good links/URLs for creating and working with custo
functions please? A subject I will need to revise!

One final thought, If I create another function (to play a WAV as abov
but with a different logic test), would I add the code to the sam
Module1 or create a new module? Thanks again
 
Thanks very much for your assistance. I have already implemente
serveral ideas into my worksheet(s) and for your assistane I am sure t
have saved hours
 
Back
Top