5

I have a VBA program running on my quad-core Acer Aspire S3 Ultrabook. The trouble is, it only uses 25% of the CPU (the other processes combined use ~1%). The laptop runs Windows 8.0. The Excel edition is 2013 (32-bit). Only 55% of system RAM is used, with Excel using half of the 55%.

I think maybe only 25% is used because only one core is used by Excel. However, I do not have anything to back this theory up. How do I speed up the program?

Thanks.

tropical
  • 53
  • 3
    Multithreading and Memory Contention in Excel 2013 – Ƭᴇcʜιᴇ007 Jul 28 '14 at 15:33
  • 4
    Sounds like Excel/VBA may not be the best platform for your program. – Ƭᴇcʜιᴇ007 Jul 28 '14 at 16:10
  • 2
    If you're concerned about run time, porting your computational logic into another platform would be a very good idea. I achieved an ~1000x speedup porting some very computationally intensive code from (Excel 03) VBA to C#: ~4 hours vs ~15 seconds. Same algorithm in both cases; and the VBA had already gotten a 5-10x speedup over the naive implementation by caching values to variables instead of reading the cell every time and disabling refreshing of the spreadsheet on screen every time a cell was changed during the computation. – Dan Is Fiddling By Firelight Jul 28 '14 at 17:16
  • 5
    It is easy to make VBA run slowly - but there are many tricks to make it much faster, even in one core. You can get 4x speedup if you could use all cores - but it's possible there is a 100x speedup just waiting to happen by changing a few lines of code. Perhaps you can show where you think the program is spending time - we might be able to help you speed it up. Example: declare most-used variables as Double or Long (instead of letting VBA choose Variant absent a declaration). It can make a big difference... – Floris Jul 28 '14 at 20:52
  • 1
    Uh what and where is it telling you that you are only using 25% this sounds like a programing error involving data writes –  Jul 29 '14 at 03:35
  • @caseyr547, my guess is the Task Manager's the one who claims the program's CPU usage is 25%. On a quad-core CPU that kind of value is normal for single-threaded programs. – Nolonar Jul 29 '14 at 09:04
  • @caseyr547 It is Task Manager – tropical Jul 29 '14 at 17:42
  • Data writes tropical is my advice something is generating interiors and that's normally what it is –  Jul 29 '14 at 17:43
  • Although only using 25% of your total resources, is this actually a problem? – Dave Jul 30 '14 at 07:21
  • @Dave I know that this is very old, but I would like to clear up that it is quite a problem at this speed, as the program takes about 20 minutes to run, but has to be run about 100 times each day ==> there are not enough minutes, and so I am not getting the best possible results from this program. Many thanks, tropical. – tropical Dec 29 '14 at 08:42

5 Answers5

11

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:
https://stackoverflow.com/q/19159025/657668
https://stackoverflow.com/q/5721564/657668

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.
Community
  • 1
Excellll
  • 12,717
  • Also, I believe it runs on the UI thread so it locks the UI while processing (which gets noticeable if you try and use UDFs for custom formatting). – Casey Jul 28 '14 at 18:40
  • Why would you want to hurt yourself with this madness when you could write your application in a way better language (both VB.NET and C# are arguably better than VBA), that by itself runs faster (JIT-compilation vs interpretation), actually supports concurrency, and still can interact with Excel via the Office PIAs? – Matteo Italia Jul 29 '14 at 11:09
6

I sounds like the program you're trying to use is single threaded, meaning it can only use a single core, it doesn't know the others exist. There really isn't a concrete way to speed this up except to buy a processor with a faster single core clock speed or use a program that supports multi-threading.

YetiFiasco
  • 508
  • Thank you for the answer. If I were to get the 64-bit edition of Excel, would it work across all the cores? Thanks again. – tropical Jul 28 '14 at 15:06
  • 4
    @tropical No. Switching to 64 bit would allow the process to use more than 4gb of memory, however it would not allow it to run on more cores. – Keltari Jul 28 '14 at 15:44
2

Like others said, natively VBA is not multithreaded. If you want to speed up you may want to consider writing user defined functions (UDF) in another language.

I would recommend you ExcelDNA and using C# or VB.Net. They are very easy to use if you already know how to write some C# and you can control multithreading within the UDF.

http://exceldna.codeplex.com/

BlueTrin
  • 324
  • 1
  • 2
  • 9
  • 1
    In most cases where you're tempted to do this, you should really think about whether Excel is the right tool for the job. Hammers and nails... – sapi Jul 29 '14 at 07:59
  • Also I agree with you, in Finance there are many cases where Excel is not the best solution but a decent solution, sometimes traders have to run Monte-Carlo simulations, or risk simulations. These benefits greatly from multithreading. Ofc you could build a proper infrastructure, but most of smaller organisations build Excel addins because they do not have the resources to build a complex infrastructure. – BlueTrin Jul 29 '14 at 08:28
2

Disclaimer: this is not a proper answer but I find there are many users that need to find this sort of hind together with other answers regarding usage of only one core and my reputation is too low to post it as a comment.

Looks like your VBA script is using only one core and that may very well be a good thing!

As others have mentioned in already good answers, I think it is important to know other aspects to this question to help you find a suitable solution. So how do you speed up the program?

  • 100% CPU usage in system overview doesn't mean your program goes as fast as it can: there may be many useless things being done, try searching for 'Optimizing VBA for speed'. Any good search engine will give you some useful answers here from the Stack Exchange network. Go for the time spent on calculation and not for CPU usage.
  • VBA in Excel is not necessarily the best tool to get the job done: for long-running number-crunching calculations it may be useful to export the data from excel and use some other platform, depending on your programming skills. There are very many to choose from, some integrate nicely with the Microsoft platform and have considerably better achievable performance (the doing useless things applies here as well).
  • Running 100% CPU probably won't give you 4x speed boost over running only 25%: depending on how good you break up your calculation, you may achieve as little as 2x boost on 4 cores. But there are cases that go as well as almost 4x boost on 4 cores - for example, if the calculation to be done on each line is completely independent of any other data in your spreadsheet. See Amdahl's law on Wikipedia.
  • It may not be worth it trying to use more cores: though it may be useful in some cases, making your calculation use more than one core can be (and usually is) a order of magnitude more difficult a task, requiring some thorough expertise in programming techniques (see this question for general overview).
  • Bonus: Maybe you don't need to speed up your program at all. If the calculation takes, say, three minutes and you only run it once a day, why not stand up from your chair for a bit and stretch your stiff bones? You are risking spending days tweaking your program to be able to run it in two minutes thirty, which pays off only in a very long run. A good programmer only spends his time on optimization when its really worth it, so let yourself be inspired by good programmers:)
Community
  • 1
Pavel
  • 374
  • 1
  • 2
  • 10
0

Like the others above have said, threading would be the answer, but it's not really a feasible solution, and upgrading to 64 bit would make a negligible difference. One thing you could try doing is increasing the process's priority, you can see how to do that here.

It's hard to say whether or not that will make your script any faster, since there may be another bottleneck elsewhere in the program (eg, reading/writing from disc), but it will at least let Windows know it is a higher priority than your other processes.

Community
  • 1
Adam
  • 1,558