Link to home
Start Free TrialLog in
Avatar of DAVID131
DAVID131

asked on

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

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
Avatar of Roy Cox
Roy Cox
Flag of United Kingdom of Great Britain and Northern Ireland image

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
Avatar of DAVID131
DAVID131

ASKER

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
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.
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
I'll take a look at the existing code and see if I can help with that.
ASKER CERTIFIED SOLUTION
Avatar of Roy Cox
Roy Cox
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
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
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
Sorry that we haven't solved it completely.