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
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
5,096
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,181
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!
 

Forum statistics

Threads
1,084,744
Messages
5,379,581
Members
401,614
Latest member
priokatm

Some videos you may like

This Week's Hot Topics

  • VBA code giving errors and stopping Excel
    Hello Experts, I have this code being used to loop through files in a file path, and copy specific data to another sheet. It is giving me several...
  • Disable MsgBox message
    Morning, I have a userform where if i leave a ComboBox empty i see a MsgBox warning me that i must enter an invoice number. It is this MsgBox i...
  • Macro Recorder into VBA, Copy Paste Data Filled Cells
    Hi Everyone, I have a macro recorder file that takes a selection of data, copies, then pastes into a new sheet on ("A2:B2") The issue is my...
  • Number format changes while pasting into a cell
    Hi, I am trying to paste a number 180204524303 from an email to an excel cell, however, whenever i try to do so , the the paste value appears as...
  • Collating data
    Hello all. Could someone please help. I am trying to pull all column data from multiple sheets (24 I total so far) into 1 master sheet without...
  • Sum Multiple Columns Based on Multiple Criteria
    I am trying to consolidate data by summing columns G through M based on material, plant, vendor, and fiscal year being identical. The period does...
Top