0

I have to write a function to sort a given range in VBA in Excel. (Bubble Sort) I have done the following :

Function MySort(M2 As Range)

    Dim r As Integer
    r = M2.Rows.Count

    Dim M1 as range
    M1 = M2

    Dim buffer

    For i = 0 To r
        For j = i To r

            If (M1.Item(i, 0) > M1.Item(j, 0)) Then

                buffer = M1.Item(i, 0)
                M1.Item(i, 0) = M1.Item(j, 0)
                M1.Item(j, 0) = buffer

            End If

        Next j
    Next i

    MySort = M1

End Function

Problems :

  • it returns "#VALUE"
  • I have tried converting the Range into an array
  • if I do not cast M2 into M1 the function returns the list unsorted
  • I have tried with basis 1 and with basis 0

I think I do have identified the main problem which is highlighted in the following example :

Function TestArray(M1 As Range)

    r = M1.Rows.Count

    Dim M2 As Range

    Dim M3()
    ReDim M3(r)

    M3 = M1
    M2 = M1

    TestArray = M3(0, 0) ' or M2.item(0, 0)

End Function

This function will return "#VALUE".

Why does M2.item(0, 0) return "#VALUE" ? What is the right way to compare two values of the same array / Range ?

Improve this question
3
  • There are many problems with this code: you are iterating through rows with an integer (and rows might become higher than what integer can bear), you are assigning ranges without using Set (compulsory), you are accessing an inexistent .Item property, you are iterating only through rows by letting the (theoretical) column as zero (columns start in 1), etc. I recommend you to take a (deep) look at VBA and how to interact with ranges before attempting anything (and coming here... remember the "minimal understanding" requirement). Commented Sep 30, 2013 at 17:56
  • Thought it was like arrays and influenced by "Option base" msdn.microsoft.com/en-us/library/aa266179%28v=vs.60%29.aspx Commented Sep 30, 2013 at 18:26
  • As said you have quite a few lacks which, IMO, require more help than just giving a specific solution (but you doing some research and testing to get used to the format before asking specific problems). Ranges are not arrays they have specific functions and all of them start from 1. Commented Sep 30, 2013 at 18:37

2 Answers 2

Reset to default
2

Well this line:

Dim M1 as range

Is creating an empty variable that can hold a reference to a range variable

M1 = M2

Is actually shorthand for

Let M1.Value = M2.Value

But since M1 is Nothing (null in other languages) you can't very well assign to one of its properties. So your UDF throws an error on that line and quits. You aren't casting the object or anthing. You shouldn't need or use M1 in the first place, you should only need M2.

Furthermore, this line:

M1.Item(i, 0) = M1.Item(j, 0)

Should be something like:

M1.Cells(i, 0) = M1.Cells(j, 0)

However it will never work in a UDF. Any function you call from a formula in an Excel cell CANNOT alter the contents of other cells. This is a big no-no in Excel. A UDF can read other cells only, then return a value. Thats it. If you want to change other cells you have to call it as a macro, not as a UDF.

Edit: References for further reading

Read up on writing UDFs and their limitations here.
Read up on the differences between Let and Set here.
You might also find it helpful to read up on the range object here and here.

Improve this answer
Sign up to request clarification or add additional context in comments.

15 Comments

I am afraid that the OP's code has lots of problems. Actually, so many that the best thing you can do with it is starting completely from scratch.
@varocarbas Yes, but we can provide hints on some of the big conceptual errors to point the OP in the right direction so that they can learn instead of just hitting a brick wall of "you don't know what you're doing go away."
It is completely up to you (I, personally, rely on the "minimal understanding to ask here" idea... but respect anyone's opinion). Although if you want to help the OP and he has written a so wrong code; perhaps the only way though is writing yourself a proper one.
Regarding "you don't know what you're doing go away.", this is a site for programmers. If you aren't programmer (or, at least, not in the specific language), perhaps you shouldn't use it; or, at least, do some work by your own (lots of free resources). Perhaps you should take a look at the rules of this site and at how are things expected to be done; one of the reasons for closing a question is "not showing enough understanding". Also, I am not sure that delivering working codes to someone not knowing too much is actually helping him. "Do it by your own for a while" sounds more helpful to me.
@varocarbas I go back and forth on that. It's easy to overestimate your knowledge and ability when you are starting out; beginners often don't realize how much they don't know. So, sometimes it helps to point out where they are going wrong. Instead of having them trying to solve the wrong problem, or a problem that doesn't exist.
|
1 
Function MySort(M2 As Range)

    Dim r As Integer
    r = M2.Rows.Count

    Dim ary()
    ReDim ary(r)

    ary = M2

    Dim buffer

    For i = 1 To r
        For j = i To r

            If (ary(i, 1) > ary(j, 1)) Then

                buffer = ary(i, 1)
                ary(i, 1) = ary(j, 1)
                ary(j, 1) = buffer

            End If

        Next j
    Next i

    MySort = ary

End Function

Changed to array I magically worked.

Improve this answer

2 Comments

and the values in M2 are magically transferred to ary ... I honestly don't get why people without the required knowledge come here: this is not the way to acquire it.
Anyway... you will be grateful to AndASM and intend to get some knowledge from what he explained or you prefer your magic solution and this is it? Because next time perhaps you should be more clear since the start such that the whole process will be quicker and without anyone wasting time unnecessarily.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.