VBA Running Slow

[wayne0881]

Hi All,

**Newbie VBA/Macro user*** - I use forums and Youtube to learn how to code but in there somewhere are probably some bad habits.

I have a form button on a project i'm working on which has now gone a bit slow to calculate.

The button picks a random number between 1 and 10 in the selected cells. I have 10 other buttons on the same page doing roughly the same calculations plus a master reset button that clears all cells. All of which do their job but at a slower pace with the more buttons/code I added.

My question is - is there a cleaner/better way to write this code to speed it up, as it has got bogged down and is running slow.(which is to be expected I guess)

my code for one of the buttons is:

Sub Button4_Click()

Range("C3") = WorksheetFunction.RandBetween(1, 5)
Range("C5") = WorksheetFunction.RandBetween(1, 10)
Range("C6") = WorksheetFunction.RandBetween(1, 10)
Range("C8") = WorksheetFunction.RandBetween(1, 10)
Range("C9") = WorksheetFunction.RandBetween(1, 10)
Range("C11") = WorksheetFunction.RandBetween(1, 10)
Range("C12") = WorksheetFunction.RandBetween(1, 10)
Range("C14") = WorksheetFunction.RandBetween(1, 10)
Range("C15") = WorksheetFunction.RandBetween(1, 10)
Range("C17") = WorksheetFunction.RandBetween(1, 10)
Range("C18") = WorksheetFunction.RandBetween(1, 10)
Range("C20") = WorksheetFunction.RandBetween(1, 10)
Range("C21") = WorksheetFunction.RandBetween(1, 10)
Range("C23") = WorksheetFunction.RandBetween(1, 10)
Range("C24") = WorksheetFunction.RandBetween(1, 10)
Range("C26") = WorksheetFunction.RandBetween(1, 10)
Range("C27") = WorksheetFunction.RandBetween(1, 10)
Range("C29") = WorksheetFunction.RandBetween(1, 10)
Range("C30") = WorksheetFunction.RandBetween(1, 10)
Range("C32") = WorksheetFunction.RandBetween(1, 10)
Range("C33") = WorksheetFunction.RandBetween(1, 10)
Range("C35") = WorksheetFunction.RandBetween(1, 10)
Range("C36") = WorksheetFunction.RandBetween(1, 10)
Range("C38") = WorksheetFunction.RandBetween(1, 10)
Range("C39") = WorksheetFunction.RandBetween(1, 10)





End Sub

Thanks All

 

[alansidman]

Add line of code to turn calculations off until the end of the code
Add line of code to turn off screen updates until the end of the code.

 

[AlphaFrog]

this may help...

[color=darkblue]Sub[/color] Button4_Click()


[color=darkblue]With[/color] Application
    .ScreenUpdating = [color=darkblue]False[/color]
    .Calculation = xlCalculationManual
    .EnableEvents = [color=darkblue]False[/color]
[color=darkblue]End[/color] [color=darkblue]With[/color]


Range("C3") = WorksheetFunction.RandBetween(1, 5)
Range("C5") = WorksheetFunction.RandBetween(1, 10)
Range("C6") = WorksheetFunction.RandBetween(1, 10)
Range("C8") = WorksheetFunction.RandBetween(1, 10)
Range("C9") = WorksheetFunction.RandBetween(1, 10)
Range("C11") = WorksheetFunction.RandBetween(1, 10)
Range("C12") = WorksheetFunction.RandBetween(1, 10)
Range("C14") = WorksheetFunction.RandBetween(1, 10)
Range("C15") = WorksheetFunction.RandBetween(1, 10)
Range("C17") = WorksheetFunction.RandBetween(1, 10)
Range("C18") = WorksheetFunction.RandBetween(1, 10)
Range("C20") = WorksheetFunction.RandBetween(1, 10)
Range("C21") = WorksheetFunction.RandBetween(1, 10)
Range("C23") = WorksheetFunction.RandBetween(1, 10)
Range("C24") = WorksheetFunction.RandBetween(1, 10)
Range("C26") = WorksheetFunction.RandBetween(1, 10)
Range("C27") = WorksheetFunction.RandBetween(1, 10)
Range("C29") = WorksheetFunction.RandBetween(1, 10)
Range("C30") = WorksheetFunction.RandBetween(1, 10)
Range("C32") = WorksheetFunction.RandBetween(1, 10)
Range("C33") = WorksheetFunction.RandBetween(1, 10)
Range("C35") = WorksheetFunction.RandBetween(1, 10)
Range("C36") = WorksheetFunction.RandBetween(1, 10)
Range("C38") = WorksheetFunction.RandBetween(1, 10)
Range("C39") = WorksheetFunction.RandBetween(1, 10)


[color=darkblue]With[/color] Application
    .ScreenUpdating = [color=darkblue]True[/color]
    .Calculation = xlCalculationAutomatic
    .EnableEvents = [color=darkblue]True[/color]
[color=darkblue]End[/color] [color=darkblue]With[/color]


[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[wayne0881]

this may help...

[COLOR=darkblue]Sub[/COLOR] Button4_Click()


[COLOR=darkblue]With[/COLOR] Application
    .ScreenUpdating = [COLOR=darkblue]False[/COLOR]
    .Calculation = xlCalculationManual
    .EnableEvents = [COLOR=darkblue]False[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]


Range("C3") = WorksheetFunction.RandBetween(1, 5)
Range("C5") = WorksheetFunction.RandBetween(1, 10)
Range("C6") = WorksheetFunction.RandBetween(1, 10)
Range("C8") = WorksheetFunction.RandBetween(1, 10)
Range("C9") = WorksheetFunction.RandBetween(1, 10)
Range("C11") = WorksheetFunction.RandBetween(1, 10)
Range("C12") = WorksheetFunction.RandBetween(1, 10)
Range("C14") = WorksheetFunction.RandBetween(1, 10)
Range("C15") = WorksheetFunction.RandBetween(1, 10)
Range("C17") = WorksheetFunction.RandBetween(1, 10)
Range("C18") = WorksheetFunction.RandBetween(1, 10)
Range("C20") = WorksheetFunction.RandBetween(1, 10)
Range("C21") = WorksheetFunction.RandBetween(1, 10)
Range("C23") = WorksheetFunction.RandBetween(1, 10)
Range("C24") = WorksheetFunction.RandBetween(1, 10)
Range("C26") = WorksheetFunction.RandBetween(1, 10)
Range("C27") = WorksheetFunction.RandBetween(1, 10)
Range("C29") = WorksheetFunction.RandBetween(1, 10)
Range("C30") = WorksheetFunction.RandBetween(1, 10)
Range("C32") = WorksheetFunction.RandBetween(1, 10)
Range("C33") = WorksheetFunction.RandBetween(1, 10)
Range("C35") = WorksheetFunction.RandBetween(1, 10)
Range("C36") = WorksheetFunction.RandBetween(1, 10)
Range("C38") = WorksheetFunction.RandBetween(1, 10)
Range("C39") = WorksheetFunction.RandBetween(1, 10)


[COLOR=darkblue]With[/COLOR] Application
    .ScreenUpdating = [COLOR=darkblue]True[/COLOR]
    .Calculation = xlCalculationAutomatic
    .EnableEvents = [COLOR=darkblue]True[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]


[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

That helped alot!!! back to instant calculations - Thank You!