Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA: How to match two sets?
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA: How to match two sets?
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" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA: How to match two sets?
On Tue, 20 Apr 2010 23:50:10 -0400, "Rick Rothstein"
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA: How to match two sets?
On Tue, 20 Apr 2010 23:50:10 -0400, "Rick Rothstein"
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA: How to match two sets?
On Tue, 20 Apr 2010 23:50:10 -0400, "Rick Rothstein"
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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA: How to match two sets?
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 Regards, Bernd |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA: How to match two sets?
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". |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA: How to match two sets?
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA: How to match two sets?
On Wed, 21 Apr 2010 00:18:56 -0700 (PDT), Bernd P
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. ;-) |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA: How to match two sets?
...
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup with 4 sets of criteria to match? | Excel Worksheet Functions | |||
Excel graph 3 sets of data. 2 sets as lines 1 as column? | Charts and Charting in Excel | |||
LOOKUP two data sets for match - return 1 or 0 - Please help! | Excel Worksheet Functions | |||
Count rows that match 3 sets of criteria? | Excel Worksheet Functions | |||
How to match sort and lineup 2 sets of data | Excel Worksheet Functions |