Hi! I have a problem with an array that I have in a udf. I send the array to
another udf in order to sort the array. Somewhere there it goes wrong and the
code stops.
€¦€¦
Call SortArray(A)
basel = A(2)

End Function
€¦..
Function SortArray(ByRef TheArray As Variant)
Sorted = False
Do While Not Sorted
Sorted = True
For X = 1 To UBound(TheArray)
If TheArray(X) TheArray(X + 1) Then
Temp = TheArray(X + 1)
TheArray(X + 1) = TheArray(X)
TheArray(X) = Temp
Sorted = False
End If
Next X
Loop
End Function

The sorting seems to work just fine but the code never goes back to the line
basel = A(2). Instead it goes back to the line before the Call SortArray. I
have no idea what is wrong. Please if anyone can help me! Thanks a lot!

Function SortArray(ByRef TheArray As Variant)
Dim Sorted As Boolean
Dim X As Long
Dim temp
Sorted = False
Do While Not Sorted
Sorted = True
For X = LBound(TheArray) To UBound(TheArray) - 1
If TheArray(X) TheArray(X + 1) Then
temp = TheArray(X + 1)
TheArray(X + 1) = TheArray(X)
TheArray(X) = temp
Sorted = False
End If
Next X
Loop
End Function



--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Arne Hegefors" wrote in message
...
Hi! I have a problem with an array that I have in a udf. I send the array
to
another udf in order to sort the array. Somewhere there it goes wrong and
the
code stops.
..
Call SortArray(A)
basel = A(2)

End Function
...
Function SortArray(ByRef TheArray As Variant)
Sorted = False
Do While Not Sorted
Sorted = True
For X = 1 To UBound(TheArray)
If TheArray(X) TheArray(X + 1) Then
Temp = TheArray(X + 1)
TheArray(X + 1) = TheArray(X)
TheArray(X) = Temp
Sorted = False
End If
Next X
Loop
End Function

The sorting seems to work just fine but the code never goes back to the
line
basel = A(2). Instead it goes back to the line before the Call SortArray.
I
have no idea what is wrong. Please if anyone can help me! Thanks a lot!

I'm guessing that you're hiding errors somewhere in your code with an "on error
resume next" line.

This could cause trouble:

For X = 1 To UBound(TheArray)
If TheArray(X) TheArray(X + 1) Then

Once x is equal to ubound(thearray), then TheArray(X+1) will blow up real good.

This worked ok for me:

Option Explicit
Sub testme()

Dim A As Variant
Dim BaseL As Long

A = Array(11, 5, -2, -3)

On Error Resume Next
Call SortArray(A)
BaseL = A(2)

MsgBox BaseL

End Sub
Function SortArray(ByRef TheArray As Variant)
Dim Sorted As Boolean
Dim X As Long
Dim Temp As Variant

Sorted = False
Do While Not Sorted
Sorted = True
For X = LBound(TheArray) To UBound(TheArray) - 1
If TheArray(X) TheArray(X + 1) Then
Temp = TheArray(X + 1)
TheArray(X + 1) = TheArray(X)
TheArray(X) = Temp
Sorted = False
End If
Next X
Loop
End Function



Arne Hegefors wrote:

Hi! I have a problem with an array that I have in a udf. I send the array to
another udf in order to sort the array. Somewhere there it goes wrong and the
code stops.
€¦€¦
Call SortArray(A)
basel = A(2)

End Function
€¦..
Function SortArray(ByRef TheArray As Variant)
Sorted = False
Do While Not Sorted
Sorted = True
For X = 1 To UBound(TheArray)
If TheArray(X) TheArray(X + 1) Then
Temp = TheArray(X + 1)
TheArray(X + 1) = TheArray(X)
TheArray(X) = Temp
Sorted = False
End If
Next X
Loop
End Function

The sorting seems to work just fine but the code never goes back to the line
basel = A(2). Instead it goes back to the line before the Call SortArray. I
have no idea what is wrong. Please if anyone can help me! Thanks a lot!

--

Dave Peterson

Hi Bob! Thanks alot for your answer! I have been spending a whole working day
on this and I still cannot solve it. I used you code but I cannot get it to
work. My code looks like this:

SortArray (A)
basel = �(2)
End Function
........
Function SortArray(ByRef TheArray As Variant)
Dim Sorted As Boolean
Dim X As Long
Dim B As String
Dim temp
Sorted = False
Do While Not Sorted
Sorted = True
For X = LBound(TheArray) To UBound(TheArray)
If TheArray(X) TheArray(X + 1) Then
temp = TheArray(X + 1)
TheArray(X + 1) = TheArray(X)
TheArray(X) = temp
Sorted = False
End If
Next X
Loop
End Function

the idea is to sort the array A and then present the number that has
position 2. But there is still someting wrong and I have no idea! If anyone
can help me please do so! Any help appreciated! Thanks alot!

"Bob Phillips" skrev:

Function SortArray(ByRef TheArray As Variant)
Dim Sorted As Boolean
Dim X As Long
Dim temp
Sorted = False
Do While Not Sorted
Sorted = True
For X = LBound(TheArray) To UBound(TheArray) - 1
If TheArray(X) TheArray(X + 1) Then
temp = TheArray(X + 1)
TheArray(X + 1) = TheArray(X)
TheArray(X) = temp
Sorted = False
End If
Next X
Loop
End Function



--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Arne Hegefors" wrote in message
...
Hi! I have a problem with an array that I have in a udf. I send the array
to
another udf in order to sort the array. Somewhere there it goes wrong and
the
code stops.
..
Call SortArray(A)
basel = A(2)

End Function
...
Function SortArray(ByRef TheArray As Variant)
Sorted = False
Do While Not Sorted
Sorted = True
For X = 1 To UBound(TheArray)
If TheArray(X) TheArray(X + 1) Then
Temp = TheArray(X + 1)
TheArray(X + 1) = TheArray(X)
TheArray(X) = Temp
Sorted = False
End If
Next X
Loop
End Function

The sorting seems to work just fine but the code never goes back to the
line
basel = A(2). Instead it goes back to the line before the Call SortArray.
I
have no idea what is wrong. Please if anyone can help me! Thanks a lot!

Hi Arne,

Give this a try.

Change your function declaration to become like this:

Function SortArray(ByVal TheArray As Variant) as Variant

Then, before the "End Function" line, put this line:

SortArray = TheArray

To use your new function, you do something like this

A = SortArray(A)
basel = A(2)


--
Hope that helps.

Vergel Adriano


"Arne Hegefors" wrote:

Hi! I have a problem with an array that I have in a udf. I send the array to
another udf in order to sort the array. Somewhere there it goes wrong and the
code stops.
€¦€¦
Call SortArray(A)
basel = A(2)

End Function
€¦..
Function SortArray(ByRef TheArray As Variant)
Sorted = False
Do While Not Sorted
Sorted = True
For X = 1 To UBound(TheArray)
If TheArray(X) TheArray(X + 1) Then
Temp = TheArray(X + 1)
TheArray(X + 1) = TheArray(X)
TheArray(X) = Temp
Sorted = False
End If
Next X
Loop
End Function

The sorting seems to work just fine but the code never goes back to the line
basel = A(2). Instead it goes back to the line before the Call SortArray. I
have no idea what is wrong. Please if anyone can help me! Thanks a lot!

Never mind. I misread your post. Bob's code worked.

In case you missed what he changed, change this line in your code:

For X = LBound(TheArray) To UBound(TheArray)

to like this

For X = LBound(TheArray) To UBound(TheArray)-1



--
Hope that helps.

Vergel Adriano


"Vergel Adriano" wrote:

Hi Arne,

Give this a try.

Change your function declaration to become like this:

Function SortArray(ByVal TheArray As Variant) as Variant

Then, before the "End Function" line, put this line:

SortArray = TheArray

To use your new function, you do something like this

A = SortArray(A)
basel = A(2)


--
Hope that helps.

Vergel Adriano


"Arne Hegefors" wrote:

Hi! I have a problem with an array that I have in a udf. I send the array to
another udf in order to sort the array. Somewhere there it goes wrong and the
code stops.
€¦€¦
Call SortArray(A)
basel = A(2)

End Function
€¦..
Function SortArray(ByRef TheArray As Variant)
Sorted = False
Do While Not Sorted
Sorted = True
For X = 1 To UBound(TheArray)
If TheArray(X) TheArray(X + 1) Then
Temp = TheArray(X + 1)
TheArray(X + 1) = TheArray(X)
TheArray(X) = Temp
Sorted = False
End If
Next X
Loop
End Function

The sorting seems to work just fine but the code never goes back to the line
basel = A(2). Instead it goes back to the line before the Call SortArray. I
have no idea what is wrong. Please if anyone can help me! Thanks a lot!

A littel bit more than that, it was

For X = 1 To UBound(TheArray)

to

For X = LBound(TheArray) To UBound(TheArray)-1



--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Vergel Adriano" wrote in message
...
Never mind. I misread your post. Bob's code worked.

In case you missed what he changed, change this line in your code:

For X = LBound(TheArray) To UBound(TheArray)

to like this

For X = LBound(TheArray) To UBound(TheArray)-1



--
Hope that helps.

Vergel Adriano


"Vergel Adriano" wrote:

Hi Arne,

Give this a try.

Change your function declaration to become like this:

Function SortArray(ByVal TheArray As Variant) as Variant

Then, before the "End Function" line, put this line:

SortArray = TheArray

To use your new function, you do something like this

A = SortArray(A)
basel = A(2)


--
Hope that helps.

Vergel Adriano


"Arne Hegefors" wrote:

Hi! I have a problem with an array that I have in a udf. I send the
array to
another udf in order to sort the array. Somewhere there it goes wrong
and the
code stops.
..
Call SortArray(A)
basel = A(2)

End Function
...
Function SortArray(ByRef TheArray As Variant)
Sorted = False
Do While Not Sorted
Sorted = True
For X = 1 To UBound(TheArray)
If TheArray(X) TheArray(X + 1) Then
Temp = TheArray(X + 1)
TheArray(X + 1) = TheArray(X)
TheArray(X) = Temp
Sorted = False
End If
Next X
Loop
End Function

The sorting seems to work just fine but the code never goes back to the
line
basel = A(2). Instead it goes back to the line before the Call
SortArray. I
have no idea what is wrong. Please if anyone can help me! Thanks a lot!

Aaah, don't put the array in brackets, use

SortArray A

or

Call SortArray(A)

but not

SortArray(A)

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Arne Hegefors" wrote in message
...
Hi Bob! Thanks alot for your answer! I have been spending a whole working
day
on this and I still cannot solve it. I used you code but I cannot get it
to
work. My code looks like this:

SortArray (A)
basel = Á(2)
End Function
.......
Function SortArray(ByRef TheArray As Variant)
Dim Sorted As Boolean
Dim X As Long
Dim B As String
Dim temp
Sorted = False
Do While Not Sorted
Sorted = True
For X = LBound(TheArray) To UBound(TheArray)
If TheArray(X) TheArray(X + 1) Then
temp = TheArray(X + 1)
TheArray(X + 1) = TheArray(X)
TheArray(X) = temp
Sorted = False
End If
Next X
Loop
End Function

the idea is to sort the array A and then present the number that has
position 2. But there is still someting wrong and I have no idea! If
anyone
can help me please do so! Any help appreciated! Thanks alot!

"Bob Phillips" skrev:

Function SortArray(ByRef TheArray As Variant)
Dim Sorted As Boolean
Dim X As Long
Dim temp
Sorted = False
Do While Not Sorted
Sorted = True
For X = LBound(TheArray) To UBound(TheArray) - 1
If TheArray(X) TheArray(X + 1) Then
temp = TheArray(X + 1)
TheArray(X + 1) = TheArray(X)
TheArray(X) = temp
Sorted = False
End If
Next X
Loop
End Function



--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Arne Hegefors" wrote in message
...
Hi! I have a problem with an array that I have in a udf. I send the
array
to
another udf in order to sort the array. Somewhere there it goes wrong
and
the
code stops.
..
Call SortArray(A)
basel = A(2)

End Function
...
Function SortArray(ByRef TheArray As Variant)
Sorted = False
Do While Not Sorted
Sorted = True
For X = 1 To UBound(TheArray)
If TheArray(X) TheArray(X + 1) Then
Temp = TheArray(X + 1)
TheArray(X + 1) = TheArray(X)
TheArray(X) = Temp
Sorted = False
End If
Next X
Loop
End Function

The sorting seems to work just fine but the code never goes back to the
line
basel = A(2). Instead it goes back to the line before the Call
SortArray.
I
have no idea what is wrong. Please if anyone can help me! Thanks a lot!

and this

For X = LBound(TheArray) To UBound(TheArray)

should be

For X = LBound(TheArray) To UBound(TheArray) - 1

as I gave you

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Arne Hegefors" wrote in message
...
Hi Bob! Thanks alot for your answer! I have been spending a whole working
day
on this and I still cannot solve it. I used you code but I cannot get it
to
work. My code looks like this:

SortArray (A)
basel = Á(2)
End Function
.......
Function SortArray(ByRef TheArray As Variant)
Dim Sorted As Boolean
Dim X As Long
Dim B As String
Dim temp
Sorted = False
Do While Not Sorted
Sorted = True
For X = LBound(TheArray) To UBound(TheArray)
If TheArray(X) TheArray(X + 1) Then
temp = TheArray(X + 1)
TheArray(X + 1) = TheArray(X)
TheArray(X) = temp
Sorted = False
End If
Next X
Loop
End Function

the idea is to sort the array A and then present the number that has
position 2. But there is still someting wrong and I have no idea! If
anyone
can help me please do so! Any help appreciated! Thanks alot!

"Bob Phillips" skrev:

Function SortArray(ByRef TheArray As Variant)
Dim Sorted As Boolean
Dim X As Long
Dim temp
Sorted = False
Do While Not Sorted
Sorted = True
For X = LBound(TheArray) To UBound(TheArray) - 1
If TheArray(X) TheArray(X + 1) Then
temp = TheArray(X + 1)
TheArray(X + 1) = TheArray(X)
TheArray(X) = temp
Sorted = False
End If
Next X
Loop
End Function



--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Arne Hegefors" wrote in message
...
Hi! I have a problem with an array that I have in a udf. I send the
array
to
another udf in order to sort the array. Somewhere there it goes wrong
and
the
code stops.
..
Call SortArray(A)
basel = A(2)

End Function
...
Function SortArray(ByRef TheArray As Variant)
Sorted = False
Do While Not Sorted
Sorted = True
For X = 1 To UBound(TheArray)
If TheArray(X) TheArray(X + 1) Then
Temp = TheArray(X + 1)
TheArray(X + 1) = TheArray(X)
TheArray(X) = Temp
Sorted = False
End If
Next X
Loop
End Function

The sorting seems to work just fine but the code never goes back to the
line
basel = A(2). Instead it goes back to the line before the Call
SortArray.
I
have no idea what is wrong. Please if anyone can help me! Thanks a lot!

Hi! Thank you very much for all your help! I would never I have solved it
myself! Your code works fine but I have final question. If all the cells that
I am taking in as argument in the main function are empty or non valid then I
get an error message in the Excel sheet. However I would like to display
"n/a". The variable k keeps track of the number of valid cells but I have not
managed to solve it but simply writing

if k = 0 then
basel = "n/a"
end if

If anyone could help me with this I would be most grateful. Again thanks for
all your help!!!

"Bob Phillips" skrev:

A littel bit more than that, it was

For X = 1 To UBound(TheArray)

to

For X = LBound(TheArray) To UBound(TheArray)-1



--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Vergel Adriano" wrote in message
...
Never mind. I misread your post. Bob's code worked.

In case you missed what he changed, change this line in your code:

For X = LBound(TheArray) To UBound(TheArray)

to like this

For X = LBound(TheArray) To UBound(TheArray)-1



--
Hope that helps.

Vergel Adriano


"Vergel Adriano" wrote:

Hi Arne,

Give this a try.

Change your function declaration to become like this:

Function SortArray(ByVal TheArray As Variant) as Variant

Then, before the "End Function" line, put this line:

SortArray = TheArray

To use your new function, you do something like this

A = SortArray(A)
basel = A(2)


--
Hope that helps.

Vergel Adriano


"Arne Hegefors" wrote:

Hi! I have a problem with an array that I have in a udf. I send the
array to
another udf in order to sort the array. Somewhere there it goes wrong
and the
code stops.
..
Call SortArray(A)
basel = A(2)

End Function
...
Function SortArray(ByRef TheArray As Variant)
Sorted = False
Do While Not Sorted
Sorted = True
For X = 1 To UBound(TheArray)
If TheArray(X) TheArray(X + 1) Then
Temp = TheArray(X + 1)
TheArray(X + 1) = TheArray(X)
TheArray(X) = Temp
Sorted = False
End If
Next X
Loop
End Function

The sorting seems to work just fine but the code never goes back to the
line
basel = A(2). Instead it goes back to the line before the Call
SortArray. I
have no idea what is wrong. Please if anyone can help me! Thanks a lot!

As far as I can see it is just sorting the data. Are you sure that you
aren't getting confused with your post on MrExcel?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Arne Hegefors" wrote in message
...
Hi! Thank you very much for all your help! I would never I have solved it
myself! Your code works fine but I have final question. If all the cells
that
I am taking in as argument in the main function are empty or non valid
then I
get an error message in the Excel sheet. However I would like to display
"n/a". The variable k keeps track of the number of valid cells but I have
not
managed to solve it but simply writing

if k = 0 then
basel = "n/a"
end if

If anyone could help me with this I would be most grateful. Again thanks
for
all your help!!!

"Bob Phillips" skrev:

A littel bit more than that, it was

For X = 1 To UBound(TheArray)

to

For X = LBound(TheArray) To UBound(TheArray)-1



--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Vergel Adriano" wrote in
message
...
Never mind. I misread your post. Bob's code worked.

In case you missed what he changed, change this line in your code:

For X = LBound(TheArray) To UBound(TheArray)

to like this

For X = LBound(TheArray) To UBound(TheArray)-1



--
Hope that helps.

Vergel Adriano


"Vergel Adriano" wrote:

Hi Arne,

Give this a try.

Change your function declaration to become like this:

Function SortArray(ByVal TheArray As Variant) as Variant

Then, before the "End Function" line, put this line:

SortArray = TheArray

To use your new function, you do something like this

A = SortArray(A)
basel = A(2)


--
Hope that helps.

Vergel Adriano


"Arne Hegefors" wrote:

Hi! I have a problem with an array that I have in a udf. I send the
array to
another udf in order to sort the array. Somewhere there it goes
wrong
and the
code stops.
..
Call SortArray(A)
basel = A(2)

End Function
...
Function SortArray(ByRef TheArray As Variant)
Sorted = False
Do While Not Sorted
Sorted = True
For X = 1 To UBound(TheArray)
If TheArray(X) TheArray(X + 1) Then
Temp = TheArray(X + 1)
TheArray(X + 1) = TheArray(X)
TheArray(X) = Temp
Sorted = False
End If
Next X
Loop
End Function

The sorting seems to work just fine but the code never goes back to
the
line
basel = A(2). Instead it goes back to the line before the Call
SortArray. I
have no idea what is wrong. Please if anyone can help me! Thanks a
lot!

basel = A(2)
End Function

Hi. Just a question. Are you sorting the array just to get the second
smallest value? Maybe...

A = Array(5, 3, 7, 1, 8)

BaseL = WorksheetFunction.Small(A, 2)
'or
Call SortArray(A)
BaseL = A(2)

You would have to adjust your indexes in case the LBound of your array is
not 1.
--
HTH :)
Dana DeLouis
Windows XP & Office 2007


"Arne Hegefors" wrote in message
...
Hi! I have a problem with an array that I have in a udf. I send the array
to
another udf in order to sort the array. Somewhere there it goes wrong and
the
code stops.
..
Call SortArray(A)
basel = A(2)

End Function
...
Function SortArray(ByRef TheArray As Variant)
Sorted = False
Do While Not Sorted
Sorted = True
For X = 1 To UBound(TheArray)
If TheArray(X) TheArray(X + 1) Then
Temp = TheArray(X + 1)
TheArray(X + 1) = TheArray(X)
TheArray(X) = Temp
Sorted = False
End If
Next X
Loop
End Function

The sorting seems to work just fine but the code never goes back to the
line
basel = A(2). Instead it goes back to the line before the Call SortArray.
I
have no idea what is wrong. Please if anyone can help me! Thanks a lot!