Search Results for: Text vs

TEXT vs VALUE vs VALUE2 – Slow TEXT and how to avoid it

I was intrigued by a recent post pointing out that using .Text to retrieve data from Excel ranges got slower and slower as you iterated through the rows. So I took some time to explore and compare the three main … Continue reading

Posted in UDF, VBA | 47 Comments

Excel JavaScript API Part 3: Benchmark of Processing Arrays Performance – Loop vs Reduce vs COUNTIFS vs VBA

In a previous post I compared several different ways of processing data with VBA. The fastest way was to get the data into a variant array and loop on it. JavaScript has some powerful array methods so I wanted to … Continue reading

Posted in arrays, JS-API, VBA | Tagged , , | 4 Comments

Finding missing items in lists: VLOOKUP vs COMPARE.LISTS performance and ease of use

Returning to the subject of finding the missmatches between 2 lists I want to compare using VLOOKUP with using SpeedTools COMPARE.LISTS. Test Data My test data consists of 2 lists of 500000 6-digit numbers. Most of these numbers match, but … Continue reading

Posted in Calculation, Lookups, UDF, XLL | Leave a comment

Excel JavaScript API Part 1: Overview & Comparison

I have been spending time in the last few months experimenting with the new JavaScript Excel API. This is the first of a planned series of posts examining the JS-API from the point of view of an Excel Office developer. … Continue reading

Posted in .NET, JS-API, UDF, Uncategorized, VBA, XLL | Tagged , , , , | 10 Comments

SpeedTools AVLOOKUP2 & MEMLOOKUP versus VLOOKUP – Performance, Power and Ease-of-Use Shootout Part 1

Its time for some performance tests to see how the new functions in SpeedTools stack up against the standard Excel functions. First up is MEMLOOKUP and AVLOOKUP2 vs VLOOKUP! SpeedTools Lookups are easier to use, more powerful and less error … Continue reading

Posted in Calculation, Lookups, UDF, XLL | Tagged | 2 Comments

From VBA to C Part 5 – Some Pratfalls – the mistakes I keep making

I have been coding in VBA since Excel 5 (1993) so I keep trying to do VBA things in C, specially when I spend a few weeks doing VBA stuff and then switch back to C. Here are (just a … Continue reading

Posted in UDF, Uncategorized, VBA, XLL | 7 Comments

Evaluate Functions and Formulas fun: How to make Excel’s Evaluate method twice as fast

Prompted by a comment from Sam on Match vs Find I thought I would take a look at Excel’s rather quirky Evaluate method with Excel 2010 to see how it performed. The Evaluate method internally uses Excel’s formula parser and … Continue reading

Posted in Calculation, UDF, VBA | 43 Comments

Excel UDF Technology Choices – Snakes & ladders with VBA, VB6, .NET, C++, COM, XLL, Interop …

I develop a commercial Excel addin product, FastExcel, one component of which contains a number of UDFs. I have been planning and developing the next version of FastExcel for some time, and it probably won’t surprise you to hear that … Continue reading

Posted in .NET, UDF, VBA, XLL | 21 Comments