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