SERVING THE QUANTITATIVE FINANCE COMMUNITY

 
User avatar
RFD
Topic Author
Posts: 2
Joined: August 24th, 2005, 3:56 pm

VBA Wrapper/UDF/Excel Dependency Tree Problem

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 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!
Attachments
slow UDF.zip
(272.26 KiB) Downloaded 10 times
 
User avatar
spursfan
Posts: 893
Joined: October 7th, 2001, 3:43 pm

VBA Wrapper/UDF/Excel Dependency Tree Problem

December 19th, 2011, 2:49 pm

Browse through anything by Charles Williams - link to his blog followshttp://fastexcel.wordpress.com/
 
sjoo
Posts: 110
Joined: March 24th, 2003, 1:54 am

VBA Wrapper/UDF/Excel Dependency Tree Problem

January 5th, 2012, 7:41 am

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()"
Last edited by sjoo on January 4th, 2012, 11:00 pm, edited 1 time in total.
 
User avatar
BramJ
Posts: 372
Joined: January 10th, 2006, 2:01 pm

VBA Wrapper/UDF/Excel Dependency Tree Problem

January 5th, 2012, 5:52 pm

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.
Last edited by BramJ on January 4th, 2012, 11:00 pm, edited 1 time in total.
 
User avatar
FastExcel
Posts: 50
Joined: December 2nd, 2003, 8:10 am

VBA Wrapper/UDF/Excel Dependency Tree Problem

January 7th, 2012, 7:09 pm

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
Last edited by FastExcel on January 6th, 2012, 11:00 pm, edited 1 time in total.
ABOUT WILMOTT

PW by JB

Wilmott.com has been "Serving the Quantitative Finance Community" since 2001. Continued...


Twitter LinkedIn Instagram

JOBS BOARD

JOBS BOARD

Looking for a quant job, risk, algo trading,...? Browse jobs here...


GZIP: On