STRANGE BEHAVIOR for User Defined Function (UDF)

[Mr. Wizard]

I have a user defined function (UDF) that I programmed and tested previously.

I saved the UDF as an add-in since I use it so frequently. It used to work without any problem, but now the function returns the error #REF!

This is very confusing since I took the formula that is in the VBA code and tried it in a cell substituting both cell references and values, and it worked without a problem.

I have no problem with other UDF's within the add-in module.

Does anyone have any ideas on where to start to fix the issue?

Thanks for any suggestions...

 

[shg]

Posting the code would be good, along the formula that uses it.

 

[Mr. Wizard]

Sorry it took so long.... I got very busy. Here is the code
that my user defined function uses. I saved it as an add-in, and it worked fine
when I first used it. There are also other functions in the same module that I saved as an add-in that work fine still.
<table class="MsoNormalTable" border="0" cellspacing="0" cellpadding="0" width="100%" style="width: 780px"><tr><td style="border: 1.0pt inset; padding: 4.5pt; background: #E6E6E6"> Function LCT1(Tx_Cost, Tx_Unit As Integer, FnCurve As Double)
If FnCurve > 1 Then
FnCurve = FnCurve / 100
Else: FnCurve = FnCurve
End If
LCT1 = (Tx_Cost * (1 / Tx_Unit) ^ (Log(FnCurve) / Log(2)))
End Function</td>
</tr></table>
I was using Excel 2003 when I originally developed it. Now
with Excel 2007, I find that when I open some of my older spreadsheets, it
includes a filepath reference to my hard drive in the function when I view the
cells contents.
As an example, normally the cell would contain the following formual:<br>
=LCT1($400,100,.75)<br>
which would yield the result 2704.8<br>
NOW the formula I get when I open an older spreadsheet containing
the formula is modified to:<br>
='C:\Documents and Settings\My Documents\Learning Curve.xla'!LCT1(G6,G5,G4)<br>
The problem is that I have created an add-in, not referenced another
spreadsheet.
The other problem I have with the UDF is the if I don't get the file reference problem, I get the #REF! error.

What happened to the UDF? When I first upgraded Excel from 2003 to 2007 it worked fine.

 

[shg]

Where is the UDF now?

You should start by changing the name. LCT1 is a valid cell reference in Excel 2007+.

 

[Mr. Wizard]

The reason the function was named LCT1 was because the
mathematical designation for the value I am finding is T1 (with the 1 in
subscript), indicating the first observation.
The add-in resides either on a server that I am linked to, or in the add-ins
folder for the Excel application under user data on my hard drive.

I suppose that the cell reference in the newer version of Excel will make it
much more difficult to find an short unused function name that is pithy. I am a
minimalist for function names when I have to type them in.<br>

I will change the function name.... although the built-in function <b>LOG10</b>
also has the same issue of being a valid cell reference.<br>

I actually have a series of functions in the add in.<br>

LCTX, LCT1, LCAVGPROD, LCBLKAVG, etc...

I have tried removing the add-in and loading it again.

 

[shg]

I have tried removing the add-in and loading it again.

And removed the workbook reference in the formula?

Minor suggestion:

Function LCT(Tx_Cost As Double, Tx_Unit As Long, FnCurve As Double) As Double
    Const k         As Double = 1 / 0.693147180559945
 
    If FnCurve > 1 Then FnCurve = FnCurve / 100
    LCT = Tx_Cost / Tx_Unit ^ (k * Log(FnCurve))
End Function

 

[Mr. Wizard]

When I load the older workbooks that have the function already used, and delete the extra workbook reference, the function works perfectly.

When I open a new workbook, and type in the formula, I get the #REF! error.

...that is where I began the thread here.

 

[Mr. Wizard]

By the way, the other functions in the same add-in do not give me any problems.

 

[shg]

If you're referencing a function in an xla (versus xlam), there's no conflict with a cell address.

By the way, the other functions in the same add-in do not give me any problems

The others you posted don't look like cell references.

 

[mikerickson]

I'm suspicious of the dollar sign in
=LCT1($400,100,.75)

is that a number of dollars or an absolute reference to the first row of a range?