Playing WAV files using VBA

Playing WAV sound files using with VBA. This method makes use of the windows winmm.dll file to play a specific wav file. An example that VBA is able to utilize windows libraries to improve productivity of Microsoft Office Tools.

'Declare this part at the top of the Module
Public Declare Function sndPlaySound Lib "winmm.dll" _
Alias "sndPlaySoundA" (ByVal lpszSoundName As String, _
ByVal uFlags As Long) As Long

'Method to play wav file
Sub PlayWavFile(WavFileName As String, Wait As Boolean)
'Check if there is a valid file
If Dir(WavFileName) <> "" Then
'Pause all running codes and play the sound
If Wait Then
sndPlaySound WavFileName, 0
'Play sound concurrently with the running code
Else
sndPlaySound WavFileName, 1
End If
'If the input was to stop the sound, this should not be able to overlap over any
'sound file as a sound file has to have a .wav extension in order to be played
ElseIf WavFileName = "Stop" Then
'Play an empty file
sndPlaySound " ", 1
End If
End Sub

Parameters:
WavFileName -- The location of the wav sound file that you want to play. You can utilize relative paths to find the sound file (ThisWorkbook.Path, ThisDocument.Path, etc.).
(Input "Stop" to stop the currently playing sound, or play another sound to cut of the currently playing sound)
Wait -- This will decide how the sound file be played. True means that all codes will be paused and the sound file will be played whereas False will play the sound as the code runs.

To use this method, simply call the PlayWavFile method

Some examples:
PlayWavFile "C:\MyWaveFileLocation.wav", True
PlayWavFile ThisWorkbook.Path & "\WavFileBesideExcelFile.wav", False
PlayWavFile "Stop" False

Connecting to MS Access Database using VBA (ADODB)

VBA is able to connect to an Access database to retrieve data using Access SQL Queries. Here is a step-by-step tutorial on how to do it.

Firstly you have to add a reference in your VBA

1. Go to your Visual Basic Editor > Tools > Reference
   
2. Tick the reference "Microsoft ActiveX Data Objects 6.1 Library" (Depending on your Microsoft Office version, the '6.1' might be a bigger or smaller number. Find a reference similar to the example above)
   
3. Press the OK button

Now here is the code you can use to connect to the Access database (you can paste it in a newly created module; right click any sheet > Insert > Module):

Sub DatabaseWithVBA()
'Handle any errors such as query syntax or file not found errors and displays it
On Error GoTo errorhandler

Dim connection As ADODB.connection
Dim query As ADODB.Command
Dim queryResult As ADODB.Recordset
Dim AccessDatabasePath As String
Dim SQLquery As String

'Fill in these properties to connect to the Access Database
'You can either specify the path relatively to the document or type in the absolute path
AccessDatabasePath = "<<Location of your Access Database File>>"
SQLquery = "<<Your SQL Query>>"

'Connect to the Access database
Set connection = New ADODB.connection
connection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & AccessDatabasePath & ";"
connection.ConnectionTimeout = 30 'Close the connection if it takes more than 30 seconds
connection.Open
Set query = New ADODB.Command
query.ActiveConnection = connection

'Execute and save the results of the query into queryResult
query.CommandText = SQLquery
Set queryResult = query.Execute

'Go through the retrieved data and manipulate it accordingly
Do Until queryResult.EOF
'Manipulate your data here! (Example prints the value of a selected column)
MsgBox queryResult.Fields("<<Column Name>>")
'Move to the next row
queryResult.MoveNext
Loop

'Close connections
If connection.State <> adStateClosed Then
connection.Close
End If
If Not query Is Nothing Then
Set query = Nothing
End If
If Not connection Is Nothing Then
Set connection = Nothing
End If

errorhandler:
If Err.Number <> 0 Then
MsgBox "Error number: " & Err.Number & vbNewLine & Err.Description
End If
End Sub

The main things you need to edit are:

• The location of the database access path
• The query to run after connecting to the database
• Column Names

Hope this has helped you on improving your productivity with Microsoft Office Tools!

Random Number Generator with VBA

Ever wanted to generate a number with a range? Here is the code snippet to do it!

Function GenerateRandomNumber(LowerNumber As Integer, UpperNumber As Integer) As Integer
Randomize
GenerateRandomNumber = Int((UpperNumber - LowerNumber + 1) * Rnd + LowerNumber)
End Function

To use this function, simply paste the method in a module and call that method when assigning a variable a random number.

Here is an example to assign x to a random number between 1 to 10

Function GenerateRandomNumber()....

Sub PrintRandomNumber()
Dim x As Integer
x = GenerateRandomNumber(1, 10)
End Sub

Change Chart Axis Properties using VBA

Here is the code snippet to change the minimum/ maximum values of the chart as well as the major/minor units

Sub ChangeChartAxis()
With ActiveSheet.ChartObjects("<<Chart Name>>").Chart.Axes(xlValue)
.MaximumScale = 5000
.MinimumScale = 0
.MajorUnit = 500
.MinorUnit = 100
End With
End Sub

You can edit the numbers accordingly or specify the sheet instead of using ActiveSheet
e.g. Instead of modifying the chart in the activesheet, you might want to edit the chart in Sheet 2, so change ActiveSheet to Sheet2.
If you are using a Word Document, you can change it to ActiveDocument or some sort. As long as that line manages to select the chart, this VBA code should work

To find the chart name:

1. Click on the chart you want to edit, you will notice that more tabs will appear with a "Chart Tools" header above.
2. Click the layout tab in the chart tools ribbon
3. You will see the chart name on the ride side of the ribbon in the "Properties" group
4. A picture if you still don't understand where to find it! (click on it to zoom in)