List members,

Please help!

In module1 of my project I have declared:
Public aryLength(1 To 12) As Integer

In the 'change' function for a listbox on a user form I fill this array with
values retrieved from a worksheet.

I then call my subprocedure SortArrayAscend (which is in module1) as follows:
SortArrayAscendArray (aryLength)

The code for SortArrayAscend is:
Public Sub SortArrayAscend(aryLength)
'Sort an array in ascending order

Dim Index01 As Integer
Dim Index02 As Integer
Dim Work As Integer

For Index01 = LBound(aryLength) To UBound(aryLength) - 1
For Index02 = Index01 + 1 To UBound(aryLength)
If CInt(aryLength(Index01)) CInt(aryLength(Index02)) Then
Work = CInt(aryLength(Index02))
aryLength(Index02) = CInt(aryLength(Index01))
aryLength(Index01) = Work
End If
Next Index02
Next Index01
End Sub

If the elements in aryLength are "16, 18, 20, 12, 8, 14, 10, 0, 0, 0, 0, 0,
0" when passed to SortArrayAscend , when I debug SortArrayAscend, at "End
Sub" time , aryLength is "0, 0, 0, 0, 0, 0, 8, 10, 12, 14, 16, 18, 20" which
is what I expect.

However, at the very next statement, after returning from SortArrayAscend,
aryLength is exactly as it was before sorting. Since I am passing 'by
reference' I do not understand why the array is not sorted.

(I had initially attempted to write 'SortArrayAscend' as a 'generic' routine
to accept dynamic arrays, but kept getting 'type mismathes', but that's
another story. However, any help here would also be appreciated!)

Could someone please help me to understand:
1) Why what I am doing does not work as I expect (Having declared aryLength
as a 'public' variable and attempting to pass by reference.)
2) What needs to be done to get it to work.

Thanks in advance for any help.

Tim,

One of the benefits of a public variable is that you do not have to pass
it back and forth between Sub routines. It is available to all subs just
by being a public variable. So just assign values to it and sort it when
and where you want.
I believe your existing code has complicated things for Excel in that
aryLength is publicly declared as an array, while in your sort sub,
a new variable (using the identical name) is declared as a variant.

Also, "SortArrayAscendArray (aryLength)" is not the same as
"SortArrayAscend(aryLength)"

Regards,
Jim Cone
San Francisco, USA



"Tim Kredlo"

wrote in message

List members,
Please help!

In module1 of my project I have declared:
Public aryLength(1 To 12) As Integer

In the 'change' function for a listbox on a user form I fill this array with
values retrieved from a worksheet.

I then call my subprocedure SortArrayAscend (which is in module1) as follows:
SortArrayAscendArray (aryLength)

'The code for SortArrayAscend is:

Public Sub SortArrayAscend(aryLength)
'Sort an array in ascending order
Dim Index01 As Integer
Dim Index02 As Integer
Dim Work As Integer
For Index01 = LBound(aryLength) To UBound(aryLength) - 1
For Index02 = Index01 + 1 To UBound(aryLength)
If CInt(aryLength(Index01)) CInt(aryLength(Index02)) Then
Work = CInt(aryLength(Index02))
aryLength(Index02) = CInt(aryLength(Index01))
aryLength(Index01) = Work
End If
Next Index02
Next Index01
End Sub

If the elements in aryLength are "16, 18, 20, 12, 8, 14, 10, 0, 0, 0, 0, 0,
0" when passed to SortArrayAscend , when I debug SortArrayAscend, at "End
Sub" time , aryLength is "0, 0, 0, 0, 0, 0, 8, 10, 12, 14, 16, 18, 20" which
is what I expect.

However, at the very next statement, after returning from SortArrayAscend,
aryLength is exactly as it was before sorting. Since I am passing 'by
reference' I do not understand why the array is not sorted.

(I had initially attempted to write 'SortArrayAscend' as a 'generic' routine
to accept dynamic arrays, but kept getting 'type mismathes', but that's
another story. However, any help here would also be appreciated!)

Could someone please help me to understand:
1) Why what I am doing does not work as I expect (Having declared aryLength
as a 'public' variable and attempting to pass by reference.)
2) What needs to be done to get it to work.

Thanks in advance for any help.

Jim - thanks for your reply. It pointed out some errors in my posting but
still leaves me with some questions.

(1) 'SortArrayAscendArray' was a typo in the posting - should have been
'SortArrayAscend' - sorry for the confusion.

(2) When I originally declared the sub 'SortArrayAscend' I was hoping to
create a 'generic' procedure that could sort an array of a variable number of
integers. I had declared it as follows:
Public Sub SortArrayAscend(ArrayIn() As Integer)

Since I know that 'aryLength' is always going to be used to fill 12 form
labels, it is declared (in the procedure that called 'SortArrayAscend') as
follows:
Dim aryLength(1 To 12) As Integer
(It was NOT originally declared anywhere else. Declaring it as 'public' in
the module was an unsuccessful attempt to get it to work. I have since
removed the 'public' declaration)

Now when I call 'SortArrayAscend' as follows:
SortArrayAscend(aryLength)
I receive the following error:
'Compile error: Type mismatch: Array or user-defined type expexcted'.
Since both arrays (aryLength & ArrayIn) are declared as arrays of integers,
I do not understand the error. Clicking the 'Help' button on the error
message brings up a blank help page (nice feature). The only difference I see
is that aryLength has a fixed number of elements, while ArrayIn is dynamic
(in order to accept variable length arrays).

If I attempt to change the declaration for the 'SortArrayAscend' argument to
match the declaration of 'aryLength' as follows:
Public Sub SortArrayAscend(ArrayIn(1 to 12) As Integer), the editor no
longer recognizes the declaration as valid. The line turns 'red' and the
procedure dividing line disappears. That's OK, since that would make the
procedure only work for 12 element arrays.


Do you see I would be getting this 'mis-match' error?

Again, thanks for your input.

"Jim Cone" wrote:

Tim,

One of the benefits of a public variable is that you do not have to pass
it back and forth between Sub routines. It is available to all subs just
by being a public variable. So just assign values to it and sort it when
and where you want.
I believe your existing code has complicated things for Excel in that
aryLength is publicly declared as an array, while in your sort sub,
a new variable (using the identical name) is declared as a variant.

Also, "SortArrayAscendArray (aryLength)" is not the same as
"SortArrayAscend(aryLength)"

Regards,
Jim Cone
San Francisco, USA



"Tim Kredlo"

wrote in message

List members,
Please help!

In module1 of my project I have declared:
Public aryLength(1 To 12) As Integer

In the 'change' function for a listbox on a user form I fill this array with
values retrieved from a worksheet.

I then call my subprocedure SortArrayAscend (which is in module1) as follows:
SortArrayAscendArray (aryLength)

'The code for SortArrayAscend is:

Public Sub SortArrayAscend(aryLength)
'Sort an array in ascending order
Dim Index01 As Integer
Dim Index02 As Integer
Dim Work As Integer
For Index01 = LBound(aryLength) To UBound(aryLength) - 1
For Index02 = Index01 + 1 To UBound(aryLength)
If CInt(aryLength(Index01)) CInt(aryLength(Index02)) Then
Work = CInt(aryLength(Index02))
aryLength(Index02) = CInt(aryLength(Index01))
aryLength(Index01) = Work
End If
Next Index02
Next Index01
End Sub

If the elements in aryLength are "16, 18, 20, 12, 8, 14, 10, 0, 0, 0, 0, 0,
0" when passed to SortArrayAscend , when I debug SortArrayAscend, at "End
Sub" time , aryLength is "0, 0, 0, 0, 0, 0, 8, 10, 12, 14, 16, 18, 20" which
is what I expect.

However, at the very next statement, after returning from SortArrayAscend,
aryLength is exactly as it was before sorting. Since I am passing 'by
reference' I do not understand why the array is not sorted.

(I had initially attempted to write 'SortArrayAscend' as a 'generic' routine
to accept dynamic arrays, but kept getting 'type mismathes', but that's
another story. However, any help here would also be appreciated!)

Could someone please help me to understand:
1) Why what I am doing does not work as I expect (Having declared aryLength
as a 'public' variable and attempting to pass by reference.)
2) What needs to be done to get it to work.

Thanks in advance for any help.

Tim,

The receiving sub/function determines what is passed to it.
If no data type declaration is made for the argument it becomes a variant.
Your code was sending an array and the receiving sub was expecting
a variant.
So "SortArrayAscend(aryLength)" should read...
SortArrayAscend(ByRef aryLength() as Integer)

If you want to change the size of an array you have to "ReDim" it.
If you pass an array with 12 elements and want to change it then
do it in the main body of the sub...
ReDim aryLength(1 to 24)
However, all existing array elements lose their values.

Following is some sample code to fiddle with.
Jim Cone
San Francisco, USA
'------------------------------------
Sub ThatCallsAndPassesArray()
Dim ArrayOut() As Integer
Dim intNum As Integer
ReDim ArrayOut(1 To 12)

'Load the array
For intNum = 1 To 12
ArrayOut(intNum) = (intNum * intNum)
Next 'intNum

'Display value in last element
MsgBox ArrayOut(12)

'Call Sub and pass array to it
SortArrayAscend ArrayOut

'Display value in last element
MsgBox ArrayOut(12)
End Sub
'----------
Sub SortArrayAscend(ByRef ArrayIn() As Integer)
Dim lngLow As Long
Dim lngHigh As Long

lngLow = LBound(ArrayIn)
lngHigh = UBound(ArrayIn)

'Display the values in the lower and upper elements of the array
MsgBox ArrayIn(lngLow) & vbCr & ArrayIn(lngHigh)

'Change value in last element
ArrayIn(lngHigh) = 1234
End Sub
'----------------------------------


"Tim Kredlo"

wrote in message
...
Jim - thanks for your reply. It pointed out some errors in my posting but
still leaves me with some questions.

(1) 'SortArrayAscendArray' was a typo in the posting - should have been
'SortArrayAscend' - sorry for the confusion.

(2) When I originally declared the sub 'SortArrayAscend' I was hoping to
create a 'generic' procedure that could sort an array of a variable number of
integers. I had declared it as follows:
Public Sub SortArrayAscend(ArrayIn() As Integer)

Since I know that 'aryLength' is always going to be used to fill 12 form
labels, it is declared (in the procedure that called 'SortArrayAscend') as
follows:
Dim aryLength(1 To 12) As Integer
(It was NOT originally declared anywhere else. Declaring it as 'public' in
the module was an unsuccessful attempt to get it to work. I have since
removed the 'public' declaration)

Now when I call 'SortArrayAscend' as follows:
SortArrayAscend(aryLength)
I receive the following error:
'Compile error: Type mismatch: Array or user-defined type expexcted'.
Since both arrays (aryLength & ArrayIn) are declared as arrays of integers,
I do not understand the error. Clicking the 'Help' button on the error
message brings up a blank help page (nice feature). The only difference I see
is that aryLength has a fixed number of elements, while ArrayIn is dynamic
(in order to accept variable length arrays).

If I attempt to change the declaration for the 'SortArrayAscend' argument to
match the declaration of 'aryLength' as follows:
Public Sub SortArrayAscend(ArrayIn(1 to 12) As Integer), the editor no
longer recognizes the declaration as valid. The line turns 'red' and the
procedure dividing line disappears. That's OK, since that would make the
procedure only work for 12 element arrays.
Do you see I would be getting this 'mis-match' error?
Again, thanks for your input.

Jim,

Thanks for the tips. Got it to work.

Tim

"Jim Cone" wrote:

Tim,

The receiving sub/function determines what is passed to it.
If no data type declaration is made for the argument it becomes a variant.
Your code was sending an array and the receiving sub was expecting
a variant.
So "SortArrayAscend(aryLength)" should read...
SortArrayAscend(ByRef aryLength() as Integer)

If you want to change the size of an array you have to "ReDim" it.
If you pass an array with 12 elements and want to change it then
do it in the main body of the sub...
ReDim aryLength(1 to 24)
However, all existing array elements lose their values.

Following is some sample code to fiddle with.
Jim Cone
San Francisco, USA
'------------------------------------
Sub ThatCallsAndPassesArray()
Dim ArrayOut() As Integer
Dim intNum As Integer
ReDim ArrayOut(1 To 12)

'Load the array
For intNum = 1 To 12
ArrayOut(intNum) = (intNum * intNum)
Next 'intNum

'Display value in last element
MsgBox ArrayOut(12)

'Call Sub and pass array to it
SortArrayAscend ArrayOut

'Display value in last element
MsgBox ArrayOut(12)
End Sub
'----------
Sub SortArrayAscend(ByRef ArrayIn() As Integer)
Dim lngLow As Long
Dim lngHigh As Long

lngLow = LBound(ArrayIn)
lngHigh = UBound(ArrayIn)

'Display the values in the lower and upper elements of the array
MsgBox ArrayIn(lngLow) & vbCr & ArrayIn(lngHigh)

'Change value in last element
ArrayIn(lngHigh) = 1234
End Sub
'----------------------------------


"Tim Kredlo"

wrote in message
...
Jim - thanks for your reply. It pointed out some errors in my posting but
still leaves me with some questions.

(1) 'SortArrayAscendArray' was a typo in the posting - should have been
'SortArrayAscend' - sorry for the confusion.

(2) When I originally declared the sub 'SortArrayAscend' I was hoping to
create a 'generic' procedure that could sort an array of a variable number of
integers. I had declared it as follows:
Public Sub SortArrayAscend(ArrayIn() As Integer)

Since I know that 'aryLength' is always going to be used to fill 12 form
labels, it is declared (in the procedure that called 'SortArrayAscend') as
follows:
Dim aryLength(1 To 12) As Integer
(It was NOT originally declared anywhere else. Declaring it as 'public' in
the module was an unsuccessful attempt to get it to work. I have since
removed the 'public' declaration)

Now when I call 'SortArrayAscend' as follows:
SortArrayAscend(aryLength)
I receive the following error:
'Compile error: Type mismatch: Array or user-defined type expexcted'.
Since both arrays (aryLength & ArrayIn) are declared as arrays of integers,
I do not understand the error. Clicking the 'Help' button on the error
message brings up a blank help page (nice feature). The only difference I see
is that aryLength has a fixed number of elements, while ArrayIn is dynamic
(in order to accept variable length arrays).

If I attempt to change the declaration for the 'SortArrayAscend' argument to
match the declaration of 'aryLength' as follows:
Public Sub SortArrayAscend(ArrayIn(1 to 12) As Integer), the editor no
longer recognizes the declaration as valid. The line turns 'red' and the
procedure dividing line disappears. That's OK, since that would make the
procedure only work for 12 element arrays.
Do you see I would be getting this 'mis-match' error?
Again, thanks for your input.

Private Sub UserForm_Initialize()
Dim sh As Worksheet
Dim rng As Range
Dim myval As Range
Dim myarray() As Variant

Range("C1").Select
Do Until ActiveCell = "GS"
Selection.End(xlDown).Select
Loop
Selection.Offset(1, 0).Select


Set sh = Workbooks("Book1.xls").Worksheets("Sheet1")
Set myval = Range(Selection, Selection.End(xlDown).Offset(1, 0))
Set rng = myval
myarray = rng
ComboBox1.List = myarray
End Sub

I am trying to figure out how to sort this array for the combobox1. Right
now it grabs about 150 names in this range and I want to sort them ascending
in the combobox. Can you help please?

"Jim Cone" wrote:

Tim,

The receiving sub/function determines what is passed to it.
If no data type declaration is made for the argument it becomes a variant.
Your code was sending an array and the receiving sub was expecting
a variant.
So "SortArrayAscend(aryLength)" should read...
SortArrayAscend(ByRef aryLength() as Integer)

If you want to change the size of an array you have to "ReDim" it.
If you pass an array with 12 elements and want to change it then
do it in the main body of the sub...
ReDim aryLength(1 to 24)
However, all existing array elements lose their values.

Following is some sample code to fiddle with.
Jim Cone
San Francisco, USA
'------------------------------------
Sub ThatCallsAndPassesArray()
Dim ArrayOut() As Integer
Dim intNum As Integer
ReDim ArrayOut(1 To 12)

'Load the array
For intNum = 1 To 12
ArrayOut(intNum) = (intNum * intNum)
Next 'intNum

'Display value in last element
MsgBox ArrayOut(12)

'Call Sub and pass array to it
SortArrayAscend ArrayOut

'Display value in last element
MsgBox ArrayOut(12)
End Sub
'----------
Sub SortArrayAscend(ByRef ArrayIn() As Integer)
Dim lngLow As Long
Dim lngHigh As Long

lngLow = LBound(ArrayIn)
lngHigh = UBound(ArrayIn)

'Display the values in the lower and upper elements of the array
MsgBox ArrayIn(lngLow) & vbCr & ArrayIn(lngHigh)

'Change value in last element
ArrayIn(lngHigh) = 1234
End Sub
'----------------------------------


"Tim Kredlo"

wrote in message
...
Jim - thanks for your reply. It pointed out some errors in my posting but
still leaves me with some questions.

(1) 'SortArrayAscendArray' was a typo in the posting - should have been
'SortArrayAscend' - sorry for the confusion.

(2) When I originally declared the sub 'SortArrayAscend' I was hoping to
create a 'generic' procedure that could sort an array of a variable number of
integers. I had declared it as follows:
Public Sub SortArrayAscend(ArrayIn() As Integer)

Since I know that 'aryLength' is always going to be used to fill 12 form
labels, it is declared (in the procedure that called 'SortArrayAscend') as
follows:
Dim aryLength(1 To 12) As Integer
(It was NOT originally declared anywhere else. Declaring it as 'public' in
the module was an unsuccessful attempt to get it to work. I have since
removed the 'public' declaration)

Now when I call 'SortArrayAscend' as follows:
SortArrayAscend(aryLength)
I receive the following error:
'Compile error: Type mismatch: Array or user-defined type expexcted'.
Since both arrays (aryLength & ArrayIn) are declared as arrays of integers,
I do not understand the error. Clicking the 'Help' button on the error
message brings up a blank help page (nice feature). The only difference I see
is that aryLength has a fixed number of elements, while ArrayIn is dynamic
(in order to accept variable length arrays).

If I attempt to change the declaration for the 'SortArrayAscend' argument to
match the declaration of 'aryLength' as follows:
Public Sub SortArrayAscend(ArrayIn(1 to 12) As Integer), the editor no
longer recognizes the declaration as valid. The line turns 'red' and the
procedure dividing line disappears. That's OK, since that would make the
procedure only work for 12 element arrays.
Do you see I would be getting this 'mis-match' error?
Again, thanks for your input.

I am an experienced RPG programmer new to Visual Basic, so while my
techniques works, it may not be the best way to do this. I had built this
'generic' sort procedure for integers that should be easily modifiable for
your string fields by changing its declkaration to:
SortArrayAscend(ArrayIn() as String):
and removing the 'CInt's.
and changing 'work' to a string

Befo
Public Sub SortArrayAscend(ArrayIn() As Integer)
'Sort an array in ascending order

Dim Idx01 As Integer
Dim Idx02 As Integer
Dim Work As Integer

For Idx01 = LBound(ArrayIn) To UBound(ArrayIn) - 1
For Idx02 = Idx01 + 1 To UBound(ArrayIn)
If CInt(ArrayIn(Idx01)) CInt(ArrayIn(Idx02)) Then
Work = CInt(ArrayIn(Idx02))
ArrayIn(Idx02) = CInt(ArrayIn(Idx01))
ArrayIn(Idx01) = Work
End If
Next Idx02
Next Idx01

End Sub

After:
Public Sub SortArrayAscend(ArrayIn() As String)
'Sort an array in ascending order

Dim Idx01 As Integer
Dim Idx02 As Integer
Dim Work As String

For Idx01 = LBound(ArrayIn) To UBound(ArrayIn) - 1
For Idx02 = Idx01 + 1 To UBound(ArrayIn)
If ArrayIn(Idx01) ArrayIn(Idx02) Then
Work = ArrayIn(Idx02)
ArrayIn(Idx02) = ArrayIn(Idx01)
ArrayIn(Idx01) = Work
End If
Next Idx02
Next Idx01

End Sub

Like I said, I am new to VB programming, and would be interested in knowing
if this works for you.

"Kryer" wrote:

Private Sub UserForm_Initialize()
Dim sh As Worksheet
Dim rng As Range
Dim myval As Range
Dim myarray() As Variant

Range("C1").Select
Do Until ActiveCell = "GS"
Selection.End(xlDown).Select
Loop
Selection.Offset(1, 0).Select


Set sh = Workbooks("Book1.xls").Worksheets("Sheet1")
Set myval = Range(Selection, Selection.End(xlDown).Offset(1, 0))
Set rng = myval
myarray = rng
ComboBox1.List = myarray
End Sub

I am trying to figure out how to sort this array for the combobox1. Right
now it grabs about 150 names in this range and I want to sort them ascending
in the combobox. Can you help please?

"Jim Cone" wrote:

Tim,

The receiving sub/function determines what is passed to it.
If no data type declaration is made for the argument it becomes a variant.
Your code was sending an array and the receiving sub was expecting
a variant.
So "SortArrayAscend(aryLength)" should read...
SortArrayAscend(ByRef aryLength() as Integer)

If you want to change the size of an array you have to "ReDim" it.
If you pass an array with 12 elements and want to change it then
do it in the main body of the sub...
ReDim aryLength(1 to 24)
However, all existing array elements lose their values.

Following is some sample code to fiddle with.
Jim Cone
San Francisco, USA
'------------------------------------
Sub ThatCallsAndPassesArray()
Dim ArrayOut() As Integer
Dim intNum As Integer
ReDim ArrayOut(1 To 12)

'Load the array
For intNum = 1 To 12
ArrayOut(intNum) = (intNum * intNum)
Next 'intNum

'Display value in last element
MsgBox ArrayOut(12)

'Call Sub and pass array to it
SortArrayAscend ArrayOut

'Display value in last element
MsgBox ArrayOut(12)
End Sub
'----------
Sub SortArrayAscend(ByRef ArrayIn() As Integer)
Dim lngLow As Long
Dim lngHigh As Long

lngLow = LBound(ArrayIn)
lngHigh = UBound(ArrayIn)

'Display the values in the lower and upper elements of the array
MsgBox ArrayIn(lngLow) & vbCr & ArrayIn(lngHigh)

'Change value in last element
ArrayIn(lngHigh) = 1234
End Sub
'----------------------------------


"Tim Kredlo"

wrote in message
...
Jim - thanks for your reply. It pointed out some errors in my posting but
still leaves me with some questions.

(1) 'SortArrayAscendArray' was a typo in the posting - should have been
'SortArrayAscend' - sorry for the confusion.

(2) When I originally declared the sub 'SortArrayAscend' I was hoping to
create a 'generic' procedure that could sort an array of a variable number of
integers. I had declared it as follows:
Public Sub SortArrayAscend(ArrayIn() As Integer)

Since I know that 'aryLength' is always going to be used to fill 12 form
labels, it is declared (in the procedure that called 'SortArrayAscend') as
follows:
Dim aryLength(1 To 12) As Integer
(It was NOT originally declared anywhere else. Declaring it as 'public' in
the module was an unsuccessful attempt to get it to work. I have since
removed the 'public' declaration)

Now when I call 'SortArrayAscend' as follows:
SortArrayAscend(aryLength)
I receive the following error:
'Compile error: Type mismatch: Array or user-defined type expexcted'.
Since both arrays (aryLength & ArrayIn) are declared as arrays of integers,
I do not understand the error. Clicking the 'Help' button on the error
message brings up a blank help page (nice feature). The only difference I see
is that aryLength has a fixed number of elements, while ArrayIn is dynamic
(in order to accept variable length arrays).

If I attempt to change the declaration for the 'SortArrayAscend' argument to
match the declaration of 'aryLength' as follows:
Public Sub SortArrayAscend(ArrayIn(1 to 12) As Integer), the editor no
longer recognizes the declaration as valid. The line turns 'red' and the
procedure dividing line disappears. That's OK, since that would make the
procedure only work for 12 element arrays.
Do you see I would be getting this 'mis-match' error?
Again, thanks for your input.