I'm trying to create a complex set of variables in an excel
spreadsheet. Since I am not a strong programmer, the best way to do
this is to basically use a very complex if/then argument. However,
excel only supports 7 nested conditionals.

Basically, what i want to do is "teach" excel the notes of a music
keyboard in terms of number.

Ex 60 = C, 61 = Db, 62 = D etc etc...and so on for all 127 notes of a
midi keyboard.

In other words, like an array or list that I can do calculations with.

Any advice?

You can make a User defined function that returns the note for a
numeric value.
In a standard module paste this

Function Nt(Score As Variant) As String

Select Case Score
Case Is = 60
Nt = "C"
End Select

End Function

This only works for a value of 60. Yo will have to write the other 126
cases and the corresponding values. Example

Function Nt(Score As Variant) As String

Select Case Score
Case Is = 60
Nt = "C"
Case is = 62
Nt = "D"
End Select

End Function

All of the cases must be between the Select Case Statement and the End
Select.

HTH


(E-Mail Removed) wrote:
> I'm trying to create a complex set of variables in an excel
> spreadsheet. Since I am not a strong programmer, the best way to do
> this is to basically use a very complex if/then argument. However,
> excel only supports 7 nested conditionals.
>
> Basically, what i want to do is "teach" excel the notes of a music
> keyboard in terms of number.
>
> Ex 60 = C, 61 = Db, 62 = D etc etc...and so on for all 127 notes of a
> midi keyboard.
>
> In other words, like an array or list that I can do calculations with.
>
> Any advice?

There are many ways with either VBA or Excel formulas, in both look at the
Mod function.

Perhaps a named formula -

Select cell B1
Ctrl-F3 Define names
Name: Note
Refersto (enter following as one line)
=INDEX({"C","Db","D","EB","E","F","Gb","G","Ab","A","Bb","B"},MOD(A1,12)+1)

in the cell offset one to right of your number enter =Note

One way with a UDF

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

=fnNote(A1)

Regards,
Peter T

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> I'm trying to create a complex set of variables in an excel
> spreadsheet. Since I am not a strong programmer, the best way to do
> this is to basically use a very complex if/then argument. However,
> excel only supports 7 nested conditionals.
>
> Basically, what i want to do is "teach" excel the notes of a music
> keyboard in terms of number.
>
> Ex 60 = C, 61 = Db, 62 = D etc etc...and so on for all 127 notes of a
> midi keyboard.
>
> In other words, like an array or list that I can do calculations with.
>
> Any advice?
>

thanks...I need to sit down with this and figure it out. thanks for
the running start.

so would I just paste this code into a cell where I would open up a VBA
module?

> so would I just paste this code into a cell where I would open up a VBA
> module?

In Excel Alt-F11 to open the VB editor
Select the name of your workbook in the left panel to highlight it
Right-click > insert module
Paste the code in the large right pane
After testing close the module window with the smaller x top right
Collapse the tree against your workbook name
Close the VBE

To use the UDF (user defined function) in a cell type =fnNote(A1) to return
the name of the note in cell A1, which should contain a number between
0-127. Alternatively
=fnNote(60)

Did you try the named formula method

Regards,
Peter T


"Omar" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> thanks...I need to sit down with this and figure it out. thanks for
> the running start.
>
> so would I just paste this code into a cell where I would open up a VBA
> module?
>

Okay I'm doing that, but the vba code is coming up with a bug, and it
point to the line that says:

Function getKeys()

any ideas?

I just tried the code as posted and it worked fine for me.

None of the code should appear with red text.
The last line of the code should read "End Function"

In the VBE, on the main menu, Debug > Compile Project

When code breaks a message box should appear with a description of the
error - what does it say.

Regards,
Peter T



"Omar" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Okay I'm doing that, but the vba code is coming up with a bug, and it
> point to the line that says:
>
> Function getKeys()
>
> any ideas?
>

okay, i can't get the VB one to work for now. i keep getting #NAME all
over the place.

However, i was able to get the named function to work.

where I plug in "=Note" to the right of a cell with the named function
pasted into it, it gives me the corresponding note name of the midi
number. thanks.

a few more questions:

1) how can i set it up so that I can use it to do transpositions? Liek
how can I make it such that C + 4 = E (meaning midi note 60 + 4 = midi
note 64, which is E)?

2) how can I change the xy orientation of this "=Note" named function?
Liek how can I make such that the number above (instead of the right
of) will give me the note name?


again, thanks

-O

I suspect you are giving up too easily with the UDF, if it's not calculating
many times on the sheet this would be the simplest in terms of overall
flexibility. I can only assume you did not paste the code as posted or you
have two functions with the same name.

> 2) how can I change the xy orientation of this "=Note" named function?
> Liek how can I make such that the number above (instead of the right
> of) will give me the note name?

Define a similar name, eg "NoteA", but this time first select A2 instead of
B1, and another similar name, NoteB, with A65536 selected.
=NoteA will return the cell Above and NoteB the cell below.

Alternatively, not as short and simple but more flexible, define a name like
this -

Name: KeyArray
Refersto: ={"C","Db","D","Eb","E","F","Gb","G","Ab","A","Bb","B"}

or put the keys in 12 cells (vertically or horizontally) and Name the 12
cells

and use this formula anywhere you want
=INDEX(KeyArray,MOD(A1,12)+1)

> 1) how can i set it up so that I can use it to do transpositions? Liek
> how can I make it such that C + 4 = E (meaning midi note 60 + 4 = midi
> note 64, which is E)?

Define KeyArray as above

=MATCH("C",KeyArray) - 1
for "C" should return value 0

=INDEX(KeyArray,MOD((MATCH(A2,KeyArray,0)-1)+A3,12)+1)
with "C" in A2 and 4 in A3 should return "E"

Regards,
Peter T


"Omar" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> okay, i can't get the VB one to work for now. i keep getting #NAME all
> over the place.
>
> However, i was able to get the named function to work.
>
> where I plug in "=Note" to the right of a cell with the named function
> pasted into it, it gives me the corresponding note name of the midi
> number. thanks.
>
> a few more questions:
>
> 1) how can i set it up so that I can use it to do transpositions? Liek
> how can I make it such that C + 4 = E (meaning midi note 60 + 4 = midi
> note 64, which is E)?
>
> 2) how can I change the xy orientation of this "=Note" named function?
> Liek how can I make such that the number above (instead of the right
> of) will give me the note name?
>
>
> again, thanks
>
> -O
>

thanks dude.. I'll go to work on this in the morning. right now its
bedtime!