I'm a beginner, and even this program is driving me crazy

[Dagda]

Alright, so I'm fairly sure this is a simple problem, but I have a program for excel that is supposed to return a number corresponding to the parts composing a small string. However, so far it will only return 1. Here's the beginning of it.

Function APValue(ABILITYNAME) As Integer

Dim APReturned

If IsNumeric(InStr(LCase(ABILITYNAME), LCase("Rally"))) Then
APReturned = 1
Else
If IsNumeric(InStr(LCase(ABILITYNAME), LCase("Ten Thousand Stars"))) Then
APReturned = 1
Else
If IsNumeric(InStr(LCase(ABILITYNAME), LCase("Music of Makers"))) Then
APReturned = 2
Else
If IsNumeric(InStr(LCase(ABILITYNAME), LCase("Music of the Dead"))) Then
APReturned = 2
Else
If IsNumeric(InStr(LCase(ABILITYNAME), LCase("Spellsinger"))) Then
APReturned = 4
Else
If IsNumeric(InStr(LCase(ABILITYNAME), LCase("Virtuoso"))) Then
APReturned = 4
Else
If IsNumeric(InStr(LCase(ABILITYNAME), LCase("Warchanter"))) Then
APReturned = 4
Else
If IsNumeric(InStr(LCase(ABILITYNAME), LCase("Lifting the Veil"))) Then
APReturned = 2
Else
If IsNumeric(InStr(LCase(ABILITYNAME), LCase("Vermin Empathy"))) Then
APReturned = 2
Else
If IsNumeric(InStr(LCase(ABILITYNAME), LCase("Adept of Wind"))) Then
APReturned = 2
Else
If IsNumeric(InStr(LCase(ABILITYNAME), LCase("Disciple of Breezes"))) Then
APReturned = 1
Else
If IsNumeric(InStr(LCase(ABILITYNAME), LCase("Master of Thunder"))) Then
APReturned = 3
Else
If IsNumeric(InStr(LCase(ABILITYNAME), LCase("Adept of Rock"))) Then
APReturned = 2
Else
If IsNumeric(InStr(LCase(ABILITYNAME), LCase("Disciple of Pebbles"))) Then
APReturned = 2
Else
If IsNumeric(InStr(LCase(ABILITYNAME), LCase("Master of Stone"))) Then
APReturned = 3
Else
If IsNumeric(InStr(LCase(ABILITYNAME), LCase("Adept of Flame"))) Then
APReturned = 2
Else
If IsNumeric(InStr(LCase(ABILITYNAME), LCase("Disciple of Candles"))) Then
APReturned = 1
Else
If IsNumeric(InStr(LCase(ABILITYNAME), LCase("Master of Bonfires"))) Then
APReturned = 3
Else
If IsNumeric(InStr(LCase(ABILITYNAME), LCase("Disciple of Puddles"))) Then
APReturned = 1
Else
If IsNumeric(InStr(LCase(ABILITYNAME), LCase("Adept of Rain"))) Then
APReturned = 2
Else....


et cetera. It's a very long program, primarily because there's an incredible number of abilities in the game I'm trying to make this for. (Yeah, I'm a lame gamer, but I'm not shy about it. XD)

Regardless, I do have all the proper End Ifs, (and there's a lot. >.<) I'm just really getting tired of seeing only 1s.

Help?

[Mavyak]

ElseIF is one word in VBA.

[Dagda]

Yes, I know. That's not the problem. It's debugging fine, but not returning what it should be.

EDIT: Whoops, misread. I thought you said End If... which isn't one word, so I have no excuse. XD Apologies. Thanks for the shortcut, though it's not going to help much here, I think.

[Dagda]

Like it says under my name, I'm a newbie. XP

[Mavyak]

Do you have:
APValue = APReturned

As the last line of your function?

[Dagda]

Yep yep. Sorry, I didn't include that in the first post. >.<

[Mavyak]

What happens when you send "Virtuoso"? What value is returned. Also, how are you calling the function?

[mikerickson]

A hidden worksheet with a VLOOKUP table would be easier to maintain than a UDF like this.
The hidden sheet would one column of ability names against one column of APvalues.
I suspect that other regions of the hidden sheet could store other useful information.

[david000]

Function APValue(ABILITYNAME As String) As Integer
Dim APReturned As Integer

Application.Volatile
If Len(ABILITYNAME) = 0 Then Exit Function

Select Case LCase(ABILITYNAME)

Case LCase("Rally"), _
LCase("Ten Thousand Stars"), _
LCase("Disciple of Breezes"), _
LCase("Disciple of Puddles"), _
LCase("Disciple of Candles")
APReturned = 1

Case LCase("Music of Makers"), _
LCase("Music of the Dead"), _
LCase("Lifting the Veil"), _
LCase("Vermin Empathy"), _
LCase("Adept of Wind"), _
LCase("Adept of Flame"), _
LCase("Adept of Rain"), _
LCase("Adept of Rock"), _
LCase("Disciple of Pebbles")
APReturned = 2


Case LCase("Master of Bonfires"), _
LCase("Master of Stone"), _
LCase("Master of Thunder")
APReturned = 3

Case LCase("Spellsinger"), _
LCase("Virtuoso"), _
LCase("Warchanter")
APReturned = 4

End Select

APValue = APReturned

End Function

[Dagda]

@_@ Whoa, thanks everyone, great responses! ^.^

I'll probably be working a lot more with excel, so I'll be around. XD But for my level of expertise, I'm thinking the VLOOKUP is the best idea. I like how neat your response is david, but I don't have the working knowledge to do it, and I'm not the type to copy-paste things I don't understand at least moderately. Thus my difficulty in solving such a simple issue. :doh:

Thanks again, though. Lots to learn, as always!

[mdmackillop]

Rather than convert eveything to LCASE, head your code with Option Compare Text which will ignore case in string comparisons.