Solved

In VBA excel looking for nreasons that lines of code ar not being read - resulting in screen flicker

Posted on 2016-09-24
10
61 Views
Last Modified: 2016-09-30
In the sheet 'macro link', macro 1 simply copies down formulae in the summary sheet from row 11 downwards.
After the user has checked the outputs then, in the sheet macro link, macro 2 simply pastes the values

However when the first iteration is run none of the lines of code shown below happens, the screen flickers however the rest of the code runs and also macro 2 runs successfully
Application.StatusBar = "MACRO IN PROGRESS please wait......"
Application.ScreenUpdating = False
Application.DisplayAlerts = False

Leaving the application open and clearing the contents from row 11 down in the 'summary' sheet, then running macro 1 again, this time it works i.e Macro in progress is shown, there are no alerts and there is no flickering.

Clearing the 'summary' content, closing the workbook, re-opening the workbook, running macro 1 again - it fails again  to apply the lines of code shown

Whilst its fine that the code works eventually it is impractical to run the code, clear the results and run it again.
I would appreciate any suggestions to resolve this
EE---flicker.xlsm
0
Comment
Question by:DAVID131
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
10 Comments
 
LVL 19

Expert Comment

by:Roy_Cox
ID: 41813734
looking at your example workbook I would suggest you learn to use more of Excel's built in features before resorting to VBA.

Data should be stored in a table format with a single header row. There should be no completely empty rows or columns within the data. Set the data up correctly and format as a Table and your formulas will copy down automatically. Like wise, if you delete rows the formulas will adjust, that is known as Calculated Fields.

Read this, it is well worth the effort.

Overview of Excel Tables
0
 

Author Comment

by:DAVID131
ID: 41813842
the workbook has been cannibalized to remove sensitive data - are you saying that vba cannot5 be applied in this instance because of the reasons you have outlined
0
 
LVL 19

Expert Comment

by:Roy_Cox
ID: 41813852
No I'm saying that you don't need VBA. Your data needs tidying up and presenting as a Table. Then formulas will be dynamic.
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

by:DAVID131
ID: 41814680
regrettably I have no control over the structure of the data that I receive as it is issued by an external  supplier, plus you have the issue that all I can allow to be seen by EE are parts of the whole

Colleagues receiving the same information take several hours to manually process the data with copy, paste and extensive off sheet manual calculations whereas this model (of which this is only part) takes c 10 minutes.
I have no control over the format for outputs as they (there are multiple outputs)  must comply with the suppliers requirement

I think under the circumstances I will withdraw the request for assistance and leave the model in its present format which as I indicated earlier does work albeit a little untidily
0
 
LVL 19

Expert Comment

by:Roy_Cox
ID: 41814684
I'll take a look at the existing code and see if I can help with that.
0
 
LVL 19

Accepted Solution

by:
Roy_Cox earned 500 total points
ID: 41814720
I'm not sure why you have a problem The code runs and works with no flickering for me. It takes a few seconds but that is to be expected with the amount of cells. I tidied the code slightly, but it shouldn't affect screen flickering

Sub Fill_summary_formulae()
'
' Fill_summary_formulae Macro
'
    With Application
        .StatusBar = True
        .StatusBar = "MACRO IN PROGRESS please wait......"
        .ScreenUpdating = False
        .DisplayAlerts = False
        '
        Sheets("Summary").Range("$G$10:$ZZ$500").FillDown


        .ScreenUpdating = True
        .DisplayAlerts = True
        .StatusBar = False
    End With

    MsgBox "Summary values have been pasted.", vbInformation, "Done!"
End Sub

Open in new window

0
 

Author Comment

by:DAVID131
ID: 41815475
I substituted your code in the full model and whilst it cut the run time for this macro from 2mins 30 secs to 2 mins 15 second the flicker remained - I managed to get a screen dump of what I see - does this help?

The sequence is
1.  I activate the macro from the macro links sheet
2.  the screen remains static for c 30 seconds
3.  the red X at top left starts to flicker
4.  the ".................... not responding" starts to flash intermittently on the top bar
5.  I lose the macro screen and a blank screen appears
6. the screen flashes 22 times by jumping between the blank screen and the logo I have for my screen saver
7. the macro completes and delivers the correct answers

I think you will agree there is nothing in either the original code or your ammended code that would cause this to happen.
No other applications were open at the time -- only this model
I have bounced this past guys in IT and they are completely baffled and in my many years of Excel model building I have never had this happen before
Given that I have no control over the data that goes into the model (I believe it originates from a SAP system that has had issues and I am beginning to wonder if this may be a contributory factor) is it possible to mask the flickering by using code to display an image while the macro is running - as you can gather I am running out of ideas but my fall back position is to accept what happens and run the model as is - with your ammended code
flicker.docx
0
 
LVL 19

Expert Comment

by:Roy_Cox
ID: 41816427
Could it be caused by the size of the file?

You could also switch off calculations.

Option Explicit


Sub Fill_summary_formulae()
'
' Fill_summary_formulae Macro
    Dim lCalc As Long
    On Error GoTo the_end
    With Application
        lCalc = .Calculation
        .StatusBar = True
        .StatusBar = "MACRO IN PROGRESS please wait......"
        .ScreenUpdating = False
        .DisplayAlerts = False
        .EnableEvents = False
        '
        Sheets("Summary").Range("$G$10:$ZZ$500").FillDown
the_end:
        .Calculation = lCalc
        .ScreenUpdating = True
        .DisplayAlerts = True
        .StatusBar = False
        .EnableEvents = True
    End With

    MsgBox "Summary values have been pasted.", vbInformation, "Done!"
End Sub

Open in new window


Running Rob Bovey's Code Cleaner sometimes helps available here

I don't think using an image or a different would not mask the flickering
0
 

Author Comment

by:DAVID131
ID: 41817289
The file is 3.6Mb - not unduly large
Applied your code and its almost as if the instructions are totally ignored
I have attached a screen grab of what happened

Without full disclosure I don't think we are going to get to the bottom of this and we both have other things to do.

I would like to propose that I accept as a solution the proposed code in ID 41814720 (after all it did speed up the code) and we leave it at that
flicker-2.docx
0
 
LVL 19

Expert Comment

by:Roy_Cox
ID: 41823743
Sorry that we haven't solved it completely.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Copy Picture from one workbook to newly created workbook 12 37
Microsoft Access 32-bit or 64-bit? 11 55
need help to look for 16 42
the UDF returns #Value when i open workbook. 19 43
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question