C excel add in example

I first wanted to say thanks and I'm really grateful for your Interp Add-ins. I don't know why Microsoft never devised this function. We are using it for a NASA SBIR project in which we interpolate the value of a bone marker (astronauts lose 1% or > of their bone mass per month in microgravity, one hurdle on the way to Mars). So we get a fluorescence reading on astronaut urine and then have to interpolate between known values for peptides, vitamin D3 etc in look up tables (LUTs) to determine bone marker levels as accurately as possible. It's a tall order, but we are getting there.

I successfully added your Interp add-ins to Excel 2007 after some difficulty and they work on one worksheet, but I want to copy and paste or call on whole columns of interpolated data from one worksheet to another worksheet. Except when I attempt to do that, Excel shuts down and returns and error message that I can't udnerstand. I'm a biochemist and programming is not my strength. Can you help? I would be willing to e-mail the file to you for your examination.

Also, I don't really see a difference between interp and interpX. I can't seem to interpolate whole arrays of data with interp, just single values.

There is a trick you need to know. When you populate more than one cell you need array formula assignment. So you select cells you want to populate then enter formula in formula box. THEN HIT Ctrl-Return INSTEAD of RETURN.

This is a little documented feature of Excel. (This makes it easy to do things like matrix multiplication and inversion). Enjoy. Laugh

in your sample, the X and Y data store in two aray, actualy I want to develope it to two dimension interpolate function but I have problem with two diomensional aray, how do I get two dimensional aray from excel?
Sign in· View Thread

When a function is inserted into the excel spreadsheet by the user, the 'Function Arguments' box pops up.

I can use the MacroOptions Method to insert a description for the function itself.

So. for this example, the function description for Interp would be 'Performs linear interpolation. This is the general version that can handle single values or arrays'

Each one of the funtion arguments has a separate definition, which appears below the function description when one clicks on the entry box.

So. for Interp, when clicking on the box for argument XArray, the description is displayed: 'A table of values that define X. Must be sorted (increasing or decreasing).'

How does one in VB get the function argument descriptions to display?

The xlfRegister option passes information about the name of each function, its passed parameter types, its return type, its help ID, a description of the function, and a description of each parameter. This information is used by Excel to give tooltip help when the function is entered in a spreadsheet, and helpful text when the insert function wizard is used.

However, I have been unable to see the tooltips pop-up when entering any of the functions defined in an XLL (this is on Windows XP Pro, Excel 2003). The functions do show up on the Function Wizard, but no tool tips ever appear.

Just to make sure we are talking about the same thing: when I enter any built-in Excel function into a cell (say, "=date(. )"), as soon as I type the '(' character, a little yellow box pops up showing the parameters for the date() function. This does NOT happen with my functions defined in an XLL. However, the Function Wizard does show all the parameters for these functions.

I have just downloaded the binary XLL from this page and verified it does NOT show any tooltips at all when I type "=Interp(. )". So, it seems to suffer from the same problem shared by my own XLLs and all others I have ever seen. Any hints?

Are there any instructions on how to create my own help file for my functions? can I link to .CHM instead of .HLP?
I read from http://en.wikipedia.org/wiki/Microsoft_WinHelp[^] that .HLP is no longer supported by Vista. Does that mean .CHM will be the only choice for Excel UDFs? How can I create my own .CHM and how to link to it? MYHELP.CHM!MyFunctionTITLE?

I add-in the function normally as per your directions, however, every time I open an excel file, I get the following error:

The file you are trying to open, 'interp32.xll', is in a different format than is specified by the file extension. Verify that the file is not corrupted and is form a trusted source before opening the file. Do you want to open the file now?

This add-in is a very nice one,but it does not seem to be compatible with some versions.
You can write your excel function,and it will appear in user-defined functions.

HOW?
In an excel worksheet,go to TOOLS,MACRO,VISUAL BASIC EDITOR then
INSERT, MODULE

PASTE THE CODE BELOW DIRECTLY INTO THE EDITOR AND SAVE.
THEN GO BACK TO EXCEL AND CHECK WITH USER-DEFINED FUNCS IF IT IS THERE.
ALSO CHECK THE NAME,IT MAY APPEAR AS MODULE1.INTERP YOU MAY CHANGE THE
NAME AS INTERP IN THE EDITOR USING THE MODULE PROPERTIES OR YOU MAY
SIMPLY USE THE NAME AS IT SHOWS.
I CHECKED WITH SOME DIFFERENT MACHINES,IT WORKS FINE.
BUT,
THIS CODE LACKS OF EXTRAPOLATION,MISSING VALUES EXCEPTIONS ETC.
IT STILL WORKS FINE FOR SIMPLE TWO-VECTOR INTERPOLATION.
BY THE WAY, I DID NOT WRITE THIS CODE,I CAME ACROSS THIS CODE
SOMEWHERE ON THE NET BUT NOW I CANT FIND THE LINK TO GIVE.THERE
WERE MORE USEFUL STUFF TO TRY IF YOU DIG FOR THAT WEBSITE. GOOD LUCK.

Option Explicit
Option Compare Text
Function RealEqual(X, Y) As Boolean
RealEqual = Abs(X - Y) End Function
Function interp(TargetVal, XVals, YVals)
Dim MatchVal
On Error GoTo ErrXit
With Application.WorksheetFunction
MatchVal = .Match(TargetVal, XVals, 1)
If MatchVal = XVals.Cells.Count _
And RealEqual(TargetVal, .Index(XVals, MatchVal)) Then
interp = .Index(YVals, MatchVal)
Else
interp = .Index(YVals, MatchVal) _
+ (.Index(YVals, MatchVal + 1) - .Index(YVals, MatchVal)) _
/ (.Index(XVals, MatchVal + 1) _
- .Index(XVals, MatchVal)) _
* (TargetVal - .Index(XVals, MatchVal))
End If
End With
Exit Function
ErrXit:
With Err
interp = .Description & "(Number= " & .Number & ")"
End With
End Function
______________________________________________________________________________________________

We tried to load the addin through the Tools, Addins, Browse menu. We located several addins folders and tried to place the file (interp32.xll) into each folder but to no avail.

I am using a Dell Precision 670 with XP Pro SP2 and Office 2003. Any other suggestions?

General News Suggestion Question Bug Answer Joke Praise Rant Admin

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.