Musical Excel

Posted on by John Walkenbach

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.

Option Explicit
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.

Sub TestMidi()
    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.

Posted in Uncategorized

33 thoughts on “Musical Excel”

  1. 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.

  2. 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

  3. 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. 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. How about:

    NoteNumDuration
    48450
    55450
    53150
    52150
    50150
    60450
    55450
    53150
    52150
    50150
    60450
    55450
    53150
    52150
    53150
    50400

  8. Here’s a little bit of “Dueling Banjos.” 106=banjo, 26=guitar

    10647200B
    10648200C
    10650250D
    10647200B
    10648200C
    10645200A
    10647200B
    10643200G
    10645500A
    2647200B
    2648200C
    2650250D
    2647200B
    2648200C
    2645200A
    2647200B
    2643200G
    2645500A
    10643150G
    10643150G
    10643150G
    10645200A
    10647200B
    10648200C
    10650200D
    10648200C
    10647600B
    2643150G
    2643150G
    2643150G
    2645200A
    2647200B
    2648200C
    2650200D
    2648200C
    2647500B

  9. 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:
    http://www.midi.org/about-midi/table1.shtml

  10. 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 !!!

  11. 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?

  12. 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?

  14. Lots of fun.

    So, to incorporate Opera Lover’s comments:

    VBA Module:

    ‘// include declarations from initial post here…

    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:

    Sub TestMidi()
        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

  15. 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?

  16. 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!!!!

  17. I should add for people not familiar with the MIDI standard:

    Volume should be an integer between 0 and 127 inclusive

    M

  18. 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.

  19. 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

  21. 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.

  22. 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.

  23. 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

  24. 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

  25. 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?

  26. 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

  27. >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

  29. 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.
    Einstein 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?”

  30. 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..

  33. Pingback: # Excel Super Links #67 – shared by David Hager | Excel For You

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.