VBA as One User Defined Function: Homework

[Jess Rulz]

Hi my name is Rebecca

I posed a thread on her last week about my VBA code as it wound not work, it works now although i need to edit it to make it one user defined function. i was wondering if you could give me a little help as i dont really understand how to do this.

This is what i have so far, it is as 5 seperate functions.

Many thanks in advance

Rebecca

[mdmackillop]

Hi Rebecca,
In H2 you are using data from B2 and E2. E2 is a calculated figure. You need to adust your function to use data from A2 and D2 which are basic data. This can be done either as one complex function, or by calling separate functions, each of which returns one value which are "put together" by the calling function.

[mdmackillop]

BTW, Consider using Select Case. It is better here than If...Then

[Jess Rulz]

Thank you for your advice,

I was wondering if you could have a look at my function now as i have spent a few days changeing it although i have having the same problem i have last week, when the function Just would not work.

Any help would be appriciated

Many thanks

Rebecca x

[mdmackillop]

A few points

Have you stepped through the code to see where it fails?

By enclosing the second part in quotes you are turning it into a string so no calculation takes place.
sngEggsDiscount = "intEggsCost - intNumberEggs / 100 * 1"
A Single data type cannot take a text value so your code fails.

Your Function is dimmed as Integer, but what do you get if you subtract a single from an integer?

TotalEggsCost = intCost - intDiscount

Similar names like this are bound to get confused; as you have demonstrated!

Dim intEggCost As Integer
Dim intEggsCost As Integer


I don't believe your discounts are calculating correctly. Your procedure for doing this seems overcomplicated. In simple terms, if the discount is 5% then DiscountPrice = Price * 0.95 etc.

How many boxes for 2 eggs? Check the value your code gives you. Do you know how to add Watches etc.? Have a look at this item (http://www.cpearson.com/excel/Debug.htm)