Hello, I am currently writing some VBA Wrapper function and I notice that the UDF(User Defined function) is very slow in VBA. Here is an example (a 90 x 82 array)Public Function cal_norm() cal_norm = WorksheetFunction.Norm_Inv(Rnd, 0, 1)End FunctionHowever, if I call the function directly on excel, the response is almost instant. =Norm.Inv(Rand(),0,1) (Attached is the spreadsheet called "Fast" and "Slow" to demostrate the problem)Questions:1) It seems that the excel will trigger the full-recalulation if I implementation in VBA/UDF, is it true? If yes, is there any solution to avoid this dependency tree problem?2) If I implement the wrapper function as C++ DLL, will it solve the problem?Thanks!

Browse through anything by Charles Williams - link to his blog followshttp://fastexcel.wordpress.com/

Application.Volatile is related to the calculation on UDFs. Plz http://www.dailydoseofexcel.com/archive ... ctions/and I tried the following statements just for curiosity. they are faster than your [Slow] worksheet at first.[A1:CD90] = "=Norm.Inv(Rand(), 0, 1)"[A1:CD90] = "=cal_norm()"

Haven't opened your spreadsheets. However, based from your description the main bottleneck making your sheet slow right now is making the 80x92 seperate calls to your function that each output data seperately. It is much faster to write back all the data at once in an array formula. Moreover, calls to WorksheetFunction.Functions are relatively slow. Writing your own cumulative normal inverse function is typically faster.I have quickly tested writing an array function doing the same thing as you need to do, using my own cdf inverse function in VBA. Timing this gives me numbers in the order of magniturde of 5*10^-3 seconds and that is using a design that is not optimised for speed other than using the array formula to output everything to excel in one shot.I vaguely remember reading somewhere that there is an upper bound to the total number of elements you can write back to excel in one go with VBA, but don't remember the number. If you want to generate more normal random numbers then you might want to google for that. EDIT: I also quickly timed just inverting the uniform random number using WorksheetFunction.norm_inv() versus my own inverse. The worksheetfunction.norm_inv() is a factor 10 to 20 times slower.

One reason your VBA UDF is very slow is probably because you have not bypassed the VBE refresh buf: this will slow down your VBA UDFs by a factor of 10 or more.See writing-efficient-vba-udfs-part-3-avoiding-the-vbe-refresh-bug http://fastexcel.wordpress.com/2011/06/ ... fresh-bug/ This is part 3 of a series (7 so far) of posts on writing efficient VBA Udfs