Help with Call function not returning correct result

[NewtVBA]

I am very new to VBA and have written a function that returns a number from a range. My issue is that I want to write several other similar functions and the use the Call command to return the results of the functions. However, when I try to call the function I have already written (which works on its own) the result that shows up when I test it is 0.

Can anyone help by letting me know what I am doing wrong and how to fix it? Here is the first function I have written:

Function B02SP8(grade, step)
    If grade = "B02" And step = 1 Then
    B02SP8 = Sheets("Sheet2").Range("F55")
    ElseIf grade = "B02" And step = 2 Then
    B02SP8 = Sheets("Sheet2").Range("G55")
    ElseIf grade = "B02" And step = 3 Then
    B02SP8 = Sheets("Sheet2").Range("H55")
    ElseIf grade = "B02" And step = 4 Then
    B02SP8 = Sheets("Sheet2").Range("I55")
    ElseIf grade = "B02" And step = 5 Then
    B02SP8 = Sheets("Sheet2").Range("J55")[/INDENT]
[INDENT]    Else
    B02SP8 = "Grade/Step not included in function"
    End If
    B02SP8 = Application.Round(B02SP8, 2)
End Function[/INDENT]
[INDENT]

However, when I try to Call it using:

Function HR(grade, step)
    Call B02SP8(grade, step)
End Function

The result always returns a value of 0 instead of the number in the range the Called function should reference. My ultimate goal is to call several similar functions at the same time.

Any advice would be much appreciated!

 

[RoryA]

Welcome to the forum.

You need to return a value from your HR function by assigning the result to HR like this:

Function HR(grade, step)
    HR = B02SP8(grade, step)
End Function

 

[NewtVBA]

Thank you so much! I knew it was going to be a simple fix, I just could not figure it out. Thanks again.

 

[RoryA]

Glad to help.

 

[NewtVBA]

If you are still around, I have a follow up question. As I mentioned in my original post I am hoping to have HR call several functions. Right now with your tip I have:

Function HR(grade, step)
    HR = B02SP8(grade, step)
    HR = S04T10(grade, step)
    HR= F01R08(grade, step)
    HR = C01(grade, step)
End Function

However, as I am sure you can imagine, it only works for the last line. Is there some way I can have it test the first functions for the correct entries and move on the next one if they don't exist (and it therefore comes up with no answer)? If you look back at my original post, the first function is a string of "if, then" statements, the new functions are nearly identical with only the values being different (i.e. the "step" value will continue to increase and the "grade" value changes several times as well).

Thanks for your help!

 

[RoryA]

If each function either returns a number or an error string, you could use something like this:

Function HR(grade, step)
    HR = B02SP8(grade, step)
    If not Isnumeric(HR) Then
        HR = S04T10(grade, step)
        if not isnumeric(HR) then
            HR= F01R08(grade, step)
            If not isnumeric(HR) then HR = C01(grade, step)
        end if
    end if
End Function

By the way, your first function will error if the values aren't matched because the last line will try and round a string.

 

[NewtVBA]

You are brilliant! Your suggestion worked perfectly, I was about to try another series of "if,then" statements but your solution is so much more elegant. You have saved me hours of work, thank you so much!