Call sub with array

[Guest]

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!

 

[Bob Phillips]

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)

 

[Dave Peterson]

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

 

[Guest]

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:

 

[Guest]

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)

 

[Guest]

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

 

[Bob Phillips]

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)

 

[Bob Phillips]

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)

 

[Bob Phillips]

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)

 

[Guest]

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:

 

[Bob Phillips]

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)

 

[Dana DeLouis]

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.