December 19th, 2011, 11:31 am
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 fullrecalulation 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!

Attachments

 slow UDF.zip
 (272.26 KiB) Downloaded 12 times