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" <(E-Mail Removed)> wrote in message
news:A67C0AA4-77B0-4BBC-BAB3-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:A67C0AA4-77B0-4BBC-BAB3-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
news:0C40F8E6-5C1C-47EC-B951-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
news:4CDFC425-4358-44B5-8537-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:A67C0AA4-77B0-4BBC-BAB3-(E-Mail Removed)...
>> > 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" <(E-Mail Removed)> wrote in message
news:4CDFC425-4358-44B5-8537-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:A67C0AA4-77B0-4BBC-BAB3-(E-Mail Removed)...
>> > 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" <(E-Mail Removed)> wrote in message
> news:0C40F8E6-5C1C-47EC-B951-(E-Mail Removed)...
> > 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!
> >> >
>
>
>