VBA, Arrays, Ranges, Sorting

Question

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 ?

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). — varocarbas, Sep 30 '13 at 17:56
Thought it was like arrays and influenced by "Option base" http://msdn.microsoft.com/en-us/library/aa266179%28v=vs.60%29.aspx — Setepenre, Sep 30 '13 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. — varocarbas, Sep 30 '13 at 18:37

score 2 · Answer 1 · edited May 08 '18 at 21:48

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.

edited May 08 '18 at 21:48

Community

1
1

answered Sep 30 '13 at 17:54

AndASM

9,458
1
21
33

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 Sep 30 '13 at 17:58
@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." – AndASM Sep 30 '13 at 18:01
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. – varocarbas Sep 30 '13 at 18:03
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 Sep 30 '13 at 18:06
@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. – AndASM Sep 30 '13 at 18:11
@AndASM +1 well explained – Santosh Sep 30 '13 at 18:12
If there would be a ranking of people wasting time on endless-discussions just to help, I would most likely be in the top 5 (well... I am very modest: actually No. 1 :)). When I say "there is no way through" is because there is no way through – varocarbas Sep 30 '13 at 18:13
@Santosh I don't think that this answer deserves objectively a +1 is not addressing even half of the problems in the question. But well... – varocarbas Sep 30 '13 at 18:14
I am not trying to modify cells. My function return the array/Range sorted without modifying the original range. the function is similar to MMULT that multiply 2 matrix and returns the product in an array – Setepenre Sep 30 '13 at 18:14
@varocarbas Its a brilliant answer. – Santosh Sep 30 '13 at 18:16
(continue) if you take a look at the original code, there are not even 3 lines making completely sense. If you look at the second code, it is still worse. Saying "you are a newbie, don't worry, just miss this bit" is not reflecting the reality here: this person has serious lack of knowledge on VBA and helping him is saying this clearly. But if you feel better doing that, do it... might attract some attention (although perhaps not for the right reasons...) – varocarbas Sep 30 '13 at 18:17
@Santosh you are free to vote anything you want without giving any kind of reason. I have commented you this, just because it seemed to me that your vote wasn't because of the answer and because I don't think that estimulating wrongly-understood attitudes is positive for anyone, but completely up to you. If it is helpful for the OP, I would be really happy (and will +1 it myself); although by looking at the original code, this seems a bit difficult to happen. – varocarbas Sep 30 '13 at 18:19
Well... the magic did its trick. I guess that everyone should be happy with a so brilliant conclusion. – varocarbas Sep 30 '13 at 18:21
+1 because your answer provides an interesting summary for a VBA newcomer and, mainly, because of your effort and your intending-to-help attitude. But don't think that this is the site to do these things; neiher people coming here with so many lacks want that (but a working code, as you can see in the answer above). – varocarbas Sep 30 '13 at 18:44
@Setepenre No, it doesn't return the modified range as an array. M1 is a Range object. A range object always points at and alters cells in a worksheet. You cannot create a range object that is not attached to a worksheet. You need to create a new array as M1 and put the values in that. – AndASM Sep 30 '13 at 19:22

score 1 · Answer 2 · answered Sep 30 '13 at 18:19

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.

answered Sep 30 '13 at 18:19

Setepenre

1,883
3
13
16

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. – varocarbas Sep 30 '13 at 18:23
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. – varocarbas Sep 30 '13 at 18:33

VBA, Arrays, Ranges, Sorting

2 Answers2

Edit: References for further reading