VBA putting String in Cell that the function is being executed in-CodePudding

I have a cell that I'm trying to have display a different String depending on the input parameter but I don't understand how to return/print a String to the cell. There is no syntax error but the cell just remains blank. Here is my code :

Function Email(Region As String) As String
    If Region = Atlantic Then
        Email = "[email protected]"
    ElseIf Region = West Then
        Email = "[email protected]"
    ElseIf Region = Pacific Then
        Email = "[email protected]"
    ElseIf Region = Ontario Then
        Email = "[email protected]"
    ElseIf Region = Atlantic Then
        Email = "[email protected]"
    ElseIf Region = Quebec Then
        Email = "[email protected]"
    Else: Region = "x"
    End If
End Function

CodePudding user response：

This is on the assumption that you're testing for literal strings eg. "Atlantic" and that you don't have a variable or string returning function elsewhere called Atlantic.

Fixing your existing code, it would end up looking something like this - (note, I've removed the 2nd Atlantic check) -

Function Email(Region As String) As String
    If Region = "Atlantic" Then
        Email = "[email protected]"
    ElseIf Region = "West" Then
        Email = "[email protected]"
    ElseIf Region = "Pacific" Then
        Email = "[email protected]"
    ElseIf Region = "Ontario" Then
        Email = "[email protected]"
    ElseIf Region = "Atlantic" Then
        Email = "[email protected]"
    ElseIf Region = "Quebec" Then
        Email = "[email protected]"
    Else: Email = "x"
    End If
End Function

A somewhat easier to read method would be to use Case Select:

Function Email(Region As String) As String
    Select Case Region
    Case "Atlantic"
        Email = "[email protected]"
    Case "West"
        Email = "[email protected]"
    Case "Pacific"
        Email = "[email protected]"
    Case "Ontario"
        Email = "[email protected]"
    Case "Quebec"
        Email = "[email protected]"
    Case Else
        Email = "x"
    End Select
End Function