Sleeper: Using Arrays and Indexes To Manipulate Variables ( A Music Project )

hello, all...this is my first time posting. seems to be a nice place here.

well, I have been grappling with trying to do a spreadsheet, that one day I would like it to grow up into being a full-boar musical application.

Basically, I want the user to be able to select a key and scale, and have the program calculate all types of chords and transitions possibilities given the key and scale selected. I have alot of ideas for the algorithms that will mae it work, but I'm having trouble at the early stages: "teaching" excel how to interpret notes as numbers.

I'm going to be using the tradition midi numbers; that is, 0-127 represent all the notes of a 12 octave keyboard. Note 60 is middle C.

I want to be able to tell the machine, "okay C + 4 = 64." and similarly, "C + 5 = F". In other words, doing math with the notes of music.

All I've got so far that is working is some code a friend helped me with to have VBA recognize midi note numbers as notes: Here it is:

Code:

---

Function fnNote(number)
Static bGotArray As Boolean
Static arrKeys
On Error GoTo errh
If Not bGotArray Then
    arrKeys = getKeys
    bGotArray = True
End If
x = arrKeys(number Mod 12)
fnNote = arrKeys(number Mod 12)
Exit Function
errh:
fnNote = CVErr(xlValue)
End Function
 
Function getKeys()
getKeys = Array("C", "Db", "D", "EB", "E", "F", _
"Gb", "G", "Ab", "A", "Bb", "B")
End Function

---

I don't even understand fully, but so far it works for translating numbers into notes, but not for "doing math" with the notes themselves.

Any assistance you all could provide would be great.

thanks
-O

=fnVariation("C",2)

Code:

---

Function fnVariation(myKey As String, num As Integer) As String
Static bGotArray As Boolean
Static arrKeys
On Error GoTo errh
If Not bGotArray Then
    arrKeys = getKeys
    bGotArray = True
End If
x = WorksheetFunction.Match(myKey,arrKeys,0) - 1
x = x + num
Do While x < 0
    x = x + 12
Loop
Do While x > 11
    x = x - 12
Loop
fnVariation = arrKeys(x)
Exit Function
errh:
fnVariation = CVErr(xlValue)
End Function

---

HTH

okay thanks. I'm going to give this a whirl and get back to you on how it works.

okay...btw thanks again for your assistance.

I've got it working to do

C + 4 = E

but not yet

C + 4 = 64


how would I adjust the code you've given me to create the latter?

Hi
try
=fnVariationByNum("C",1,2)
when 2nd argument = 1 (1 by default), based on "C" = 60

Code:

---

Function fnVariation(myKey As String, Optional kyRef As Byte = 1, num As Integer) As Byte
Static bGotArray As Boolean
Static arrKeys
On Error GoTo errh
If Not bGotArray Then
  arrKeys = getKeys
    bGotArray = True
End If
fnVariationByNum = WorksheetFunction.Match(myKey,arrKeys,0) + 47 + num + keyRef * 12
If fnVariationByNum > 127 Or fnVariationByNum < 0 Then fnVariationByNum = CVErr(xlValue)
End Function

---

I think, after looking at this, that you can get it to work the way your going. But you could also, take the stuff you already know, like the midi notes, and interpret it for Excel, BEFORE you code anything. What I mean is, you could make that range of possible selections, into a big named range in Excel, and a subset of a bunck of little named ranges. Then you pre interpret the ranges, with names- that seem to make easier sense, or fare for Excel to interpret, because the named range, need have no confusion. Thats where I don't know exactly what you are doing, but the concept with a range with a name, is you can call it by it's simpler name, yet it will still carry the value with in it. You can access arrays with a name, you can cut up and divide, and re-amalgamate with names, names within names, etc. You may have to hardwaire alot of stuff, in the midi end, but I think the concept could make you're coding easier afterwards, because you will work with names in code, which can add another layer of intelligence b4 you get to the code. It will even make you think about it a little differently. Good Luck, I would like to try it out when your done- I made one, for drawing scales in AutoCAD for guitar, then you could do these real alien random scales, and stuff that was microtoned, or layer with var cacophony by degree... hmmm

@jindon: thanks, I will give this a try and let you know.

@10 west: yes, I think I sorta see what you mean. If understand you correctly, I have a sheet with something similar I could use in way you mentioned...


it has:

column A, rows 3 through 38, filled with midi notes 48 to 83

then

column B, rows 3 through 38, filled with note names C all the way up to the B that corresponds to midinote 83

but :dunno

I don't know how to "dimension" this range into an array that the program can intelligently cycle through and do calculation with, especially flipping between notes and note number at will. Let me know if you know how to do that.

as far as scales....that's exactly what this program is designed to do in the long run -- generate intervals from scales and other cool tools for doing chord progressions.

if you want to keep in touch for the purpose of discussing algorithms for such things, that'd be cool. I've set up a google group about music theory programming called

music-theory-programming
(I can't post links yet)

you can post your projects there for discussion, too :yes

I think you could hook up a midi keyboard to it (Excel and App), or a little virtual one. I have a friend, who has this keyboard about as big as a candy bar, with super senstive responsive mouse keys, for when he slides the mouse over the keys, he can jam it, like I see the screen, and even the mouse as a whole new instrument. He sound like via, with a mouse and a mac, with some retro-as1 synth screaming sounds for sustain. I could not believe the sound quality, and how easy it is maneuvere a good mouse. But, today, with direct music and tons of apps open to COM, you could hook a keyboard (musical, to type from. what an irony. But I think, seriously, you could hook the two up. I saw some guy playing soungs from bitmaps. I think what would be cool, is also building in a time structure, with sampled human riffs etc, then some how, get a pivot table in on the action, and create some surreal digital chaos music, but layer between a logical sonic order, to completet cacophany, with a joystick interface. I think the guitar in particular is a almost new candidate for midi too. There some guy layering songs mirrored on eachother, and multi-songs layers, etc, and making a hybrid monster where all the subliminals get confused. Not really, but sonic science is really expanding, hope to see your program, and the others music site.

Now I read it again, and I'm not sure what you mean dimension, the beauty of a range which is named, is that it's dynamic, so you can cahnge it's dimensions, but once the range's name is called, Excel has the whole range automatically, as you defined it, so I think it should, esppecially in vba, maybe be fast enough to ran through the ranged named table. Maybe there is a way, to take your base framework array, and build extra dimensions to it, for pre logicing stuff peculiar to musicians or students or whatever, the stuff you would know best about, to manipulate. good luck, post a link soon please

I am not a musician. base 12 system will be your savior.

0 1 2 3 4 5 6 7 8 9 A B 10 = C C# D D# E F F# G G# A A# B C

Duo-Decimal or base-12 is your key to numerically identifying each key and doing the math. Duodecimal will be encased in square brackets here. Some calculators let you use custom base systems.

In the decimal system middle C is C6 = 60.
In the base 12 system 60 = [50]. This is translated as 5*12 + 0 = 60.

And adding 4 keys to middle C is
60 + 4 = [50] + [4] = [54] = 5*12 + 4 = 64 or the key E6

Middle C + 11 keys is
60 + 11 = [50] + [b] = [5B] = 5*12 + 11 = 71 or B7

One more:
Then C6 + 13 is
60 + 13 = 73 = [50] + [11] = [61] = 6*12 + 1 = 73 = C7#

Note this identifies the octave and a change in key is fantastically easy since they are all increased or decreased by the same number.

A translation table will convert between the two bases but you might consider other symbols for A & B in duo, maybe Greek alpha and beta. I hope this was not too complicated but, except for the confusion of A and B, this seems to solve the math problem. Add an octave with [10].

Its only been two years!!

made several mistakes - it is confusing. it is best to identify an octave with the first digit (the left digit) of the duodecimal value. Rewriting those examples:

In the decimal system middle C is C5 = 60.
In the base 12 system 60 = [50]. This is translated as 5*12 + 0 = 60.

And adding 4 keys to middle C is
60 + 4 = [50] + [4] = [54] = 5*12 + 4 = 64 or the key E5

Middle C5 + 11 keys is
60 + 11 = [50] + [b] = [5B] = 5*12 + 11 = 71 or B5

One more:
Then C5 + 13 is
60 + 13 = 73 = [50] + [11] = [61] = 6*12 + 1 = 73 = C6#

Again, as I very briefly learned years ago, a change of key is simply adding or subtracting a constant number to all the keys in the music piece.

Sorry for the confusion.