I also found that when using the simple Excel function "=SUMIF(...)" it is very quick, while using VB code it takes ages. I could not believe the difference in time.

I asked ChatGPT: "Why is SUMIF in Excel so much faster than doing the same with visual basic code?" It gave me the following:

The SUMIF function in Excel is indeed faster than writing equivalent code in VBA for several reasons:

Internal Optimization: Excel functions like SUMIF are highly optimized in their native code. They run at a low level directly in the Excel engine, which processes them much faster than VBA, which is an interpreted language.

Efficient Memory Management: Excel functions operate within Excel’s own efficient memory management, while VBA often requires additional overhead for memory allocation and array handling. VBA is slower because it has to manage the data movement between the worksheet and the VBA runtime.

Parallel Processing: Excel’s functions are optimized to take advantage of multithreading when possible, especially in newer versions. VBA, however, is single-threaded, which means it can't leverage multi-core processors for parallel computation.

Overhead of VBA Loops: When you try to replicate a SUMIF using VBA, you often use loops and conditional statements, which add significant overhead. Excel functions like SUMIF handle large ranges in a single pass, while a VBA loop performs individual operations for each cell.

Fewer Interactions with the Worksheet: Excel’s built-in functions minimize interactions between Excel’s interface and the code itself. VBA, however, has to read and write back to the worksheet, adding more time to each operation.

For scenarios with large data sets or frequent calculations, using Excel's native functions is often much faster than relying on VBA.