# Variant vs Range

#### DarthGremlin

##### New Member
Hello,

So I recently posted a question on here to figure out what was wrong with my code which I had as
Code:
``````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:
Code:
``````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?

### Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

#### RoryA

##### MrExcel MVP, Moderator
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

##### New Member
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?

Yep

yup.

#### RoryA

##### MrExcel MVP, Moderator
Rich, stop doing that - it's starting to freak me out!

#### Richard Schollar

##### MrExcel MVP

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

Replies
3
Views
62
Replies
3
Views
104
Replies
16
Views
223
Replies
1
Views
71
Replies
2
Views
84

1,190,739
Messages
5,982,670
Members
439,790
Latest member
jonaust

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back