|
Call to custom function appears as typed not as result
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 |
Call to custom function appears as typed not as result
David,
In the workbook that shows formulas, goto menu ToolsOptions 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) "David Bernheim" wrote in message ... 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 |
Call to custom function appears as typed not as result
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) "Bob Phillips" wrote in message ... David, In the workbook that shows formulas, goto menu ToolsOptions 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) "David Bernheim" wrote in message ... 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 |
Call to custom function appears as typed not as result
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. "Bob Phillips" wrote in message ... David, In the workbook that shows formulas, goto menu ToolsOptions 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) "David Bernheim" wrote in message ... 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 |
Call to custom function appears as typed not as result
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" wrote in message ... 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. "Bob Phillips" wrote in message ... David, In the workbook that shows formulas, goto menu ToolsOptions 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) "David Bernheim" wrote in message ... 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 |
Call to custom function appears as typed not as result
Great, that's fixed it. Many thanks
David "A.W.J. Ales" wrote in message ... 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" wrote in message ... 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. "Bob Phillips" wrote in message ... David, In the workbook that shows formulas, goto menu ToolsOptions 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) "David Bernheim" wrote in message ... 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 |
Call to custom function appears as typed not as result
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) "Bob Phillips" wrote in message ... 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) "Bob Phillips" wrote in message ... David, In the workbook that shows formulas, goto menu ToolsOptions 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) "David Bernheim" wrote in message ... 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 |
Call to custom function appears as typed not as result
"David Bernheim" wrote in message
... 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 -- Michael Hopwood (Phobos) |
| All times are GMT +1. The time now is 05:38 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com