Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old May 21st 19, 08:39 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2009
Posts: 178
Default 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   Report Post  
Old May 22nd 19, 01:06 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 1,042
Default 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   Report Post  
Old May 22nd 19, 01:09 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 1,042
Default 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   Report Post  
Old June 7th 19, 01:26 PM
Junior Member
 
First recorded activity by ExcelBanter: Apr 2017
Location: Indiana, USA
Posts: 1
Default Missing operator?

Possibly you left out the "=" in this line...

If InStr(StartString, Element) 0 Then Tester = True
  #5   Report Post  
Old June 7th 19, 04:23 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 1,042
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Logic Test ? Zeno Excel Worksheet Functions 4 July 11th 06 01:18 PM
MORE THAN ONE LOGIC TEST jan kay Excel Worksheet Functions 3 February 5th 06 01:41 AM
Need help on Logic test!!! jbf frylock Excel Discussion (Misc queries) 5 January 9th 06 09:43 PM
logic test toolman New Users to Excel 1 October 8th 05 05:19 AM
Logic test Ajit Excel Programming 1 October 13th 04 09:11 PM


All times are GMT +1. The time now is 12:09 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017