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...

 

[ZVI]

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

1. The code extended for the case of XLA to XLAM transferring.

' 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()
  Set App = Application
  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 Lnk, LnkUC As String, MyAddIn As String, Sh As Worksheet, xla As String
  MyAddIn = UCase(ThisWorkbook.Name)
  xla = MyAddIn
  If Right(xla, 5) = ".XLAM" Then xla = Left(xla, Len(xla) - 1)
  On Error GoTo exit_
  With Wb
    For Each Lnk In .LinkSources(Type:=xlExcelLinks)
      LnkUC = UCase(Lnk)
      If (LnkUC Like "*" & MyAddIn) Or (LnkUC Like "*" & xla) Then
        If LnkUC <> 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

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

2. Not sure, what the exact the reason of your issue.
BTW, there can be different ways to get only some UDF formulas working.
For example, if you copy cells with UDF in its formulas from old workbook to the new one then that copied cells can be problematic because of it reference to the old AddIn.

Put the code above in your AddIn
Try to reload problematic workbook.
Ignore warning Excel message at loading.
Let us know if corrupted links are fixed or not, and what path to AddIn is in problematic formulas with UDF.

 

[Mr. Wizard]

That seemed to solve the issue.

I find it ironic that the function LOG10 does not cause similar problems because it too is a valid cell reference. It could possibly lead to other issues though.

This is one of those hidden problems that arises when you upgrade Excel from 2003 to a later product.

Thanks for your help.

 

[shg]

I reckon the developers added a little magic to accommodate native functions.

 

[ZVI]

Behind every cloud is another cloud, I am very glad to clear one of it
Thank you for the feedback!