Convert to VBA Application.WorksheetFunction

Dear Reader,

Can anybody help convert this formula to its VBA version.

Please Login or Register  to view this content.

Many thanks!

I don't know that there will be a single "VBA version" of that formula. I'm also doubtful that it will fit in a single VBA statement. There will probably also be questions related to the procedure context (what variables are holding the information needed by this block of VBA code, what information does this block of VBA code need to find).

Is there a specific part of converting this VBA that you have trouble with? For example, the "outer" IFERROR() function performs simple error trapping. Are you familiar with error trapping strategies in VBA? If not, you may want to review tutorials such as this (http://www.cpearson.com/Excel/ErrorHandling.htm ) to research how to handle errors in code.

I also see some IF() functions in there. Can w assume that you are familiar with VBA If ... Then ... Else ... End If structures? https://msdn.microsoft.com/en-us/library/752y8abs.aspx If not, that would likely be more useful research. Your function looks like it is trying to choose between two different conditional sums, and this If ... then ... else ... end if seems like the closest equivalent in VBA:

Please Login or Register  to view this content.

Beyond that, as we break that ugly megaformula down into individual parts, are there specific parts that you do not understand how to convert to VBA?

What exactly is this function supposed to do? What are these two conditional sums? What does the source data look like? How many copies of this function are in the spreadsheet.

Simple things that can speed up something like this:

1) Your formula uses full row and column references. Unless you really are using the entire 1000000 row x 60000 column spreadsheet, many formulas are faster if you limit the references to a suitable size.

2) It appears that your formula is using two named ranges. I recently learned that, depending on the exact structure, named ranges can slow calculation down: http://www.excelforum.com/excel-form...ml#post4218709 It would probably help to understand what those named ranges/formulas are and see if they are contributing to the slow calculation.

3) https://msdn.microsoft.com/en-us/lib...ice.12%29.aspx This article has more than you probably want to read about spreadsheet performance. There are several suggestions given. The most common one I suggest is to unwrap these large, complex megaformulas into helper cells. Oftentimes, megaformulas like this are repeating the exact same calculation multiple times. By pulling the repeated calculation out into a helper cell, that calculation need only be performed once rather than hundreds of times.

4) A lot of times, conditional sums like this turn out to be about "creating a summary report from a large database". Many times, these database reports can be accomplished much faster by using pivot tables. I personally am not very skilled at using pivot tables, but have seen many cases where a pivot table report calculates much faster than several SUMIFS().

Those are a few ideas, I'm sure there are other suggestions that could be made. In order to really make specific suggestions, we would probably need you to upload a sample spreadsheet.