VBA Running Slow

wayne0881

Board Regular
Joined
Nov 2, 2009
Messages
87
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
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
5,188
Office Version
2019
Platform
Windows
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.
 
Last edited:

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,215
this may help...

Code:
[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

Board Regular
Joined
Nov 2, 2009
Messages
87
this may help...

Code:
[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!
 

Watch MrExcel Video

Forum statistics

Threads
1,089,923
Messages
5,411,266
Members
403,354
Latest member
JolynnCh

This Week's Hot Topics

Top