Function to get rid of spaces, commas, and appostrophies

[craigachan]

I've written a function that takes a person's name and gets rid of all periods, spaces, commas, and appostrophies. When I run the code outside of a function it works. but when I put it in a public function, somehow I get a compile error. I just can seem to figure it out. Can anyone tell me what I'm doing wrong? Here is the code:

Public Function FixPtSaveName(strSaveName As String)

On Error GoTo FPS_Err

Dim x As Integer
Dim ct As Integer
Dim strFName As String

ct = Len(strSaveName)
strFName = ""

For x = 1 To ct
If Mid(strSaveName, x, 1) <> " " Then
If Mid(strSaveName, x, 1) <> Chr(46) Then
If Mid(strSaveName, x, 1) <> "," Then
If Mid(strSaveName, x, 1) <> "'" Then
strFName = strSaveName & Mid(strSaveName, x, 1)
End If
End If
End If
Else
strFName = strFName
End If

Next x

FPS_Err:
MsgBox "OOPS"

Resume FPS_Exit

FPS_Exit:
Exit Function
End Function

This code runs until it finishes the last character then errors out. It's probably something real simple but I've been pulling my hair out.

thanks for the help.

 

[Ron_dK]

You might try changing your code to this :

Next x
 
FPS_Exit:
    Exit Function

FPS_Err:
    MsgBox "OOPS"
       
    Resume FPS_Exit
    
End Function

Hth

 

[stopher]

As Ron_DK has pointed out, your error handler needs to be after your exit point (otherwise it will just run anyway)

Just a couple of other observations...

You haven't returned anything in your code. i.e. when calling the function, nothing will get returned. To return a result, use the function name:

FixPtSaveName= whatevervalueyouwanttoreturn

You've used nested if statements but you could use AND in this situation.


Also, the line strFName = strSaveName & Mid(strSaveName, x, 1) will just add a character to your original string. Needs to be
strFName = strFName & Mid(strSaveName, x, 1)

Do indent your code as it makes it easier to read (use the code tags when posting)

Here's my take on this:

Public Function FixPtSaveName(strSaveName As String)

On Error GoTo FPS_Err

Dim x As Integer
Dim ct As Integer
Dim strFName As String
Dim testChar As String

ct = Len(strSaveName)
strFName = ""

For x = 1 To ct
    testChar = Mid(strSaveName, x, 1)
    If testChar <> " " And testChar <> Chr(46) And testChar <> "," And testChar <> "'" Then
        strFName = strFName & testChar
    End If
Next x

FixPtSaveName = strFName

FPS_Exit:
    Exit Function

FPS_Err:
    MsgBox "OOPS"
    Resume FPS_Exit

End Function

hth
Chris

 

[raskew]

Hi -

To add to the list, this will remove unwanted characters from a string:

Function ChopIt2(pstr As String, ParamArray varmyvals() As Variant) As String
'*******************************************
'Purpose:   Remove a list of unwanted
'           characters from a string
'Coded by:  raskew
'Inputs:    From debug window:
'           1) ? chopit("123-45-6789", "-")
'           2) ? chopit(" the quick brown fox ", " ", "o")
'Output:    1) 123456789
'           2) thequickbrwnfx
'*******************************************

Dim strHold As String
Dim strKeep As String
Dim i       As Integer
Dim n       As Integer
    strHold = Trim(pstr)
    'check for entry
    If UBound(varmyvals) < 0 Then Exit Function
    For n = 0 To UBound(varmyvals())
       Do While InStr(strHold, varmyvals(n)) > 0
          i = InStr(strHold, varmyvals(n))
          strHold = Left(strHold, i - 1) & Mid(strHold, i + Len(varmyvals(n)))
       Loop
    Next n
    ChopIt2 = Trim(strHold)

End Function

HTH - Bob

 

[craigachan]

Thank you all for the help. It works great now. It's hard learning VB without much resources. this forum is great!

 

[MarkK]

Here's another approach...

Function ExpungeChars(ByVal Text As String, ParamArray CharList() As Variant) As String
   Dim char As Variant
   
   For Each char In CharList
      Text = Replace(Text, char, "")
   Next var
   
   ExpungeChars = Text
   
End Function

 

[Ron_dK]

.. It works great now. .....

Since there were a number of proposals, would you mind giving us some details on how and what solved your issue. This may be a great help to others who might read this thread.

Thanks, Ron

 

[craigachan]

My pleasure.

I first started my code, it was about 12 midnight, and started to build it step by step, thus the nesting. I have not had a chance to learn VB from a book but mostly from looking at other people's code. So I have never learned any rules and is mostly by trial and error. The key that solved my problem was that I had not placed my exit code before my error code. I had them reversed. Once I switched it, it worked. Then once I got the code to work properly I then unnested my 'Ifs' and dimmed my variables.

Since I'm relatively new to VB, I'm not familiar enough with arrays, so have not yet tried code from Reskew and Lagbolt, but I'm anxious to try out these methods as these seem much more efficient.

Thanks everyone for all your help.

 

[Ron_dK]

OK, thanks for posting that craig