Monthly Archives: June 2011

Excel 2007/2010 Used Range Voodoo – Big Grid used range woes

Posted on June 27, 2011 by fastexcel

I have been struggling with cleaning the used range on large workbooks with Excel 2007 and 2010. Besides the UsedRange.Rows.Count problem discussed in the previous post I have met: out of memory Excel hangs performance problems Statusbar not updating Out … Continue reading →

Posted in VBA | 5 Comments

Excel 2007/2010 range.count too many cells bug

Posted on June 22, 2011 by fastexcel

Ian Bennett just sent me details of a bug in build 501 of FastExcel: the error message he got is – VBA Error 6 is Overflow and line 1630 says nCells = Worksheets(j).UsedRange.Count nCells is dimmed as a Long and … Continue reading →

Posted in UDF, VBA | 8 Comments

Writing efficient VBA UDFs (Part5) – UDF Array Formulas go faster!

Posted on June 20, 2011 by fastexcel

Just in case you thought the previous posts on writing efficient VBA UDfs (Part1, Part2, Part3, Part 4) meant we had finished making UDFs run faster, think again – its time to explore UDF Array Formulas. Single and Multi-Cell Array … Continue reading →

Posted in UDF, VBA | 31 Comments

Writing efficient VBA UDFs (Part 4) – Variants, References, Arrays, Calculated Expressions, Scalars

Posted on June 20, 2011 by fastexcel

In part 1 and part 2 of “Writing efficient UDFs” I used parameters defined as Range to get data from Excel. Function VINTERPOLATEB(Lookup_Value As Variant, Table_Array As Range, Col_Num As Long) This works OK if the function is called from … Continue reading →

Posted in UDF, VBA | 3 Comments

Multi-threaded UDFs – Technology, Locking, Race conditions and Deadlocks

Posted on June 19, 2011 by fastexcel

One of the major disadvantages of VBA (and VB6) UDFs is that they cannot be multi-threaded. And since everyone now has PCs with multiple cores and Excel 2007 or Excel 2010 (well except for my better half Jane who is … Continue reading →

Posted in UDF, Wine, XLL | 8 Comments

VBA Read/write Speed Formula – Benchmarking Excel Versions

Posted on June 13, 2011 by fastexcel

Previous posts on Writing Efficient UDFs Part 1 and Part 2 have looked at the importance of optimising the way you transfer data between Excel and VBA. This is often a major factor in the speed (or lack of it) … Continue reading →

Posted in VBA | 3 Comments

Writing efficient VBA UDFs (Part 3) – Avoiding the VBE refresh bug – Updated

Posted on June 13, 2011 by fastexcel

In Part 1 and Part 2 of Writing Efficient VBA UDFs I looked at some simple ways of changing the VBA code you write to make it run massively faster. In this post I look at a bug in Excel … Continue reading →

Posted in UDF, VBA | 14 Comments

Writing efficient VBA UDFs (Part 2) – using Excel Functions inside a UDF

Posted on June 6, 2011 by fastexcel

In part 1 of Writing efficient VBA UDFs I looked at more efficient ways for the UDF to process a Range of data by reading it all into a Variant array. In this post I look at a case (using … Continue reading →

Posted in UDF, VBA | 18 Comments