ExcelBanter - UDF becomes #NAME

Ok, the addin has a few modules.
The test case will be:
1. Open test#NAME.xla
2. Create a new file
3. In cell A1, enter s1
cell A2, enter s2
cell B1, enter '=foo(A1,A2)
4. The value in B1 is 10.5
5. Save the workbook and close
6. Now open the saved workbook again
7. In cell A1, change the content to "blah"
4. Notice the value in B1 is changed to #NAME, it is expected to be 100.5

eventHandler - Class module
======================
Option Explicit
Public WithEvents appevent As Application

Private Sub appevent_NewWorkbook(ByVal Wb As Workbook)
AddK4XLreference Wb
End Sub

Private Sub appevent_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI
As Boolean, Cancel As Boolean)
RemoveK4XLreference Wb
End Sub

Private Sub appevent_WorkbookOpen(ByVal Wb As Workbook)
AddK4XLreference Wb
End Sub

functions - Standard module
=====================

Option Explicit
Option Private Module

Private hEvents As New eventHandler

Sub Auto_Open()
Set hEvents.appevent = Application
End Sub

Public Sub AddK4XLreference(Wb As Workbook)
Dim i As Integer
Dim found As Boolean

If (Wb.FullName < ThisWorkbook.FullName) Then
found = False
For i = 1 To Wb.VBProject.References.Count
If Wb.VBProject.References.Item(i).Name = "TY" Then
found = True
Exit For
End If
Next i

If (Not found) Then
Wb.VBProject.References.AddFromFile ThisWorkbook.FullName
End If

End If

End Sub

Public Sub RemoveK4XLreference(Wb As Workbook)
Dim i As Integer
Dim found As Boolean

If (Wb.FullName < ThisWorkbook.FullName) Then
found = False

'Add the reference if it doesn't already exist
For i = 1 To Wb.VBProject.References.Count
If Wb.VBProject.References.Item(i).Name = "TY" Then
found = True
Exit For
End If
Next i

If found Then
Wb.VBProject.References.Remove Wb.VBProject.References.Item(i)
End If

End If
End Sub

Function FOO(Optional s1 As String, Optional s2 As String)
If (s1 = "s1") Then
FOO = 10.5
Else
FOO = 100.5
End If

End Function

"Bob Phillips" wrote in message
...
Post the code, not an attachment.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"TY" wrote in message
...
The problem is when updating the source value of an UDF's argument will
result in #NAME.

1. Open test#NAME.xla
2. Open test#NAMEwkbk.xls in automatic calculation mode
3. Change the cell A4 to "blah"
4. Notice UDF in B4 is changed to #NAME, which is expected to be 100.5

Anyone knows why?

Thanks.

Teresa