Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
can we hide the steps of a macro
Hello,
I have a macro in excel 2003 that reads a value from a cell on one worksheet, processes it, and records the result into a cell on another worksheet. The trouble is, it does this for hundreds of cells and when I run the macro the spreadsheet flicks between the sheets until the macro terminates. This looks very ugly. Is it possible to stop the screen updates at the beginning of the macro and then, when all the calculations have been done, let the screen be updated? I hope this makes sense. thank you |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
can we hide the steps of a macro
try something like this...
Sub Whatever() Application.ScreenUpdating = False 'Your code Application.ScreenUpdating = True End Sub -- HTH... Jim Thomlinson "none" <""john"@(none)" wrote: Hello, I have a macro in excel 2003 that reads a value from a cell on one worksheet, processes it, and records the result into a cell on another worksheet. The trouble is, it does this for hundreds of cells and when I run the macro the spreadsheet flicks between the sheets until the macro terminates. This looks very ugly. Is it possible to stop the screen updates at the beginning of the macro and then, when all the calculations have been done, let the screen be updated? I hope this makes sense. thank you |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
can we hide the steps of a macro
On 4 Oct, 22:02, none <""john\"@(none)" wrote:
Hello, I have a macro in excel 2003 that reads a value from a cell on one worksheet, processes it, and records the result into a cell on another worksheet. The trouble is, it does this for hundreds of cells and when I run the macro the spreadsheet flicks between the sheets until the macro terminates. This looks very ugly. Is it possible to stop the screen updates at the beginning of the macro and then, when all the calculations have been done, let the screen be updated? I hope this makes sense. thank you Hello None I would suggest that using object variables would resolve this. That way, you don't have to select the cell that you're either reading from or writing to - hence no flicker! Assuming that you're reading from Sheet1 and writing to Sheet3, and that you're just doing a simple squaring of the value (just to take an example) your code might look something like this: Regards Andrew PS Are you really a Nun? Just kidding ;-) Sub TransformValues() Dim rngSource As Range, rngDest As Range Dim sngCalcAnswer As Single Dim i As Integer 'Get the original data from A1 on Sheet1 Set rngSource = Sheets("Sheet1").Range("A1") 'Put the answer in A1 on Sheet3 Set rngDest = Sheets("Sheet3").Range("A1") 'Use i to offset the source and destinations 'so that we move down the rows for calculations 'Continue calculating until there is no more data. Do Until Len(rngSource.Offset(i).Value) = 0 'Modify the next line to include your desired calculations sngCalcAnswer = rngSource.Offset(i).Value ^ 2 'Enter the answer on Sheet3 rngDest.Offset(i).Value = sngCalcAnswer 'Don't forget to increment i to look at the next row i = i + 1 Loop End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Here are the steps I want to automate...I have recorded a macro but here is my problem. | Excel Programming | |||
how can we hide the steps of macro running | Excel Discussion (Misc queries) | |||
Don't show the steps in a macro | Excel Programming | |||
Steps from Macro recorder for Pivot table will not run as a Macro | Excel Programming | |||
Fill series macro with different steps | Excel Programming |