Speed up macro screenupdating

Hi all, So I see a lot of very good VBA solutions provided by people, and it makes me feel all warm and fuzzy.

I love VBA and tend to use it everywhere, even when a formula will do the job for me.

Defining your variables greatly improves code readability./u/woo545: Go to Tools | Options and turn on Require Variable Declaration.

speed up macro screenupdating-81speed up macro screenupdating-65

If you look at the formula bar, you can see that the formula itself is not inserted into cell A3. For example, place a command button on your worksheet and add the following code lines: As a result, your code will run much faster and you will only see the end result (10000).Public basically means that the object can be referenced from outside.Outside could mean another method, or another class.A very common thing I see is people using terrible names for their variables. Public Sub Do Stuff() Dim d Number1 as Double, d Number2 as Double On Error Resume Next ' As types are Double, if user enters a string then we have a problem d Number1 = Input Box("Number 1: ") If Is Null(d Number1) Or Not Is Numeric(d Number1) Then d Number1 = 0 d Number2 = Input Box("Number 2: ") If Is Null(d Number2) Or Not Is Numeric(d Number2) Then d Number2 = 0 d Result = Add Numbers(d Number1, d Number2) End Sub Private Function Add Numbers (By Val u Number1 as Double, By Val u Number2 as Double) As Double ‘ We pass By Value because we are not changing the values, only using them Add Numbers = u Number1 u Number2 End Function We could have used two Sub Routines and Global Variables, but globals are generally bad.See below: NOTE: Do whatever you feel comfortable with, just remember to make your variables mean something, and always stick to the same format, Magic Numbers are very convenient and save on typing and memory. They take up more memory and make your code harder to read.Dim ws As Worksheet 'Step 2: Turn screen updating off to speed up your macro code Application. Close Save Changes:=True 'Step 6: Loop back around to the next worksheet Next ws 'Step 7: Turn screen updating on Application.Screen Updating = False 'Step 3: Start the looping through sheets For Each ws In This Workbook. However, they are very confusing to other readers and even to yourself when you go back through your code the next week! I can easily see that Add Numbers requires two Doubles, and returns a Double.If I were to have used Globals then it makes it hard for me to see where these values are coming from!Create a global sub that logs errors I usually set this on a module named "Globals".Public Sub g Write Log(p File Name, p Message) On Error Resume Next '* you don't want an error occurring when trying to log an error!

Leave a Reply