Solved

Excel 2013 VBA  runtime vs Excel 2010 and 2007

Posted on 2015-02-04
13
1,042 Views
Last Modified: 2016-02-10
Dears, ...

I build a re-rating application in VBA which ran perfectly under office 2010 even under office 2007, without loosing speed during run time.
The App re-rates "Customer calling record", the results which do come out are then used for analysis purposes. In fact it's a  full functioning Mini "Mobile Billing engine" which simulates several scenario's for financial optimisation purposes.
The tool  came in several versions, but worked "bug free" "fast" and "perfectly fine" on Office 2010 at a fast speed.
Due to company policy, I was forced to upgrade to office 2013 a few weeks ago, but since then the problems began.
VBA runs significantly slower, which make that simulation run times last now for several minutes instead of seconds. Big ones even take up to 30 min while in Office 2010, it was only a matter of a few minutes.

Funny thing is that the App starts fast, but slows down as scenario's and time are progressing. ( changing affinity/priority does not help)
In terms off optimizing, I did already all necessary to optimise on screenupdating, events, calculation,  you name it, ...

Does anyone have the same problem.  Problem is that my collegues are dependent on the app and the outcome, and currently, the App becomes too slow to work with, especially when larger simulations are required. Are there any know issues in VB 2013 where I should be aware off, and do you have any suggestions how to solve the issue.

Thank you and best regards
Chrißt'll
0
Comment
Question by:Chrißt'll Scholiers
  • 5
  • 3
  • 2
13 Comments
 
LVL 80

Assisted Solution

by:byundt
byundt earned 500 total points
ID: 40590379
I suggest putting statements in your code that log the time so you can identify which parts of it are running slowly. You will get more resolution using the Timer function (hundredths of a second) than you do with the Time function (seconds). If you need even more resolution, you can get it with Windows API functions. The statement below uses Debug.Print to write some text and the number of seconds (with two decimal places) since midnight
Debug.Print "First step " & Timer

Open in new window

Once you have identified the slow parts of your code, you can then analyze whether the delay is caused by an external library, network file reference, or something unique to Excel VBA.

If you can reproduce the delay in a small block of code, we might be able to help you with a workaround in this forum. We could also consult Microsoft's KnowledgeBase to see if it is a known problem.

I suspect that you may ultimately want to elevate the issue to Microsoft. You can do this via their (paid) technical support. If they find the problem is caused by a bug in their code, there is no charge.
0
 

Accepted Solution

by:
Chrißt'll Scholiers earned 0 total points
ID: 40590796
All,

Part of the problem is solved, though speed is still a bit lower then in 2010.
The main problem was a graphical problem. Eventhough "screenupdating"  was disabled, I noticed some flickering on the scrollbars on the "sheet view" sheets

In 2010 it was never an issue in terms of performance, but apparently in "2013" it is an issue if the scroll bars start to blink ( as a result of switching between sheets in the background) during VBA runtime.
So apparently it means that the computer is still waisting a lot of its resources.
(High resource usage on graphics card might also give an additional indication of the problem.)

On top I suspect it even might cause a "memory leak", as when time progresses, the problem gets worse and even fully destabilises the "Insight App" and "Excel 2013".

Another effect is that in the corrected situation, I  also saw that the Calculating processors were picking up a lot more, increasing the speed of calculation.

the solution of the problem I applied is to put underneath in several modules : Application.DisplayScrollBars = True/False



Option Explicit

Sub DataBundleRatingV_0()
    
    '//////////////////////////////////////////////////////////////////////
    '/////////////////////////INPUT VARIABLES//////////////////////////////
    '//////////////////////////////////////////////////////////////////////
    
    '//////////////////////////////////////////////////////////////////////
    ' Declare Progressbar Variables
    '//////////////////////////////////////////////////////////////////////
    
    Dim Cancelled As Boolean
    Dim Diag As IProgressBar
    Dim CounterSub_D_0 As Double
    Dim ProcessedSubscribers As Double
    
    '//////////////////////////////////////////////////////////////////////
    ' Declare working Variables
    '//////////////////////////////////////////////////////////////////////

    Dim SubscriberID As Variant, SubDetails As Variant
    Dim CdrTime As Double, VAT As Double, CeilingByt .... 

on error goto ErrTap

    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
[b][u]    Application.DisplayScrollBars = False ' without this one added, the app will slow down[/u][/b]





    '//////////////////////////////////////////////////////////////////////
at the end of the module
    '//////////////////////////////////////////////////////////////////////


ErrTap:

Diag.Hide 'Hide Dialog progressbar
Set Diag = Nothing 'Set Diag = Nothing
Application.ScreenUpdating = True
Application.EnableEvents = True
[b][u]Application.DisplayScrollBars = True ' Make it active agian
[/u][/b]Application.StatusBar = False
Application.Calculation = xlCalculationAutomatic
If Err.Number <> 0 Then MsgBox "Error occurred: " & Err.Description, vbCritical, _
    "Error: " & Err.Number
End

JumpToEnd:

Diag.Hide 'Hide Dialog progressbar
Set Diag = Nothing 'Set Diag = Nothing
Application.ScreenUpdating = True
Application.EnableEvents = True
[u][b][i]Application.DisplayScrollBars = True
[/i][/b[/u]]
Application.StatusBar = False
Application.Calculation = xlCalculationAutomatic

End Sub

Open in new window

0
 

Author Comment

by:Chrißt'll Scholiers
ID: 40590802
The fact that the processors take up more also indicates that there are no real bottlenecks present.
Still all code always is waiting for improvement :)

/Chrißt'll
0
 

Author Comment

by:Chrißt'll Scholiers
ID: 40590888
I've requested that this question be closed as follows:

Accepted answer: 0 points for Chrißt'll Scholiers's comment #a40590796

for the following reason:

He/She made me aware about other potential basic processes. He ignited another way of thinking
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40590867
He/She made me aware about other potential basic processes. He ignited another way of thinking
You should reward some points for their effort.
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:Chrißt'll Scholiers
ID: 40590890
done :)
0
 
LVL 80

Expert Comment

by:byundt
ID: 40591601
If you have a series of subs running (e.g. MasterSub calls Sub1, Sub2, Sub3, etc.), make sure that only the first one (MasterSub) turns screen updating off. Your flickering might have been caused by statements within Sub1, Sub2, etc. that set Application.ScreenUpdating = False at the beginning and then Application.ScreenUpdating = True at the end.

Also, comment out any statements that turn screen updating back on. VBA will automatically turn screen updating back on when the last sub finishes running. I used to think it was a good habit to turn screen updating back on programmatically, but experience has taught me that it is better not to do so.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40591659
I normally use a driver routine that disables and enables screen updating.
0
 

Author Comment

by:Chrißt'll Scholiers
ID: 40593117
Thank you, wiil also take this in consideration. Only thing what puzzles me is the fact the  app-speed keeps on going down and down as time progresses, ... , For me it is solved soo far, but still I wonder

/Chrißt'll
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40593483
Try saving the file in xlsb format
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Having just graduated from college and entered the workforce, I don’t find myself always using the tools and programs I grew accustomed to over the past four years. However, there is one program I continually find myself reverting back to…R.   So …
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
The viewer will learn additional member functions of the vector class. Specifically, the capacity and swap member functions will be introduced.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

758 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

21 Experts available now in Live!

Get 1:1 Help Now