none
Problem to Enter VBA Code RRS feed

  • Question

  • Dear All,

    I am facing problem that when ever I try to RUN the following Function code, Macro Window opens up. It asks Macros Name. The only options are  "Cancel"    and    "Create" are activate.  If I click Create then it opens up   "Sub  and End Sub" Procedure for another code under my following function code. It Does not run this procedure what could be the reason. I am using Microsoft Excel 2016

    Function mark2grade(mark As Integer) As String

    If mark >= 70 Then

    mark2grade = "A"

    ElseIf mark >= 60 And mark <= 69 Then   

    mark2grade = "B"

    ElseIf mark >= 50 And mark <= 59 Then  

    mark2grade = "C"

    Else   mark2grade = "F"

    End If

    End Function

    Wednesday, February 17, 2016 10:43 AM

Answers

  • sub grade ()

    'the code

    end sub

    this will make the macro avaliable to run. I dont know if this is what you mean?

    P.s. if this is the only code you are using and you have a list of grades you could add a do while loop and the macro will run through all of the results for you.

    Id be more than happy to explain more if you need.

    Happy Excelling!

    Soliddrew


    • Edited by Soliddrew Wednesday, February 17, 2016 11:35 AM
    • Proposed as answer by ManyMandaBertin Wednesday, February 24, 2016 8:08 AM
    • Marked as answer by David_JunFeng Sunday, February 28, 2016 2:23 PM
    Wednesday, February 17, 2016 11:34 AM
  • >>>I am facing problem that when ever I try to RUN the following Function code, Macro Window opens up. It asks Macros Name. The only options are  "Cancel"    and    "Create" are activate.  If I click Create then it opens up   "Sub  and End Sub" Procedure for another code under my following function code. It Does not run this procedure what could be the reason. I am using Microsoft Excel 2016

    According to your description, I have reproduced this issue, this issue is cause by that you could not run function directly, function will then be available to be called from other VBA procedures or to be used in the worksheets of your Excel workbook.

    Calling a VBA Function Procedure From Another Procedure

    You can call a Function procedure from within another VBA procedure by simply assigning the Function to a variable. The following example shows a call to the simple mark2grade function that was defined above:

    Sub main()
    
        Dim result as string
        result = mark2grade(80)
    
    End Sub
    


    Calling a VBA Function Procedure From A Worksheet

    You can call VBA Function procedures from an Excel Worksheet, in the same way as you can call any of the built-in Excel functions.
    Therefore, you could call the mark2grade Function procedure by typing the following into any cell of your worksheet:

    =mark2grade(20)

    • Proposed as answer by ManyMandaBertin Wednesday, February 24, 2016 8:08 AM
    • Marked as answer by David_JunFeng Sunday, February 28, 2016 2:23 PM
    Thursday, February 18, 2016 1:43 AM
  • Hi,

    You can't directly run a Function as a subroutine. You have two options

    • Create a subroutine that will popup your result either in a Message box or a cell in a worksheet or
    • Use the formula bar as in the picture I below. Function coded are available in the formula bar. This is really funny

    I hope it helps

    Cheers


    Many

    • Proposed as answer by ManyMandaBertin Wednesday, February 24, 2016 8:08 AM
    • Marked as answer by David_JunFeng Sunday, February 28, 2016 2:23 PM
    Wednesday, February 17, 2016 2:13 PM

All replies

  • sub grade ()

    'the code

    end sub

    this will make the macro avaliable to run. I dont know if this is what you mean?

    P.s. if this is the only code you are using and you have a list of grades you could add a do while loop and the macro will run through all of the results for you.

    Id be more than happy to explain more if you need.

    Happy Excelling!

    Soliddrew


    • Edited by Soliddrew Wednesday, February 17, 2016 11:35 AM
    • Proposed as answer by ManyMandaBertin Wednesday, February 24, 2016 8:08 AM
    • Marked as answer by David_JunFeng Sunday, February 28, 2016 2:23 PM
    Wednesday, February 17, 2016 11:34 AM
  • Hi,

    You can't directly run a Function as a subroutine. You have two options

    • Create a subroutine that will popup your result either in a Message box or a cell in a worksheet or
    • Use the formula bar as in the picture I below. Function coded are available in the formula bar. This is really funny

    I hope it helps

    Cheers


    Many

    • Proposed as answer by ManyMandaBertin Wednesday, February 24, 2016 8:08 AM
    • Marked as answer by David_JunFeng Sunday, February 28, 2016 2:23 PM
    Wednesday, February 17, 2016 2:13 PM
  • >>>I am facing problem that when ever I try to RUN the following Function code, Macro Window opens up. It asks Macros Name. The only options are  "Cancel"    and    "Create" are activate.  If I click Create then it opens up   "Sub  and End Sub" Procedure for another code under my following function code. It Does not run this procedure what could be the reason. I am using Microsoft Excel 2016

    According to your description, I have reproduced this issue, this issue is cause by that you could not run function directly, function will then be available to be called from other VBA procedures or to be used in the worksheets of your Excel workbook.

    Calling a VBA Function Procedure From Another Procedure

    You can call a Function procedure from within another VBA procedure by simply assigning the Function to a variable. The following example shows a call to the simple mark2grade function that was defined above:

    Sub main()
    
        Dim result as string
        result = mark2grade(80)
    
    End Sub
    


    Calling a VBA Function Procedure From A Worksheet

    You can call VBA Function procedures from an Excel Worksheet, in the same way as you can call any of the built-in Excel functions.
    Therefore, you could call the mark2grade Function procedure by typing the following into any cell of your worksheet:

    =mark2grade(20)

    • Proposed as answer by ManyMandaBertin Wednesday, February 24, 2016 8:08 AM
    • Marked as answer by David_JunFeng Sunday, February 28, 2016 2:23 PM
    Thursday, February 18, 2016 1:43 AM