vba - Excel custom function: Control filter/sort behavior for mixed (string) output

Question

I have a column with cells computing a value representing file volumes in bytes and a function outputting a string converting to appropriate units (96949643551 -> "90.291 GiB") which I then decided to wrap around that value:
=Format_Filesize(B3-B3*Settings!$B$4/E3)

• B3-B3*Settings!$B$4/E3 calculates the difference between the original file size B3 using the original bitrate E3 and the projected file size using a new bitrate in Settings!$B$4, and might result in something like 53954174.66
• Format_Filesize() returns 51,45 MiB for this example

This was quite pleasing until I realized I might want to sort that column along with the others (using the "Data -> Sort&Filter -> Filter" functionality.

Is there a way to either

• Supply a handler for sort evaluations? Maybe create a custom class that has different getter functions or an override method like onSort?
• or to dynamically decide what a cell displays? Like:
  123456;"Display Text" sorting using the whole value but only displaying "Display Text"?
  nvm, Excel's A-Z/Z-A sorting is not natural sort

My thoughts so far:

1. I can have a column for sorting (Calculated number) and a column for pretty display (Format_Filesize() of the numeric cell)
   That's easy to do but ugly; ideally I'd hide the numeric column, but then I couldn't easily sort with it anymore
2. I can use =[NumericResult] & "; " & Fortmat_Filesize([NumericResult]) to have the numeric value in front for sorting and the formatted string to read on the right.
   No, I can't: the filter sort function would sort strictly alphabetically (2>100)
3. I can write my own sort function and insert a shape or control tied to a sorting macro and click on that instead of using the Filter Sort functionality for that column
4. instead of a custom function to output a formatted string value, I could use a custom number format like [<1024]##0.00" B";[<1048576]##0.00," KB";##0.00,," MB"
   Downsides:
   - I'd have to use base 10 units (KB, MB) instead of base 2 units (KiB, MiB)
   - Only a maximum of 2 conditions are possible, followed by a catch-all, which means that it would display 5.34 TB as 5340000 MB

I think 1 or 2 are pretty good work-arounds, but I'd be very interested in any better solutions, even if they're seemingly too complicated to be worth implementing - for fun and/or sake of completeness.

question from:https://stackoverflow.com/questions/65870890/excel-custom-function-control-filter-sort-behavior-for-mixed-string-output

Answer 1

Waitting for answers