And maybe the cell was formatted as text, too. Format the cell as General and
then hit F2 and enter.
And are you sure the equal sign appears in the formula, but not in the cell
(after you enter it)??
I've never seen anything like that.
And another version of AddCC:
Option Explicit
Function AddCC(str1 As String, str2 As String, _
Optional Sep1 As String = ",", _
Optional Sep2 As String = "/") As String
Dim myStr As String
If Right(str1, 1) < Sep1 Then
str1 = str1 & Sep1
End If
myStr = Application.Substitute(str1, Sep1, Sep2 & str2 & Sep1)
myStr = Left(myStr, Len(myStr) - 1)
AddCC = myStr
End Function
(If you're using xl2k or higher, you could replace "application.substitute" with
"replace". Replace was added in xl2k.)
David Bernheim wrote:
I placed the same custom function in a module in 2 workbooks, using
identical code, where it is the only contents of the VBA module. One works
fine, the other does not! Instead of displaying the resulting value I get
the text I typed in to call the function.
My function is AddCC(). The call AddCC("A,B","C") should show the result
"A/C,B/C".
In one workbook this is OK, in the other the cell displays
'AddCC("A,B","C")', with the formula showing as '=AddCC("A,B","C")'
Have I missed an option somewhere. Any other ideas, as I am beat!
CODE
-------
Option Explicit
Option Base 1 ' All arrays to start at 1, not 0
Function AddCC(szAccount As String, szCC As String)
Dim szNewString As String 'Work string to return
Dim i As Integer 'Counter
szAccount = Trim(szAccount)
'Return account + CC if there is only one
If InStr(szAccount, ",") = 0 Then
AddCC = szAccount & "/" & szCC
Exit Function
End If
For i = 1 To Len(szAccount)
If Mid(szAccount, i, 1) < "," Then
'Add next char if not a comma
szNewString = szNewString & Mid(szAccount, i, 1)
Else
'Char is a comma, so add CC
szNewString = szNewString & "/" & szCC & ","
End If
Next
If Right(szAccount, 1) < "," Then
szNewString = szNewString & "/" & szCC
End If
AddCC = szNewString
End Function
--
Dave Peterson