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
51 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 18

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 18

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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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 18

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 18

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 18

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 18

Expert Comment

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

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

786 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