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]

I assumed it was clutter and removed it ...

 

[mikerickson]

If the UDF is returning a #REF error, that must be generated by the cell/function interface.
Syntax, etc. errors in the UDF will give a #VALUE error.

(Unless the UDF has a branch that returns a CVErr value, which this one doesn't)

 

[Mr. Wizard]

In that usage, it is the number of dollars.

It could be 400 without the dollar sign, and it could be represent hours or dollars.

I just happened to put in the $ in my example. I ususally do not enter that into the formula for the function, and let the formatting do the dollars.

Don't know if I am any closer to understanding the nature of the problem that is causing errors or why there is a file reference in this at all.

 

[ZVI]

='C:\Documents and Settings\My Documents\Learning Curve.xla'!LCT1(G6,G5,G4)

Haven't you missed the profile name part in the path to AddIn?

The reference to UDF with full path of its host XLA add-in file is stored in workbook with UDF in formulas.
It is the same as for any formula which is referenced to external workbook (to AddIn in this case).

If AddIn was saved in one OS profile and is loaded in different profile then the problem comes because UDF in workbook is still referenced to the old full path of the AddIn file.
In common case, if AddIn has been moved to another folder then the described issue happens.

There is a solution - put the code below to AddIn’s ThisWorkbook module for auto-fixing of corrupt links to UDF of that AddIn:

' Code of AddIn's ThisWorkbook module
Dim WithEvents App As Application

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
  FixLnk Wb
End Sub

Private Sub Workbook_Open()
  Dim Wb As Workbook
  On Error Resume Next
  For Each Wb In Workbooks
    FixLnk Wb
  Next
End Sub

Private Sub FixLnk(Wb As Workbook)
  Dim MyAddIn, Lnk, Sh
  MyAddIn = UCase(ThisWorkbook.Name)
  On Error GoTo exit_
  With Wb
    For Each Lnk In .LinkSources(Type:=xlExcelLinks)
      If UCase(Lnk) Like "*" & MyAddIn Then
        If UCase(Lnk) <> MyAddIn Then
          .ChangeLink Name:=Lnk, NewName:=MyAddIn
          For Each Sh In .Worksheets
            Sh.Calculate
          Next
        End If
        Exit For
      End If
    Next
  End With
exit_:
End Sub

 

[shg]

Nice, ZVI.

 

[ZVI]

Nice, ZVI.

Thanks, Shg. Stepped on these rake earlier with my addin

 

[Mr. Wizard]

The dollar sign was just indicating four hundred dollars.... not a cell reference. The function is not affected by the dollar sign.

What is the difference between using a UDF in an XLA file versus an XLAM file? This could possibly be the crux of the issue.

 

[ZVI]

Extension of AddIn file is a part of full path.
Therefore if you save XLA as XLAM then the described problem will happen too.

P.S. This line was missed in Workbook_Open subroutine: Set App = Application
The fixed sub:

Private Sub Workbook_Open()
  Set App = Application
  Dim Wb As Workbook
  On Error Resume Next
  For Each Wb In Workbooks
    FixLnk Wb
  Next
End Sub

 

[Mr. Wizard]

It would appear from the coding and explanation that the code that you provided could resolve issues when the add-in was referenced from my hard drive. That occurred while the add-in was being developed.

What happens when I get the #REF! error in the cell. I have the add-in loaded, and there is no problem with my other functions in the add-in?

There are two distinct problems.

1. Opening an older spreadsheet where the function used to work. (could have a different add-in location reference)

2. Using the function in a new spreadsheet with the add-in loaded.

The second case does not deal with links that were added by Excel, but it does produce the #REF! error. The first one gives the error when the links are automatically added in by Excel.

I am still at a loss to understand why I do not get errors with all of the functions in the second case. There is only one of my UDF's in the add-in that produces the error when used.

 

[shg]

I am still at a loss to understand why I do not get errors with all of the functions in the second case.

Just to repeat, that's the only one that looks like a cell reference.