View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.programming
Dave Peterson[_3_] Dave Peterson[_3_] is offline
external usenet poster
 
Posts: 2,824
Default Call to custom function appears as typed not as result

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