Help with user defined function

firstly, correct the code (always compile your code before running it)

Please Login or Register  to view this content.

is wrong. it should be:

Please Login or Register  to view this content.

One of the first debugging skills I learned and that I probably use the most is the ability to step through a function one step at a time: http://office.microsoft.com/en-us/su...819.aspx?CTT=1 My first suggestion in debugging something like this is to set a breakpoint somewhere early in the function (I often use the function statement itself), then step through the code. This will allow you to identify which statement fails to execute, or which values are present in variables that can cause errors, etc.

When I stepped through it, the first error I found (after the typo mentioned above) was with the use of the .trunc() worksheet function. According to this list of worksheet functions available to VBA, trunc is not on the list: http://msdn.microsoft.com/en-us/libr...ice.15%29.aspx You will probably need to search through the available rounding functions in VBA to get the effect you want here.

See where that gets you.

My second issue is that I want the value to x to be a value that is pulled from a cell on another sheet.

For this, add another argument to the function statement so you can pass this second argument to the function.

the function Trunc does not exist in VBA nor in VBA.Worksheetfunctions.

What do you really want to do with your code?

I changed my code since I can't use the trunc function. Instead I am using INT() to achieve my desired value. However, changing this still does not produce my desired results and I am having issues using the debugging tools. I am unable to step through my code. I am running Mac Excel 2011.

Originally Posted by mbxs3

Not to get off topic, but I am attempting to step through the code line-by-line and when I click "Step Into" or use the keyboard shortcut, nothing happens.

With UDF's, I have found that the "step into" command fails -- because this command has no way to assign values to the required arguments of the function. The best way I have found to enter debug mode with UDF's is as I described above, set a breakpoint, then call the function from the spreadsheet (just like you are doing now). When the code execution encounters the breakpoint, it pauses, enters break mode, and you can then use the other debugging tools to determine what is going on. (in the link I added above, study the section "setting a breakpoint" for detailed instructions).

Ok, so my code started working, although I don't think I changed anything major. Im starting to think my Mac Excel version is glitchy. I am still having one issue which I will explain below my code. Here is my updated UDF code:

Please Login or Register  to view this content.

My issue occurs when the value for "slots" is 5, 9, 13, …etc. I never get the return value of 2. Instead, the item with the ranking that should produce the value 2 produces the value of 0. However, if the "slots" value is odd integers 3, 7, 11, ... etc. it works fine.

Perhaps instead

	A	B	C
1	Rank	Price Factor
2	1	4	B2: =Pricefactor(A2, 5)
3	2	2
4	3	0
5	4	-2
6	5	-4
7
8	1	5	B8: =Pricefactor(A8, 6)
9	2	3
10	3	1
11	4	-1
12	5	-3
13	6	-5

Please Login or Register  to view this content.

I know I get to sounding like a broken record (for those who can remember what vinyl records are), but I think you should be able to see where the problem occurs if you step through the function. You would see that this result is caused when rank = zero.

The reason that rank and zero are sometimes equal when you may not expect them to be is that VBA's round() function uses banker's rounding (rounding to the nearest even number) when you get an even 0.5. So 5/2=2.5 rounds to 2 (not 3 the way many of us are used to seeing). http://support.microsoft.com/kb/194983/en-gb You will probably need to think through your code and see what this means for your code and how you approach the arithmetic.

I feel so ... ignored

So what should the results be for 6 slots?

	A	B	C
1	Rank	Price Factor
2	1	4	B2: =Pricefactor(A2, 5)
3	2	2
4	3	0
5	4	-2
6	5	-4
7
8	1	4	B8: =Pricefactor(A8, 6)
9	2	2
10	3	0
11	4	0
12	5	-2
13	6	-4

Please Login or Register  to view this content.

You're welcome.