Forum Discussion

sneakypueblo's avatar
sneakypueblo
Copper Contributor
Dec 04, 2018

Excel VBA function

I just created a function in VBA within excel and I have questions about how to apply the function into my workbook. (Specific question and details in response)

Macros and VBA

3 Replies

  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    Dec 04, 2018
    It would've been more efficient if you had posted those questions immediately :-)
    • sneakypueblo's avatar
      sneakypueblo
      Copper Contributor
      Dec 04, 2018

      Your'e right I was not sure what the actual chances of being answered or seen were. Below is the function I have created as seen in the code window of VBA. My question deals with using the custom function itself. When inputting this function into a cell within the workbook =TotalAttendanceCost() I am confused as to which inputs/what format I actually put into the equation.

       
      FunctionTotalAttendanceCost(intNumberAttending As Integer, intTotalAttendance As Integer, curMemberFee As Currency, curCostperMeal As Currency, sngMealTax As Single)
       
      'This function calculates the cost incurred by members
      attending the
      annual meeting
      'intNumberAttending represents the number of members attending the annual meeting
      'curMemberFee represents the cost per member to attending the meeting
      'intTotalAttendance
      represents the total number of people (members plus guests) attending the annual meeting
      'curCostperMeal represents the cost for lunch provided at the meeting
      'sngMealTax represents the taxes incurred for each meal
      TotalAttendanceCost = (intNumberAttending * curMemberFee) + ((intTotalAttendance * curCostperMeal) * (1 + sngMealTax))
      End Function
       
      • JKPieterse's avatar
        JKPieterse
        Silver Contributor
        Dec 05, 2018

        To be honest, there is no point in using a VBA function for this simple calculation. Suppose you have your information laid out as follows:
        the number of members attending the annual meeting: Cell A2
        the cost per member to attending the meeting: Cell B2
        the total number of people (members plus guests) attending the annual meeting: Cell C2
        the cost for lunch provided at the meeting: Cell D2
        the taxes incurred for each meal: Cell E2
        In cell F2 you can calulate the Total Attendance Cost using:

         

        = (A2*B2)+(C2*D2)*(1+E2)

Resources