vmser Posted September 28, 2014 Share Posted September 28, 2014 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? Link to comment Share on other sites More sharing options...
Hedgehog Posted September 28, 2014 Share Posted September 28, 2014 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 More sharing options...
vmser Posted September 28, 2014 Author Share Posted September 28, 2014 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. Link to comment Share on other sites More sharing options...
Hedgehog Posted September 28, 2014 Share Posted September 28, 2014 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 More sharing options...
vmser Posted September 29, 2014 Author Share Posted September 29, 2014 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. Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now