Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
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
Medium Priority
?
71 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 22

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 22

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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 22

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 22

Accepted Solution

by:
Roy Cox earned 2000 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 22

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 22

Expert Comment

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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

636 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