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
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