Public Function Code in Excel VB Programming

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?

• Status: Open
• Question Views: 1397
• Answer Count: 6
• Vote Up 22 Vote Down

• Answer Accepted: Yes
• Question Category: MS Excel

Best Answer by Simmy C-me

Go To Solution

Best Answer

Best Answer

Answered By Simmy C-me 0 points N/A #97853

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"

About Simmy C-me

Questions
0

Answers
16

Best Answers
6

• Vote Up 0 Vote Down

• Posted on - 05/16/2011
• Question Category: MS Excel

Answered By AngelOfTech 5 points N/A #97854

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.

About AngelOfTech

Questions
0

Answers
25

Best Answers
4

• Vote Up 0 Vote Down

• Posted on - 05/16/2011
• Question Category: MS Excel

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?

About Alex J

Questions
1

Answers
2

Best Answers
0

• Vote Up 0 Vote Down

• Posted on - 05/16/2011
• Question Category: MS Excel

Answered By Simmy C-me 0 points N/A #97857

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.

About Simmy C-me

Questions
0

Answers
16

Best Answers
6

• Vote Up 0 Vote Down

• Posted on - 05/16/2011
• Question Category: MS Excel

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.

About Alex J

Questions
1

Answers
2

Best Answers
0

• Vote Up 0 Vote Down

• Posted on - 05/16/2011
• Question Category: MS Excel

Answered By Simmy C-me 0 points N/A #97860

Public Function Code in Excel VB Programming

Welcome. Glad to know that I could help.

About Simmy C-me

Questions
0

Answers
16

Best Answers
6

• Vote Up 0 Vote Down

• Posted on - 05/16/2011
• Question Category: MS Excel