VBA: How to match two sets?

[Prof Wonmug]

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?

 

[Rick Rothstein]

I'm a little confused at what you are attempting to do. I think what is
confusing me is your debug string is shown as being optional... if you were
to leave it out, exactly what is your function going to do? Can you post a
clear example showing your initial condition, what you want to do with it
and what value you want your function to return?

 

[Prof Wonmug]

I'm a little confused at what you are attempting to do. I think what is
confusing me is your debug string is shown as being optional... if you were
to leave it out, exactly what is your function going to do? Can you post a
clear example showing your initial condition, what you want to do with it
and what value you want your function to return?

Yes, it's optional. Each of the flags are activated only they are
present. If nothing is passed, then all of the tests will fail, which
is what I want.

The debug string is a list of flags or options that are activated only
if they are present.

 

[Prof Wonmug]

I'm a little confused at what you are attempting to do. I think what is
confusing me is your debug string is shown as being optional... if you were
to leave it out, exactly what is your function going to do? Can you post a
clear example showing your initial condition, what you want to do with it
and what value you want your function to return?

PS: I should add that I need it to be optional because I may call the
UDF from hundreds of cells and may only want the debug code to execute
from one specific cell.

 

[Prof Wonmug]

I'm a little confused at what you are attempting to do. I think what is
confusing me is your debug string is shown as being optional... if you were
to leave it out, exactly what is your function going to do? Can you post a
clear example showing your initial condition, what you want to do with it
and what value you want your function to return?

Sorry for the multiple posts. I think I just realized what the
confusion is.

The UDF already exists, but was returning funny results from some
cells in some conditions. I was looking for some code I could add to
the UDF for debugging purposes and then remove when it's working.

If the UDF already has this syntax

=MyUDF(p1,p2,p3)

then I would add an optional 4th argument

=MyUDF(p1,p2,p3,"B")

which would make the debug code active from just that cell without
affecting any of the other calls. I could even make the argument
conditional.

=MyUDF(p1,p2,p3,IF(A+B,"M",""))

Does that clear it up?

 

[Bernd P]

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

Regards,
Bernd

 

[Prof Wonmug]

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".

 

[Bernd P]

Instr is slightly faster than Like here (not that it matters much).

Debug.Assert False behaves identically to Stop. I use it if I am in
sort of a hurry: If there is not enough time to take care of all
possible error conditions (with adequate messages, logs, etc.) then I
try to ensure at least the success conditions for a program first. The
price is that the application is not foolproof yet.

Regards,
Bernd

 

[Prof Wonmug]

Instr is slightly faster than Like here (not that it matters much).
OK.

Debug.Assert False behaves identically to Stop. I use it if I am in
sort of a hurry: If there is not enough time to take care of all
possible error conditions (with adequate messages, logs, etc.) then I
try to ensure at least the success conditions for a program first.

Thanks. Is there ever a reason to use one over the other?

The price is that the application is not foolproof yet.

Fools are so devilishly clever, it's nearly impossible to get it truly
foolproof. ;-)

 

[Bernd P]

...

Thanks. Is there ever a reason to use one over the other?
...

Correction: I use Debug.Assert <success condition> if I am in sort of
a hurry. Debug.Assert False I never use. Stop should be used then.

Regards,
Bernd