none
How to improve spreadsheet speed when single-threaded VBA is the bottleneck. RRS feed

  • Question

  • My brother works with massive Excel spreadsheets and needs to speed them up. Gigabytes in size and often with a million rows and many sheets within the workbook. He's already refined the sheets to take advantage of Excel's multi-thread recalculation and seen significant improvements, but he's hit a stumbling block. He uses extensive VBA code to aid clarity, but the VB engine is single-threaded, and these relatively simple functions can be called millions of times. Some functions are trivial (e.g. conversion functions) and just for clarity and easily unwound (at the expense of clarity), some could be unwound but that would make the spreadsheets much more complex, and others could not be unwound. 

    He's aware of http://www.analystcave.com/excel-vba-multithreading-tool/ and similar tools but they don't help as the granularity is insufficiently fine. 

    So what can he do? A search shows requests for multi-threaded VBA going back over a decade.

    qts

    Monday, April 20, 2015 11:33 PM

Answers

  • I seriously doubt the Excel workbook is Gigabytes.  If that's true, I hope you have the 64-bit version and a heck of a lot of RAM on that machine.

    If the data sets are truly that large, maybe you need to switch to Matlab, R, or Python may be another option for you.  Both R & Python are completely free.  Matlab is pretty expensive, but it's really fast, and it slices through large data sets easily.  Also, because you pay a lot for it, the support is great.  The support for R & Python is basically some discussion forums scattered around the web.

    Excel is a great tool for many things, but it sucks when trying to get through really large data sets, just as you're finding out.

    Good luck!!


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Tuesday, April 21, 2015 5:17 PM
  • Hi,

    >> The VB engine is single-threaded, and these relatively simple functions can be called millions of times.

    The Office Object Model is Single-Threaded Apartments, if the performance bottleneck is the Excel Object Model operation, the multiple-thread will not improve the performance significantly.

    >> How to improve spreadsheet speed when single-threaded VBA is the bottleneck.

    The performance optimization should be based on the business. Since I’m not familiar with your business, so I can only give you some general suggestions from the technical perspective. According to your description, the size of the spreadsheet had reached Gigabytes and data volume is about 1 million rows. If so, I will suggest you storing the data to SQL Server and then use the analysis tools (e.g. Power Pivot).

    Create a memory-efficient Data Model using Excel 2013 and the Power Pivot add-in

    As ryguy72 suggested, you can also leverage some other third party data processing tool according to your business requirement.

    Regards,

    Jeffrey


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, April 23, 2015 2:57 AM
    Moderator

All replies

  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel, I'll move your question to the MSDN forum for Excel

    http://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev&filter=alltypes&sort=lastpostdesc

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Thanks

    George Zhao
    Forum Support
    ________________________________________
    Come back and mark the replies as answers if they help and unmark them if they provide no help.
    If you have any feedback on our support, please click "tnfsl@microsoft.com"

    Tuesday, April 21, 2015 1:39 AM
  • I seriously doubt the Excel workbook is Gigabytes.  If that's true, I hope you have the 64-bit version and a heck of a lot of RAM on that machine.

    If the data sets are truly that large, maybe you need to switch to Matlab, R, or Python may be another option for you.  Both R & Python are completely free.  Matlab is pretty expensive, but it's really fast, and it slices through large data sets easily.  Also, because you pay a lot for it, the support is great.  The support for R & Python is basically some discussion forums scattered around the web.

    Excel is a great tool for many things, but it sucks when trying to get through really large data sets, just as you're finding out.

    Good luck!!


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Tuesday, April 21, 2015 5:17 PM
  • Doubt away. He uses a dual-CPU Xeon box at his workplace with 12 or 16 cores with many GB RAM and gets a significant improvement when he switches to a 24 core box. The main issue he has is the single-threaded nature of VBA, and he'd really like to find a way around it.

    qts



    Tuesday, April 21, 2015 9:19 PM
  • Quentin,

      You may have to hire a programmer to move from VBA to C and create those functions in an Excel add-in to get multi-thread power.  You can take a look at FastExcels website from some information on speeding up calculation or contact the author and ask for a quote to create you an add-in.

    http://www.decisionmodels.com/fastexcel.htm

    Regards

    Harry

    Wednesday, April 22, 2015 7:52 PM
  • Hi,

    >> The VB engine is single-threaded, and these relatively simple functions can be called millions of times.

    The Office Object Model is Single-Threaded Apartments, if the performance bottleneck is the Excel Object Model operation, the multiple-thread will not improve the performance significantly.

    >> How to improve spreadsheet speed when single-threaded VBA is the bottleneck.

    The performance optimization should be based on the business. Since I’m not familiar with your business, so I can only give you some general suggestions from the technical perspective. According to your description, the size of the spreadsheet had reached Gigabytes and data volume is about 1 million rows. If so, I will suggest you storing the data to SQL Server and then use the analysis tools (e.g. Power Pivot).

    Create a memory-efficient Data Model using Excel 2013 and the Power Pivot add-in

    As ryguy72 suggested, you can also leverage some other third party data processing tool according to your business requirement.

    Regards,

    Jeffrey


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, April 23, 2015 2:57 AM
    Moderator
  • I'm with Jeffrey

    Excel if brilliant- but has it's place

    SQLserver Express is free and works really well with excel as a front end- definitly the way forward

    D

    Thursday, April 23, 2015 10:55 AM