title: User Defined Functions in Excel and Speed Issues
body: <p>I have an Excel model that uses almost all UDFs. There are say, 120 columns and over 400 rows. The calculations are done vertically and then horizontally --- that is first all the calculations for column 1 are done, then the final output of column 1 is the input of column 2, etc. In each column I call about six or seven UDFs which call other UDFs. The UDFs often output an array.</p>
  
  <p>The inputs to each of the UDFs are a number of variables, some range variables, some doubles. The range variables are converted to arrays internally before their contents are accessed.</p>
  
  <p>My problem is the following, I can build the Excel model without UDFs and when I run simulations, I can finish all computations in X hours. When I use UDFs, the simulation time is 3X hours or longer. (To answer the obvious question, yes, I need to work with UDFs because if I want to make small changes to the model (like say add another asset type (it is a financial model)) it takes nearly a day of remaking the model without UDFs to fit the new legal/financial structure, with UDFs it takes about 20 minutes to accommodate a different financial structure.)</p>
  
  <p>In any case, I have turned off screen updating, there is no copying and pasting in the functions, the use of Variant types is minimal, all the data is contained in one sheet, i convert all range type variables to  arrays before getting the contents.</p>
  
  <p>What else can I do other than getting a faster computer or the equivalent to make the VBA code/Excel file run faster? Please let me know if this needs more clarification.</p>
  
  <p>Thanks!</p>