VBA: How to match two sets?
in [Programming]
|
Prev: Count previous 30 cells two sheets
Next: Help with code
From: Bernd P on 21 Apr 2010 02:13 On 21 Apr., 05:20, Prof Wonmug <won...(a)e.mcc> 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 Regards, Bernd
From: Prof Wonmug on 21 Apr 2010 02:31 On Tue, 20 Apr 2010 23:13:52 -0700 (PDT), Bernd P <bplumhoff(a)gmail.com> wrote: >On 21 Apr., 05:20, Prof Wonmug <won...(a)e.mcc> 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".
From: Bernd P on 21 Apr 2010 03:18 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
From: Prof Wonmug on 21 Apr 2010 03:34 On Wed, 21 Apr 2010 00:18:56 -0700 (PDT), Bernd P <bplumhoff(a)gmail.com> wrote: >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. ;-)
From: Bernd P on 21 Apr 2010 05:49
> ... > >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? > ... 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 |