How to improve the runtime performance of Excel Application - VBA

Welcome to Logically Proven blog.

This post demonstrates how to improve the runtime performance of Excel VBA (Visual Basic for Programming).

We all do programming but what makes the difference between good and bad programming. They are many which differentiates good and bad programming but one among them is "How fast your code is executing the given task".
 
Consider a scenario - you have to implement a macro where the functionality is to import the worksheet data from a different workbook.

Please have a look at the picture. when user clicks on  "Import Data" button, the workbook imports the data from the user browsed macro-enabled excel file "D:\PerformanceTest\TrainingPlanner.xlsm".

It is a very simple task and I am sure that almost everyone had worked with this scenario.
But while the code is running background ask these questions to yourself?

1. Is it important to update the screen?
2. Is it required to calculate the complete workbook after every cell modification?
3. Is it required to raise events when you modify a cell?
4. Is it necessary to run the events (workbook open and close) when you open a macro-enabled workbook?
5. Is it required to show alerts or warnings to the user?

If your answer is No. Then add the following sub routine in your code before start of your code.

'Method Overview
'***********************************************************************
'name:      App_state
'params:    Boolean, false - to disable
'                    true - to enable
'returns:   none
'comments:  To speed the performance, disabling events, screenupdating,
'           automatic calculation etc.,
'***********************************************************************

Sub App_state(pblnEnable As Boolean)

    With Application
        'To speed up process, turn off calculation
        .Calculation = IIf(pblnEnable, xlCalculationAutomatic, xlCalculationManual)
        .StatusBar = IIf(pblnEnable, "", "Please wait...")
        .Cursor = IIf(pblnEnable, xlDefault, xlWait)
        .ScreenUpdating = pblnEnable
        .EnableEvents = pblnEnable
        .DisplayAlerts = pblnEnable
    End With
End Sub

What actually this sub routine is doing  -

.Calculation - Setting automatic calculation to OFF before start and setting it to ON at the end. This will avoid unnecessary calculations. Calculate a particular sheet or a range only when required.

.Cursor - Setting the cursor mode to wait. This will avoid the application to register the unnecessary clicks on the workbook when the code is running background.

.StatusBar -In the previous step, the cursor mode is in wait mode. So the user may think the application is got stopped. So we are setting the status bar to please wait. So the user know that the application is still runnung.

.ScreenUpdating - It saves a lot of time in terms of  performance. Consider you are opening a new workbook inside the code, in certain cases it is not required to show the workbook to the user. In another case you are doing a lot of modifications in your workbook, you don't want to show all screen updates to the user (jumping from one sheet to another sheet, scrolling through the worksheet). In this scenarios disable the screen updates.

.EnableEvents - Disable the application events if you don't required. For example you are modifying a cell, the application raises a worksheet change event every-time you modify the cell value. By disabling this you can improve the performance time.

.DisplayAlerts - Disable this if you want to hide the unwanted alerts. For example you are deleting a sheet or rows or columns, then the application alerts you "Do you want to delete?". If you want to ignore such alerts disable alerts which improves the performance of your application.

Usage:

Sub ImportData()
    
    Dim wkb As Workbook
    Dim wkbInput As Workbook
    Dim wks As Worksheet
    Dim wksInput As Worksheet
    
    'error handler
    On Error GoTo err_handler
    
    'Improve the performance
    App_state False
    
    'Do your work
    
    'In case of message box, enable screen updates
    Application.ScreenUpdating = True
    MsgBox "Import is successful", vbOKOnly, "Import"
    Application.ScreenUpdating = False
    
    'Before exit
    App_state True
    
    'release the objects
    
    Exit Sub

err_handler:
     App_state True
     MsgBox Err.Description, vbOKOnly, "Error"
     'release the objects
Exit Sub

Now you got an idea how to improve the performance of your application. But there are some things which you need to understand -

• Don't forget to set App_state to true before closing the workbook or in case of error handler or before exiting the sub routine or function.
• Set only screen updating to true if you are popping up a message box to the user. And set back to false once the user interacted with the message box. If you don't do this, dragging the message box creates cluttering on the monitor. Test it then you will understand what actually it does.
• In case of disable events, the sheet events (e.g. worksheet change) will not be triggered. Decide whether you want to disable events or not with respect to context.
• In case of disable events, if you are opening a macro -enabled workbook through your code, it will not trigger the workbook open and close events. If you want to trigger this events set false to disable events.

Please write your comments if you find anything is incorrect or do you want to share more information about the topic discussed above.


Logically Proven
Learn, Teach, Share

Read More

How to read an entire line from input in C++

Welcome to Logically Proven blog.
This post demonstrates how to read an entire line from input in C++.

 The following example can read only a single word. The white-space terminates the input.

#include<iostream>
#include<string>
using namespace std;

int main()
{
 cout << "Please enter:\n";

 string s;
 cin>>s;
 
 cout << "You entered " << s << '\n';
}

/*Output: Please enter:
This program takes only a single word
You entered This
*/

Note that there is no explicit memory management and no fixed-size buffer that you could possible to overflow.

The following program reads an entire line.

#include<iostream>
#include<string>
using namespace std;

int main()
{
 cout << "Please enter:\n";

 string s;
 getline(cin,s);
 
 cout << "You entered " << s << '\n';
}

/* output: Please enter:
This program reads an entire line.
You entered This program reads an entire line.
*/

Please write your comments if you find anything is incorrect or do you want to share more information about the topic discussed above.

Logically Proven
Learn, Teach, Share

Read More

Convert an integer (decimal) to string in C++

Welcome to Logically Proven blog.
This post demonstrates how to convert an integer to a string in C++.

There are two ways to achieve this functionality.

1) using stringstream
2)using to_string function (C++ 11)

Using stringstream: (better if you are not using latest version)

#include<iostream>
#include<string>
#include<sstream>
using namespace std;

 string itos(int i) // convert int to string
 {
  stringstream s;
  s << i;
  return s.str();
 }

 int main()
 {
  int i = 128;
  string ss= itos(i);
  const char* p = ss.c_str();

  cout << ss << " " << p << "\n";
 }

This technique works for converting any type that you can output using <<.

Using to_string function:

Converts a numerical value to std::string

The following functions are available in string library since C++ 11 version to convert numeric to string .

std::string to_string(int value); //converts a signed decimal to string
std::string to_string(long value); //converts a signed long decimal to string
std::string to_string(long long value); //converts a signed long long decimal to string
std::string to_string(unsigned value); //converts an unsigned decimal to string
std::string to_string(unsigned long value); //converts an unsigned long decimal to string
std::string to_string(unsigned long long value); //converts an unsigned long long decimal to string
std::string to_string(float value); //converts a float value to string
std::string to_string(double value); //converts double float value to string
std::string to_string(long double value); //converts long double float value to string

These functions takes 'value' as a parameter and returns a string equivalent.

Example:

#include <iostream>
#include <string>
 
int main() 
{
    double dVal = 28.28;
    std::string d_str = std::to_string(dVal);
    std::cout << d_str << '\n';
}

//output: 28.280000

You may run into some errors in the second case if your compiler doesn't support. The error message is
"to_string is not a member of std".  In this case please follow this link how to fix -
http://stackoverflow.com/questions/12975341/to-string-is-not-a-member-of-std-says-so-g

Please write your comments if you find anything is incorrect or do you want to share more information about the topic discussed above.

Logically Proven
Learn, Teach, Share

Read More

Pure virtual function in C++

Welcome to Logically Proven blog.

This post demonstrates "Pure Virtual Function" in C++.

A pure virtual function is a function that must be overridden in a derived class and need not to be defined.
A virtual function is declared to be "pure" using the curious "=0" syntax.

For example:

class Base {
 public:
  void f1();  // not virtual
  virtual void f2(); // virtual, not pure
  virtual void f3() = 0; // pure virtual
 };

 Base b; // error: pure virtual f3 not overridden

If a class is having any pure virtual functions, then the class is called "abstract class". Thus Base is an abstract class. So no objects are created directly for the class Base.

class Derived : public Base {
  // no f1: fine
  // no f2: fine, we inherit Base::f2
  void f3();
 };

 Derived d; // ok: Derived::f3 overrides Base::f3

Abstract classes are very useful for defining interfaces. Interface contains only method declarations. The classes which extends interface must contain method implementations. In fact, a class with only pure virtual functions is often called an interface.

If you don't override a pure virtual function in a derived class, that derived class becomes abstract.

class D2 : public Base {
  // no f1: fine
  // no f2: fine, we inherit Base::f2
  // no f3: fine, but D2 is therefore still abstract
 };

 D2 d; // error: pure virtual Base::f3 not overridden

Here D2 doesn't contain method implementation for function f3. So D2 is also an abstract class and no object is created. So in this context, classes Base and D2 are abstract classes.

In the below example class D3 is derived from the class D2 and contain implementation for the function f3. So we can create object successfully for the derived class D3.

class D3 : public D2 {
  // no f1: fine
  // no f2: fine, we inherit D2::f2
  void f3();
 };

 D3 d; // ok: D3::f3 overrides D2::f3

Please write your comments if you find anything is incorrect or do you want to share more information about the topic discussed above.

Logically Proven,
Learn, Teach, Share

Read More