String handling

[dsc]

I have an array of three columns and an indeterminite number of lines.

All the array sorting code I see in VBA seems to sort arrays in alphabetical
order.

Is there any way in VBA to sort strings by string length (preferably in
bytes)?

If not, is there a function in Visual C++ that I can compile into a .dll and
use?

 

[Helmut Weber]

Hi,
have a look at ...public.word.vba.general.
There were two postings concerning sorting lately.
Basically, every sorting algorithm contains
comparisons, like
if a > b then...
or
while a > b then...
which compares alphabetically,
if the variables are strings.
To sort according to length,
the command would look like this:
if len(a) > len(b) then...
or
while len(a) > len(b) then...
---
Greetings from Bavaria, Germany
Helmut Weber, MVP
"red.sys" & chr(64) & "t-online.de"
Word XP, Win 98
http://word.mvps.org/

 

[Jezebel]

The quick and dirty way to sort in VBA is to use a hidden form with a
listbox on it. Set the ListBox's Sorted property to true. Put the values to
be sorted into the .List() array, and the reference -- in this case your
array index values -- into the ItemData() property.

So if your original string array is called MyArray(), your routine will be
something like --

With new frmMySortForm
With .ListBox1

'Sort the array by len
For i = LBound(MyArray,1) to UBound(MyArray,1)
.AddItem format(Len(MyArray(i,1)), "00000")
.ItemData(.NewIndex) = i
Next

'Now read back the sorted array
For i = 0 to .ListCount - 1
MsgBox "Item i = " & MyArray(.ItemData(i),1)
Next

End with
End with


Alternatively, put your array data into Excel and do it there. There's a
Len() function; and you can sort on the column.

 

[dsc]

The quick and dirty way to sort in VBA is to use a hidden form with a

Thanks for your help.

I'm trying to use your code, but I keep getting an error:

Compile error:
Method or data member not found

Debug takes me to the line:

..ItemData(.NewIndex) = i

with .NewIndex highlighted.

Is there an include file or declaration I'm missing?

Here's the code I'm using.

Sub TestSort()

Dim MyArray(10, 3) As String
Dim x As Integer

ListBox1.ColumnCount = 3

If x < 10 Then
MyArray(x, 0) = x
MyArray(x, 1) = x
MyArray(x, 2) = x
x = x + 1
End If

With New GlossarySortForm
With .ListBox1

'Sort the array by len
For i = LBound(MyArray, 1) To UBound(MyArray, 1)
.AddItem Format(Len(MyArray(i, 1)), "00000")
.ItemData(.NewIndex) = i
Next

'Now read back the sorted array
For i = 0 To .ListCount - 1
MsgBox "Item i = " & MyArray(.ItemData(i), 1)
Next

End With
End With
End Sub

I created a form, GlossarySortForm, with ListBox1 in it.

Any assistance appreciated.

 

[dsc]

Thanks for the help, Helmut.

 

[Helmut Weber]

Hi,
Jezebel probably uses VB, too.
In VB listboxes have a property "sorted".
In VBA, unexpectedly, there is no such property.
Of course, applies to Word VBA and the version
I am using here and now.
---
Greetings from Bavaria, Germany
Helmut Weber, MVP
"red.sys" & chr(64) & "t-online.de"
Word XP, Win 98
http://word.mvps.org/

 

[dsc]

In VBA, unexpectedly, there is no such property.

Aaarrrgggghhh. Bill Gates, you have much to answer for.

Drat. Now what do I do?

 

[Helmut Weber]

Hi,
do it yourself. The simplest and most often slowest
algorithm is bubblesort. But I get always blamed,
if I mention it. Google for "bubblesort" in this group,
or "sort listbox", but be aware of the fact, that bubblesorters
are scorned upon. Sorry, my english is limited.
Better, try to adapt the quicksort example here.
1 Line
http://www.google.de/groups?as_q=qu...general&as_uauthors=Helmut Weber&lr=&hl=de---
---
Greetings from Bavaria, Germany
Helmut Weber, MVP
"red.sys" & chr(64) & "t-online.de"
Word XP, Win 98
http://word.mvps.org/

 

[Jezebel]

Sorry about leading you astray. Yes, I usually work with VB, at least for
forms -- that's one area where VB and VBA differ a lot.

As Helmut says, do the sort yourself. Google will find you plenty of VB/VBA
algorithms. And don't be shy of using bubblesort, provided your list of
items is reasonably short. Although it's the least efficient algorithm in an
absolute sense, it also has the least amount of code overhead. So while the
number of code lines executed per list item may be high, the *total* number
of instructions executed for the list as a whole may be lower. Quicksort is
another algorithm to look at, if your list count is in the hundreds.

 

[dsc]

Thanks, both Helmut and Jezebel.