Category Archives: VBA

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 | 8 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 , , , , | 9 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

Writing Efficient VBA UDFs (Part13): MaxMinFair Resource Allocation – an array UDF example

Last year we got to spend a great day at Microsoft Research in Cambridge (UK). one presentation was about storage strategies and featured (amongst other stuff) an interesting algorithm called MaxMinFair. The MaxMinFair algorithm You can read about it here … Continue reading

Posted in arrays, Calculation, UDF, VBA | Tagged , , | Leave a comment

Excel ForceFullCalculation: Trading off Editing speed vs Calculation speed

A recent post at Stack Overflow reminded me that I have not posted about the pros and cons of Excel’s Workbook.ForceFullCalculation property. So what does Workbook.ForceFullCalculation do? The simple answer is that it switches off Excel’s smart recalculation algorithm, so … Continue reading

Posted in Calculation, Memory, Uncategorized, VBA | Tagged | 7 Comments

Calling XLAM/XLL/Automation UDFs from VBA: Evaluate, Run, or Reference?

I have this nagging feeling that I do not know the best way to call UDFs from VBA. So after a couple of days of research here is what I have found out so far. The Available Methods Application.ExecuteExcel4Macro This … Continue reading

Posted in Calculation, UDF, VBA, XLL | Tagged , , , , | 4 Comments

Timing the Ins and Outs of User Defined Functions: Multi-Cell array formulas can be slow

I was looking at some multi-cell array formula UDFs with John and Rich and could not understand why they seemed so much slower than I expected. Each UDF (written as C++ XLLs) read data from around 200 cells using around … Continue reading

Posted in Calculation, UDF, Uncategorized, VBA, XLL | Tagged , , , , , | Leave a comment

Extracting Digits from Text: Using Formulas and Designing a Missing Excel Function: GROUPS

An Excel problem that crops up quite often is how to extract digits (0-9) from text. The text might be part numbers, or web addresses, or currency values or … Some cases are easy to handle with formulas: A fixed … Continue reading

Posted in UDF, VBA, XLL | Tagged , | 4 Comments