Everything below applies to Excel 2007 and earlier. According to the link @Ƭᴇcʜιᴇ007 posted in the comments above, there is some native support for multithreading in Excel 2013. That said, the warning to just forget it unless you are an experienced programmer still applies.
Unfortunately, VBA does not support multithreading, so your VBA calculations will be limited to one core of your processor.
However, there is an advanced method of tricking VBA into running multiple threads by generating VBscript files and executing them concurrently. This gets around the problem by running your code outside the Excel process and allows Windows to manage the resources allocated to the different threads.
That said, getting this to work will more than likely mean completely rethinking the logic of your code (i.e., you'll have to figure out how to divvy up the tasks in a way that makes sense for them to run concurrently), which may very well be unfeasible for your project. I've never implemented this myself, so I can't really help you with this any more than by telling you what I've already told you.
If you wish to step into the rabbit hole, though, here's an interesting blog post that shows an example of this being done. Be warned: unless you are an accomplished programmer, you might as well forget this idea and just accept that VBA runs in a single thread.
Other resources on Stack Overflow for the daring:
There are of course other ways to optimize your VBA code without using multiple threads. Without seeing your code, it's impossible to make pointed suggestions, but here are a couple of the usual suspects:
- Load data from your sheet into an array for faster processing. Interactions with the worksheet are a major bottleneck in VBA execution, and they can be minimized by working with arrays.
- A related issue is Excel recalculating the workbook after each change made to a cell. This can be avoided by setting
Application.Calculation = xlManual. Just be sure to set it back to
Application.Calculation = xlAutomatic before exiting your Sub.