Call sub with array

  • Thread starter Thread starter Guest
  • Start date Start date
G

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!
 
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)
 
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
 
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:
 
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)
 
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
 
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)
 
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)
 
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)
 
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:
 
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)
 
basel = A(2)
End Function
Click to expand...

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.
 
Back
Top