Playing a sound file using VBA

I recently received a request from a client to add a specific sound when displaying a message box warning.  I knew I’d done this before, and from memory knew it was fairly simple.  So, after ploughing through some old code I came across the code I have put below.  I hope it proves useful to you.

The code uses a call to an API, below.  The API code needs to be copied and pasted to the top of your module code.

' This is required to play a .wav sound with VBA
Public Declare Function sndPlaySound32 _
    Lib "winmm.dll" _
    Alias "sndPlaySoundA" ( _
        ByVal lpszSoundName As String, _
        ByVal uFlags As Long) As Long

Below is the routine which plays the sound, this can be placed in a utilities module and called by various routines, each passing in the name of the sound file you want to be played.

Windows has a folder full of .wav files, they can be found here; C:\Windows\Media.  The routine below allows you to pass in the full file path of a music file, or just the filename.  If you pass in a filename the routine assumes you want to play a Windows default sound and guesses the file path.

Public Sub PlaySound(ByVal SoundFileName As String)
    If Dir(SoundFileName, vbNormal) = "" Then
        ' SoundFileName is not a file. Get the file named by
        ' SoundFileName from the Windows\Media directory.
        SoundFileName = Environ("SystemRoot") & "\Media\" & SoundFileName
        If InStr(1, SoundFileName , ".") = 0 Then
            ' If SoundFileName does not have a .wav extension, add one.
            SoundFileName = SoundFileName & ".wav"
        End If
        If Dir(SoundFileName, vbNormal) = vbNullString Then
            ' Can't find the file. Just use a beep.
            Beep
            Exit Sub
        End If
    Else
        ' SoundFileName is a file, Use it.
    End If
    ' Play the sound, before continuing code
    sndPlaySound32 SoundFileName, 0&
    ' Play sound and continue code.
    sndPlaySound32 SoundFileName, 1&)
End Sub

I have included two lines at the bottom of the code, you can comment out which-ever you choose not to use.  The first plays the sound before allowing the code to continue, the second plays the code and allows the code to continue.  This means you can play a specific sound as you display a message box for example.  Have a play, see which way suits you best.

As always, if you have any questions do let us know via the comments section.

Do… Loop and For… Next in VBA

Loop statements are used to get Excel to repeat a piece of code a certain number of times. The number of times the code gets repeated can be specified as a fixed number (e.g. do this 10 times), or as a variable (e.g. do this until there are no more rows of data).

The are several ways in which loops can be constructed, depending on the circumstances in which they are going to be used.  Quite often the wanted result can be obtained in different ways, depending on your preferences.

There are two basic kinds of loops, examples of both: Do…Loop and For…Next loops. The code to be repeated is placed between the key words.

For our first example open a new workbook and place the numbers 1 to 10 in cells A1 through to A10. 

We are going to loop down the list, adding 5 to the number in column ‘A’.

Now open the VBE, add a new module and start a new sub routine call MyLoopStatement.  Copy the code below into your new routine.

Public Sub MyLoopStatement()
    Range("A1").Select 

    Do While IsEmpty(ActiveCell) = False 
        ActiveCell.Offset(0, 1).FormulaR1C1 = "=SUM(RC[-1]+5)" 
        ActiveCell.Offset(1, 0).Select 
    Loop
End Sub 

 

This routine loops down the list placing a formula in each of the cells to the right of the  activecell.  It will continue WHILE the active cell is not empty.

The next example uses Do until.

    Range("A1").Select 
    Do
        ActiveCell.Offset(0, 1).FormulaR1C1 = "=SUM(RC[-1]+5)"
        ActiveCell.Offset(1, 0).Select
    Loop Until IsEmpty(ActiveCell)

This example loops until it finds a blank cell.  It is generally considered better practise to use Do Until, rather then Do While.  Both of the above examples runs the code within the loop at least once before testing whether to move on.  You could write the same Do Until / Do While loops in the following way. 

    Range("A1").Select
    Do Until IsEmpty(ActiveCell)
        ActiveCell.Offset(0, 1).FormulaR1C1 = "=SUM(RC[-1]+5)"
        ActiveCell.Offset(1, 0).Select
    Loop

Moving the test to the start of the loop means it is carried out before carrying out any of the instructions within the loop.  This could be useful if, for example, sometimes cells A1:A10 were blank.

if you know or can use VBA to find out how many times you want to repeat the code you can use a For… Next loop.

In the following example we know that we have 10 rows of data, so we can tell the code to loop 10 times.

    Dim l As Long
    Dim lRowCount As Long

    lRowCount = Range("A1").CurrentRegion.Rows.Count - 1
    ' You could also try
    ' lRowCount = Cells.SpecialCells(xlCellTypeLastCell).Row

    For l = 1 To lRowCount
        ActiveCell.Offset(0, 1).FormulaR1C1 = "=SUM(RC[-1]+5)"
        ActiveCell.Offset(1, 0).Select
    Next l

For… Next loops are particularly good a looping through objects.

    Dim wb As Workbook
    Dim ws As Worksheet

    Set wb = ThisWorkbook

    For Each ws In wb.Worksheets
        Debug.Print ws.Name
    Next ws

The above code would be quicker then:

    Dim l As Long
    Dim wb As Workbook

    Set wb = ThisWorkbook

    For l = 1 To wb.Worksheets.Count
        Debug.Print wb.Worksheets(l).Name
    Next l

As always, I hope the above examples have been helpful.  If you have any questions or comments please do let us know.

Working with Collections

By using Collections within VBA you can store multiple objects, similar to an array.  The key advantage with Collections is that the objects can be added, removed and used by referring to the objects ‘Key’, rather then having to loop through an array.

In this blog post I am going to use the example code provided in my previous blog post Working with Class Modules.

Within the class module cls_Employee we have the following properties, Name, Department, Age and Salary.  In this blog post I will run though how to create multiple employee class objects and add them to a collection.  We will then loop through the collection and retrieve and remove an employee.

To store multiple instances of a class, in this example a group of employees, create several instances of the class and load them to a collection as follows.

    Dim clsEmp As cls_Employee
    Dim colEmp As Collection

    Set colEmp = New Collection

    ' Loop through your existing list
    For Each Item In SomeList

        ' Create a new instance of the employee class
        clsEmp = New cls_Employee

        ' Set the properties of the class
        ' As per the example in the previous blog post

        'Load the class to the collection, _
         using the employee's name as the 'key'
        colEmp.Add clsEmp, clsEmp.Name

    Next Item

Now, you can use a simple For Each loop to loop through the collection and iterate through the collection and access each instance of cls_Employee sequentially:

    Dim clsEmp As cls_Employee
    ' Loop through a collection to retrieve each item
    For Each clsEmp In colEmp
        Debug.Print clsEmp.Name
        Debug.Print clsEmp.Dept
        Debug.Print clsEmp.Age
        Debug.Print clsEmp.Salary
    Next clsEmp

To retrieve or remove a specific employee you can use the object’s ‘key’ to refer to it.

    ' Use the key to retrieve a specific employee
    Dim clsEmp As cls_Employee
    clsEmp = colEmp("Fred Smith")

    ' Use the key to remove a specific employee
    Dim clsEmp As cls_Employee
    colEmp.Remove "Fred Smith"

Something worth noting is that the key needs to be a text value.  Other values can be used but may require the Str() function wrapped around them.

Excel Picture Quiz 3

Right, so finally to stage 3, adding the counter and locking the cells. First choose where you are going to put the counter, and use the following formula;

=countif(B5:J17, "correct")

Again changing the italicised bits to fit your quiz;

B5:j17 - the range of cells where the answers are marked
correct - or whatever you have set your quiz to say for correct answers





The final stage is to set the cells you want people to put their answers into as unlocked so we can protect the sheet and stop anyone from checking the formulas to get the answers.

To do this either select all the answer cells using the control button, or one at a time right click and select properties, and uncheck the locked setting under protection











Finally protect the sheet; select 'Protect Sheet' from the Review ribbon, you can set a password if you wish. This will mean you can only select the cells we set as unlocked.

Once you have selected ok your quiz is finished.

I'll upload a copy of my incomplete version here for anyone to play with, please let me know if you have any hints or tips to make this better.

Thanks
Dan

Working with Class Modules

I’d been working with Excel and VBA for more then 6 years before I caught on to just how useful class modules can be.  Class modules can be a powerful tool used within intermediate to advanced level VBA programming.  Below is something I hope you will find a useful introduction to how to use them.

This example does assume some basic knowledge of the VBE (Visual Basic Editor).

We’ll start where almost all examples for class modules start; with an employee, the employee is called Fred Smith (original, I know), works in accounts, is 25 and earns £22k.

To start, add a new class module to your VBA project.  For this example change the name from Class1 to cls_Employee.  Do this by editing the (Name) item in the ‘Properties’ list for the class module.

You then need to create the variables which are going to be used within the class module, since they are declared Private, they are not available outside of the class module.

Option Explicit

Private strName As String
Private strDept As String
Private lngAge As Long
Private dblSalary As Double

Now we need to add the property procedures which allow us to write to and read from the above variables.  To do this we need Property Get and Property Let functions.  (Property Set is used for objects).

' Name property
Public Property Get Name() As String
    Name = strName
End Property
Public Property Let Name(value As String)
    strName = value
End Property

' Department property
Public Property Get Dept() As String
    Dept = strDept
End Property
Public Property Let Dept(value As String)
    strDept = value
End Property

' Age property
Public Property Get Age() As Long
    Age = lngAge
End Property
Public Property Let Age(value As Long)
    lngAge = value
End Property

' Salary property
Public Property Get Salary() As String
    Salary = dblSalary
End Property
Public Property Let Salary(value As String)
    dblSalary = value
End Property

The Let procedure is used to assign values to the class. The get procedure is used to get the values from the class.  Please note that the data types must be the same for the ‘value’ be passed through and the Get procedure.

A property can be made read-only simply by omitting the Let procedure. For example, a read-only property might be tax, which is calculated when it is called. E.g.,

'Tax property
Public Property Get Tax() As Double
    Tax = Calculate value
End Property

Class modules can also contain sub routines and functions, such as the PrintEmployeeData below.

' Print employee data routine
Public Sub PrintEmployeeData()
    ' You code appears here
End Sub

Now that we have the class module ready we can create objects based on the class module.  Add a standard code module to your project.  Within a new sub routine declare a new variable as type cls_Employee like the example below.

Option Explicit

Sub AddEmployee()
    Dim clsEmp As cls_Employee

End Sub

ClsEmp has to be set as a new instance of the the class before we can assign values to it’s properties, like below.

    Set clsEmp = New cls_Employee
    clsEmp.Name = "Fred Smith"
    clsEmp.Dept = "Accounts"
    clsEmp.Age = 22 
    clsEmp.Salary = 25000

You can retrieve the data value from the class using code similar to the below example.

    MsgBox "Employee Name: " & clsEmp.Name;

I hope this has helped with your understanding of class modules within VBA.  The above example code will work in Excel and Access.

I would like to add that this blog post and code examples, although my own, is based a on the article from which I learned about class modules.  That article can be found at Chip Pearson’s website: http://www.cpearson.com/. 

In my next blog post I will cover how to store multiple objects (employees) in a collection.

Excel Picture Quiz stage 2

In stage 2 we get the quiz to mark the questions, there are several ways to do this however for this tutorial I am going to use an IF statement. This allows us to have two responses, correct and incorrect, these can be substituted for your own message.

I am going to have the answer in cell B4 and the responce in B5, so the code for B5 is

=IF(B4="Johnny Depp", "Correct", "Wrong")

the italicised parts are the ones you will have to change for each picture.

B4 - the cell reference for the answer you are checking.

"Johnny Depp" - the correct answer.

It is important to note that for the counter I am going to create later to work the 'correct' message must be the same for every picture.

Set the answer cells to change colour using conditional formatting, in Office 2007 this is to the right on the home tab.











Simply set the cell to change colour when the cell is equal to your 'correct' message.

Excel Picture Quiz

So I thought it was about time I put something on here and I thought what better than to start with some instructions for creating a simple and fun picture quiz in Excel.

The instructions presume you have some basic knowledge of Excel to start with, but if there are any steps which you want explaining in more detail feel free to contact me using the quick contact on my website www.zypher.co.uk

I will split this into the following stages / lessons

1. Set out the quiz
2. Add functions and conditional formatting
3. Add a score counter
4. block access to the answers

We start by setting up the layout, set the column widths so you have a thin column followed by a wider one and repeating across the screen, you should start and end with thin columns. The wider columns will hold the images so set these appropriately.

Next set the height of some of the rows to allow them to hold the images, leaving at least 2 rows normal height, this is where the answer and correct or incorrect message will go.

Set the background colour of the cells and add a title to the centre of your top row.

Insert your images into the larger cells and add instructions to the side, I have used a text box though you could just as easily merge some cells for this.