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
42 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
  • 6
  • 4
10 Comments
 
LVL 17

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 17

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
 

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 17

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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 17

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 17

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 17

Expert Comment

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

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Suggested Solutions

The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now