Excel IF statements in VBA user defined Function 1

[SellOut]

I have been trying to make a function so that I can use 30+ IF statements in Excel (and using several different workbooks). I have discovered how to make a function but am now having problems with the ElseIf parts, it complains there is no corresponding If. Has anyone any ideas? This whole thing is quite a stab in the dark for me so it may just be punctuation.

Public Function WHATCOLOUR(st1 As String) As String
If st1 = "*Barn*" Then WHATCOLOUR = "Red"
ElseIf st1 = "*Tree*" Then WHATCOLOUR = "Green"
ElseIf st1 = "*Road*" Then WHATCOLOUR = "Black"
ElseIf st1 = "*Sky*" Then WHATCOLOUR = "Blue"
Else: WHATCOLOUR = "No Colour"
End Function

The first column holds the text string cells and the adjacent holds the =Whatcolour(xx) function. I am trying to search for parts of text string hence the * (eg "*road*" would be true for "The long and winding road that..." and would give the corresponding cell the value "Black")

I promise that the purpose is a bit more important than the examples I have filled in!!!

Thanks!

SO

 

[bluedragon2]

you need THEN after each IF/ELSE

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[SellOut]

Is the Then I have already in the wrong place?

SO

 

[TonyJollans]

Hi SellOut,

There are two forms of the [blue]If[/blue] statement: the single line form and the block form. There are limitations on the single line form and, in this case you need to use the block form using physical line breaks:

[blue]If st1 = "*Barn*" Then
    WHATCOLOUR = "Red"
ElseIf st1 = "*Tree*" Then
    WHATCOLOUR = "Green"
ElseIf st1 = "*Road*" Then
    WHATCOLOUR = "Black"
ElseIf st1 = "*Sky*" Then
    WHATCOLOUR = "Blue"
Else
    WHATCOLOUR = "No Colour"
End If[/blue]

From the look of your code you are after pattern matches rather than exact equality and might want to use [blue]Like[/blue] instead of [blue]=[/blue]:

[blue]If st1 [red]Like[/red] "*Barn*" Then
    WHATCOLOUR = "Red"
ElseIf st1 [red]Like[/red] "*Tree*" Then
    WHATCOLOUR = "Green"
ElseIf st1 [red]Like[/red] "*Road*" Then
    WHATCOLOUR = "Black"
ElseIf st1 [red]Like[/red] "*Sky*" Then
    WHATCOLOUR = "Blue"
Else
    WHATCOLOUR = "No Colour"
End If[/blue]

Alternatively, if you are looking for exact matches you might like to consider the [blue]Case[/blue] statement, but note that you cannot use the Like operator in a Case statement:

[blue]

Select Case st1
    Case "*Barn*": WHATCOLOUR = "Red"
    Case "*Tree*": WHATCOLOUR = "Green"
    Case "*Road*": WHATCOLOUR = "Black"
    Case "*Sky*":  WHATCOLOUR = "Blue"
    Case Else:     WHATCOLOUR = "No Colour"
End Select

[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[SellOut]

Hello,

I have worked out some of what I did wrong. Firstly the statement needs to start on a new line after the Then. Secondly my use of * as a wildcard does not work (I am a bit of a vba novice). I have adapted this to using the Right function but this only works where the strings I am searching end in the colour. i.e. It will work for 'The old Barn' but not for 'the old Barn door'. I am still looking for a way of solving this.

Public Function WHATCOLOUR(st1 As String) As String
If Right(st1,4) = "Barn" Then
WHATCOLOUR = "Red"
ElseIf Right(st1,4) = "Tree" Then
WHATCOLOUR = "Green"
ElseIf Right(st1,4) = "Road" Then
WHATCOLOUR = "Black"
ElseIf Right(st1,3) = "Sky" Then
WHATCOLOUR = "Blue"
Else
WHATCOLOUR = "No Colour"
EndIf
End Function

NB. I should also point out that the case of the condition matters, "Barn" will not match with 'barn'.

SO

 

[SellOut]

Tony,

Thank you!!! We must have been writing our replies at the same time! The Like statement is exactly what I was looking for! Thats great, my whole day is going to be a lot easier!!!

Thanks,

SO