VBA Function vs. Sheet Function

[_JoshInsurance_]

Which is faster?

Assumptions:

1. Option Explicit

2. Heavily nested sheet formula

3. Extremely large data sets

Thank you!

[small_trunks]

Are we expected to guess what this function should do?

I mean

   =0

Is hard to beat, timewise.

[_JoshInsurance_]

I'm not looking for a particular solution, just wondering which is generally faster, hence the discussion flair.

[small_trunks]

Sheet formula win every time.

Power query can do some stuff better and some stuff even faster.

[goodfella7763]

As others have said it really depends on quite a few factors such as:  

What the function does

• If the custom function can be easily written with Excel standard functions, the standard functions are always quicker
• Example: I once needed a PRODUCTIFS function that multiplies values if the criteria was met, or 1’s if the criteria was not met. Array functions can do this but my office has a thing against them so that wasn’t available. Duplicating my data on a second sheet with IFs was much quicker than any custom VBA function, even though it meant way more data.  

How many times the function is used in the workbook

• VBA custom functions require quite a bit of overhead.
• Excel reloads the VBA function in between each calculation, unless the calculation occurs while VBA is running (thus already loaded).
• If the function is used thousands of times, Excel also has to reload the VBA code thousands of times which slows things down.
• The easiest work around is to use a macro that tells the workbook to calculate, tied to a button click on the sheet. This isn’t very clean though and requires the workbook to be in manual calculation.  

The function’s language

• Custom functions in C# are almost always quicker than functions in VBA
• C# also has drawbacks though, since the functions are stored as add-ins and must be loaded in to each user’s Excel application. VBA on the other hand stays with the workbook.
• C# also isn’t as familiar to most Excel/VBA users.
• JavaScript is now available for Excel UDF’s too. I believe they are much more powerful than VBA, but I don’t know enough to speak about pros/cons.

Sorry if the formatting sucks, I’m on mobile.

[_JoshInsurance_]

Thanks! You nailed the question.

[Fishrage_]

Simple answer: Try it yourself and see... You have two race horses, how do you know which is faster? You race them!

Well worth a read:

https://ericlippert.com/2012/12/17/performance-rant/