Call sub with array
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!
|