VBA: How to match two sets?
in [Programming]
|
Prev: Count previous 30 cells two sheets
Next: Help with code
From: Prof Wonmug on 20 Apr 2010 23:20 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?
From: Rick Rothstein on 20 Apr 2010 23:50 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? -- Rick (MVP - Excel) "Prof Wonmug" <wonmug(a)e.mcc> wrote in message news:ocpss552cder5hfj5fr3bd0surrrdb5qk3(a)4ax.com... > 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?
From: Prof Wonmug on 21 Apr 2010 00:08 On Tue, 20 Apr 2010 23:50:10 -0400, "Rick Rothstein" <rick.newsNO.SPAM(a)NO.SPAMverizon.net> wrote: >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.
From: Prof Wonmug on 21 Apr 2010 00:32 On Tue, 20 Apr 2010 23:50:10 -0400, "Rick Rothstein" <rick.newsNO.SPAM(a)NO.SPAMverizon.net> wrote: >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.
From: Prof Wonmug on 21 Apr 2010 00:42
On Tue, 20 Apr 2010 23:50:10 -0400, "Rick Rothstein" <rick.newsNO.SPAM(a)NO.SPAMverizon.net> wrote: >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? |