Public Function Code in Excel VB Programming

Asked By 220 points N/A Posted on -
qa-featured

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?

SHARE
Best Answer by Simmy C-me
Best Answer
Best Answer
Answered By 0 points N/A #97853

Public Function Code in Excel VB Programming

qa-featured

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"

Answered By 0 points N/A #97854

Public Function Code in Excel VB Programming

qa-featured

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 220 points N/A #97856

Public Function Code in Excel VB Programming

qa-featured

Thanks it worked.

Anyway how do I set the return type?

Answered By 0 points N/A #97857

Public Function Code in Excel VB Programming

qa-featured

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 220 points N/A #97859

Public Function Code in Excel VB Programming

qa-featured

Thanks very much. That was a very clean solution.

Answered By 0 points N/A #97860

Public Function Code in Excel VB Programming

qa-featured

Welcome. Glad to know that I could help.

Login/Register to Answer

Related Questions