Latest Information on TriLookup

2004-06-02

Version 2.2 Released

·        From this version on TriLookup will also work on Excel for Macintosh versions 98, 2001, and X.

·        Fixed a bug in the TVPOLYDATA and THPOLYDATA functions that caused R² to be calculated as 1 when the actual value of R²  was less than 0.01.

·        Changed the way the best fit curve is determined by the TVPOLYDATA and THPOLYDATA functions. The curve with the maximum value of R² is selected as the best fit curve, as stated in TriLookup documentation. Previous versions picked the curve with the minimum Sum(Error²). While in most cases the maximum R² corresponds to the minimum Sum(Error²), sometimes the two don't coincide.

·        TriLookup example workbooks were reformatted to use old style Forms controls instead of ActiveX controls on the worksheets. This solved compatibility issue on Macintosh and it also shortens the time required to open the example workbooks.

·        A new combo box has been added to the worksheets in the Interactive Examples workbook which lets you select whether or not to hide the #N/A points on the line diagrams. The process of removing the #N/A points can take a long time on a slow computer.

·        Starting in this version, the evaluation version of TriLookup will stop functioning after 30 days and all TriLookup functions will return the following message:

“Error: TriLookup 30 day evaluation period expired!”

2004-03-21

Version 2.1 Released

·        A new parameter called Index_mode has been added to the TVLOOKUP, TVLKP, THLOOKUP, THLKP, TVPOLYDATA and THPOLYDATA functions. Index_mode is used to explicitly choose the way lookup and return rows/columns are selected: either by their titles (Index_mode = FALSE), or by their index number, i.e. the position in Table_array (Index_mode = TRUE).

Note that in version 2.0 the way of identifying lookup and return rows/columns was not explicitly specified. Instead, identifying a row/column by its title took precedence over specifying it by its index number, which in some cases could have caused ambiguity regarding which row/column is being selected. This ambiguity has been eliminated with the addition of the Index_mode parameter.

Note that Index_mode is the last parameter in the list, it is optional and its default value is FALSE. Therefore, the formulas created with TriLookup versions 1.x, (which only allows selecting lookup and return rows/columns by titles) will not be affected and can be used without modifications. However, the formulas created with TriLookup version 2.0 that implicitly use row/column selection by index number, have to be modified by setting Index_mode = TRUE.

Thanks to Mark Ivanov from EchoStar Satellite Corporation for suggesting the option of specifying lookup and return rows/columns by position, and for pointing out the shortcomings of the method implemented in Version 2.0.

2004-03-19

Version 2.0 Released

·        Thanks to Laurent Longre’s FunCustomize.dll add-in, the problem described in section Problem After Moving Old Workbooks with TriLookup Functions to Another Computer was eliminated. The new workbooks will automatically reference TriLookup 2.0 if you refer to any of the TriLookup functions in the cell formulas. The references will stay valid after the workbook is moved from one computer to another.

·        Thanks to Laurent Longre’s FunCustomize.dll add-in, the Formula Palette dialog now also displays a short help message for each of the function parameters.

·        In addition to identifying the lookup and return rows/columns by their titles, you can now also use the Lookup_title and Return_title parameters to specify the lookup and return rows/columns by their position in Table_array (1 for the first row/column, 2 for the second row/column, etc.).  The affected functions are TVLOOKUP, TVLKP, THLOOKUP, THLKP, TVPOLYDATA and THPOLYDATA.

·        The TriLookup functions now appear in a separate TriLookup function category in the Paste Function dialog.

·        Fixed the bug that caused T2LOOKUP, T2LKP, T3LOOKUP and T3LKP functions to return a #VALUE error when the number of rows multiplied by the number of columns exceeded 32767.

2004-01-21

Link to Version 1.2 Fixed

If you downloaded and installed version 1.2 between January 15 and January 21 2004, chances are that you got the version 1.1 again because of an incorrect link on the TriLookup Download page. If this is the case, you should download version 1.2 again and reinstall it.

2004-01-15

Version 1.2 Released

• The setup program will now also install TriLookup for Excel 2003 in addition to Excel 97, 2000 and 2002 (XP).

• Fixed a bug in handling extrapolation for Interpolate (X/Y/Z_interpolate) = 6, when the values of the last three points in the series (the ones used to calculate the extrapolated result) are not uniformly ascending or uniformly descending, i.e., they produce a local maximum or a local minimum.
  
  Versions 1.0 and 1.1 return Excel’s #VALUE error, even for Error_msg = TRUE.
  
  Version 1.2 returns a #N/A error, or if Error_msg = TRUE the following error message:

“N/A {Err.315} Cannot extrapolate for Interpolate = 6, when the end 3-point segment has a minimum or a maximum.”

• Changed the way extrapolation is performed when Interpolate (X/Y/Z_interpolate) = 6.
  
  Versions 1.0 and 1.1 extrapolate by extending the hyperbolic curve defined by the end 3-point segment. Due to divergent nature of the hyperbolic curve, this usually leads to highly unpredictable results.
  
  Version 1.2 extrapolates by extending the tangent drawn through the end point of the hyperbolic curve defined by the end 3-point segment. The extrapolated values now lie on a straight line, which makes the results of extrapolation much more predictable.

• Fixed a bug in the TriLookup Interactive Examples.xls workbook which prevented hiding of the #N/A points on the line graphs.

2003-09-29

Version 1.1 Released

Some minor changes and bug fixes:

• Added switching to manual calculation when loading interactive example to avoid recalculation.

• Fixed a bug in processing tables with constant Y values in TVPOLYDATA and THPOLYDATA.

2003-05-17

Error: 1004: Programmatic Access to Visual Basic Project is not trusted

Cause

Microsoft Excel 2002 contains a new security feature that allows you to choose whether or not programmatic access to the Visual Basic project should be trusted.
The default setting is to not trust programmatic access to the Visual Basic project.

TriLookup uses programmatic access in order to establish reference from the example workbooks to TriLookup.xla.

Resolution Option 1

To allow programmatic access to TriLookup, and all other Visual Basic projects, follow these steps:

1. On the Tools menu, point to Macro and then click Security.
2. In the Security dialog box, click the Trusted Sources tab.
3. Click to select the Trust access to Visual Basic Project check box.

Resolution Option 2

To remove programmatic reference from the example workbooks, follow these steps:

1. Press <Alt><F11> to open Visual Basic Editor.
2. Locate the name of the workbook in the Project Explorer window and click on it.
3. Click on Modules and then on double click on mMain.
4. Find Sub InitializeWorkbook() and comment our or delete the following line:
5. GetTriLookupReference
6. Close Visual Basic Editor and save the example workbook under a different name.