Excel macro to list all comments to a new worksheet in workbook!!!

Comment in excel is a way of annotate calculation logic of certain measure or KPI. It will be very useful to transfer data manipulation logic user to user. As a result, there is extension use of comments take place in a workbook. It will be very difficult to check each and every comments if comments scattered in different ranges in the workbook. Thus, it will be a quite problematic task to gather information of all the comments on the singe place.

Continue Reading...

Excel VBA macro to delete specific name ranges in a workbook!!!

Excel application works on concept of cell which is nothing but intersection of row and column. However a single cell is called as a range which is a most important member of excel object hierarchy. Various formulae in excel application will work on the ranges to get final result. Ranges can be named to an object know as Named Range which will be further used in macro code to perform certain data manipulation.

Continue Reading...

Excel VBA macro to list all name ranges in a workbook!!!

Name range is one the useful feature to provide name to a cell or group of cell in excel worksheet. Many a times, user needs multiple named ranges in excel workbook. Then it becomes very difficult to list down all the names ranges created in each worksheet. We can automate this task and list down all named ranges from excel workbook.

Continue Reading...

Excel macro to list folder and total size!!!

Sometimes, we need list of system folder and their size in MB. This is not a regular task, but it takes a lot of manual effort when such kind of requirement occurs. Thus, it's need a automation which will give us list of all subfolders and their size. Fortunately, this task can be automated using a small excel macro which will provide list of all subfolder and their size.

Continue Reading...

How to call a macro from another workbook?

Executing macro from same workbook is a routine task in Excel automation. Many a times, We face a situation where we need to a call macro from another workbook. Sometimes, it's very useful to call a macro from another workbook which will help us optimize macro code. Also, it is very easy to modify the code in individual macro of separate workbook.

Continue Reading...

VBA code to delete multiple sheets at once!!!

Most of the time we face a situation where we need to delete multiple sheets for a workbook. it become a monotonous job when there are large number of sheets which should be deleted. Sometime, because of repetitive task user accidentally deletes useful worksheets. As a result, it consumes lot of productive time which is not a worth in reporting project.

Continue Reading...

VBA code to refresh pivot table when source data update in Excel?

Pivot table is one the most useful visualization is excel reporting projects. Its greatest strength is its ability to structure, summarize and display large amounts of data. Pivot tables can also be used to determine whether there is a relation between the row variable and the column variable or not.

Continue Reading...

Excel macro to validate if input file is excel workbook?

Input data validation is one of the most important activity in report generation process. Which will help user to avoid run time issues while performing report generation tasks. Thus, it is very vital to have input in proper format. To identify file could be a one of validation activity while automation the project.

Continue Reading...

How to select folder using excel VBA code?

Many a times we need to select folder to get input data path during project automation. And it is a first activity before macro execution. Selecting folder using dialogue box is very vital task any automation activity. It helps as a input data validation to use correct input data in further execution. Also, it helps to achieve data quality in report automation task.

Continue Reading...

VBA code to delete multiple picture from worksheet !!!

Use picture and images is very good practice to make excel projects more interactive, Sometimes, there is a need to delete pictures from the workbook. It will be quite tedious task to select each picture and delete it manually..

Continue Reading...

How can we send email with attachment using VBA in excel?

Sending email is a routine task for all kind of industry. it may be reporting, marketing, data science, communication, retail industry and so on. It will become a tedious job when same kind of email needs to be sent over multiple stakeholders.

Continue Reading...

How to use SQL queries in excel macros?

SQL queries are better way to handle large amount of data in database applications. Also, it generates result within a second. As, we aware the SQL queries only can be used in Microsoft Access and Microsoft SQL server applications.

Continue Reading...

Excel macro to create new folder at runtime!!!

Maintain folder structure is a vital task in automation projects. Sometimes, it is very essential to delete and create folders at runtime. To achieve this task user needs to add a code which will be create folder at runtime.

Continue Reading...

Excel macro to delete a excel workbook from selected folder?

Delete specific workbook or excel file is a usual task in many reporting project. As a routine activity user needs to delete old report and create new one based on latest data. Performing delete activity seems to be very risky when it is a manual, specifically when folder contain several report with slightly change in names.

Continue Reading...

VBA code to switch between tabs on the ribbon in MS Word and Excel !!!

Switch between various tabs is one the very useful activity for certain VBA automation. This is different for excel and word VBA programming. I got trick to navigate tabs on MS-Word or MS-Excel ribbon programmatically.

Continue Reading...

VBA code to delete unused items and refresh pivot table in active workbook

Pivot in excel is most useful functionality to aggregate and summarize huge data. It is one the most useful reporting tool in excel which used to represent meaningful data from a large data set. Many a times back end data get refreshed for these pivots. This generates missing items in pivot table which not require anymore. Removing missing items from various pivots sometime would be a time consuming task.

Continue Reading...

Excel macro to check the size of each worksheet of workbook?

Excel is highly recommended reporting tool which used in diverse industry for reporting purpose. Though, it is very useful by it's various reporting functionality. It has some limitation which will be taken into consideration while generation reporting tools. Workbook size is one of the limitation which slow down performance of reports which contains huge data. There could be various causes which impact on file size. In fact, to overcome this file size limitation, we identify the sheet which is result in increase in file size and optimize it to decrease overall size on disk.

Continue Reading...

Excel macro to save bulk email attachments from outlook to a specific folder on PC?

Outlook is one the best email processing application used by user on daily basis. Sometimes, it is used for bulk data transfer medium. users sending bulk emails consist of lots of attached file of various extension such as excel (Microsoft Excel), word (Microsoft Word), PDF (Adobe), Text files, Images (Photoshop file) and so on. Then, it becomes very tedious job to handle large number of attachment in Microsoft Outlook. Saving bulk attachment into specific folder takes lot of productive time which definitely impact on other activities.

Continue Reading...

Excel VBA function 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.

Continue Reading...

Excel VBA function to remove special characters from text in excel

Outlook 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 Spotifire, 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.

Continue Reading...

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.

Continue Reading...

Excel Add-ins 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 provides 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.

Continue Reading...

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.

Continue Reading...

How to unhide all hidden sheets using VBA code?

Handling multiple sheets 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.

Continue Reading...

Excel macro to Break all external links from workbook

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.

Continue Reading...

Excel macro to convert numbers into text for selected colunms in worksheet

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 use 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.

Continue Reading...

Excel macro which will help to apply IFERROR formula to the selected cell in Excel

Error handling is very important task in excel reporting. It is extensively used in excel dashboard designing projects. There are several situations 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.

Continue Reading...

VBA code 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 situations 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.

Continue Reading...

3 Best ways to remove spaces or special characters from text values from cells in worksheet

Removing spaces or special character from text values are 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.

Continue Reading...

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 will be very useful to save couple of hours.

Continue Reading...

How to implement Error Handling in VBA projects?

Error handling implementation is one of the best practices 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 if there is any unexpected exceptions occur during execution then 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. Thus, we should ensure about proper termination of code execution.

Continue Reading...

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. Macro execution time is 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 project complexity can be calculated.

Continue Reading...

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.

Continue Reading...

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.

Continue Reading...

Excel VBA Password recovery using Excel Unlocker Addins 100% Working!!!

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 VBA projects, we cannot make any changes to our existing automation solution.

Continue Reading...

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.

Continue Reading...

Excel macro to list all files in a folder!!!

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 calculations on these listed files as per requirement.

Continue Reading...

Excel macro which execute batch file!!!

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.

Continue Reading...

Excel macro which retrieve system infomation!!!

Importing In some situations user need to know system information to automate their excel VBA automation. So, the user comes with below question. Can I get System Info by Excel Macro? And the answer for this is "Yes"

Continue Reading...

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.

Continue Reading...

SHARE THIS PAGE!

VBA Tricks and Tips Android App

VBA Tricks and Tips Android App