Solved: remove signs (wildcards) from a string

[ukdane]

I have some code which creates a new folder, with a name based on a string from user input.

However before I can use the string provided by the user I need to strip the string of any invalid characters (such as \/:*?"<> and |)

Does anyone have a snip of code that can quickly do this for me?

Thanks

[lucas]

If the string comes from a textbox you can validate it there:

[VBA]
Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox1.Value > 100 Then
Cancel = True
Me.TextBox1.SelStart = 0
TextBox1.SelLength = Len(TextBox1.Text)
MsgBox "Error"
End If
End Sub
[/VBA]

just an example.....replace the > 100 with = your wildcard charactors.

[Oorang]

[VBA]Option Explicit

Public Sub Example()
Dim test As String
test = "I\Am/A?Test"
MsgBox StripChars(test)
End Sub

Public Function StripChars(ByVal text As String) As String
Dim bytVals() As Byte
Dim bytRtnVal() As Byte
Dim lngChr As Long
Dim lngIndx As Long
If LenB(text) Then
bytVals = text
ReDim bytRtnVal(UBound(bytVals))
For lngChr = 0 To UBound(bytVals) Step 2&
Select Case bytVals(lngChr)
Case 34, 42, 47, 58, 60, 62, 63, 92, 124 'Do Nothing
Case Else
bytRtnVal(lngIndx) = bytVals(lngChr)
lngIndx = lngIndx + 2&
End Select
Next
ReDim Preserve bytRtnVal(lngIndx)
End If
StripChars = bytRtnVal
End Function[/VBA]

[Paul_Hossler]

Quick question: Why the 'Step 2&' and the '+ 2&' ?

It looks like you're working a byte pairs. Is that necessary for a string?

Paul

[Oorang]

In VB and it's many derivatives Strings are in encoded as Unicode. Unicode is two bytes, however it's set up in such a way that for ASCII characters the second byte is always zero. As the characters you want to eliminate are all from the original ASCII set you can just check the first digit. Although if you want to 100% sure you should double check the second digit is a zero to prevent certain non-English characters from being eliminated. I put a quick and dirty way below. You can, of course, restructure if you wish to avoid the evil goto :
[vba]Public Function StripChars(ByVal text As String) As String
Dim bytVals() As Byte
Dim bytRtnVal() As Byte
Dim lngChr As Long
Dim lngIndx As Long
If LenB(text) Then
bytVals = text
ReDim bytRtnVal(UBound(bytVals))
For lngChr = 0 To UBound(bytVals) Step 2&
Select Case bytVals(lngChr)
Case 34, 42, 47, 58, 60, 62, 63, 92, 124 'Do Nothing
If bytVals(lngChr + 1&) <> 0 Then Goto SaveChar
Case Else
SaveChar:
bytRtnVal(lngIndx) = bytVals(lngChr)
lngIndx = lngIndx + 2&
End Select
Next
ReDim Preserve bytRtnVal(lngIndx)
End If
StripChars = bytRtnVal
End Function [/vba]

[mdmackillop]

or one I can understand!!!
[VBA]
Sub Test()
Dim arr, a
Dim cel As Range
arr = Array("\", "/", ":", "*", "?", Chr(34), "<", ">", "|")
For Each cel In Selection
For Each a In arr
cel.Value = Application.Substitute(cel, a, "")
Next
Next
End Sub

[/VBA]

[Oorang]

Mine runs faster

[mdmackillop]

I'm sure it does. I'll work on it!

[Benzadeus]

Aaron,

Quick question:

Is there a difference using 2 or 2& or it is just esthetics?

I mean... does the virtual machine that execute VBA code identify faster that 2& is a long than using just 2?

[Oorang]

Honestly... I really doubt it makes any measurable difference at all Technically it prevents a type conversion. 2 is an integer 2& is a long. Maybe if you ran the function a couple million times in a row you would can a few milliseconds. I suppose I've gotten a bit anal in my code. Probably comes from writing UDFs for database(s) where a function really could be called a million times in a query, micro-optimization is more important under those circumstances. But for most purposes it will make little to no noticable difference. (For that matter, you probably wouldn't even notice the difference between mine and Malcoms unless you were using them A.) In a UDF and B.) Using that UDF tens of thousands of times).

I just like to hassle Malcolm cause he's got broad shoulders.

But as a habit I usually try to type a literal to the same type as the variable it's being used with. It's not always possible though. Notice that there is no type declaration character for Byte so I left that case alone.