On Tue, 20 Apr 2010 23:13:52 -0700 (PDT), Bernd P
wrote:

On 21 Apr., 05:20, Prof Wonmug wrote:
In VBA for Excel (2007), is there an easy way to get a True result if
*any* character in one set matches *any* characters in another set?

If I understand the Like operator, the result is True only if *all*
characters in the first set match a character in the second set.

I would like to pass a string of debug parameters to a UDF. Each
parameter is a single character, such as:

* *"M" = Display a message
* *"B" = Set a breakpoint
* *"I" = Dump some info to the immediate window.

I would like to pass these as a string that can include zero or more
in any order and I'd like it to work whether they come in as upper or
lower case:

* *=MyUDF(p1,p2,...,"im")
* *=MyUDF(p1,p2,...,"B")
* *=MyUDF(p1,p2,...,D5)

I fooled around with the Like operator, but couldn't get it to work
without some setup work. Here's what I came up with. Please comment:

* *Public Function MyUDF(P1, P2, Optional DebugStr As String)
* *DebugStr = "[" & UCase(DebugStr) & "]"

* *If "B" Like DebugStr Then Debug.Assert False
* *If "M" Like DebugStr Then MsgBox "Test message", , "DSPwr"

* *...
* *End Function

Is there a better way?

Hello,

Yes:

Function jackofalltradesparam(Optional s As String) As Variant
Dim slc As String
slc = UCase(s)
If InStr(slc, "B") 0 Then Stop 'breakpoint
If InStr(slc, "I") 0 Then Debug.Print "blabla" 'immediate window
If InStr(slc, "M") 0 Then Call MsgBox("blabla", vbOKOnly) 'message
box
End Function

Why is this solution better? They seem more or less the same to me.



I do like the Stop statement. I wasn't aware of that. I've never liked
Debug.Assert. I always get it backwards.

Does Stop work differently than Debug.Assert in any way? The help only
says that it is "similar".