Forum Discussion

Anders_J_Rosen's avatar
Anders_J_Rosen
Copper Contributor
Jan 02, 2021

IFS in VBA (Multiple IF statements)

Hey all, I'm trying to define a simple VBA function for quick use in my Excel spreadsheets, but I'm receiving a #VALUE! error. I'm new to Visual Basic and Excel programming, so I'm likely missing something basic. Here is my code.

 

Function Grade(p As Variant) As String
Grade = WorksheetFunction.IFS(p >= 90, "A", p >= 80, "B", p >= 70, "C", p >= 60, "D", p < 60, "F", True, "Error")
End Function

 
The https://support.microsoft.com/en-us/office/ifs-function-36329a26-37b2-467c-972b-4a39bd951d45 works perfect with the same parameters in an Excel worksheet. Are my types wrong? I noticed that IFS is not included in the https://docs.microsoft.com/en-us/office/vba/excel/Concepts/Events-WorksheetFunctions-Shapes/list-of-worksheet-functions-available-to-visual-basic and I receive a "Sub or function not defined" error if I remove WorksheetFunction. Is there a way to include the IFS function in an Excel VBA module separate from using https://docs.microsoft.com/en-us/office/vba/excel/Concepts/Events-WorksheetFunctions-Shapes/using-excel-worksheet-functions-in-visual-basic?

Any help would be greatly appreciated.

excel
Formulas and Functions
Macros and VBA

1 Reply

Sort By
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jan 02, 2021

    Anders_J_Rosen 

    Apparently IFS hasn't been added to the WorksheetFunction object. You can use a Select Case block instead:

     

    Function Grade(p As Variant) As String
    Select Case p
        Case Is >= 90
            Grade = "A"
        Case Is >= 80
            Grade = "B"
        Case Is >= 70
            Grade = "C"
        Case Is >= 60
            Grade = "D"
        Case Else
            Grade = "F"
    End Select
End Function

    or use the LOOKUP function:

    Function Grade(p As Variant) As String
    Grade = WorksheetFunction.Lookup(p, Array(0, 60, 70, 80, 90), Array("F", "D", "C", "B", "A"))
End Function

Resources