0

Not sure if this is a code-specific issue or not, so I will be general for now.

I have a somewhat complicated macro that begins by reading financial market data that is manually inputted by a user into a worksheet, and then proceeds to process that market data, generating the required market curves, etc., and then calculate the certain valuations of interest.

The process requires a lot of looping since there are thousands of instruments that need to be valued. However, I noticed that every now and then the macro will loop extremely slowly - on the order of about 2-3 seconds per iteration. When I have the Excel workbook up, I can see down at the bottom it is saying "Calcuating (4 Processors x% complete)".

To resolve the issue I have to manually force Excel to shut down; usually this fixes the problem and the next time I run the program it works fine.

I am running Windows 8 (not 8.1) and Excel 2013. I've heard that this combination is particularly prone to crashing/bugs (I've experienced this several times myself where Excel will take a very long time to process basic requests such as font formatting or will spontaneously crash for no apparent reason).

However, I'd like to ask the community to see if the problem is more universal/known.

Thanks!

Community
  • 1
  • 1
Sargera
  • 265
  • 2
  • 11
  • 1
    Turn off `ScreenUpdating` and set `Calculation` to `xlCalculationManual` before running the iterations, then reverse them at the end. Additionally, make sure that your code, if possible, is working on an array, rather than on the ranges themselves. See [my answer here](http://stackoverflow.com/a/20754562/2548721) for some steps you can do to speed up your code. – WGS Mar 03 '15 at 03:26

3 Answers3

1

Try application.visible = false in the beginning of your code. Then make sure to do application.visible = true at the end. Should help.

Eman4real
  • 538
  • 5
  • 12
1

As a general tip for creating fast excel macros: wherever prossible don't loop through cells, you will get much better performance using a with statement on a range object, or where you need to operate on the data in a more elaborate way try copying your range of data into a 2 dimensional array, looping through an array will be orders of magnitude faster than looping though cells in a worksheet, you can then dump the array back to the range.

Coder375
  • 1,535
  • 12
  • 14
0

Try to change the cursor to xlBeam It changes everything in terms of speed in Windows 8.1

Sub CurseurDefault(zz As Boolean)
    If zz = True Then
        Application.Cursor = xlDefault
        'Call ShowCursor(True)
    Else
        Application.Cursor = xlIBeam
        'Call ShowCursor(False)
    End If
End Sub
rink.attendant.6
  • 44,500
  • 61
  • 101
  • 156
BenoitP.
  • 1