Jump to content

Sorting algorithm in VBA for Excel


vmser

Recommended Posts

In a VBA-project, i have an array which can range from 100x9 to 2500x9 (depending on the file it's run on).

 

The way I'm currently sorting the array is based on alphabetically sorting the values in one of the 9 columns, based on a bubblesort implementation, and then swapping the 9 elements arounds (read 9 to temp, replace first with second, replace second with temp).

 

However, for large arrays, which I'm inevitable running into, it's not nearly efficient enough. Which sorting algorithm would be the best to implement, taking into account I need to swap 9 elements around each time?

Vmser.png
Link to comment
Share on other sites

Do you know what big-O notation is? If not, read up on it. It's not a real hard concept, but I'd rather not waste time explaining it when there are some really good explanations out there already.

 

Anyway, bubble sort runs in O(n2) time, so it's basically as slow as insertion sorts. It actually ends up being a little slower, but not asymptotically. Insertion sorts are also a lot easier to implement anyway, so there's not really any reason to even know how to implement a bubble sort.

 

You really want to use an algorithm that runs in O(nlogn) time. The easiest one is quicksort. It's so much faster than bubble sort. A 1,000,000 element array takes, at most, 1,000,000,000,000*m (where m is how long it takes to do 1 "step") with bubblesort and only about 20,000,000*m with quicksort.

 

Here's a really dorky (but informative) video about it:

Link to comment
Share on other sites

Yes, I had figured bubblesort was O(n²), problem is I wasn't sure what the optimal was.

 

Suppose I'll look up a Quicksort algorithm in VBA and modify it to alter the 9 columns rather then one for a single dimension array.

Vmser.png
Link to comment
Share on other sites

Yeah, all you really need to know is how to implement quicksort. There are situations where it's slower than other sorts, but those aren't very common and it's usually more important to produce readable, easily interpreted code than it is to save a few milliseconds.

Link to comment
Share on other sites

Yeah, all you really need to know is how to implement quicksort. There are situations where it's slower than other sorts, but those aren't very common and it's usually more important to produce readable, easily interpreted code than it is to save a few milliseconds.

 

Usually, miliseconds don't bother me much either. But once a macro in Excel takes over 10 seconds to execute, I like to figure out what's taking so long.

Vmser.png
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.