0

If you have variables and values in vba is it possible to sort them from max to min, but also to match them with variables. So for example if you have in VBA: 

Sub sort()
...
x = 5
y = 3
z = 8
...
End sub

That in excel you get (in A column variables, in B column values):

 A  B
 z  8
 x  5
 y  3

It's pretty easy to sort 8, 5, 3. But how to add z, x, y in their proper positions?

user155754
  • 83
  • 3
  • 11
  • Is it on sheet you want to sort? In that case you have the range A:B and sort. Excel will maintain the positions relative to values – Andreas Jul 19 '17 at 10:43
  • No, it's something I calculated in VBA. There's no x, y, z, 5, 3, 8 anywhere in excel. I want to paste it in excel sorted, but just the values don't represent anything. Names of the variables are as important. – user155754 Jul 19 '17 at 10:48

2 Answers2

2

AFAIK there isn't any way to do exactly what you are asking. Variable names are part of your code; using and accessing them as data would need reflection which is not something Excel VBA offers*.

But I don't think what you are asking is the best way to achieve what you want by a long way. What you seem to need is a way of storing (name, value) pairs and accessing both the name and the value. One straightforward way to do this is to use the Dictionary object.

There are alternatives if you need different functionality, e.g. using a pair of Arrays (one to hold the names, one to hold the values) - to make this neater you could write a class to keep the two together and implement whatever functions you need as methods.

Once you have the (name,value) pairs outputting them in a sorted list is straightforward. The simplest way is to write them to the spreadsheet and then use Excel's built-in Range.Sort (see MSDN documentation).

Putting it all together (note this needs a reference to the Microsoft Scripting Runtime library for the Dictionary object):

Dim dict As New Dictionary
Dim ws As Worksheet, rng As Range
Dim ky As Variant, itm As Variant

Set ws = ThisWorkbook.Worksheets(1)

'Add items to dictionary
dict.Add "x", 5
dict.Add "y", 3
dict.Add "z", 8

'You can use these in code like this:
For Each ky In dict.Keys
    Debug.Print "The value of " & ky & " is " & dict(ky)
Next ky
'you can change values
dict.item("z") = 10
dict.item("z") = 8

'Output the values and keys (the key/value arrays are horizontal while
'the range is vertical, hence transpose)
Set rng = ws.Range(ws.Cells(1, 1), ws.Cells(dict.Count, 2))
rng.Columns(1).Value = Application.Transpose(dict.Keys)
rng.Columns(2).Value = Application.Transpose(dict.Items)
'Sort them
rng.Sort Key1:=ws.Range("B1"), Header:=xlNo

*not easily anyway - there are some objects accessible through VBProject which allow limited access to the code. But using that to do what you are asking for would be nigh on impossible.

Graham
  • 7,431
  • 18
  • 59
  • 84
aucuparia
  • 2,021
  • 20
  • 27
0

In VBA there is no such thing as order between variables. You can determine if x is bigger than y, but that doesn't help you since you want them in a specific order.
My answer is not exactly what you are asking for, but I suspect it'll help you anyway.
Since you've got your output to the sheet, albeit unsorted, it is easier to sort it there. Simply sort columns A and B together with column B as primary.
Doing this in code is a bit more complex. You would need to put both name (x, y, z) and value(8, 5, 3) in an array and then sort your array. How to sort arrays have been describe several times before (like here: Excel VBA Quickest way to sort an array of numbers in descending order?)

Sam
  • 5,424
  • 1
  • 18
  • 33