How to return column letter of a cell in excel?

Excel is cell based data processing application. Mostly, used for finance and reporting purpose in daily life. It uses various Functions/Formulas to generate output from various measures. Most of the functions in excel make use of cells which is intersection of row and column. Thus, in excel formula requires column and row index as a input which in nothing but cell Address. Many times, we need column letter as a input in calculation.

On other hand, Excel does not have any function which will provide column letter as a result. Therefore. someone needs to write complex formulas to get column letter as a output. And writing complex formula in excel is quite time consuming task. which results into slowdown of excel calculation task and make excel heavy. As a result excel take more time to open the file.

Excel VBA user defined function to remove special characters from text in excel

Many times we need to remove specific character from text string in excel worksheet. We usually perform this activity as a data cleansing process before generating reports/dashboard in reporting tools like Microsoft Excel, Tibco Spotfire, Tableau, Qlikview and so on. Removing specific character from excel cells manually is a quiet time consuming task. Also, there might be a possibility to forget to miss few characters in data which is not adequate.

We can automate this task by creating a user defined function which will be used to delete specific character from the input text. We can customize a function in such a way that it will remove specific character or special character from input text.

How to delete all Pivot tables in active workbook using VBA code?

Pivot table is a very useful and powerful functionality for summarizing or calculating data in Excel, So we may usually insert Pivot tables into a worksheet or multiple worksheets.But, Sometimes we need to delete all pivot tables from active workbook to create brand new  pivot tables which Target new data and design. It will be a very time consuming task to delete all pivot table from workbook which contains large number of pivot table created.

Do you know how to delete all pivot tables in the whole workbook?

Simple utility to Create Speedometer Chart in excel

Speedometer chart is very popular graph which is being used is dashboards. it usually gives a quick context of your performance and provide top level details. It is useful in visualization such as high level reporting, Dashboards and Presentations that are about reporting. They are colorful, easy to understand and easy to enter data and customize based on any situation. Speedometer chart is very powerful tools which enables reporting too much interactive and helpful to understand performance of any product.

Excel VBA code to determine if chart is exists

Many a times it is very difficult to determine if chart is exists. Also it is very time consuming to check all sheets in workbook to check for a specific chart. So, checking charts in project will be a one of the data validation and performing this activity is manually is a quire tedious job. But, we can make this task very simple by using a short code which will help us to check if chart is exists.

By using VBA code in project to validate charts existence is very powerful and accurate method for graphical data representation. Which certainly saves a huge amount of time while data validation.

How to unhide all hidden sheets using VBA code?

Handling multiple sheet simultaneously is too much challenging and complex task for anybody. Considering this point, Excel is powered with hide and unhide functionality which helps end user to make visible required sheets for data manipulation. But, excel has a limitation to unhide multiple sheets at a single go. Thus, it becomes a very tedious and time consuming task to unhide multiple sheets in single attempt.

Can we unhide all hidden excel sheets at single attempt?and the answers is "Yes".

Here is the code which will make this task very simple.

VBA Short code to Break all external links with ease

Whenever we implement formula in excel cells which contains reference to external workbook, excel create a link to refer external workbook. So, it requires all external linked excel workbooks to calculate formula whenever someone open the excel workbook with formula.

Sometimes these external workbooks may be moved or removed from the specified folder. As a result, excel ask for the linked excel workbook which is missing from linked path. this will impact on formula calculation and result into #NA values. Due to large number of workbooks that have links in them and they are getting very large. 

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.

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.

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.

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.

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.

Most Useful Excel VBA Control Structures

Control Structures are most powerful feature of any programming language. Similarly, there are some Control Structures which is commonly used by VBA programming. Usually, these control structures are very useful to save ample amount of time to write large number of line of codes. It helps to facilitate user to work on very complex logic and calculations.

Following is a list of the most commonly used Excel VBA programming control structures. Detailed illustration with few simple example and brief description is given below. Which will be very helpful to know when you would use that structure into your project.

How can I check existence of File, Folder and Sheet in Excel VBA Project?

It very essential in many excel VBA project to check existence of File, Folder and Worksheet for particular excel file. It consumes a lot time to write the functions or macros again and again. I have included all those if exists macros on this article. Thus, you can make use of them in your project to help yourselves to save ample amount of time.

All these, if exists VBA codes are listed as below. To Verify, You can copy and paste mentioned code into standard module in your project or new workbook.

Superb VBA Password recovery using Excel Unlocker Addins 100 % Working S...

Password recovery is very challenging task for excel VBA project. Most of the times, we forgot password of excel VBA project which we developed at past. Thus, it is very difficult to made changes in existing VBA project which we using by couple of years. Without knowing password of such a projects we can not made any changes to our existing automation solution.

Awesome VBA code to make excel talk !!!

Text to Speech is built into MS Office products from version XP on-wards. There are options to make Excel read cells.

Certainly, MS Excel can talk. MS Excel text to speech speaks in computer voice, which is close to a female voice.

Superb code to list all files in a folder by excel VBA?

Importing List of file into excel worksheet is very popular task in several excel automation.
Such a list of files will empower user to work on file listed in worksheet. User can perform various calculation on these listed files as per requirement.

Can I execute batch file by excel VBA ?

Creating and executing batch file is very useful way for project automation. It is very comprehensive to create complex automation. you can use batch file with excel VBA combination for several operations.

How to get system info using excel vba

In some situations user need to know system information to automate their excel VBA automation. So, the users comes with below question.

Can I get System Info by Excel VBA ?

and the answer for this is "Yes"

How to show VBA code execution status information on excel statusbar

There are several situations where we need to know execution status of macro code. we can show status of macro execution in excel status bar. Below block of code will help you to do this.