Excel VBA sort array function

Question

I've written the function displayed below. It works when I use it in the Sub. This is the first time that I'm trying to write a function. I know this is a noob question however I can't seem to get the function to run. It's probably something about not calling the function correctly or maybe not defining the variables in the function header correctly

Can anyone please point me in the right direction.

Function SortArray(ArrayToSort As String)
    Dim x As Long, y As Long
    Dim TempTxt1 As String
    Dim TempTxt2 As String

    For x = LBound(ArrayToSort) To UBound(ArrayToSort)
        For y = x To UBound(ArrayToSort)
            If UCase(ArrayToSort(y)) < UCase(ArrayToSort(x)) Then
                TempTxt1 = ArrayToSort(x)
                TempTxt2 = ArrayToSort(y)
                ArrayToSort(x) = TempTxt2
                ArrayToSort(y) = TempTxt1
            End If
        Next y
    Next x
End Function


Sub CreateUniquesList()
    Dim References() As String
    ...
    SortArray (References)
    ...
End Sub

What are you trying to do with the code and what is it not doing? It looks like you're doing comparisons on the same array, when one is larger, switch them around. Essentially ascending order? — Jimmy Smith
– Jimmy Smith, Commented May 19, 2017 at 19:41
The function is supposed to sort an array in alphabetical order. Like I said above, this works in the Sub. I've never written a Function before so I'm not sure if this is the correct way to implement the code as a function. Essentially, the code within the function is identical to the code that was in the Sub, so I'm not sure where I'm going wrong — Eitan Waks
– Eitan Waks, Commented May 19, 2017 at 19:46
You can just make it a Sub since your Function isn't returning anything... — MacroMarc
– MacroMarc, Commented May 19, 2017 at 19:52

interesting-name-here · Accepted Answer · 2017-05-19 19:55:03Z

3

Add parentheses to make sure the parameter is an array

Function SortArray(ArrayToSort() As String)
    'your code
End Function

Sub CreateUniquesList()
    Dim References() As String
    '...
    SortArray References 'lose the parentheses
    '...
End Sub

edited May 19, 2017 at 19:55

interesting-name-here

1,8901 gold badge22 silver badges35 bronze badges

answered May 19, 2017 at 19:51

MacroMarc

3,3242 gold badges13 silver badges22 bronze badges

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

Collectives™ on Stack Overflow

Excel VBA sort array function

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related