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)

Hiding Text in Microsoft Word

Here is a short tutorial with pictures (because pictures speaks a thousand words!) on how to hide texts from being printed/ viewed by people. If you can't see the picture clearly, click on them to get a bigger/larger image of the selected picture! :)

1. Select the text that you want to hide
   
2. Go to the Font options using either method


• Right Click the selected text > Font
  
• Click the "more options" in the Font group under the home tab
  
3. Tick the "Hidden" checkbox
   

Now your selected text will be hidden from normal and everyday users!




BUT WAIT!!! What if you want to view the text for editing purposes or to show it?

1. Enable the special characters options using either method
• Press the button at the top right hand of the paragraph group under the home tab
  
• Go to File > Options > Display > tick the "Hidden text" under the "Always show these formatting marks on the screen"
  
  
2. You will notice that hidden texts will have dotted lines underneath the words. Now you can edit the text without unhiding it. If you want to unhide it, untick the "Hidden" option in the fonts options again
   
   

Cheers!

Jokes

MANAGEMENT COURSE!

Lesson 1:

A man is getting into the shower just as his wife is finishing up her shower, when the doorbell rings.

The wife quickly wraps herself in a towel and runs downstairs.

When she opens the door, there stands Bob, the next-door neighbour.

Before she says a word, Bob says, 'I'll give you $800 to drop that towel.'

After thinking for a moment, the woman drops her towel and stands naked in front of Bob, after a few seconds, Bob hands her $800 and leaves.

The woman wraps back up in the towel and goes back upstairs.

When she gets to the bathroom, her husband asks, 'Who was that?'

'It was Bob the next door neighbour,' she replies.

'Great,' the husband says, 'did he say anything about the $800 he owes me?'

Moral of the story:

If you share critical information pertaining to credit and risk with your shareholders in time, you may be in a position to prevent avoidable exposure.

-------------------------------------------------------------------------

Lesson 2:

A priest offered a Nun a lift.

She got in and crossed her legs, forcing her gown to reveal a leg.

The priest nearly had an accident.

After controlling the car, he stealthily slid his hand up her leg.

The nun said, 'Father, remember Psalm 129?'

The priest removed his hand. But, changing gears, he let his hand slide up her leg again.
The nun once again said, 'Father, remember Psalm 129?'

The priest apologized 'Sorry sister but the flesh is weak.'

Arriving at the convent, the nun sighed heavily and went on her way.

On his arrival at the church, the priest rushed to look up Psalm 129. It said, 'Go forth and seek, further up, you will find glory.'

Moral of the story:
If you are not well informed in your job, you might miss a great opportunity.

-------------------------------------------------------------------------

Lesson 3:

A sales rep, an administration clerk, and the manager are walking to lunch when they find an antique oil lamp.
They rub it and a Genie comes out.
The Genie says, 'I'll give each of you just one wish.'
'Me first! Me first!' says the admin clerk. 'I want to be in the Bahamas , driving a speedboat, without a care in the world.' Puff! She's gone.

'Me next! Me next!' says the sales rep. 'I want to be in Hawaii , relaxing on the beach with my personal masseuse, an endless supply of Pina Coladas and the love of my life.' Puff! He's gone.

'OK, you're up,' the Genie says to the manager.
The manager says, 'I want those two back in the office after lunch.'

Moral of the story:
Always let your boss have the first say.

-------------------------------------------------------------------------

Lesson 4

An eagle was sitting on a tree resting, doing nothing.

A small rabbit saw the eagle and asked him, 'Can I also sit like you and do nothing?' The eagle answered: 'Sure, why not.'

So, the rabbit sat on the ground below the eagle and rested. All of a sudden, a fox appeared, jumped on the rabbit and ate it.

Moral of the story:
To be sitting and doing nothing, you must be sitting very, very high up.

-------------------------------------------------------------------------

Lesson 5

A turkey was chatting with a bull.

'I would love to be able to get to the top of that tree' sighed the turkey, 'but I haven't got the energy.'
'Well, why don't you nibble on some of my droppings?' replied the bull.. They're packed with nutrients.'

The turkey pecked at a lump of dung, and found it actually gave him enough strength to reach the lowest branch of the tree.

The next day, after eating some more dung, he reached the second branch.

Finally after a fourth night, the turkey was proudly perched at the top of the tree.

He was promptly spotted by a farmer, who shot him out of the tree.

Moral of the story:
Bull shit might get you to the top, but it won't keep you there..



Lesson 6


A little bird was flying south for the winter. It was so cold the bird froze and fell to the ground into a large field.

While he was lying there, a cow came by and dropped some dung on him.

As the frozen bird lay there in the pile of cow dung, he began to realize how warm he was.

The dung was actually thawing him out!

He lay there all warm and happy, and soon began to sing for joy.

A passing cat heard the bird singing and came to investigate.

Following the sound, the cat discovered the bird under the pile of cow dung, and promptly dug him out and ate him.


Morals of the story:
(1) Not everyone who shits on you is your enemy.

(2) Not everyone who gets you out of shit is your
friend.

(3) And when you're in deep shit, it's best to keep
your mouth shut!


THUS ENDS THE FIVE MINUTE MANAGEMENT COURSE

Jokes

Working people frequently ask retired people what they do to make their days interesting.
Well, for example, the other day my wife and I went into town and went into a shop.
We were only in there for about 5 minutes.
When we came out, there was a cop writing out a parking ticket.
We went up to him and said, 'Come on man, how about giving a senior citizen a break?'
He ignored us and continued writing the ticket. I called him a Nazi turd.... He glared at me and started writing another ticket for having worn tyres.
So my wife called him a shit-head. He finished the second ticket and put it on the windshield with the first. Then he started writing a third ticket. This went on for about 20 minutes. The more we abused him, the more tickets he wrote.
Personally, we didn't care. We came into town by bus. We try to have a little fun each day now that we're retired. It's important at our age.