Category Archives: VBA

Strategies for getting the last row in Excel with VBA

Using VBA to get the last row in Excel can be a surprisingly complex task. This post will explore some of the different strategies you can use. The first thing to do is to decide what you mean by “The … Continue reading

Posted in Excel, Formatting, VBA | Tagged , | 7 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

Excel JavaScript API Part 2: Benchmark of Read/Write Range Performance

As an Excel Office Developer many of the Excel based applications I develop depend on reading data from Excel worksheet ranges, processing it and then writing the processed data back to a worksheet. In VBA the way to do this … Continue reading

Posted in arrays, Calculation, JS-API, VBA | Tagged , , | 1 Comment

Excel Range.Value(ValueType): What is this parameter?

Whilst visiting Microsoft Research in Cambridge I was chatting to fellow MVP Roger Govier. He mentioned that he was using this strange parameter to the Range.Value (ValueType) method, which I had been meaning to look at for a long time … Continue reading

Posted in Formatting, VBA | Tagged , | 5 Comments

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 , , , , | 7 Comments

Excel Versions Screen Test (Updated): how fast is Screen Updating?

Gurs has an interesting benchmark he has been running on various systems and Excel versions over the years. His results seem to show a massive performance decline in later Excel versions. Looking at his benchmark and its VBA code you … Continue reading

Posted in Calculation, Formatting, Uncategorized, VBA | 9 Comments

Excel Memory Checking Tool: Using LAA to increase useable Excel memory

Most of us sooner or later get hit by one or more of the dreaded messages “Out of Memory” or “Excel cannot complete this task with available resources” or “Not enough System Resources/Memory to Display Completely” , regardless of how … Continue reading

Posted in Memory, VBA | Tagged , , , , | 9 Comments

Writing Efficient VBA UDFs Part 15: Adding Intellisense to your UDFs

For several years people have been asking the Excel Dev team to add the capability to create Intellisense for UDFs to work the same way as native Excel functions. Well, now Govert van Drimmelen, author of the wonderful Excel DNA, … Continue reading

Posted in .NET, UDF, VBA, XLL | Tagged , , , , | 10 Comments

Writing Efficient VBA UDFs Part 14: Handling whole columns using Implicit Intersection – Updated

Excel has an interesting and very efficient trick called Implicit Intersection which allows you to use large Named Ranges and whole column references efficiently. What is Implicit Intersection? When Excel expects to get a single cell reference but you give … Continue reading

Posted in arrays, Calculation, UDF, VBA, XLL | Tagged , , , , | 14 Comments

Excel What-If Data Tables: Faster calculation with VBA

For some reason this year I keep on meeting Excel’s What-If Data Tables. These are used (often in real estate and financial valuation models) to simplify doing sensitivity analysis against a range of values for certain inputs such as interest … Continue reading

Posted in arrays, Calculation, VBA | 3 Comments