Musical Excel
I found some code that uses API functions to play MIDI music at a French site. I adapted the code so it's easy to use. Copy the code below and paste it into a VBA module.
Private Declare Function midiOutOpen Lib "winmm.dll" _
(lphMidiOut As Long, _
ByVal uDeviceID As Long, _
ByVal dwCallback As Long, _
ByVal dwInstance As Long, _
ByVal dwFlags As Long) As Long
Private Declare Function midiOutClose Lib "winmm.dll" _
(ByVal hMidiOut As Long) As Long
Private Declare Function midiOutShortMsg Lib "winmm.dll" _
(ByVal hMidiOut As Long, _
ByVal dwMsg As Long) As Long
Private Declare Sub Sleep Lib "Kernel32" (ByVal dwMilliseconds As Long)
Dim hMidiOut As Long
Public lanote As Long
Sub PlayMIDI(voiceNum, noteNum, Duration)
Dim Note As Long
On Error Resume Next
midiOutClose hMidiOut
midiOutOpen hMidiOut, 0, 0, 0, 0
midiOutShortMsg hMidiOut, RGB(192, voiceNum - 1, 127)
lanote = 12 + CLng(noteNum)
Note = RGB(144, lanote, 127)
midiOutShortMsg hMidiOut, Note
Sleep (Duration)
midiOutClose hMidiOut
End Sub
The PlayMIDI Sub procedure accepts three arguments, and plays a single note. The argument are:
- voiceNum: A number from 1-128 that represents the instrument sound. Here's a list of the MIDI voice numbers.
- noteNum: A number that indicates the note to play. For reference, C is 0, 12, 24, 36, etc. C# is 1, 13, 25, 37, etc.
- Duration: A number that indicates how long to play the note, in milliseconds (1,000 equals 1 second).
To play around with this, I set up a worksheet that has a 4-column list of notes. A lookup table provides the actual note letters for the values in column B. In the figure, I have it set up to generate random notes and durations. Then, a simple macro plays the song.
Then, a simple macro plays the song represented by the worksheet data.
Dim r As Long
ActiveSheet.Calculate
For r = 2 To Application.CountA(Range("A:A"))
Call PlayMIDI(Cells(r, 1), Cells(r, 2), Cells(r, 3))
Next r
End Sub
By the way, I have no idea how this code works. Using the RGB function is a mystery to me. One final note. Avoid stopping the code by pressing Ctrl+Break. If you do that, you may get a stuck note that requires closing Excel.
chip:
This is really interesting and a terrific opportunity for practical jokes, if nothing else.
My guess on the RGB function: it returns a Long integer, and that's getting passed as the note to play, so it has to be that the musical notes for MIDI are designed to correspond to the RGB numbers used to display video. Knowing little about music and less about MIDI (other than those annoying background midi's that drive me away from a web site faster than nearly anything else,) that's my best guess.
6 February 2007, 2:25 pmJason B:
RGB returns a long I think by doing some bit shifting and AND-ing. It is probably used here because the MIDI dwMsg arguement is supposed to be a 32 bit number between some apparently obscure range of values.
Interesting find J-walk. I am sort of curious as to what our banjo lovin' compadre has composed in this example... not interested enough to duplicate it yet. Any takers on relieving me from my laziness?
Jason
6 February 2007, 3:46 pmRJS:
True to his word (or post text) the tune is random notes and durations. If someone implements with the random number function and hits F9 enough, one might eventually generate something recognizable. How many spreadsheet programmers does it take to compose Beetoven's Fifth?
6 February 2007, 4:30 pmJohn Walkenbach:
I only had to hit F9 seven or eight times before it generated the chorus to "Oops I Did It Again."
6 February 2007, 8:24 pmchip:
John, that's priceless!
6 February 2007, 9:05 pmMikeC:
So now we know how Britney did it first time round! Thanks for clearing up that mystery J-Walk....
I'm with Chip on thinking of practical jokes.... setting it to run on opening... *grin*
7 February 2007, 4:47 amSimon Herbert:
How about:
NoteNum Duration
7 February 2007, 7:07 am48 450
55 450
53 150
52 150
50 150
60 450
55 450
53 150
52 150
50 150
60 450
55 450
53 150
52 150
53 150
50 400
John Walkenbach:
Here's a little bit of "Dueling Banjos." 106=banjo, 26=guitar
106 47 200 B
7 February 2007, 9:26 am106 48 200 C
106 50 250 D
106 47 200 B
106 48 200 C
106 45 200 A
106 47 200 B
106 43 200 G
106 45 500 A
26 47 200 B
26 48 200 C
26 50 250 D
26 47 200 B
26 48 200 C
26 45 200 A
26 47 200 B
26 43 200 G
26 45 500 A
106 43 150 G
106 43 150 G
106 43 150 G
106 45 200 A
106 47 200 B
106 48 200 C
106 50 200 D
106 48 200 C
106 47 600 B
26 43 150 G
26 43 150 G
26 43 150 G
26 45 200 A
26 47 200 B
26 48 200 C
26 50 200 D
26 48 200 C
26 47 500 B
Opera Lover:
I find this code funny
Never imagined this possibility in Excel.
But, I'm a musician and I know something from MIDI. If you want to know the meaning of all these RGB() calls, this is the explanation:
All Midi communication is made of short messages sent from the computer to the Synthesizer (in this case Windows MM itself). And they all have the following syntax:
[Status Byte] + [1 or more Data Bytes]
When you put: RGB(192,voiceNum-1,127) this translates to this 3 bytes:
11000000 + 0xxxxxxx + 01111111
This seems to be a "Midi Program Change on Channel #0"
Syntax is: 1100nnnn (nnnn -> channel number in bin)
0ppppppp (ppppppp -> Program Change in bin)
(the last "01111111" is not needed, not nice but it works)
So, you start the MidiInterface by sending it:
"Change program on channel #0 to ...", this tells it to change the voice number on the channel #0 (by the way, Midi standard has 16 channels, so it is possible to make a polyphonic melody in Excel)
Then, when you put RGB(144, lanote, 127) this translates to:
10010000 + + 0111111
This is a Note On Event.
Syntax is: 1001nnnn (nnnn -> channel number in bin)
0kkkkkkk (kkkkkkk -> key number in bin)
0vvvvvvv (vvvvvvv -> velocity in bin)
If you want to clean up the code (no need to close MIDI on each note, only at the end), you can implement easily:
Note Off event.
Syntax is: 1000nnnn (nnnn -> channel number in bin)
0kkkkkkk (kkkkkkk -> key number in bin)
0vvvvvvv (vvvvvvv -> velocity in bin)
with RGB(128,lanote, 127)
If you want to know more, all MIDI info on:
7 February 2007, 10:08 amhttp://www.midi.org/about-midi/table1.shtml
DUBOURJALE Gilbert:
Hello John ...
I am so proud to found here, some of my codes ...
but for information I only use code found in apiguide.exe
Thanks to http://www.allapi.net
As I did for your "Hypocycloids" ,
I have also a little tilted your "Guitar Scales and modes" ;o)))
you can find a sound enhancement of it, in guitar97.xls
wich is in this zip :
http://excelabo.net/moteurs/compteclic.php?nom=gd-instruments
Click on Frets and tune your guitar !!!!
Click on your picture ;o))) there is a play of Narcisso Yepes
translations are not finalised, some bug remains...
There is also Virtualdrummer, I already sent you some years ago.
I am retired now, thought i don't have really time to improve my Excel skillness.
Improvements of those pieces of code are not ended, so they are not protected
and free for everybody.
Cherrio !!!
7 February 2007, 6:00 pmchip:
Opera Lover, that's a helpful start on MIDI details, but why the RGB calls? Why does what normally calculates a color space work to calculate MIDI notes?
And more importantly, I'm taking my wife to Lohengrin in a couple of weeks. What does one wear to the opera these days?
7 February 2007, 6:03 pmJohn Walkenbach:
Thanks, Opera Lover. Believe it or not, I used to know all of that stuff. Well, most of that stuff. I once sold a software product that managed sound patches for a Roland JV-80 synth. I wrote it in Visual Basic, and I think I sold about 10 copies. But I sure learned a lot programming it. And I've now forgotten 90% of what I learned.
chip, regarding your wardrobe question. I also know a bit about dressing. My advice? You just can't go wrong with a top hat and tails. Trust me on this, OK?
7 February 2007, 6:18 pmJohn Walkenbach:
Holy crap! The internet is forever. I eventually converted my MIDI software to freeware, and it's still available on the Web:
http://www.geocities.com/mstella/jvedit/jvedit.html
And for those who are too lazy to copy/paste the code listed here, you can download the workbook here:
http://j-walkblog.com/index.php?/weblog/posts/dueling_banjos_in_excel/
And there's also a little contest going on for the best Excel MIDI song.
7 February 2007, 6:26 pmKevin Roth:
Lots of fun.
So, to incorporate Opera Lover's comments:
VBA Module:
Sub OpenMIDI()
midiOutOpen hMidiOut, 0, 0, 0, 0
End Sub
Sub PlayMIDI(voiceNum, noteNum, Duration)
On Error Resume Next
midiOutShortMsg hMidiOut, RGB(192, voiceNum - 1, 127) 'Program Change
midiOutShortMsg hMidiOut, RGB(144, noteNum + 12, 127) 'Note On
Sleep Duration
midiOutShortMsg hMidiOut, RGB(128, noteNum + 12, 127) 'Note Off
End Sub
Sub CloseMIDI()
Sleep 250
midiOutClose hMidiOut
End Sub
ThisWorkbook:
On Error GoTo EndSub
Dim r As Long
ActiveSheet.Calculate
OpenMIDI
For r = 2 To Application.CountA(Range("A:A"))
Call PlayMIDI(Cells(r, 1), Cells(r, 2), Cells(r, 3))
Next r
EndSub:
CloseMIDI
End Sub
chip:
John, great news on the top hat and tails. The local Al's Formalwear has a set in my size in powder blue, and it's a steal of a rental! Think that'll work?
8 February 2007, 12:43 amMpemba:
My immediate change to the code was to add a volume column (D):
Sub TestMidi()
Dim r As Long
'ActiveSheet.Calculate - I commented this out as really could not see point
For r = 2 To Application.CountA(Range("A:A"))
Call PlayMIDI(Cells(r, 1), Cells(r, 2), Cells(r, 3), Cells(r, 4))
Sub PlayMIDI(voiceNum, noteNum, Duration, Optional Volume As Long = 127)
Note = RGB(144, laNote, Volume) '144 = h90 = "Note On Channel 0"
midiOutShortMsg(hMidiOut, Note),
Sleep (Duration)
Note = RGB(144, laNote, 0)
midiOutShortMsg hMidiOut, Note
'I was getting some obscure hung notes too so added declaration to another WMI function:
Private Declare Function midiOutReset Lib "winmm.dll" _
(ByVal hMidiOut As Long) As Long
'And put this after the on error resume next line:
midiOutReset hMidiOut
Now I just need to get it to play chords!!!!
8 February 2007, 3:57 amMpemba:
I should add for people not familiar with the MIDI standard:
Volume should be an integer between 0 and 127 inclusive
M
8 February 2007, 4:21 amBarry Downes:
John,
What a neat discovery you made as a means to handle music playback via an Excel worksheet. I tried it out and found the one other thing needed was to be able to enter rests between notes where needed. I found using 129 as the note number (1 number out of range) accomplished it. Adding whatever duration needed and it will provide the proper rest between notes.
Thanks again for your having made the discovery on the French site and adapting the code to make it easier to use.
8 February 2007, 9:47 amMichael:
Guys -
I can't get it to run twice. Have to Exit Excel to play it again. I downloaded the sheet from J-Walk and it's slightly different.
I've made Kevin's changes and picked up Stereo! Now why?
And into whose code do Mpemba's mods go?
...Michael
8 February 2007, 4:49 pmMichael:
Oh fudge...I missed John's caution...
...mrt
8 February 2007, 4:50 pmJohn Walkenbach:
The latest version is here:
http://j-walkblog.com/docs/midi-player.xls
This incorporates a lot of good ideas from Chip and others who have commented here. Plus a few of my own.
New features include:
* Easier data entry (use note names rather than numbers)
* Easier duration entry
* Ability to select instrument by name
* Ability to transpose the key
* Ability to change the tempo
* Ability to add a rest
* A Stop! button which lets you stop the music and prevent those hung notes.
* And you can make changes while the music is playing.
I haven't added the volume feature yet. It's still very rough, but I think it's time for someone else to take over and add some more stuff to it.
This is turning into a fun little project.
8 February 2007, 5:58 pmJohn Walkenbach:
I didn't review all of the comments here very carefully. There are some other good suggestions that need to be added. I'll pass the baton to someone else for now.
8 February 2007, 6:17 pmMpemba:
I can't get it to run twice. Have to Exit Excel to play it again. I downloaded the sheet from J-Walk and it's slightly different.
I've made Kevin's changes and picked up Stereo! Now why?
And into whose code do Mpemba's mods go?
The mods go into the original post: directly.
Using my system reset line (having declared the function first)
midiOutReset hMidiOut
should cure your not being able to run it twice problem
FOR RESTS:
use my "volume" version and set vol to 0 will give you a rest for sure
M
9 February 2007, 3:25 amMichael:
M -
I get a type mismatch error on this line:
Call PlayMIDI(Cells(r, 1), Cells(r, 2), Cells(r, 3), Cells(r, 4))
I presume it's in Cells(r, 4) but I don't see why
...mrt
9 February 2007, 3:23 pmchip:
Michael, that looks like the old version, so download the new one. (John comment #21 has the link). But it probably indicates trying to pass a string value instead of a number to the midi routine. Is there a text value in column D where there should only be numbers?
9 February 2007, 3:36 pmMichael:
Hi Chip -
Actually, they're blank (ISBLANK(D2) = TRUE).
I was thinking that ...Optional Volume As Long = 127) would take care of me,
but I'm learning here.
Thanks for replying.
...Best, Michael
9 February 2007, 5:23 pmMpemba:
>Is there a text value in column D where there should only be numbers?
Thanks for the reminder (to think of how others will use the function) too.
It would have been SO easy to trap that in the function.
M
12 February 2007, 3:03 amFlashLadyBug.com » Some code for Musical Excel Fun:
[...] . I’m just going to go straight to the link on this one.http://www.dailydoseofexcel.com/archives/2007/02/06/musical-excel/ [...]
17 February 2007, 12:04 pmSantinasi:
Einstein dies and goes to heaven only to be informed that his room is not yet ready. "I hope you will not mind waiting in a dormitory. We are very sorry, but it's the best we can do and you will have to share the room with others" he is told by the doorman.
5 June 2007, 8:35 amEinstein says that this is no problem at all and that there is no need to make such a great fuss. So the doorman leads him to the dorm. They enter and Albert is introduced to all of the present inhabitants. "See, Here is your first room mate. He has an IQ of 180!"
"That's wonderful!" says Albert. "We can discuss mathematics!"
"And here is your second room mate. His IQ is 150!"
"That's wonderful!" says Albert. "We can discuss physics!"
"And here is your third room mate. His IQ is 100!"
"That's wonderful! We can discuss the latest plays at the theater!"
Just then another man moves out to capture Albert's hand and shake it. "I'm your last room mate and I'm sorry, but my IQ is only 80."
Albert smiles back at him and says, "So, where do you think interest rates are headed?"
DonX:
Hi Guys, I'm really into this stuff.. been creating some interesting music ( a sort of impromptu pattern-based music) thanks to this resource. My problem is that if I stop my code, I can't start up the midi again. I declared the MidiOutReset function and tried to call it, but to no avail. I wonder if it has anything to do with the lifetime of the api function variables? I don't yet understand enough about calling api's.. i dont know how the hmidiout value is grabbed in the first place for instance. But it seems that when you stop the code it doesn't pick it up again when you try to run the sub again..
1 July 2007, 6:28 pmDick Kusleika:
Don: See http://www.dailydoseofexcel.com/archives/2007/05/31/more-dueling-banjos/ for one method.
2 July 2007, 11:01 amKevin:
Check out this Excel template:
http://office.microsoft.com/en-us/templates/TC300009571033.aspx
Has four songs in it:
26 October 2007, 1:26 pmAmazing Grace
Pachabel's Canon
Twinkle Twinkle Little Star
Star Spangled Banner