Call to custom function appears as typed not as result

[David Bernheim]

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

 

[Bob Phillips]

David,

In the workbook that shows formulas, goto menu Tools>Options and on the View
tab, look to see if the Formulas box (left hand of the Windows Options
section) is checked. If so, clear it down.

--

HTH

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

 

[Bob Phillips]

Here's a slightly simpler version as well if you have XL2000 or above.

Function AddCC(szAccount As String, szCC As String)
Dim szNewString As String 'Work string to return
Dim i As Integer 'Counter
Dim aryAccount

szAccount = Trim(szAccount)

aryAccount = Split(szAccount, ",")
szNewString = aryAccount(LBound(aryAccount))
For i = LBound(aryAccount) + 1 To UBound(aryAccount)
szNewString = szNewString & "/" & aryAccount(i)
Next

If Right(szAccount, 1) <> "," Then
szNewString = szNewString & "/" & szCC
End If

AddCC = szNewString

End Function


--

HTH

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

 

[David Bernheim]

Somehow I managed to get some of the formulae to work in the problem
workbook - but others still do not. Changing the View / formulas box does
not cure this; somehow Excel seems to think that the formulae are just text,
not including a function to be evaulated.

In the formula bar they look the same, but in the cell one shows the
formula, the other the result.

If I copy the formula of one of the correct formulae, and paste it, this is
fine as I see the result. If I edit the formula, then copy the text of the
formula to the text of a new cell, then I just see the formula in the cell.

If I use "Insert function" to setup the formula, when I enter the paramters,
this shows the correct result, but clicking on OK it just shows the entered
formula.

Am very frustrated as I am an expeienced programmer. Thanks also for your
better cod, very neat.

 

[Dave Peterson]

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

 

[A.W.J. Ales]

Maybe you have looked for this already but it seems that your faulty cells
are formatted as text cells. With text cells you get this behaviour.

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

 

[David Bernheim]

Great, that's fixed it. Many thanks

David

Maybe you have looked for this already but it seems that your faulty cells
are formatted as text cells. With text cells you get this behaviour.

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

 

[Bob Phillips]

David,

If you are happy with just one argument, it could even become

Function AddCC(szAccount As String)
Dim szNewString As String 'Work string to return
Dim i As Integer 'Counter
Dim aryAccount

szAccount = Trim(szAccount)

aryAccount = Split(szAccount, ",")
szNewString = aryAccount(LBound(aryAccount))
For i = LBound(aryAccount) + 1 To UBound(aryAccount)
szNewString = szNewString & "/" & aryAccount(i)
Next

AddCC = szNewString

End Function

and you would use it like =AddCC("A,B,C")

--

HTH

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

 

[Michael Hopwood]

Am very frustrated as I am an expeienced programmer. Thanks also for your
better cod, very neat.

His lemon sole is quite tidy too! <g>