Variant vs Range

[DarthGremlin]

Hello,

So I recently posted a question on here to figure out what was wrong with my code which I had as

Public Function SortRange(rngToSort As Range, valCol As Integer)
    Dim Swapper As Variant
    Dim i As Integer, _
    j As Integer, _
    k As Integer

    For i = 1 To rngToSort.Rows.Count
        For j = 1 To rngToSort.Rows.Count - i
            If rngToSort(j + 1, valCol) < rngToSort(j, valCol) Then
                For k = 1 To rngToSort.Columns.Count
                    Swapper = rngToSort(j, k)
                    rngToSort(j, k) = rngToSort(j + 1, k)
                    rngToSort(j + 1, k) = Swapper
                Next k
            End If
        Next j
    Next i
    SortRange = rngToSort
End Function

and Someone named Rorya helped be out by changing it to:

Public Function SortRange(rngToSort As Range, valCol As Integer)
   Dim Swapper As Variant, varData
   Dim i As Integer, _
   j As Integer, _
   k As Integer
   
   varData = rngToSort.Value
   For i = 1 To rngToSort.Rows.Count
      For j = 1 To rngToSort.Rows.Count - i
         If varData(j + 1, valCol) < varData(j, valCol) Then
            For k = 1 To rngToSort.Columns.Count
               Swapper = varData(j, k)
               varData(j, k) = varData(j + 1, k)
               varData(j + 1, k) = Swapper
            Next k
         End If
      Next j
   Next i
   SortRange = varData
End Function

Now the question I have is, why didn't my code work exactly? I see that what he has done was he set my incoming range to a variant with the line varData = rngToSort.Value and then used that through out the remainder of the code. What exactly is the difference between using the range that came in and changing it to a variant? Isthere a way to switch back and forth between the two? am I not able to alter any incoming range? or am I not able to alter a range at all? Also Everything I've done so far has just been done from observing code that others wrote and trying to use the same patterns to get correct syntax and all, is there a resource of the syntax for VB code used in a UDF?

 

[RoryA]

You were trying to alter the range. As I said, you can't do that from a function in a cell. Assigning the values to a variant array, allows you to reorder them. (Your original code would have worked if not called from a cell).

 

[DarthGremlin]

So if I am understanding you correctly, a variant array is data only while a range is tied directly to a range of cells on the spreadsheet?

 

[Richard Schollar]

Yep

 

[RoryA]

yup.

 

[RoryA]

Rich, stop doing that - it's starting to freak me out!

 

[Richard Schollar]

Would you believe I was wavering between "Yup" and "Yep"!

 

[RoryA]

Would you believe I was wavering between "Yup" and "Yep"!

Yep