Non-functioning function

[jlingle]

There seems to be something fundamental I don't understand about writing function. I wanted a simple function that strips spaces and periods out of a line of text. The following function seems to work well. It strips out the spaces and periods according to the msgbox in the function. However, it is not passing the value back to the subroutine variable that called it according to the msgbox in the call sub routine. What am I missing here? Thanks for any help you can offer.


Option Explicit
Dim inString As String
Dim i As Single

Sub CleanStr()
inString = "1. The Medicines Company Total"
inString = CleanString(inString)
MsgBox (inString)
End Sub

Function CleanString(myString As String) As String 'Removes spaces/"."
i = 1
Do Until i > Len(myString)
If Mid(myString, i, 1) = "." Or Mid(myString, i, 1) = " " Then
myString = Left(myString, (i - 1)) & Right(myString, Len(myString) - i)
i = 1
Else
i = i + 1
End If
Loop
MsgBox (inString & " " & myString)
End Function

[John Wilson]

You have forgotten (or maybe didn't understand) to set CleanString to the result.

[VBA]Dim inString As String
Dim i As Single

Sub CleanStr()
inString = "1. The Medicines Company Total"
MsgBox CleanString(inString)
End Sub

Function CleanString(myString As String) As String 'Removes spaces/"."
i = 1
Do Until i > Len(myString)
If Mid(myString, i, 1) = "." Or Mid(myString, i, 1) = " " Then
myString = Left(myString, (i - 1)) & Right(myString, Len(myString) - i)
i = 1
Else
i = i + 1
End If
Loop
CleanString = myString
End Function[/VBA]

[jlingle]

Thanks John. I figured it was something simple. I did not realize I had to do that at the end of the function code.

[Paul_Hossler]

Another way would be to just use a Sub since it doesn't need to return anything

[VBA]
Option Explicit
Dim inString As String
Dim i As Single

Sub CleanStr()
inString = "1. The Medicines Company Total"
Call CleanString(inString)
MsgBox (inString)
End Sub

Sub CleanString(myString As String)
i = 1
Do Until i > Len(myString)
If Mid(myString, i, 1) = "." Or Mid(myString, i, 1) = " " Then
myString = Left(myString, (i - 1)) & Right(myString, Len(myString) - i)
i = 1
Else
i = i + 1
End If
Loop

End Function
[/VBA]

Not tested

Paul

[jlingle]

Thanks Paul. The code is set up to use a returned value in several commands so I will probably leave as is. I'm never sure when to use a procedure or a function. I tend to use a procedure when several values need to be set or a changed value is only used once in one line of code. Don't know if that's right, just the way I tend to do it. I appreciate the help.

[John Wilson]

The point of a function is to return a value (in this case the cleaned string) In this particular case since the returned type is the same as the input type it's fine to use a procedure.

[Cosmo]

I pretty much use functions for just about everything; even if there's no direct applicable value for the function, I'll use a boolean return value to determine success/error occurred.

I can choose to disregard the returned value if I don't need to be concerned about it, but it's available if I should choose to use it at any point elsewhere.

[Paul_Hossler]

I suggested a ByRef sub approach because it's (supposed) to be faster:


http://www.aivosto.com/vbtips/stringopt.html#whyslow

String parameters

Procedure string parameters differ from numeric parameters in that with strings, the chosen parameter passing convention makes a real performance difference.
Pass strings ByRef

How should you define procedure parameters for calls from within the same project?
ByVal is slow for string parameters. ByVal makes a copy of the string on every call. The good side is that a ByVal parameter is safe to modify: the modifications aren't passed back to the callers.
ByRef is faster because the string doesn't get copied. The drawback is that you have to be careful. If your intention is not to return a value in the ByRef parameter back to the caller, you may not accidentally write to this parameter.
Use ByRef instead of function return value

There's also an optimization trick for returning a string value. Returning a string as the function return value is the normal practice. However, returning a string in a ByRef parameter is faster.
The ByRef trick for return values applies to both functions and Property Get's. Here's the usual (and slower) way:
' Slow:Property Get Name() As String Name = m_sNameEnd Property' Slow:Function Name() As String Name = m_sNameEnd FunctionThis way is faster if you have to make a large number of calls:
' Fast:Sub GetName(ByRef Name_out As String) Name_out = m_sNameEnd Sub

Also, if you 'process' the original string once, then any further use of the string variable will automaticall get the processed copy

Of course I don't do this everytime, but if there's a lot of text processing, then I try to be as effecient as possible

Paul

[jlingle]

Wow! Thanks guys. Very informative discussion.