Adding Sound to Your VBA Program

Microsoft removed support for playing sound files in Excel several versions ago. This leaves two choices for playing sounds in Excel applications with VBA: ActiveX controls and the Windows API (application programming interface).

As there are no ActiveX controls for playing sound that currently ship with VBA, the Windows API will be used for adding sound to your VBA programs in this book.

There is a multimedia control that comes with Windows and it can be used to play sound files in your VBA programs; however, it cannot be accessed from the Control toolbox, so its use is beyond the scope of this book. That's really just as well, because using it to play sound files is actually more difficult than using the Windows API.

The Windows API

The Windows Application Programming Interface (API) is the interface used to program-matically control the Windows operating system. The Windows API is comprised of numerous procedures that provide programmatic access to the features of the Windows operating system (for example, windows functions, file functions, and so on). The API procedures are stored in the system directory of Windows as .dll (dynamic link library) files. There can be dozens of procedures stored within a single .dll file. The API procedures are conceptually the same as procedures used in any programming language, including VBA; however, because the API procedures are written in C/C++, accessing them via the VBA programming environment can be difficult—in some cases, impossible.

Normally, the Windows API is left as an advanced programming topic for some very good reasons. Using the Windows API can be dangerous as it bypasses all of the safety features built into VBA to prevent the misuse of system resources and the subsequent system crashes they usually cause (but nothing that can't be fixed by rebooting your computer); however, the API can greatly extend the ability and therefore, the power of a program.

Fortunately, tapping into the Windows API to play a .wav file (Wave Form Audio) is about as easy as it gets. This section of the book will only show you how to play .wav files using the Windows API and will not discuss the Windows API in any detail. Instead, the Windows API is left as an advanced topic for you to consider after becoming comfortable with VBA. The Windows API is the best (and probably easiest) tool available to all VBA programmers for adding sound to a program, but it should not be used extensively by beginning programmers; therefore, I will only show you how to use it to add sound to a VBA program.

To use a function from the Windows API in VBA, open a code module and use a Declare statement in the general declarations section to create a reference to the external procedure (Windows API function). Note that line continuation character has been used in the declaration below due to its length.

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

In reality, this is a relatively short API declaration. This declaration creates a reference to the sndPlaySoundA() function found in the file winmm.dll. It looks a lot like a function call in VBA, but it is only a declaration; the call to the function will come later. Capitalization is important and will not be corrected automatically if typed incorrectly.

The function accepts two arguments as listed in the declaration. The argument lpszSoundName represents the string specifying the filename and path to the .wav file to be played, and the argument uFlags represents the integer used to denote whether or not program execution should proceed immediately (1) or wait until after the file is done playing (0). The sndPlaySoundA() function returns a value of type Long that may be discarded. Hence, calls to the sndPlaySoundA() function from a VBA procedure can appear as follows.

sndPlaySoundA "Path to .wav file", 1

returnVal = sndPlaySoundA("Path to .wav file", 0)

Playing Wav Files Via the Windows API

I entered the code for playing these files in a new standard module named General. (I used a new module to make it easy to export this code to other VBA projects.) The code is very simple, consisting of the declarative statement for the API function and a short sub procedure with one argument representing a file path. The PlayWav() sub procedure consists of one line of code that calls the sndPlaySoundA() API function passing the file path to the .wav file and the value for the uFlags argument (0 indicates that program execution will pause while the sound file is playing). The PlayWav() sub procedure is called when the user selects a target to fire at and when the game ends (see the PlayerFire(), HitOrMiss(), and ComputerFire() procedures).

Option Explicit

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

Public Sub PlayWav(filePath As String)

sndPlaySoundA filePath, 0 End Sub

The .wav files used in the Battlecell program are courtesy of http://www .a I

You can see how easy it is to play sound files using the Windows API. Although programming via the Windows API is an advanced technique, there really is nothing simpler for the VBA programmer to use for playing sound files.

This concludes the Battlecell program. The program is not terribly long or complex, but is starting to approach a level of programming that makes the game fun even for adults. The intention of the program is to help you get comfortable using VBA objects and navigating through Excel's object hierarchy. The Range object is used extensively in the Battlecell program and that will be typical of the VBA programs you write. The use of Workbook and Worksheet object event procedures is also prevalent in the Battlecell program. To take full advantage of the power of VBA, you should get comfortable identifying and using these procedures.

This chapter represents a critical phase in your development as a VBA programmer. Understanding objects and their role in creating dynamic and powerful applications is critical in any programming language including VBA.

In this chapter, we learned how to use several of Excel's top-level objects and how to navigate through its object model. Specifically, you looked at the Application, Workbook, Window, Worksheet, and Range objects in detail. Some of the event procedures, methods, and properties of these objects were also introduced.

Next, you learned about some of the tools available in VBA for working with objects. This included the Object Browser for navigating through the object hierarchy and getting fast help to an object of interest. The With/End With code structure, object data type, and For/Each loop were also introduced.

Finally, the Battlecell program illustrated a practical and fun programming example that relied heavily on Excel's top-level objects. As there is a tendency for such things to occur, a few subordinate objects also appeared in the program.

Biorhythm Awareness

Biorhythm Awareness

Who else wants to take advantage of biorhythm awareness to avoid premature death, escape life threatening diseases, eliminate most of your life altering mistakes and banish catastrophic events from your life.

Get My Free Ebook


Post a comment