Help with UBound Please

[Greg]

I don't understand the results that I am seeing with a UBound process.
It seems that the value is 1 greater than it should be.

Can anyone explain to me why the last three message boxes in the code
below are returning the result they do? Thank you.

Sub Test()
Dim oFld As FormField
Dim oFldDDArray() As String
Dim numEntries As Integer
Dim i As Integer
Dim a As Integer
Dim z As Integer

'I have a Dropdown formfield with 5 entries
Set oFld = Selection.FormFields(1)
numEntries = oFld.DropDown.ListEntries.Count
MsgBox numEntries 'Returns 5

ReDim oFldDDArray(numEntries)
For i = 1 To numEntries
'Add entries to array
oFldDDArray(i - 1) = oFld.DropDown.ListEntries(i).Name
Next i
a = LBound(oFldDDArray)
MsgBox a 'Returns 0
MsgBox oFldDDArray(a) 'returns first item in array
z = UBound(oFldDDArray)
MsgBox z 'Returns 5. Why? Only 5 items in the array (0-4)
MsgBox oFldDDArray(z) 'Empty
MsgBox oFldDDArray(z - 1) 'Returns last item in array

End Sub

 

[Jay Freedman]

Hi Greg,

When you Dim or ReDim an array, the value you put in the parentheses
there becomes the UBound of the array -- in this case, 5. The LBound
defaults to 0 (unless you use the statement Option Base 1 at the top
of the module). Therefore you get UBound + 1 entries in the array --
in this case there are 6 entries numbered 0 through 5.

Your For loop assigns values to entries 0 through 4 but never assigns
a value to entry 5. That's where your last three MsgBoxes come from.

It may help to draw a picture with a box for each array entry, or use
Post-It notes to simulate them. Even if I don't do that, it helps me
to picture things like this as physical objects.

There's an entire class of logic mistakes known as "off-by-one errors"
or "boundary errors" that involve miscalculating the top or bottom
values of arrays and loops. In some programming languages the compiler
can actually catch some of these errors before execution.

 

[Greg]

Jay,

I think I've got it. Actually I was playing around with some of your
code I found on Google to Delete a DropDown field entry with VBA. I
thought it would be neat to try to add an entry and sort the new
dropdown. To do this I adapted some of your code and a routine I found
for sorting an array. If you get a chance could you look at the method
I have used:

Option Explicit
Const cNoValue = -2
Sub AddItemToDropDown()
Dim oFld As FormField
Dim newEntry As String
Dim oFldDDArray() As String
Dim numEntries As Integer
Dim i As Integer
Dim a As Integer
Dim z As Integer

'Process currently selected Downdown field
'Note Other\Add entry must be the last entry in the dropdown list!!!
Set oFld = Selection.FormFields(1)
'Max size of dropdown is 25
If oFld.Type <> wdFieldFormDropDown Then End

If oFld.DropDown.ListEntries.Count = 25 Then
MsgBox ("The Dropdown List is full. You must delete one or more" _
& " entries before adding others.")
End
End If

If oFld.Result = "Other\Add" Then
newEntry = InputBox("Type your selection/new entry here:")
oFld.Result = newEntry
numEntries = oFld.DropDown.ListEntries.Count
'Delete current "Other\Add" Entry
oFld.DropDown.ListEntries(numEntries - 1).Delete
numEntries = numEntries - 1
'Put current new list in an array
ReDim oFldDDArray(numEntries - 1) 'because array starts with 0
For i = 1 To numEntries
oFldDDArray(i - 1) = oFld.DropDown.ListEntries(i).Name
Next i
'Clear existing list
oFld.DropDown.ListEntries.Clear
'Sort array alphabetically
a = LBound(oFldDDArray)
z = UBound(oFldDDArray)
QuickSort oFldDDArray, a, z
'Build new sorted dropdown list
i = 0
For i = LBound(oFldDDArray) To UBound(oFldDDArray)
oFld.DropDown.ListEntries.Add oFldDDArray(i)
Next i
'Restore "Other\Add" entry at end of list
oFld.DropDown.ListEntries.Add "Other\Add"
'Display result
oFld.Result = newEntry
End If
End Sub
Public Sub QuickSort(myArray As Variant, _
Optional iLeft As Integer = cNoValue, _
Optional iRight As Integer = cNoValue)

Dim iVarA As Integer
Dim iVarB As Integer
Dim vTest As Variant
Dim iMid As Integer

If iLeft = cNoValue Then iLeft = LBound(myArray)
If iRight = cNoValue Then iRight = UBound(myArray)
If iLeft < iRight Then
iMid = (iLeft + iRight) \ 2
vTest = myArray(iMid)
iVarA = iLeft
iVarB = iRight
Do
Do While myArray(iVarA) < vTest
iVarA = iVarA + 1
Loop
Do While myArray(iVarB) > vTest
iVarB = iVarB - 1
Loop
If iVarA <= iVarB Then
Swap myArray, iVarA, iVarB
iVarA = iVarA + 1
iVarB = iVarB - 1
End If
Loop Until iVarA > iVarB
If iVarB <= iMid Then
Call QuickSort(myArray, iLeft, iVarB)
Call QuickSort(myArray, iVarA, iRight)
Else
Call QuickSort(myArray, iVarA, iRight)
Call QuickSort(myArray, iLeft, iVarB)
End If
End If
End Sub
Private Sub Swap(vItems As Variant, iItem1 As Integer, iItem2 As
Integer)
Dim vTemp As Variant
vTemp = vItems(iItem2)
vItems(iItem2) = vItems(iItem1)
vItems(iItem1) = vTemp
End Sub

 

[Jay Freedman]

Hi Greg,

That's great -- works as intended, and I don't see any problems. I
applaud your enthusiasm!

My only comment is that, while it's nice to know how QuickSort works,
this code is overkill on a couple of levels.

First, there is an array-sort routine built into VBA, more or less.
It's the SortArray method of the WordBasic object, which encapsulates
most of the macro language from Word 6/Word 95. The "documentation" is
at http://word.mvps.org/FAQs/MacrosVBA/WordBasicCommands.htm. All you
need is to replace the line
QuickSort oFldDDArray, a, z
with the line
WordBasic.SortArray oFldDDArray
and then you can remove the entire QuickSort and Swap procedures, and
delete the declarations and assignments of the variables a and z. It's
just that much less code to maintain.

On a more computer-science level, QuickSort isn't necessarily a good
choice for the job when the array is known to be 25 or fewer items
*and* it's known to be almost sorted already, with only one item out
of order. The reason is that QuickSort has a high "overhead" of
computing sorted sub-arrays and choosing the "pivot point" iMid, and
its worst performance occurs with arrays that are already sorted. It's
more appropriate for a large array (say, more than 100 items) that's
most likely to be in random order.

In this case, I'd suggest that a more efficient algorithm would be:
- Store the current dropdown list in the array.
- Clear the dropdown.
- Starting from the LBound of the array, add the array items in order
to the dropdown list while the current array item is less than the new
item.
- Add the new item to the dropdown list.
- Add the rest of the array to the dropdown list.

This will involve a maximum of 23 comparisons (when the new item is
larger than any of the existing ones, not counting the "Other\Add"
entry), with an average of 12 comparisons.

On a practical level, of course, none of these considerations are
going to make a difference of more than a few millseconds in the
execution time.

 

[Greg Maxey]

That's is great. Thanks.

I think I follow you on the alternate method that you suggested. Something
like this:
'Clear existing list
oFld.DropDown.ListEntries.Clear
For i = LBound(oFldDDArray) To UBound(oFldDDArray)
If oFldDDArray(i) < oFldDDArray(numEntries - 1) Then
oFld.DropDown.ListEntries.Add oFldDDArray(i)
Else
oFld.DropDown.ListEntries.Add oFldDDArray(numEntries - 1)
Exit For
End If
Next i
For j = i To UBound(oFldDDArray) - 1
oFld.DropDown.ListEntries.Add oFldDDArray(j)
Next j
'Restore "Other\Add" entry at end of list
oFld.DropDown.ListEntries.Add "Other\Add"
'Display result
oFld.Result = newEntry

This seems to work. I like the abbreviate sort method better.

Thanks for all the help.

- Store the current dropdown list in the array. (No problem)
- Clear the dropdown. (No problem
- Starting from the LBound of the array, add the array items in order
to the dropdown list while the current array item is less than the new
item.

- Add the new item to the dropdown list.
- Add the rest of the array to the dropdown list.