Monday, October 26, 2015

Suberb excel VBA trick to convert numbers into text for selected colunms in sheet

It is very crucial to have certain columns value format as text before it use for data transformation in your project. There may be different causes to change digits stored as numbers to text. it has multiple ways that  you can solve this problem. you can used text formula too. but, to convert values cell by cell takes a lot time and tedious task as well. There might be chances of miss few value to convert into text format.

Saturday, October 24, 2015

Robust Excel VBA code which will help to apply IFERROR to the selected range in Excel

Error handling is very important task in excel reporting. It is extensively used in excel dashboard designing projects. There are several situation in reports where cell formula returns error value. It seems very bad to represent error values in dashboards. Thus, to overcome such a scenario where formula error occurs, we use iferror function in excel to assign some values which will be visible instead of error values in formulate cell.

To apply iferror function in multiple formulated cells in excel is a quite hectic task. It consumes lot of hours to apply iferror function on thousand of cells. But, by using below code this task can be finish in only couple of minutes.

Saturday, October 17, 2015

How to get nth occurrence position of a character in text value in Excel ?

To get nth occurrence position of character is very challenging task by using formula in excel. I observed that there are various situation in reporting project where nth occurrence position of character is used for certain data calculations. In such a scenario, it is very difficult to complex excel formula which returns nth position of character for targeted input text.

To resolve such a requirement where nth occurrence position of character needed, I have create a function which will easily give us n position of specific character in targeted text.

3 Best way to remove spaces or special characters from text values from Excel cells

Removing spaces or special character from text values is very crucial task in excel reporting projects. Because, if text value contains spaces or white character or any special then it results into data mismatch for using functions like Vlookup, Index-Match, sumif and so on.

However, to minimize chances of data mismatch in various such functions which works on text input is to trim text values for spaces or special character which is not required in data. Excel is enriched with few functions which will help us to remove spaces or unwanted special character in text values.

Sunday, October 11, 2015

How to do SaveAs and Change FileFormat by using Excel VBA Code?

Read this article to get detail information to change excel file format. It is very useful requirement in various excel projects. Most of the excel reports uses input from csv files. Several times we need to convert .csv files into .xls or xlsx which is very tedious task. Usually, it consumes lot of hours for file conversion. However, we can simply automate this file conversion task writing excel VBA code. which we save couple of hours.

Wednesday, October 7, 2015

How to implement Error Handling in VBA projects?

Error Handling implementation is one of the best practice to write VBA code. Thus, error handling is very important part of every macro and it ease to handle unexpected exceptions in excel macros. Including error handling code in macro refers to good practice of coding. Because in case of any unexpected exceptions macro code doesn't break. Thus, it is very essential to have error handling in macro to avoid fatal unexpected error occurs in the code. So that we should ensure about proper termination for execution.

How to calculate and show total execution time taken by a VBA code?

It is very important to know total execution time taken by any VBA code. Because, it's very important parameter to optimize performance of the code. Also, it is very useful to determine time complexity of the project. Based on this execution time calculation it very ease to define project complexity.

In order to consider VBA code execution time requirement in the project. I have written few line of codes which will very useful to calculate total execution time taken by any VBA code.