By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,986 Members | 1,522 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,986 IT Pros & Developers. It's quick & easy.

VBA listbox help

P: 21
I have 8 list boxes and i need to compare values stored in them, i need to find the first non-numerical character in each line in each listbox and then compare between listboxes does anyone have any ides how to do this?

cheers

Black box
Nov 13 '06 #1
Share this Question
Share on Google+
16 Replies


100+
P: 267
I have 8 list boxes and i need to compare values stored in them, i need to find the first non-numerical character in each line in each listbox and then compare between listboxes does anyone have any ides how to do this?

cheers

Black box

hi

dim lstbLine(7)

'find first non-numerical chr is line of listbox and remember
'assumed listboxes are put into an array
'lstb is counter for listboxes
'ndx is counter for non-numerical line

for lstb=0 to 7
for ndx=0 to ListBox(lstb).ListCount-1
if asc(left(ListBox(lstb).List(ndx),1)) > 64 then
lstbLine(lstb)=ndx
exit for
end if
next ndx
next lstb

'compare different listbox lines
for lstb=0 to 6
for lstb1=lstb+1 to 7
if lstbLine(lstb)=lstbLine(lstb1) then
msgbox "Listbox " & lstb & " has same value as Listbox " & lstb1
end if
next lstb1
next lstb
Nov 13 '06 #2

Expert 5K+
P: 8,434
for lstb=0 to 7
for ndx=0 to ListBox(lstb).ListCount-1
if asc(left(ListBox(lstb).List(ndx),1)) > 64 then
lstbLine(lstb)=ndx
exit for
end if
next ndx
next lstb
This doesn't actually match the spec. The request was to find the "first non-numeric character in each line". I believe this code will find the first line which starts with a non-numneric. You might actually need to use a third nested loop to run through each character in ListBox(lstb).List(ndx), and do something with the character you find, then Exit For.

Just in case you're interested, the Left() function in the above code is unnecessary; the ASC() function will only return the ASCII value of the first character anyway.
Nov 13 '06 #3

Expert 5K+
P: 8,434
P.S. Sorry if I sounded somewhat brusque, just very busy at work right now.
Nov 13 '06 #4

Expert 5K+
P: 8,434
Ok, I've had time to go over this one a little. Here's the code I've come up with - I've just expanded a little on albertw's code. Note that while this was written in VB6, it has not been tested.
Expand|Select|Wrap|Line Numbers
  1. ' Find first non-numerical chr in each line of listbox.
  2. ' Assumptions:
  3. '   1) listboxes are in an array ListBox(0 to 7)
  4. '   2) Maximum entries in any listbox: 100
  5.  
  6. ' lstb is counter for listboxes
  7. ' ndx is counter for line within listbox
  8. ' char is count for charater within line
  9.  
  10. ' Array to hold first non-numeric char for each line in each
  11. ' listbox...
  12. Dim FirstChar(0 To 7, 1 To 100) As String
  13.  
  14. Dim lstb As Long, ndx As Long, chnum As Long
  15. Dim Char As String
  16. Dim MaxLine As Long
  17.  
  18.  
  19. For lstb = 0 To 7
  20.   With ListBox(lstb)
  21.     If .ListCount > MaxLines Then
  22.       MaxLines = .ListCount
  23.     End If
  24.     For ndx = 0 To .ListCount - 1
  25.       For chnum = 1 To Len(.List(ndx))
  26.         Char = Mid$(.List(ndx), Char, 1)
  27.         Select Case Char
  28.           Case "0" To "9"
  29.             ' Ignore
  30.           Case Else
  31.             FirstChar(lstb, ndx) = Char
  32.             Exit For
  33.         End Select
  34.       Next
  35.     End With
  36.   Next
  37. Next
  38.  
In theory, this should leave you with a two-dimensional array (listboxnum, linenum) giving the character which was found on each line of each listbox. Variable MaxLines should contain the number of lines in the longest list.

I don't know exactly what sort of comparison you want to do between them, but albertw's routine should give you a good base to work from.
Nov 14 '06 #5

100+
P: 267
Ok, I've had time to go over this one a little. Here's the code I've come up with - I've just expanded a little on albertw's code. Note that while this was written in VB6, it has not been tested.
Expand|Select|Wrap|Line Numbers
  1. ' Find first non-numerical chr in each line of listbox.
  2. ' Assumptions:
  3. '   1) listboxes are in an array ListBox(0 to 7)
  4. '   2) Maximum entries in any listbox: 100
  5.  
  6. ' lstb is counter for listboxes
  7. ' ndx is counter for line within listbox
  8. ' char is count for charater within line
  9.  
  10. ' Array to hold first non-numeric char for each line in each
  11. ' listbox...
  12. Dim FirstChar(0 To 7, 1 To 100) As String
  13.  
  14. Dim lstb As Long, ndx As Long, chnum As Long
  15. Dim Char As String
  16. Dim MaxLine As Long
  17.  
  18.  
  19. For lstb = 0 To 7
  20.   With ListBox(lstb)
  21.     If .ListCount > MaxLines Then
  22.       MaxLines = .ListCount
  23.     End If
  24.     For ndx = 0 To .ListCount - 1
  25.       For chnum = 1 To Len(.List(ndx))
  26.         Char = Mid$(.List(ndx), Char, 1)
  27.         Select Case Char
  28.           Case "0" To "9"
  29.             ' Ignore
  30.           Case Else
  31.             FirstChar(lstb, ndx) = Char
  32.             Exit For
  33.         End Select
  34.       Next
  35.     End With
  36.   Next
  37. Next
  38.  
In theory, this should leave you with a two-dimensional array (listboxnum, linenum) giving the character which was found on each line of each listbox. Variable MaxLines should contain the number of lines in the longest list.

I don't know exactly what sort of comparison you want to do between them, but albertw's routine should give you a good base to work from.

hi

agree to create a 2dim array

dim lb(num,lin)

for a=0 to 7
for b=1 to lstbox(a).listcount
for i=1 to len(lstbox(a).list(b))
c=mid(lstbox(a).list(b),i,1)
n=instr(1,"0123456789",c)
if n=0 then
lb(a,b)=c
exit for
endif
next i
next b
next a

then compare diff lb's
Nov 14 '06 #6

Expert 5K+
P: 8,434
hi

agree to create a 2dim array
Expand|Select|Wrap|Line Numbers
  1. dim lb(num,lin)
  2.  
  3. for a=0 to 7
  4.    for b=1 to lstbox(a).listcount
  5.      for i=1 to len(lstbox(a).list(b))
  6.      c=mid(lstbox(a).list(b),i,1)
  7.      n=instr(1,"0123456789",c)
  8.      if n=0 then
  9.      lb(a,b)=c
  10.      exit for
  11.      endif
  12.      next i
  13.    next b
  14. next a
then compare diff lb's
I prefer my version :)

Seriously, you forgot about the zero-base on the "b" loop. You'll get a bad-index error (I forget the specifics, been working in other languages too much).
Nov 14 '06 #7

100+
P: 267
I prefer my version :)

Seriously, you forgot about the zero-base on the "b" loop. You'll get a bad-index error (I forget the specifics, been working in other languages too much).
yes yr right k
b should start at 0
Nov 14 '06 #8

P: 21
hate to point it out that my VB doesn't like either of your responses, though they look as if they work in spectacular form it likes neither lstbox(a) (or ListBox(a)) or ListBox(lstb) (or lstbox(lstb))
Nov 14 '06 #9

Expert 5K+
P: 8,434
hate to point it out that my VB doesn't like either of your responses, though they look as if they work in spectacular form it likes neither lstbox(a) (or ListBox(a)) or ListBox(lstb) (or lstbox(lstb))
Well, you have to keep in mind that we are making assumptions and you need to adjust for them. For instance, we have assumed that your listboxes are in an array. This is certainly the most convenient way to deal with them, as you can use a loop to run through them, as albertw and I have been demonstrating.

If they are not an array, then the coding gets rather awkward, as you need to explicitly reference each one. At the simplest level, this may mean writing the same code 8 times, though there are ways you might get around it.

Also, even if they are a control array, you have to use the right name. We just assumed a name of lstbx, or ListBox, or whatever. You need to use your name here, not ours.

If you're not sure, making a control array involves giving controls (of the same type) the same name, and a value in the Index property. If you simply copy a control and paste it back into the same form, it will ask whether you want to make it an array, and if you say yes, will give the original one an Index value of 0. The pasted one will have Index = 1, and so on.
Nov 14 '06 #10

P: 21
Well, you have to keep in mind that we are making assumptions and you need to adjust for them. For instance, we have assumed that your listboxes are in an array. This is certainly the most convenient way to deal with them, as you can use a loop to run through them, as albertw and I have been demonstrating.

If they are not an array, then the coding gets rather awkward, as you need to explicitly reference each one. At the simplest level, this may mean writing the same code 8 times, though there are ways you might get around it.

Also, even if they are a control array, you have to use the right name. We just assumed a name of lstbx, or ListBox, or whatever. You need to use your name here, not ours.

If you're not sure, making a control array involves giving controls (of the same type) the same name, and a value in the Index property. If you simply copy a control and paste it back into the same form, it will ask whether you want to make it an array, and if you say yes, will give the original one an Index value of 0. The pasted one will have Index = 1, and so on.

sorry if i sounded a little ungrateful on my last post i was a little busy, i think the problem is a misunderstanding between VB6 and VBA and have managed to straighten most of it out there might be a few problems left firstly in the following:

Expand|Select|Wrap|Line Numbers
  1.     For Each Thing In UserForm1.Controls      ' thing is a control
  2.     If TypeName(Thing) = "ListBox" Then
  3.     For lstb = 1 To 8
  4.     With Thing
  5.     For ndx = 0 To .ListCount - 1
  6.       For chnum = 1 To Len(.List(ndx))
  7.         Char = Mid$(.List(ndx), Char, 1)
  8.         Select Case Char
  9.           Case "0" To "9"
  10.             ' Ignore
  11.           Case Else
  12.             FirstChar(lstb, ndx) = Char
  13.             Exit For
  14.         End Select
  15.       Next
  16.   Next
  17.   End With
  18. Next
  19. End If
  20. Next Thing
  21.  
when it reaches "For ndx = 0 To .ListCount - 1" it skips straight down to "end with" why would it do this? and how can i stop it?
Nov 15 '06 #11

100+
P: 267
sorry if i sounded a little ungrateful on my last post i was a little busy, i think the problem is a misunderstanding between VB6 and VBA and have managed to straighten most of it out there might be a few problems left firstly in the following:

Expand|Select|Wrap|Line Numbers
  1.     For Each Thing In UserForm1.Controls      ' thing is a control
  2.     If TypeName(Thing) = "ListBox" Then
  3.     For lstb = 1 To 8
  4.     With Thing
  5.     For ndx = 0 To .ListCount - 1
  6.       For chnum = 1 To Len(.List(ndx))
  7.         Char = Mid$(.List(ndx), Char, 1)
  8.         Select Case Char
  9.           Case "0" To "9"
  10.             ' Ignore
  11.           Case Else
  12.             FirstChar(lstb, ndx) = Char
  13.             Exit For
  14.         End Select
  15.       Next
  16.   Next
  17.   End With
  18. Next
  19. End If
  20. Next Thing
  21.  
when it reaches "For ndx = 0 To .ListCount - 1" it skips straight down to "end with" why would it do this? and how can i stop it?
if there is nothing in the "thing" list, the loop will stop directly
Nov 15 '06 #12

P: 21
if there is nothing in the "thing" list, the loop will stop directly
the "Thing list" isn't the problem it's the "For ndx = 0 To .ListCount - 1" line. When stepping through the code, it'll run the "with Thing" line, highlight the "For ndx = 0 To .ListCount - 1" and skip straight to "end with" on the next step. no error message it just runs through that same process for each lstb value.
Nov 15 '06 #13

100+
P: 267
the "Thing list" isn't the problem it's the "For ndx = 0 To .ListCount - 1" line. When stepping through the code, it'll run the "with Thing" line, highlight the "For ndx = 0 To .ListCount - 1" and skip straight to "end with" on the next step. no error message it just runs through that same process for each lstb value.
hi
just what i meant
if the thing.listcount=0 then the procedure will be skipped
Nov 15 '06 #14

Expert 5K+
P: 8,434
the "Thing list" isn't the problem it's the "For ndx = 0 To .ListCount - 1" line. When stepping through the code, it'll run the "with Thing" line, highlight the "For ndx = 0 To .ListCount - 1" and skip straight to "end with" on the next step. no error message it just runs through that same process for each lstb value.
When it gets to the For statement as mentioned here, could you hover over the .ListCount and see what value it shows? (If hovering doesn't work, just select it and do a quick watch.)
Nov 15 '06 #15

Expert 5K+
P: 8,434
hi
just what i meant
if the thing.listcount=0 then the procedure will be skipped
Yes, as albertw points out, if the listcount is 0 the For statement will be set to loop from 0 to -1. In which case, we will never enter the loop.
Nov 15 '06 #16

P: 21
Yes, as albertw points out, if the listcount is 0 the For statement will be set to loop from 0 to -1. In which case, we will never enter the loop.
my mistake i'd left in the userform1.show above the code, then closed it to run the code underneath and it'd reset the .listcount to 0. works now.

cheers

black box
Nov 16 '06 #17

Post your reply

Sign in to post your reply or Sign up for a free account.