This section of the tutorial explains how to add sounds to the Pong game created previously.
There are four types of sound effects: collision with the walls, collision with the bats, missed ball by the player (crowd laughter) and missed ball by the virtual opponent (crowd applause).
[sociallocker]
[/sociallocker]
Excel PONG Tutorial #7 – associating sound effects to ball collision events
by George Lungu
– The previous tutorial showed how to add adjustable skill levels to the
virtual pong opponent. This section (which is a continuation of part#6)
adds three different sound effects to following events: ball bouncing of
the horizontal walls, ball bouncing off the bats and ball missing one of
the bats (when score is supposed to change). In the next tutorial a score
display scheme will be introduced.
42. Create a new copy of the workbook in a new directory
– Create a new folder called “Pong_Tutorial_Archive”
– In the new folder, save a copy of the Excel Pong file (as
Pong_Tutorial_Advanced.xls) plus a number of four sound
effects in the .wav format downloaded off the internet.
– In the new workbook delete all the worksheets except the last
one (Pong_Tutorial_6).
– Change the name of the worksheet to “Pong_Tutorial_7”.
– Delete all the code in Module1 except for the API function
declaration on the top of the page.
– Copy all the code from Module3 into Module1 under the API
function declaration, then delete Module2 and Module 3 and
now you are left with a single worksheet and a single module.
-Change the macro names to: “Serve_7” and “Play_Tutorial_7”
– Verify the functionality of the game
Sound macro theory
– Among other .wav files in the same directory with the Excel file there are four important sound files:
– crowd_applause.wav – used when the ball is missed by Bat #1 (the player scores)
– crowd_laugh.wav – used when the ball is missed by Bat #2 (the opponent scores)
– wall_bounce – used when the bal bounces off the upper or the lower wall
– bat_bounce – used when the ball bounces off either of the bats
<excelunusual.com> 2
– In order to create a VBA macro which plays sound from a .wav file you need to start the VBA code with the
PlaySound API function declaration, which all has to be typed in one line on the top of the page (an underscore
will physically break the line in two but it will keep the 1-line code functionality):
Private Declare Function PlaySound Lib “winmm.dll“_(ByVal someName As String, ByVal hModule As Long, ByVal dwFlags As Long) As Long
Specifies options for playing the sound using one or more of the flags explained in the next page
Arguments: Indicates the file name and file path to play
Besides the first argument which is the file name and path, this function has flag arguments by which its run options can be controlled. You could declare these as constants as follows (or just use the number):
SND_SYNC = &H0 – The sound is played synchronously and the function does not return until the sound ends.
SND_ASYNC = &H1 – The sound is played asynchronously and the function returns immediately after beginning the sound.
SND_NODEFAULT = &H2 – If the sound cannot be found, the function returns silently without playing the default sound.
SND_LOOP = &H8 – The sound will continue to play repeatedly until PlaySound is called again. You must also specify the
SND_ASYNC flag to loop sounds (use the compound condition: SND_ASYNC or SND_LOOP).
SND_NOSTOP = &H10 – If a sound is currently playing, the function continue playing the old sound and it will immediately return False without playing the newly requested sound.
-Out of the above list of flags we will use &H1 since we want the action of the game to be continued while sound
effects are played (if we used &H0 the action will freeze until the sound effect ends – I recommend you change
&H1 with &H0 and see the effect for yourself).
– We could also use &H2 in order to make our code simpler (by giving a file a name that doesn’t exist when we
need the macro silent). The effect of using this flag could possibly simplify the code by saving an IF statement.
<excelunusual.com> 3
43. Create the sound macro
– This is an auxiliary macro which will be used by the main “Play_Tutorial_7” macro during each loop cycle.
– This macro is named “Collision_Effects_7” and it will be upgraded later to include score effects.
While studying the macro make sure you consult the Collision Events table
Private Declare Function PlaySound Lib “winmm.dll” (ByVal someName _
As String, ByVal hModule As Long, ByVal dwFlags As Long) As Long
Sub Collision_Effects_7()
If [T15] Then Call PlaySound(ThisWorkbook.Path & “\wall_bounce.wav”, 0&, &H1)
If [T16] Or [T18] Then Call PlaySound(ThisWorkbook.Path & “\bat_bounce.wav”, 0&, &H1)
If [T17] Then Call PlaySound(ThisWorkbook.Path & “\crowd_applause.wav”, 0&, &H1)
If [T19] Then Call PlaySound(ThisWorkbook.Path & “\crowd_laugh.wav”, 0&, &H1)
On Error Resume Next
End Sub
– The results in the Collision Events table are used as
conditions for selecting which macro plays at that time.
– If all the results in the collision table are “FALSE”, no sound
will play, if the horizontal wall collision event is “TRUE” then
the “wall_bounce.wav” will play and so on.
– We chose the asynchronous flag in the sound function to
make sure the game does not freeze for the duration of the sound effect
<excelunusual.com> 4
44. Create an “Enable_Sounds” button for the sound effects
– In order to be able to turn the sounds on and off, a button is created which toggles the value of cell P1 between “ON” and “OFF”
Private Sub Level_Opponent_Change()
Range(“P6”) = Level_Opponent.Value
End Sub
Sub Bat_Stroke_Change()
Range(“P8”) = Bat_Stroke.Value
End Sub
Private Sub Serve_Speed_Change()
Range(“P10”) = Serve_Speed.Value
End Sub
Private Sub Bat_Size_Change()
Dim BArr As Variant
BArr = Array(2, 5, 10, 15, 20, 40)
[P12] = 10 * BArr(Bat_Size.Value)
End Sub
– The P1 cell value will be used in the “Play_Tutorial_7” macro to [P1] = “OFF” enable or disable the sound effects as it will be shown in the next page.
– All the macros associated to the current sheet are displayed to the right (Module1 macros are not shown here).
– The last macro (“Enable_Sounds” in red) is the newly added macro.
Sub Enable_Sounds()
If [P1] = “ON” Then
Else
[P1] = “ON”
End If
End Sub
<excelunusual.com> 5
45. Integrate the sound macro within the Module1
-This is a snapshot of the Module1 code-On the very top in yellow is the declaration of the sound function
-Below that still in yellow is the declaration of the mouse position API function and the structure PointAPI-The last yellow line is a declaration of a “latch” variable
–”RunPause”. This variable allows the “Play_Tutorial_7” macro to be started and stopped from the same button.
-The serve, play and collision effects macros are situated below, each with its own color
Conditional call of the auxiliary macro “Collision_Efects_7” macro within the main “Play_Tutorial_7” macro
“On Error Resume Next”Specifies that when a run-time error occurs, control goes to the statement immediately following the statement where the error occurred where execution continues. It is useful while handling multiple sound effects.
Private Declare Function PlaySound Lib “winmm.dll” (ByVal someName _As String, ByVal hModule As Long, ByVal dwFlags As Long) As Long
Public Declare Function GetCursorPos _Lib “user32” (Some_String As POINTAPI) As Long
Type POINTAPIX As Long
X As Long
Y As Long
End Type
Dim RunPause As Boolean
Sub Serve_7()
[A42] = [BG1].Value
Range(“R28:Y30”).Clear
Range(“R28:U28”) = Range(“R23:U23”).Value
End Sub
Sub Play_Tutorial_7()
RunPause = Not RunPause
Dim Pt0 As POINTAPI
Dim Pt1 As POINTAPI
GetCursorPos Pt0
Do While RunPause = True
DoEvents
GetCursorPos Pt1[S6] = [P8] * (-Pt1.Y + Pt0.Y)
On Error Resume Next
If [P1]=“ON” Then Collision_Effects_7
Range(“R28:Y29”) = Range(“R27:Y28”).Value
Loop
End Sub
Sub Collision_Effects_7()
If [T15] Then Call PlaySound(ThisWorkbook.Path & “\wall_bounce.wav”, 0&, &H1)
If [T16] Or [T18] Then Call PlaySound(ThisWorkbook.Path & “\bat_bounce.wav”, 0&, &H1)
If [T17] Then Call PlaySound(ThisWorkbook.Path & “\crowd_applause.wav”, 0&, &H1)
If [T19] Then Call PlaySound(ThisWorkbook.Path & “\crowd_laugh.wav”, 0&, &H1)
On Error Resume Next
End Sub
by George Lungu <excelunusual.com> 6
Hi George
After reading dynamicmacro#1 tutorial, I can create an animation for my data. I simplied my problem and found the common things just like what you said.
My problem is I want to move a curve corresponding to my data. The following is a set of simple data
time x1(location) x2 x3 x4
1 0 1 0 0
2 0 0 1 0
3 0 0 0 1
Based on the macro in Dynamicmarco excel file, I make it. The number of changing cells are 5. One is time index, the others are 4 locations.
However, what if the problem is 2D? 3D? Can excel do that? Here is an example
time1 1 1
1 0
time2 1 0
1 1
time3 0 1
1 1
I am thinking if excel can do this. The no. of changing cells will be huge if 2D area is big. Also, it seems that the raw data at each time have to occupy one worksheet, so if there is a hundred time steps the workbook will contain 100 sheets? It makes thing complicated…
That is what I think, hope this will not interupt you. Thank you.
Chang, In your file the hard part is done. Animation is easy. You can put everything in one sheet just stack it vertically with a pitch of 100 rows for instance. I would however do this in a dynamic macro solution (copy-paste-in-a-loop macro) but for now for the next few days exhaust what you started. You’ll learn something well and have fun at the same time. Later you can do the dynamic version. Question: how long does your VBA need to fill a sheet like you sent me with numbers?
If worse comes to worst then you might need to put most of your calcs in worksheet formulas. What you have there is fairly small compared to what Excel can do. Say a 100×100 array of points on excel 2003 or earlier you must be able to do at least 10 in a second but more likely 100 in a second. The arrays might be large but the individual cell calculations are small which helps the speed. Your problem is manageable if you keep it within say 5,000- 50,000 points (this is a wild guess). You should absolutely use 2003 or earlier. The earlier the faster but 2003 is up there in terms of speed.
You centainly open my appetite for this. I will have something for you by the end of this weekend. But you go ahead and experiment until my schedule gets less congested.
Hello George
Your work is amazing. It is really new to me. You are so unselfish to share your knowledge, thanks a lot for that!
If you don’t mind I want to talk about the problem I want to solve. Thanks for your patient 🙂
I have calculated numerical solution for a transportation problem by solving PDE using VBA (this problem describe how a variable changes with time and location, for example contaminant in a flowing river is convected by the fluid and diffusive by itself.) and then I generated charts for those calculated data. Actually this problem is one of my homework, I have done it. But I am thinking can I create an animation for that?if I can do, the data will be interpreted perfectly. So I google “Excel Animation”, although I thought Excel cannot do that… However, I found some resources and finally was abstracted by your blog. You don’t know how excited I am after reading some pages on your blog…
Hope you forgive me for this boring story : )
So here is my question, can I make that data move forward?(I want the charts in sheet to move as an animation). I have download some basic tutorial, could you give me a suggestion where should I get started? Thank you very much.
I sent a email to you and the attachment contained charts that I want to convert to animation. I am so sorry about my English…
Hope you have a good day.
Chang, I believe what you you have here is diffusion equation which applies to heat transfer too. Transportation equation, from what I read, applies to logistics (roads and vehicles) and optimum ways to carry freight between various points on a map. I need to study your problem since I never did diffusion before. Initially I thought you had a table with a parameter that I could vary to animate but you just have numbers from VBA. I cannot animate that without some parameter to change. Lesson#0, never use 1 mile long VBA code. You can do the same thing in Excel and 10 times lighter and easier. You still need VBA but perhaps 3-5% of what you have here just to run a parameter loop. Let me analyze the problem more and I’ll come up with more details. When do you need the animation?
Thanks George
Actually this problem don’t need animation. I just want to create animation for that by myself. So, you can teach me at any time you are convenient.
Besides, sorry about missing parameter in the excel sheet. I will describe the problem again later. Thanks a lot.
Thanks for the quick feedback. I have my daily due work but as a hobby I am working at your problem. I will however warn you that I keep away from solutions from other people no matter who those are, some teachers, Newton, Crank-Nicolson etc. You are probably young. At your age I was just an ignorant sheep. As I matured I realize that the most important asset is not some book, research papers, school, teachers or any study. The most powerful asset was always in my head and I didn’t use just like all the other sheeps around me. Reading, studying, formal methods, classifications, getting info from teachers simply destroy your curious and enthusiast ignorance. Those things make you an erudit parrot. A graduated parrot. Ignorance and curiosity can solve any problem (if you just try to use it). On the other side there are the ignorants who never trust themselves and never try to solve anything by themselves. You need to take the middle path and get some very basic background but most of your learning needs to be done by playing (on Excel for instance) and solving problems by trial-tinking and error. Don’t rush. Take 1 hour 10 hours or 100 hours to solve a problem by just thinking and trying. It is not a waste of time, on the contrary it is a much better spent time than reading what other people did or listening to some older parrot (read teacher) how to do it.
As a technique try to solve a EXTREMELY simple version (1D- 3 time steps for instance) first. Always find a basic spatial or mechanical analogy to your problem. Something you can just run it in your head while you take the bus or drive or sleep. At the most you need is a purchase receipt and a pen to sketch 3 lines of a simple drawing but you should mostly be able to take all the problem with in your head wherever you go. Screw the complex equations, methods, books, classifications (implicit-explicit etc). Those are your only enemies. The challenge in the problem is your true friend. After you solve the very simple problem it means you TRULY understand the real life physics and not just some Mickey-mouse Greek letter scribbling on a paper from a class. After that increase complexity add some colors. Then after you feel even more comfortable with it add some animation. Then after that you are even better at it and you like the problem even more, then add one more dimension. Then combine this with some other phenomena. At this point go and read the universally sheep-approved theory. You will need between few seconds to few minutes to understand it and perhaps realize what is of value and what is just crap in the official theory, maybe even improve it. As opposed to the others you will understand the essence of the matter while other people’s understanding remains limited at the package or shell level (they barely scratched the surface by starting by playing formula-puzzles). Those guys forget that stuff next day after the exam whereas you can go on a deserted island, not worry about the problem for 10 years, then one morning remember it and manage to solve it within few hours of scratching on beach sand with a stick (you will need few hours still since you forget more details with time). Of course you might be asking, but what about that prosessor he is a guru, the other teacher really knows it. I look like an ignorant compared to them. I spend many years in grad school and in industry and I tell you, those are the guys who would have forgotten all the science in the drunk orgy after the exam but they just got a job in which they needed to recant that song and their parrot-list of words and Greek notations every day.
Thanks George
I saved your reply and read several times. I think a lot about what you said.
That is also new to me just like Excel Animation. You are right. I always forget almost things I have learned from one course after exam, so do my classmates around me… When I try to remember what I have learned from that course, I always feel sad at that moment because I give all knowledge back to school. Although same things happened to other people, I am worried about it because I feel I know nothing. I always tried to remember and understand the knowledge from book, but you know my curiosity disappeared gradually whenever I met the crazy math and complex description. I am thinking what should I do, just like I am getting things while I am losing things at the same speed.
I think you give me a good suggestion “try to solve a EXTREMELY simple version (1D- 3 time steps for instance) first. Always find a basic spatial or mechanical analogy to your problem.” Could you tell me how do you read science book? In order to having no fears of equations and complicated things in book, what did you do?
I know it will take your time, so sorry for that. But you really give me inspiration today. Thanks a lot.
Chang, you just read it like everyone else, read the first 30 pages get bored then put it on the shelf until 10 years later when you try to sell it at a garage sale or just start a fire with it. In my opinion though, you don’t read a science book :-). You look a little through after you now the stuff well by experimentation and practice (can be modeling too) and you have 10000% self confidence. At that point you are “stealing” info from the book. The author has good reasons to be afraid of you. Know the high school stuff well. Not everything of course, about 70-80% you will never use. Ever. Know mechanics, electricity, heat. Do 1000 problems. Including the the ones given at the national and international olympiad. Also learn very well to integrate on surface and on volume. It’s elmentary but do few hundred of problems till you really understand it. You don’t need to know a lot. Know a little but very well and you can figure out everything by yourself.
Hello George
I should say this again.. Thank you for sharing your knowledge.
I want to create 1D and 2D animation by solving transportation equation. But I don’t know where to get started. Can you give me some suggestions? I will send you a email later to support@excelunusual.com
Thank you very much.
Hi Chang,
Help me with this one. I am an electrical engineer and the only transportation equation I know, refers to semiconductors. Give me some more information.
Regards, George