Excel VBA Exercises: 10 Real World Problems

This article will provide you with ten Excel VBA exercises to test your macro solving abilities. One with advanced knowledge of Excel should be able to solve all problems with ease. You need to know the following topics to solve the ten problems: count colored cells, VBA SUMIFS multiple criteria, unhide rows within a range, find the square root, clear the contents of specific cells, save the workbook as a new file, hide rows based on cell value, capitalize the first letter of each word, generate random numbers, and find duplicate rows. Additionally, you should know how to make VBA code faster and enable the developer tab. Excel 2019 or later versions will be enough to solve the problems. However, if you have Excel 2010, you should be able to solve most of the problems.

Download Practice Workbook

You can download the Excel file from the following link.

Problem Overview

There are ten Excel VBA related exercises in this file. The datasets are all different for each of the problems. You should open the Excel file and go through the problem statements. The exercises are provided in the “Problem” sheet, and the solutions to those are in the “Solution” sheet. In addition, this article’s introduction lists the problems’ solutions in order. The first two exercises are shown in the following picture.

• Exercise 01 Count Cells by Fill Color: There are two sets of colors in the dataset. Use VBA to count the number of cells that have the same fill color.

This animated image shows a custom function to count the fill color.

• Exercise 02 Use VBA SUMIFS with Multiple Criteria: Selling price of several products are provided. Your task is to add the price of all mobile or AC.
• Exercise 03 Unhide Rows Within Range: We have hidden two rows in the dataset. Prepare a VBA macro to unhide the rows.
• Exercise 04 Find Square Root of Numbers: Eight numbers are given. You must calculate the square roots of the numbers and then put the results in the cells next to the original values.
• Exercise 05 Clear Cell Contents If It Contains Specific Value: We have provided two columns of values. Prepare a VBA code to clear cell contents if this contains a specific value (in this case 96) is present.
• Exercise 06 Save This Workbook as a New File: Write a VBA code to save the exercise file as a new file.
• Exercise 07 Hide Rows Based on Cell Value: The dataset contains the grades of six students. Create a VBA code to hide the rows that have grade “F”.
• Exercise 08 Capitalized First Letter of Each Word: Use a VBA code to capitalize the first letter of each word in the two rows of the dataset. Additionally, rather than hard coding the cell range, insert the input box to prompt users to select the cell range.
• Exercise 09 Generate Random Number from Range: Type a VBA code to return random numbers that have the lowest value 50, highest value 90.
• Exercise 10 Find Duplicate Rows: Highlight the duplicate rows in a range using VBA.

The following image shows the answers to the first two exercises.

Conclusion

Thank you for reading this article. By completing these practice exercises, we hope that you have gained knowledge about Excel VBA. You can find more articles similar to this on the ExcelDemy website. If you have any questions or suggestions, feel free to comment below. However, remember that our website implements comment moderation. Therefore, your comment may not be instantly visible. So, have a little bit of patience, and we will solve your query as soon as possible. Keep excelling!