Asked By
Alex J
220 points
N/A
Posted on - 05/16/2011
Hi,
I am learning Excel VB programming. It is quite easy. But I don't get how to use custom function in here?
Public Function gradeCal(gpa As Double)
           If (gpa >= 5) Then
               Return "A+"
           ElseIf (gpa >= 4) Then
               Return "A"
           ……
End Function
But its not working. What is the proper syntax of returning some value from a Excel function?
Public Function Code in Excel VB Programming
Alex,
The function returning value in Excel is a little tricky and funny too. You have to assign (yes literally assign) the value to be returned to the function. For example
Public Function gradeCal(gpa As Double)
           If (gpa >= 5) Then
               gradeCal = "A+"
           ElseIf (gpa >= 4) Then
               gradeCal = "A"
Public Function Code in Excel VB Programming
You also have to specify the return type of the function. I don’t know if it is compulsory but it is the proper syntax.
Answered By
Alex J
220 points
N/A
#97856
Public Function Code in Excel VB Programming
Thanks it worked.
Anyway how do I set the return type?
Public Function Code in Excel VB Programming
Here is how it should be:
Public Function gradeCal(gpa As Integer) As String
           If (gpa >= 5) Then
               gradeCal = "A+"
           ElseIf (gpa >= 4) Then
               gradeCal = "A"
           ElseIf (gpa >= 3.5) Then
               gradeCal = "A-"
           ElseIf (gpa >= 3) Then
               gradeCal = "B"
           ElseIf (gpa >= 2) Then
               gradeCal = "C"
           ElseIf (gpa >= 1) Then
               gradeCal = "D"
           ElseIf (gpa < 1) Then
               gradeCal = "F"
           End If
End Function
Hope this helps.
Answered By
Alex J
220 points
N/A
#97859
Public Function Code in Excel VB Programming
Thanks very much. That was a very clean solution.
Public Function Code in Excel VB Programming
Welcome. Glad to know that I could help.
