# Excel macro screen updating



## dmcclure (Jul 29, 1999)

I have a macro in which I have entered "Application.ScreenUpdating = False" at the beginning of a subroutine and "Application.ScreenUpdating = True" at the end. But the screen updating is not turned off and I can see all the actions the macro is taking as it moves around the worksheet. Is there something that may be ignoring that line of code? I've succesfully used this code in many other worksheets.

Thanks for the help.

Don


----------



## OBP (Mar 8, 2005)

Don, I have never heard of this happening, can you post a copy of the Workbook?


----------



## Zack Barresse (Jul 25, 2004)

You probably still have things like *Activate* and *Select* in your code. Try posting your code. 

_Edit: My post was cut off, apologies. The ScreenUpdating does not work like you are thinking. If you activate cells, you will still see them activated. The *screen* will not update. Think of it in terms of you looking through a window at your workbook. That window will not move or scroll, but that's it. Everything else is fair game._


----------



## dmcclure (Jul 29, 1999)

I have a lot of Activates and Selects (for example: Range("F7").Activate and ActiveCell.Offset(0, (RemLife + 1)).Activate) and (Range(Anchor1).Select). Is there some other way to do this?
I tried to send the Excel file but it exceeds the 200 KB limit on xls attachements.

Don


----------



## Zack Barresse (Jul 25, 2004)

Yes, there are other, more efficient and elegant ways of doing this. You could alternatively post your code using the code tags, or a scaled down sample of your work.


----------



## Jimmy the Hand (Jul 28, 2006)

dmcclure said:


> I tried to send the Excel file but it exceeds the 200 KB limit on xls attachements.Don


Why don't you try it compressed to, for example, zip.

Jimmy


----------



## dmcclure (Jul 29, 1999)

I've attached the zipped file. The macro runs from the page named "Final Annual Costs Worksheet" and macro name is "PostFutureCosts".

This macro is designed to do the following: For each asset place an inflation adjusted cost in each year the cost is incurred. For example, an asset that has a Useful Life of 5 years will have an inflation adjusted cost posted every 5 years until you past 2036.

Thanks for the help


----------



## Zack Barresse (Jul 25, 2004)

First things first. In your module, at the very top, put this line ..


```
Option Explicit
```
You can set this to appear in all new modules by going to Tools | Options | Editor (tab) | Require Variable Declaration (check) | Ok (click). Then you're going to need to uncomment some variables and add a couple more (I'll show you an example in a little bit). Once you do that, check that all variables are accounted for by attempting to compile your code (Debug | Compile VBAProject). If you do not get any errors, then compilation is good.

Next thing we'd need to know is where theses routines are supposed to run. There are not worksheets associated with them so they could run on any sheet.

I would love to help you with these routines, but they are (please, please, please, do not take offense) crappy. They are not efficient or intuitive enough to run on their own, or have a monkey run them (which is our goal). I say that because if we can have a monkey run them, then they are less likely to break down and will give us quick and accurate results. At least that's my way of thinking.

So as I don't want to try to fix something that is broken from the start, can you explain what it is that you are trying to do? I think it will be easier than you think when it's all said and done.


----------



## OBP (Mar 8, 2005)

The problem is not with Application.ScreenUpdating, it is your Extend Sub Routine, It is switching it back on again.


----------



## Jimmy the Hand (Jul 28, 2006)

I didn't mean to cross a VBA Master, but... I've worked on this for an hour, and am reluctant to let it sink and disappear.... 

Anyway, this subroutine generates the same result as the one you posted, except that it's limited to Sheet "Final Annual Costs Worksheet". And *Sub Extend* is not needed anymore, its functionality being included in the main sub.


```
Option Explicit

Sub PostFutureCosts()

'       This module calculates to first future cost for each element including inflation.
'       It determines the Replacment Year based on the Current Year, Remaining Life
'       and Replacemnt Year. It then calculates the future cost = CurrentValue*(1+Interest Rate)^Number of Years.


    Dim Wks As Worksheet
    Dim AssetRng As Range, Cel As Range
    
    Dim UsefulLife As Long, RemLife As Long, ReplYear As Long, CurrentYear As Long
    Dim InterestRate As Single, CurrentCost As Single
    
    Set Wks = Sheets("Final Annual Costs Worksheet")
    
    Wks.Range("G7:AN268").ClearContents      'Clear out previous calculations
    CurrentYear = Wks.Range("G3").Value
    InterestRate = Wks.Range("C1").Value
    
    Set AssetRng = Wks.Range("F7", Wks.Range("F7").End(xlDown))
    For Each Cel In AssetRng.Cells
        If Cel > 0 Then
        
            CurrentCost = Cel.Value
            UsefulLife = Cel.Offset(, -3)       'Get values for current asset
            RemLife = Cel.Offset(, -2)
            
            For ReplYear = 0 To 33
                If ((ReplYear - RemLife) Mod UsefulLife = 0) And ((ReplYear - RemLife) \ UsefulLife > -1) Then
                    Cel.Offset(, ReplYear + 1) = CurrentCost * (1 + InterestRate) ^ ReplYear
                End If
            Next
        End If
    Next
End Sub
```
One more piece of advice.
Listen to Zack, he is the best :up:

Regards,

Jimmy


----------



## dmcclure (Jul 29, 1999)

Thanks for all your suggestions. And - I do not take offense! On the contrary I truly appreciate the help. As you have observed I am not skilled at writing macros. I'm going to try the code posted by Jimmy the Hand and go through it see how it's done.

Thanks again for all your help

Don


----------



## dmcclure (Jul 29, 1999)

The code posted by Jimmy the Hand worked great! I need to do a lot more studying. The trouble is my 78 yaear old mind isn't as sharp as it used to be and there is no one around here that teaches that sort of stuff.

Anyway - a great big thanks to Jimmy the Hand!!!!

Don


----------



## Jimmy the Hand (Jul 28, 2006)

Hi Don 
You're welcome! I'm really joyful to see some people stay young forever 
Do you need explanation on my code?

Jimmy


----------



## Zack Barresse (Jul 25, 2004)

Don, don't let Jimmy speak too lightly of his skill - or too highly of mine! There are some truly great experts at this board, Jimmy is one of the few. A couple others that come to mind are bomb #21, Rollin Again and OBP. I'm sure there are others, but those are the few I've had the good fortune of running across. Truly worthy of the title 'expert'. 

Now, I hope I can point out a couple of issues that give me concern, only conceptually. I'm pulling this out of Jimmy's code...


```
CurrentYear = Wks.Range("G3").Value
    InterestRate = Wks.Range("C1").Value
```
The code is good, but I wonder about the intuitiveness of it. Are you going to remember to change your code every year? Are you actually going to want to? I certainly would not. My idea of good code is code that can be self-reliant, easy to troubleshoot, easily updatable (modular) and using good techniques (should I say best practices?).

Jimmy's code takes all of those things into account and has done a superb job. The question lies with you, Don. How would you like to setup your code to be dynamic and self-reliant? Is your data structue going to change? You could set your 'CurrentYear' variable either by column number or searching for it (all in code). So let's say the year 2007 is in G3, maybe you could use something like this ...


```
CurrentYear = Wks.Cells(3, Year(Date) - 2000).Value
```
Will your interest rate ever change? If not, maybe you could leave it hard coded to one cell, but if you ever change its location, you would also have to remember to change your code.

Just some food for thought. You're in very good hands when Jimmy the Hand is working with you.


----------



## dmcclure (Jul 29, 1999)

The interest rate will change every year and that's why I gave it a range name. The tip about the current year is most useful and I think I can include it in the code. While the code works OK for 2007 I was concerned about how to set it up in future years. 

We don't have a staff that is fluent in Excel macros so I am trying to include a lot of comments and write a "Usrs Manual" so that whoever picks this up in the future can hopefully figure what I've done.

Thanks again for all the help!

Don


----------



## Zack Barresse (Jul 25, 2004)

That is an excellent idea Don! Maybe you should look into a help file creator (there are some free software options out there); and yes, comment your code up! A logical description will work wonders, then comments explaining, throughout your code, what you are trying to accomplish.

Education is the best key. Good luck with it!


----------

