Error Expected: )

[cheuschober]

Well, here it is folks--my first official foray into a piece of my own code... and it flopped with an 'Expected: )' error! But, I can feel how close I am and though I'd ask around for a little help.

Basically, I'm trying to create a function that will evaluate a list of special characters and remove them from my string field.

Example: delspec("8$5AD%Q#F","%$#/.'")
Output: 85ADQF

Here is how far I've gotten! Any help would be greatly appreciated!

Public Function delspec(ByVal scrubstring As String, ByVal spec As String) As Variant

Dim strSpecs As String, bytSpecLen As Byte, n1 As Byte
strSpecs = LTrim(RTrim(spec))
bytSpecLen = Len(strSpecs)

Dim strHold As String, intScrubLen As Integer, n2 As Integer
scrubstring = RTrim(scrubstring)
intScrubLen = Len(scrubstring)
scrubstring = ""

For n1 = 1 To bytSpecLen

    For n2 = 1 To intScrubLen
               scrubstring = scrubstring & IIf((Mid(scrubstring, n2, 1)) <> (Mid(spec, n1, 1)), Mid(scrubstring, n2, 1), "")
    Next n2

Next n1

delspec = scrubstring

End Function

Thanks,
~Chad

 

[Louie]

Try this function.

Public Function DelSpec(Scrub As String, Spec As String)

Dim s As String, sL As Integer, p As String, pL As Integer
Dim x As String, h As String, i As Integer

s = Trim(Scrub)
sL = Len(s)

p = Trim(Spec)
pL = Len(p)

x = ""
For i = 1 To sL
h = Mid(s, i, 1)
If (InStr(1, p, h, vbTextCompare) = 0) Then
x = x & h
End If
Next i

DelSpec = x

End Function

 

[cheuschober]

Try this function.

Public Function DelSpec(Scrub As String, Spec As String)

Dim s As String, sL As Integer, p As String, pL As Integer
Dim x As String, h As String, i As Integer

s = Trim(Scrub)
sL = Len(s)

p = Trim(Spec)
pL = Len(p)

x = ""
For i = 1 To sL
h = Mid(s, i, 1)
If (InStr(1, p, h, vbTextCompare) = 0) Then
x = x & h
End If
Next i

DelSpec = x

End Function

Thank you Louie! Back in the office today so I think I'll try it. I'm just curious, however, what I might have done wrong or where my logic was in error? Trying to learn this vba thing so if anyone might have an explanation it would really help.

Thanks again,
~Chad

 

[Mile-O]

I'm just curious, however, what I might have done wrong or where my logic was in error?

This is your function call:

delspec("8$5AD%Q#F","%$#/.'")

This is your function declaration.

Public Function delspec(ByVal scrubstring As String, ByVal spec As String) As Variant

This means that scrubstring is "8$5AD%Q#F","%$#/.'".
But you have not passed a value for spec. This is what is Expected.

 

[cheuschober]

This is your function call:

delspec("8$5AD%Q#F","%$#/.'")

This is your function declaration.

Public Function delspec(ByVal scrubstring As String, ByVal spec As String) As Variant

This means that scrubstring is "8$5AD%Q#F","%$#/.'".
But you have not passed a value for spec. This is what is Expected.

Actually, sadly enough, my experience with this is just limited enough that I was executing the function the immediate window improperly (with a space bewteen ? and delspec()). Once I figured that out I ceased having the Expected error, but I was being given Null returns so it's still broken.

Oi ve! I never knew this stuff would be this difficult!
~Chad

 

[Mile-O]

TO be honest, I'm not clear as to what your function is supposed to be doing.

You say it is to remove special characters. I don't see how it is supposed to know which are special characters and which are typical alphanumeric characters.

Also, you are still trying to call it with one parameter but it needs two as you have not set the second parameter as Optional.

 

[cheuschober]

Hmm...

I thought that it would be able to determine the two strings from

delspec("8$5AD%Q#F","%$#/.'")

As being: "8$5AD%Q#F" as the string to be searched and scrubbed and "%$#/.'" as the string of special characters to remove, separated by the comma between the two quotations.

Is this any help? (Thank you for the interest SJ, I appreciate it!)
~Chad

 

[Mile-O]

I thought that it would be able to determine the two strings from

delspec("8$5AD%Q#F","%$#/.'")

Oh it could. I couldn't. I thought it was only one string.

You could loop through the second parameter and then loop within that through the second using the REPLACE() function on the first string and returing the final value.

 

[Louie]

Cheuschober,

You are storing into an argument that is being passed by value. Not a good idea to destroy your input. Use a different string to accunulate your result.

 

[cheuschober]

Thank you both SJ and Louie. I think I understand it a bit better.


~Chad