Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Logic Test
For whatever reason, I'm stuck on this problem.
I know how to loop through an Array and then 'do things' if my test item is in the array: Dim Arr(1 to 4) as String, Element as Variant 'code assigning values to Arr(1 to 4) here For Each Element in Arr If InStr(StartString, Element) 0 Then Call 'subroutine here Next What I'm trying to do is to do things if Element is Not in StartString. I've thought about putting a nested Boolean in the loop, default it to False, loop through the entire array and swap it to True if there's a hit, then test the Boolean to determine to take the next action or not: Dim Tester as Boolean, Element as Variant, Arr(1 to 4) as String 'code assigning values to Arr(1 to 4) here Tester = False For Each Element in Arr If InStr(StartString, Element) 0 Then Tester = True Next If Tester = True Then Call 'subroutine here Is there a more effective/efficient way to accomplish this? Am I trying to overcomplicate it and this is the best route to take? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Logic Test
For whatever reason, I'm stuck on this problem.
I know how to loop through an Array and then 'do things' if my test item is in the array: Dim Arr(1 to 4) as String, Element as Variant 'code assigning values to Arr(1 to 4) here For Each Element in Arr If InStr(StartString, Element) 0 Then Call 'subroutine here Next What I'm trying to do is to do things if Element is Not in StartString. I've thought about putting a nested Boolean in the loop, default it to False, loop through the entire array and swap it to True if there's a hit, then test the Boolean to determine to take the next action or not: Dim Tester as Boolean, Element as Variant, Arr(1 to 4) as String 'code assigning values to Arr(1 to 4) here Tester = False For Each Element in Arr If InStr(StartString, Element) 0 Then Tester = True Next If Tester = True Then Call 'subroutine here Is there a more effective/efficient way to accomplish this? Am I trying to overcomplicate it and this is the best route to take? A few Q's: Why are you sizing 1 to 4, specifically rather than using a zero-based array? Why are you using a For Each loop rather than a counter loop (For...Next)? Do you need to know the position if the element is in StartString? What is StartString? Could it be better named to more accurately depict/suggest its purpoase? (InStr takes Start as its 1st arg and so this var name is somewhat misleading) If InStr(StartString, Element) = 0 Then 'it's not there! so... Tester = (InStr(StartString, Element) 0) ..will result true if its there, false if not! I prefer a different approach... Const sCheckString$ = "some text that might contain certain sub-texts" Assumes array values are not from a worksheet: Dim iPos%, n&, vData(4) 'code to load array vData(0) = 'value vData(1) = 'value vData(2) = 'value vData(3) = 'value 'loop the array For n = LBound(vData) to UBound(vData) iPos = InStr(CheckString, vData(n)) If iPos = 0 Then 'not there 'do this Else 'is there 'do this End If 'iPos = 0 Next ' Assumes array values are from a worksheet: Dim iPos%, n&, vData vData = ActiveSheet.Range("A1:A4) 'dump it into a 2D array in one shot 'loop the array For n = LBound(vData) to UBound(vData) iPos = InStr(CheckString, vData(n, 1)) 'specify the (row, col) If iPos = 0 Then 'not there 'do this Else 'is there 'do this End If 'iPos = 0 Next ' Assumes array values are from a text file: Dim iPos%, n&, vData, sTextIn$, sFile$ sFile = Get_FileToOpen: If sFile = "" Then Exit Sub sTextIn = ReadTextFile(sFile): vData = Split(sTextIn, vbCrLf) 'loop the array For n = LBound(vData) to UBound(vData) iPos = InStr(CheckString, vData(n)) If iPos = 0 Then 'not there 'do this Else 'is there 'do this End If 'iPos = 0 Next ' Function Get_FileToOpen$(Optional FileTypes$) Dim vFile If FileTypes = "" Then FileTypes = "All Files ""*.*"", *.*" vFile = Application.GetOpenFileName(FileTypes) Get_FileToOpen = IIf(vFile = False, "", vFile) End Function 'The helper functions... Sub Test_ReadTextFile() Dim vTextIn As Variant, lNumLines As Long Dim oTimer As New cHiResTimer oTimer.StartTimer vTextIn = Split(ReadTextFile("c:\vbastuff\combinedvbastuff.t xt"), vbCrLf) lNumLines = UBound(vTextIn) + 1 oTimer.StopTimer Debug.Print "ElapsedTime: " & Format(oTimer.Elapsed, "#.0000") & " seconds;" & " Line Count: " & Format(lNumLines, "#,000") Set oTimer = Nothing End Sub -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Logic Test
Oops.., some typos...
A few Q's: Why are you sizing 1 to 4, specifically rather than using a zero-based array? Why are you using a For Each loop rather than a counter loop (For...Next)? Do you need to know the position if the element is in StartString? What is StartString? Could it be better named to more accurately depict/suggest its purpoase? (InStr takes Start as its 1st arg and so this var name is somewhat misleading) If InStr(StartString, Element) = 0 Then 'it's not there! so... Tester = (InStr(StartString, Element) 0) ..will result true if its there, false if not! I prefer a different approach... Const sCheckString$ = "some text that might contain certain sub-texts" Assumes array values are not from a worksheet: Dim iPos%, n&, vData(4) 'code to load array vData(0) = 'value vData(1) = 'value vData(2) = 'value vData(3) = 'value 'loop the array For n = LBound(vData) to UBound(vData) iPos = InStr(sCheckString, vData(n)) If iPos = 0 Then 'not there 'do this Else 'is there 'do this End If 'iPos = 0 Next ' Assumes array values are from a worksheet: Dim iPos%, n&, vData vData = ActiveSheet.Range("A1:A4) 'dump it into a 2D array in one shot 'loop the array For n = LBound(vData) to UBound(vData) iPos = InStr(sCheckString, vData(n, 1)) 'specify the (row, col) If iPos = 0 Then 'not there 'do this Else 'is there 'do this End If 'iPos = 0 Next ' Assumes array values are from a text file: Dim iPos%, n&, vData, sTextIn$, sFile$ sFile = Get_FileToOpen: If sFile = "" Then Exit Sub sTextIn = ReadTextFile(sFile): vData = Split(sTextIn, vbCrLf) 'loop the array For n = LBound(vData) to UBound(vData) iPos = InStr(sCheckString, vData(n)) If iPos = 0 Then 'not there 'do this Else 'is there 'do this End If 'iPos = 0 Next ' Function Get_FileToOpen$(Optional FileTypes$) Dim vFile If FileTypes = "" Then FileTypes = "All Files ""*.*"", *.*" vFile = Application.GetOpenFileName(FileTypes) Get_FileToOpen = IIf(vFile = False, "", vFile) End Function 'The helper functions... Sub Test_ReadTextFile() Dim vTextIn As Variant, lNumLines As Long Dim oTimer As New cHiResTimer oTimer.StartTimer vTextIn = Split(ReadTextFile("c:\vbastuff\combinedvbastuff.t xt"), vbCrLf) lNumLines = UBound(vTextIn) + 1 oTimer.StopTimer Debug.Print "ElapsedTime: " & Format(oTimer.Elapsed, "#.0000") & " seconds;" & " Line Count: " & Format(lNumLines, "#,000") Set oTimer = Nothing End Sub -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#4
|
|||
|
|||
Missing operator?
Possibly you left out the "=" in this line...
If InStr(StartString, Element) 0 Then Tester = True |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Logic Test
Possibly you left out the "=" in this line...
If InStr(StartString, Element) 0 Then Tester = True Hey Rich, Possibly you replied to the wrong thread! Also, that line reads... If InStr(StartString, Element) 0 Then Tester = True ...where your line omits the greater than character before 0. -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Logic Test ? | Excel Worksheet Functions | |||
MORE THAN ONE LOGIC TEST | Excel Worksheet Functions | |||
Need help on Logic test!!! | Excel Discussion (Misc queries) | |||
logic test | New Users to Excel | |||
Logic test | Excel Programming |