by William Whooper (whose site is down. Text copied, links and linked files not available.)

  I found and contacted him in the net on 2010/01/14, so I can report: "donated by william from an old web site of his dating back several years" in his own words.  Thank you, William! Great stuff, following now ... 

On 2010/01/28, William gave me this file with .xlls and Workbooks, so you might find out about Lookup2d and XLLs yourself. Do not forget to manually "add-in" the Add Ins in the .zip-file!

Tip 1 - Writing Complex Workbooks
Tip 2 - Sample C++ XLL with Automation
Tip 3 - XLL Handles / Pointers / Cell Arrays
Tip 4 - More Excel Calc Tree & New VLookup

(1) Working with large books - Calculation Tips

Excel 2002+

In Excel 2002 Microsoft changed the calculation engine so that pages no longer calculate in alphabetical order. Although this has helped some users who run small unplanned sheets, it has had a disastrous impact on very large complex sheets. Excel 2002 maintains a single calculation tree for the entire workbook, whereas Excel 2000 maintains a separate tree for each page. In Excel 2002+ the single large tree can quickly spiral out of control, and calc time go through the roof to such an extent that it's often no longer possible to F9 a large book. Essentially in Excel 2000 users can spit complex calculations across multiple pages in a carefully arranged fashion, but this is no longer possible in 2002+. If the Excel 2002+ calc does succeed, it will store an optimized tree so subsequent calculations are faster. However, simply editing a formula, e.g. with F2 enter, will cause Excel to return back to the starting point. Very, very depressing.

Download Example Workbook (not available here!)

Excel 2000:

In Excel 2000 I have used excel workbooks that are over 100Mb in size (formulas not values!), have calc times of 20 minutes plus, and full bump risk runs lasting literally days. Although these books ran flawlessly, many people complain that excel falls apart and develops strange bugs long before this point. Well the truth is that as long as certain guidelines are followed, Excel2000 is an amazingly powerful tool able to calculate even unbelievably complex books.

The pages must calculate in strictly alphabetical order

The most important rule to remember is that the pages in the workbook must calculate in strictly alphabetical order. Ie, if you have a workbook with sheets "a", "b", "c", "d" then sheet "d" can have formulas pointing to sheet "a", but not the other way around. Why? Because the Excel dependency tree calculates the sheets in alphabetical order and it hates having to come back on itself. So if sheet "a" were to have formulas pointing to sheet "d", it would begin by calculating sheet a, then move on to the next alphabetical sheet, then after d, have to jump back to recalculate some of a, then go back and finish d. Also, start the sheet names with letters not numbers; it sounds strange but the calc order with "001", "002",...,"021" etc can be erratic. If you have more than 26 sheets just have "aa", "ab" etc. Of course you are still free to have sheet names "a.Setup", ..., "z.Results" etc. To see an example of alphabetical calculation open this small workbook and press F9. So this is the most important rule, and you break it at your own peril! In large books possible consequences are: long or uneven calc time, calc fails, calc finishes but you can F2 enter on a formula and it changes necessitating a full calc (the nightmare!). In the unlikely event that you can not avoid breaking this rule: be aware of the risk you taking, do it as simply as possible, and watch out for any calc bugs.

Volatile Functions & Iteration

Volatile functions and Iteration should be avoided when building advanced books. You certainly need to understand what volatile functions are, which excel worksheet function are volatile, and how to recognize a book using them. Volatile Functions recalculate every time a calc event is generated. If calculation is automatic a calc event is generated any time a cell is changed. If calculation is manual a calc event is generated by F9 etc. If a volatile function recalculates it will cause every cell downstream of it to recalculate as well (read tip 4 on this page). In Excel there are several volatile worksheet functions including the popular: INDIRECT( ), OFFSET( ), NOW(), TODAY(), & RAND() (note INDEX( ) is not volatile). A large manual calc volatile book can often be recognized by pressing Ctrl-Alt-F9 and then pressing F9 again. The second calc should be instantaneous (assuming no new DDE / RTD links have come into the sheet etc), if it is not you are looking at a volatile book... and chances are it's just bad design... Quick Tip: How do you avoid OFFSET? Use INDEX(...):INDEX(...) instead as shown in this example: (not available)

Avoid VBA Worksheet Functions & XLL Worksheet Functions with XLOPER Inputs

There is a nasty dependency tree issue with Visual Basic Worksheet Functions and XLL Worksheet Functions with XLOPER inputs. The Microsoft Excel 2002 support site (link is below) has this vague advice:

Excel 2002 is designed so that you do not have to do much to ensure that calculation takes place as fast as possible. The exception is when you are using user-defined functions. User Defined Functions (UDFs) are custom functions created using Visual Basic® for Applications (VBA).

One way to optimize UDFs is to prevent repeated calls to the UDF by entering them last in order in an on-sheet formula. For example, enter =A1+UDF in a worksheet cell rather than =UDF+A1. This way, if A1 is marked for recalculation, but hasn't recalculated yet, the UDF will be called only after A1 has recalculated, avoiding an unnecessary call.

Make sure that the code in the UDF that accesses cells values are as near the top of the code as possible. For example, you might have a UDF that has initial code that takes a long time to run (for instance, it might make a call to an external dynamic link library (DLL) which does some calculation-intensive work), and then code below that which accesses cell values. If the cell values aren't recalculated before you start running code in the UDF, you'll twice pay the penalty of running through the long, slow code at the top of your UDF: first, when the UDF is called, and the not-yet-recalculated cells that you're accessing are moved back to the top of the internal list of linked cells so they be recalculated, and second, after the cells to which the UDF refers have been recalculated.

To understand this issue consider the following VBA worksheet function: 

Function junk(x as double, y as double)
 Static i As Integer
 Debug.Print x & " - " & y
 i = i + 1
 junk = i
End Function 

Open Excel 2000 or Excel 97. Create a workbook with three pages called "a", "b" and "c". Add the VBA function above. Put =RAND() in a cell on each page. Now on page "b" add the junk function and hook it up to the RAND() cells on page "a" and "b". Press F9 a few times. Notice the static variable increments by one each time you press F9. Now change the argument linking to sheet "a" to link to the RAND() cell on page "c" instead. Press F9 a few times and notice it no longer jumps by one on each calculate - in fact it jumps by three! In Excel 2002 (which handles non alphabetical sheet dependencies better) it does not happen in this example - but I believe in other more complex cases it could. 

What is happening is that Excel does not properly incorporate the dependencies of a VBA worksheet function into the calc tree. Instead it calls the function, then realizes that it is passing an uncalculated cell, which it passes as null (0 in this example), then corrects itself by calling the function again. 

For complex functions with long calc times this is a disaster! You can get around it by writing things like = 0*ROWS(rangeinput) + my_vba_function(rangeinput), but it's horrible. 

Moral: Avoid VBA Worksheet Functions! XLLs Worksheet Functions do not do this as long as you avoid inputs with the XLOPER data type (use "P" instead). 

Excel 'prefers' its pages to calc from top to bottom then left to right 

Bear in mind this rule, but don't get too carried away. If a page is complex the first calc might take a while but subsequent calculations will be faster. If it's still too slow then split the logic across more than one page. 

VBA Auto_Open Application.OnKey Calc Code 

With a large slow workbook you need calculation automatically set to manual on open. When the user presses F9, you also want the calc to complete without being accidentally interrupted. The Excel 2000+ code below shows an example of how to achieve this: 

Sub Auto_Open()
 Application.Calculation = xlManual
 Application.CalculateBeforeSave = False
 Application.OnKey "{F9}", "CalcNorm"
 Application.OnKey "^%{F9}", "CalcFull"
 Application.OnKey "+{F9}", "CalcPage"
End Sub

Sub CalcNorm()
 Dim d As Double
 If ThisWorkbook.Names("CalcTimeLast").RefersToRange.ID = "" Then
' This is the first calc since book was opened so
' we need to make it a full calc
CalcFull
Exit Sub
 End If
 Application.StatusBar = "Calc..."
 d = Timer
 Application.Calculate
 d = Timer - d
 Application.StatusBar = False
 ThisWorkbook.Names("CalcTimeLast").RefersToRange.Value = Now()
 ThisWorkbook.Names("CalcTimeDuration").RefersToRange.Value = d
End Sub

Sub CalcFull()
 Dim d As Double
 If ThisWorkbook.Names("CalcTimeLast").RefersToRange.ID = "" Then
 ThisWorkbook.Names("CalcTimeLast").RefersToRange.ID = Now()
 End If
 Application.StatusBar = "Calc Full..."
 d = Timer
 Application.CalculateFull
 d = Timer - d
 Application.StatusBar = False
 ThisWorkbook.Names("CalcTimeLast").RefersToRange.Value = Now()
 ThisWorkbook.Names("CalcTimeDuration").RefersToRange.Value = d
End Sub

Points to notice in the code above 

The logic above is designed for an environment in which each workbook is run all alone inside its own excel session. For very large books with no dependencies on external sheets that is probably the norm. The F9, Ctrl-Alt-F9 and Shft-F9 have been trapped and assigned to macros to avoid the user interrupting the calc. The calc time and duration are recorded in a couple of worksheet cells with range names "CalcTimeLast" and "CalcTimeDuration". The Range.ID logic is designed to ensure the first calc is always a full calc - important because Excel does not always consider every cell to be dirty on open. When a workbook is loaded the .ID of every cell will be "" and you can modify it as you like with the changes persisting until it's closed. The ID property of the Range was added in Excel 2000. It has another neat feature: you can modify the ID of a cell from inside a vba function; thus breaking the normal principle that a worksheet functions can not modify cells in a workbook. RefersToRange logic: Range("xyz") refers to the active book so this code uses ThisWorkbook.Names("xyz").RefersToRange instead. This is a important programming tip for beginners. Notice the book calculates with Application.Calculate alone. There is no need to manually set the page calc order etc. 

Misc Stuff 

In Excel 97 Application.CalculateFull will not work. This is the workaround: 

Sub CalcFull()
Dim i As Integer
i = Application.Calculation
Application.Calculation = xlCalculationManual
Dim xb As Workbook
Dim xw As Worksheet
For Each xb In Application.Workbooks
For Each xw In xb.Worksheets
xw.EnableCalculation = False
xw.EnableCalculation = True
Next
Next
Dim d As Double
d = Timer
Application.Calculate
d = Timer - d
Application.Calculation = i
MsgBox Format(d, "0.00")
End Sub 

Also in 97 running this code cleaner utility from time to time on your books is a good plan. In 2000+ it's not required. 

In Excel 2002 several new calc methods were added. Keystroke Ctrl-Alt-Shft-F9 or Application.CalculateFullRebuild rebuilds all dependencies across all open books and then calculates (in older versions you had to rebuild the tree by Edit, Replace, "=" with "="). Also Application.CalculationInterruptKey addresses one of the issues mentioned in the code above. Application.CheckAbort stops recalculation except for an optional range. Range.Dirty marks cells as dirty (in older versions you had to dirty cells by .formula = .formula which was very slow). There is also Application.CalculationState which changes to xlPending when calc is set to manual and at least one cell is dirty, then xlCalculating during a calc, then xlDone when finished. Application.CalculationVersion is another new method, maybe it will be useful in the future. If calculation is set to automatic and the dependency tree becomes too deep the status bar constantly shows the word "CALCULATE" and every calc is a full calc.... but its a limit you are unlikely to hit as a book of this size would probably be impossibly slow in auto calc mode anyway. A Microsoft Technical article on Excel calculation is at this link. 

Conclusion 

With the points above, plus the Lookup2dRef addin on this page, you should be able to get on and build some gigantic sheets. Nevertheless, to do a really smart job, you need to add xll function handles, and ideally build yourself a large book template / framework / xla. This might include: Calc keys as above + Calc with market data refresh + Selection.Calculate key (useful trick). Also, Menus / Command bars for: Workbook Protection* , Page Navigation, Hide Pages, Hide Ranges on a page. Also Workbook usage log, zip and mail or message, etc etc. At one time I had a number of people working for me, and over a two year period we gradually build up a framework of such beauty that those once ugly and chaotic Excel Spreadsheets became works of art that brought tears to my eyes! * Workbook Protection hint: Ever noticed the auditing bar does not work after protection is turned on? It's a pain, but you can circumvent it. Build your own auditing bar with the same icons that, whenever the user clicks the button, turns protection off, traces, and then turns protection back on again. While on the subject, in your auditing bar allow the user a button to enter a protected cell and use F9 etc in the formula, then when he presses Return to exit, it reverts back to its original state. 

(2) Building a C++ Excel Addin - XLL C++ XLL Sample Visual Studio Project / Template 

This is a C++ Visual Studio 6 Project, not .Net. 

An XLL is a renamed DLL with a few extra functions that allows Microsoft Excel to open it directly. XLLs are primarily used to add fast custom worksheet functions written in C++. The speed difference between an XLL worksheet function and VBA worksheet function is simply gigantic. The book to buy on the subject is the Excel 97 Developer's Kit. However, even with the book, some people find it a little tricky to put together their first XLL. Therefore, at this link I provide a complete and working C++ XLL Visual Studio project (I used Visual Studio 6.0) with several sample worksheet functions, macros and an Automation example. Just open Anewxll.dsp and build. Note Microsoft links about building an XLL and the framework (frmwrk32.exe, once called the Generic Template for XLLs, GENERIC.C etc) are here. If you prefer to write your XLLs in C instead of C++, here is a link to an old addin project I wrote that includes a Black-Scholes calculator. I used to use this addin to trade LIFFE Short Sterling and FTSE index options and a sample workbook is also included. You are welcome to use the code, or just the xll addin and sample sheet for free and in whatever way you wish. One of the models has two time inputs, one for volatility time and one for discounting time (eg set volatility time to working days / 252 and discounting time to days/365 ). Please email me at email@whooper.co.uk if you download this project and find it useful. No, its not to put you on some junk email list, its just because I like a little appreciation and feedback! Other XLL frameworks: If you prefer to pay for a framework try XLL Plus. 

More Help on Building XLLs

First you will want to read online, or better still buy, the Excel Developer's Kit. 

- Understand that an XLL is just a DLL with a few extra functions that allows Excel to open it and register the exported functions and macros in such a way that they can be used directly on the worksheet or in VBA using Application.Run. It is possible, but would be unusual, to use a DLL with some REGISTER commands in code instead of an XLL. 

- Think of an XLL as a super fast, easy to write, easy to install, C++ Excel specific addin. The downside is (a) they do not use standard data structures, eg variant safe arrays, hence usage in VBA is normally only possible via Application.Run and (b) they use the old Excel4 macro language methods to control Excel, not the OLE Automation Visual Basic for Applications Object Model. Point (a) can be best circumvented, if required, by building the functionality in a COM DLL and calling it from an XLL. Point (b) can be best circumvented by switching to an entirely different technology, MS Office COM Add-ins. However, an Office COM Add-in function can only be used directly on the worksheet in Excel 2002 or above. At this time, the XLL definitely remains the standard and fastest way to add custom worksheet functions to Excel (eg the Analysis Toolpak which ships with Excel). However, you might want to consider using an Office COM addin when adding fancy macros and forms (as opposed to just worksheet functions) to Excel. Note, the story is actually more complicated that this because as long as you can find the correct IDispatch pointer (by iterating through the ROT) you can automate XLLs as well, and the example above does this. Also, you can build hybrid Office Com Addins / XLLs. An Excel 2000+ Office COM Add-in sample project is at this link (download, extract and open the Readme.txt file). 

- Learn about the XLL data types and REGISTER function. B is a double, J is an integer, C & D are a strings etc. The K type is great for passing in or out an array of doubles and you can still return an error (#NUM!) by passing back a null pointer. The OPER and XLOPER types are more complex but allow your function to accept / pass any data type or error. Use OPERs instead of XLOPERs unless you have a good reason to do otherwise. Note, because the OPER has the same data structure as the XLOPER, you can leave LPXLOPER in the function declaration and just change the REGISTER declaration to type "P" (more about this). Remember that if your XLL function returns a pointer, the data itself cannot be contained in local variables (this is clear from the Anewxll examples which often use static variables). 

- Remember to maintain the external functions list in the Anewxll.def file. Notice that the name of the xll also appears inside the xlAddInManagerInfo function. It's easy to create a new XLL project, you don't have to rename all the files in my sample project: in visual studio, select file new, choose a name and the MFC AppWizard(dll) option, then just click finish (or for automation with a type library select the 'Automation' option before finish). Then copy into the directory, and add to the project, the files: framewrk.cpp, framewrk.h, xlcall32.lib, xlcall.h. Then open the project_name.cpp file and paste in the XLL code from whooperX.cpp. Then open whooperX.def and copy the xlAutoOpen, xlAutoClose and xlAddInManager declarations to project_name.def. Finally in 'project settings', 'link' change the name to xll from dll and build. 

- Try debugging your addins; set breakpoints and step through the code as excel runs the addin. In Visual Studio 6 set the debug info to "Program database" not "Program database for edit and continue" (this has already been done in the sample project above). If the path to excel.exe differs between your machine and mine you will need to change this before debugging the enclosed addin. - The sample XLL contains an Automation example, but remember that XLLs are really only supposed to access the (faster) Excel 4 XLM Macro Language Interface, not the Visual Basic for Applications OLE Automation Object Model (more about this). To access the VBA interfaces Microsoft recommends using an Office COM addin (mentioned earlier). Some useful articles on automation are at this link. Nevertheless, it should be said that, Microsoft's caution aside, Automation even inside XLL worksheet function calls does work in many circumstances (in the wrong ones Excel will usually just crash) - and it allows nifty things like setting Application.OnTime(Now(), Macro) to call back the XLL, accessing the Range.ID property of cells, and tracking cell locations in RTD like functions. 

- Learn about the EXCEL4 function to access XLM macro functions and native excel worksheet functions. You can download the old Excel 4 XLM Macro Language Function Reference Help Manual (MACROFUN.HLP) at this link. The integer constants you need to pass to EXCEL4 to access the XLM language are defined in XLCALL.H. Learn about xlFree and its importance in preventing memory leaks when EXCEL4 returns an XLOPER (with a pointer to attached data) to your XLL. 

- Consider adding handles to Excel so a cell can contain, and xll worksheet function can pass, pointers to arrays of data. This vastly simplifies complex sheets. Ie a function can return to the sheet the string value "~ab7c" which is a pointer to an array of data stored on the heap. This can then be passed into other functions or exploded using a function like GetValue( pointer, row, col ). 

- Consider a debugging console either in or alongside excel so your complex functions can return more than just #VALUE!. Add a global debugging flag that gets checked by your addins in order to see, when required, detailed debugging calculation detail even if there is no error. 

(3) XLL Worksheet Function Handles / Pointers / Arrays in a Cell. 

Many custom worksheet functions need to pass and accept arrays as well as single values. However, dealing with array inputs and outputs quickly becomes very painful, and the solution is a technology that allows an entire array (of any dimension) to sit inside a single worksheet cell. 

One of the problems with array inputs is that they can't be fed into VLOOKUP. For example, suppose you have a function that prices bonds of different currencies given an array input of discount factors. Given a currency input, you can not use Vlookup to point the pricing function at the correct table of currency discount factors - instead you have add range names and then use the INDIRECT function. However, INDIRECT is a volatile function with potentially dreadful effects on calc time (see this link). 

Another problem with array inputs and outputs is that you need to know the array size in advance. Suppose for example, you wanted to build a DSUM like function that sorted an array input, calculated the number of unique rows and returned an array of the summarised data (there is one inside the sample addin). The return array has no fixed size so if you used conventional Excel array formulas you would have to guess the largest possible size and press Ctrl-Alt-F9 leaving hundreds of #N/As around the edges etc. 

As well as the lookup problem and dynamic size issue, extensive use of arrays clutters complex sheets. For example, a worksheet function might need to take an array input of all US bank holidays over the next 50 years; but the user doesn't want to see all that data and dumping it onto the sheet is just plain ugly. 

All these problems are overcome by allowing Excel to hold an entire array in a single cell. Unfortunately, Excel doesn't have this functionality built in (and given that it would be a substantial upgrade probably never will), so the C++ XLL developer has to add this feature to Excel himself. 

The trick is to return and pass string handles, or array pointers, between worksheet functions. A cell containing a handle might look like "~a01b", and as far as Excel is concerned just contains a string, but xll functions passed this value understand that it is actually a pointer to an array stored on the heap. One downside is that built in Excel worksheet functions like SUM don't understand that it's an array, but this can be overcome easily because if a SUM function is required the developer just adds one that does understand his handles. 

Adding XLL handles to Excel enormously simplifies complex sheets, indeed it's the probably the most important upgrade to Excel . Because it's fairly complex you only tend to encounter this technology at large banks - and even then only a few of them implement it really well. 

How does it work in more detail? Well the sample sheet and xll you can download at the end of this section demonstrates the technology nicely. When an xll function is passed a string handle it's fairly obvious that it can decode the string to access the array easily. Understanding how a function returns a handle is the complex part. What happens is that there is a map of cell location and unique attached handle, and when a function returns a handle it searches this map for the calling cell address, and if it finds a handle attached overwrites it. Think about it for a moment, and you will realise that a problem occurs when the user moves or deletes cells - this can be overcome by a cleanup macro that can be run from time to time. Note, this logic still allows one to write the formula = createhandle( creathandle( createhandle ( ... ) ... ) ... ) but =createhandle( createhandle(...), createhandle(...), ... ) would be impossible. 

To use xll functions that pass handles in VBA (via application.run) it's best to write simple vba versions of each function that take an extra handle argument. So on the worksheet you use =createhandle( ... ), but in vba you use createhandle_vba( string hndl, ... just as worksheet function ... ), and the vba developer manages his use of handles as required. Eg he passes "" to create a new handle, or passes a string he received earlier to overwrite. I haven't bothered adding vba counterpart functions to the demo xll worksheet functions here, but the concept should be clear enough. 

A sophisticated implementation of xll function handles will ensure that every xll written by the organization uses the same compatible handle technology. Also, there might be a very large number of general handle manipulation functions - eg the PasteValue, Merge, Sort, Crop etc functions you will see in my demo. Purchasing a framework which already includes many general handle manipulation functions may be cost effective. If you are interested in purchasing one, contact me at email@whooper.co.uk, it offers significant savings over the in house cost of development. The sample sheet and XLL can be downloaded at this link. Open handles.xll and then the demo.xls sheet. The demo version just supports 2D arrays, but 3D+ is of course possible. Note this download is only really designed to demonstrate xll function handles. However, some users may find the xll DSUM function genuinely useful, and to them I offer it as freeware. 

(4) Excel Calculation Tree Issue & New VLOOKUP 

There is an issue with the excel calculation tree which haunts all serious Microsoft Excel users. What the Excel calc tree should do, had it been more sensibly designed, is check as it calculates each node along the tree whether or not the result of the calculation has changed the value of the node; and if not, discontinue further calculation along that path of the tree. 

For example, consider the following: 

[cell A1] = 2 
[cell A2] =IF( A1<10, 0, 1) 
[cell A3] =some_slow_function( A2, ...) 

Now if the user changes cell A1 from 2 to 3 and recalculates the spreadsheet, excel will begin by calculating dependent cell A2. However, because both 2 and 3 are less than 10 the result of cell A2 will remain equal to 0. At this point excel should stop (end the calc), however, it will instead still mark cell A2 as dirty and continue on to calculate A3 (which is dependent on cell A2). Hence the calc time has become unnecessarily slow. 

This quirk (not quite a bug) of the calc tree (also called the excel dependency tree / excel dependency engine) becomes especially unacceptable when ones makes use of the VLOOKUP function (volatile functions, like TODAY(), are another big problem). This is because the VLOOKUP function is often used to point to a very large range of data, and changing any cell inside that range will trigger all cells dependent on the VLOOKUP to recalculate even if the VLOOKUP result remains unchanged. 

For example suppose you have a named range (A1:B7) called "Bonds" containing a table of bond prices as follows: 

(missing graphics here) 

And you link into that table with formulas like: 

=VLOOKUP( "Bond One", Bonds, 2 , FALSE) 

and then you feed the result of this lookup into a complex function that calculates a forward price etc. Now suppose you have a large number of VLOOKUP formulas into that table, and you change the price of Bond One. Well at this point all those complex formulas are going to recalculate even if they don't depend on the the price of Bond One. This effect becomes horrendous if you want to bump the price of each bond in turn by a small amount then observe the total p&l change on your portfolio to obtain deltas. 

Microsoft could of course solve this problem by changing their dependency engine, unless and until they do, one has to work around it. 

The solution is a new lookup addin which must break the dependency on the entire range, but still always point to the correct location in the table even if the position of data moves around. 

Ok, so here is how one can do it. 

Instead of: 

=VLOOKUP( Row Wanted, Range, 2, FALSE) 

use: 

=VLOOKUPNEW(Row Wanted, String Range Name,  Row Link, Value Link) 

for Bond One in the table above we would have: 

=VLOOKUPNEW( "Bond One", "Bonds",B2, C2 ) 

and what the function does is check whether Row Wanted = Row Link , and if they do, it immediately returns the Value Link argument. Ie, with the line above, if "Bond One" = B2 then the function returns C2. If, on the other hand, the function finds that the Row Wanted <> Row Link it momentarily returns #REF, and then automatically searches the Range for a match and rebuilds the cell formula (without any user intervention) to have the correct Row Link and Value Link arguments. So the Row Wanted and String Range Name are user inputs, but the Row Link and Value Link inputs can be ignored by the user as they are constructed by the addin automatically. 

So if you typed: 

=VLOOKUPNEW( "Bond One", "Bonds", 0, 0) 

or even 

=VLOOKUPNEW( "Bond One", "Bonds", B3, C3 ) 

the function would momentarily return #REF and then rebuild its own formula to become: 

=VLOOKUPNEW( "Bond One", "Bonds", B2, C2 ) 

Notice that if the price of Bond Two in cell C3 changes the function is unaffected because its only dependency is on B2 and C2. Yet at the same time, the 'saving' of the Row Link argument inside the formula by the addin ensures that it will always point to the correct result or rebuilt itself. The C++ addin and sample sheet at this link makes all this very clear. 

How does it rebuild itself? 

What happens is that when the worksheet function detects that Row Wanted <> Row Link it adds its cell address (via the application.caller object) to a global variable. Then another piece of code handles the Excel Application Calculate Event (which fires at the end of the calc), and checks if the global variable contains any 'bad' cells. If there are bad cells they are rebuilt and a new calculation is fired, without the user even noticing. 

How fast is it? Well, if the Row Wanted = Row Link the worksheet function instantly returns the Value Link argument without having to do any searching. So if the the lookup is in good condition the speed is as fast an an XLL can be. If the lookup is incorrect the rebuild process is a little slow (at least compared to VLOOKUP), maybe one second per 1,000 lookups. 

How reliable is it? This technique really does work even of spreadsheets of gigantic proportion (eg 50Mb in size, tens of thousands of lookups, and complex demanding functions taking calc time to 20 minutes or more). The function is fairly invisible as well: when you write application.calculate in VBA an extra calc may take place but the programmer need not think about it. The second calc is only an F9 not a Ctrl-Alt-F9 so it's usually super fast. 

Downside? Well the rebuilt time could become onerous if the Row Wanted or the order of the data in the table is in constant flux. Also, its worth thinking about lookups which are constantly bad. Whenever a lookup is bad it will be added to the global variable and the event code will search through the range to make a match if possible. So a bad lookup is much slower than a good lookup even if its not going to rebuilt. Most importantly, you have to be careful not to use (badly designed) volatile sheets. If you press Ctrl-Alt-F9 and then immediately press F9 again (I am assuming no new DDE etc data has come into the sheet) does the monster take another five minutes to calculate? It should come back instantly unless you have volatile functions like INDIRECT( ) and NOW() causing large portions of the sheet to recalculate. This would be a (serious) problem because the second calc triggered by the addin would no longer be simply fixing a few broken cells. 

Conclusion: Do not simply replace every vlookup in your books - use this addin only if the dependency fix will improve the speed of your sheet. However, do not be afraid to use it when appropriate, indeed, many complex Excel applications are impossibly slow without this solution. 

Ok, you have heard enough, you want to see some sample sheets and play with the function. The link is below. If you like this please show your appreciation by emailing me. Also do any other users already do this, perhaps in another way? I dreamed this up one night a long time ago - I can't tell you how amazing and useful it is. Of course the general technique is applicable to a whole series similar problems - eg replacement of the volatile functions OFFSET and INDIRECT. I believe the Bloomberg real time data addin may use the same technique (of handling the calc event to change cell formulas). 

Note the version here takes both a row and column input. Ie a two dimensional version of VLOOKUP / HLOOKUP that searches the first row and the first column column of the range for an exact match. Syntax: 

=Lookup2dRef( String Range Name, Row Wanted, Col Wanted, Integer Option, Row Link, Col Link, Value Link) 

Lookup function in C++ XLL, VBA XLA & Samples (Excel 97+) (broken link!)

All the files contain some code so you will have enable macros - but it's harmless stuff, no install, no registry setting, no messing with the toolbars etc. Calc is set to manual in the sample sheets. Just open the .xll, then open the .xla, and then the sample sheets. In the Lookup2dRef demo sheet start by carefully reading Example 1 and then move to Example 2. whooper.xla password is "x". There are small additional utility functions in the XLL which are described here.