String handling

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

dsc - 25 Oct 2004 16:10 GMT

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?

Reply to this Message

Helmut Weber - 25 Oct 2004 18:58 GMT

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/

Reply to this Message

dsc - 27 Oct 2004 15:28 GMT

Thanks for the help, Helmut.

> Hi,
> have a look at ...public.word.vba.general.
[quoted text clipped - 17 lines]
> Word XP, Win 98
> http://word.mvps.org/

Reply to this Message

Jezebel - 25 Oct 2004 22:35 GMT

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.

> I have an array of three columns and an indeterminite number of lines.
>
[quoted text clipped - 6 lines]
> If not, is there a function in Visual C++ that I can compile into a .dll and
> use?

Reply to this Message

dsc - 27 Oct 2004 15:00 GMT

> 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.

Reply to this Message

Helmut Weber - 27 Oct 2004 15:46 GMT

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/

Reply to this Message

dsc - 27 Oct 2004 15:57 GMT

> In VBA, unexpectedly, there is no such property.

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

Drat. Now what do I do?

Reply to this Message

Helmut Weber - 27 Oct 2004 16:19 GMT

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=quicksort&as_ugroup=microsoft.public.word.vba.g
eneral&as_uauthors=Helmut%20Weber&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/

Reply to this Message

dsc - 31 Oct 2004 07:06 GMT

Thanks, both Helmut and Jezebel.

> Hi,
> do it yourself. The simplest and most often slowest
[quoted text clipped - 11 lines]
> Word XP, Win 98
> http://word.mvps.org/

Reply to this Message

Jezebel - 27 Oct 2004 23:07 GMT

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.

> > In VBA, unexpectedly, there is no such property.
>
> Aaarrrgggghhh. Bill Gates, you have much to answer for.
>
> Drat. Now what do I do?

Reply to this Message