Excel formula vs user-defined VBA function

[jdmc45]

Hi,

I have created a simple UDF in VBA and the same formula on my spreadsheet. The values created by both ways are quite different, and I cannot figure out why there is such a large discrepancy.

In VBA I am using many VBA functions such as Log, Exp and then some Excel functions such as WorksheetFunction.Pi, WorksheetFunction.NormSDist(x) for example.

Firstly, why do you think the results are so different? I am talking errors in the range of 50% of the answer to 150% of the answer.

Secondly, is there a way to troubleshoot a UDF in VBA the same way as a procedure? The help menu is useless in VBA. Can I see the values for certain inputs in my code, similar to using the break point and run method for sub routines?

Any help is much appreciated.

James

 

[Sektor]

To debug UDF, do the following.

1. Set breakpoint in the beginning of UDF.
2. Go to the cell with UDF.
3. Press F2.
4. Press Enter.

You'll be in VBE with yellow line at breakpoint. Press F8 to step-by-step.

 

[jdmc45]

thanks for that, works a treat. Is there a way to check the value of more than just the variable though? for example, instead of placing the cursor over the x in sqr(x) and see 4, is there a way to see the value of the whole sqr(x) value, which in this case would be 2?

So frustrating! the formulas in both are exactly the same, using the same data. Maybe VBA does some rounding in the intermediate steps that makes it inaccurate?

Cheers!

 

[Sektor]

Then post Excel's formula and VBA's one.

 

[RoryA]

Use the Locals or Watch windows to see the current values of any variables.

 

[Sektor]

To see the result of sqr(x), you should place curson over "sqr", not "x".

 

[Ruddles]

Are you confusing common logs with natural logs by any chance?

The VBA function Log() is the equivalent of the worksheet function =LN, not =LOG.

Verify this for yourself using some simple numbers and the Immediate window (Ctrl-G).

(I dont understand any of this - I'm not a mathematician.)