The following is a summary of the steps we need to accomplish to perform this task:
- Write a macro that will loop through each cell in the used range of a sheet. We don’t want to loop through all 17 billion cells of the sheet; only the used cells.
- Set up a counter to keep track of the number of cells containing formulas. As we examine each cell in the used range, if the cell contains a formula we will add one to our counter.
- Once the examination is complete, display the result stored in the counter in cell B6 of the current worksheet.
- Create a macro button and assign the macro to the button. When the user clicks the button, the macro will execute and the value in cell B6 will be updated.
Featured Course
Unlock Excel VBA & Excel Macros
Writing the Macro
Open the Visual Basic Editor (Alt-F11 or Developer (tab) -> Code (group) -> Visual Basic).
Create a new module sheet called “ProjectActivity” by right-clicking on any item in the current workbook list of the Project Explorer and selecting Insert -> Module.
In the Properties windows, change the name of the new module sheet from Module1 to ProjectActivity.
In the code window we will create a new subprocedure named Count_If_Formula.
To loop through each cell, we will utilize a collection in conjunction with an IF…Then construct. The collection is the used range, and we need to declare a variable to hold a tiny part of the collection during the examination process. In this case, each tiny part is a single cell.
We need to establish a variable to hold the single cell during examination.
Now we will create the loop instruction to loop through each cell in the used range. Because “UsedRange” is a property of a worksheet, we can refer to the active sheet’s used range. We will go ahead and close the logic of the loop with the “Next cell” instruction.
Testing for the Presence of a Formula
Inside of the For…Next structure, let’s create a test to determine if the currently examined cell contains a formula. We will use an IF statement to compare the cell’s contents to the range property HasFormula.
The HasFormula property returns a “True” if the cell contains a formula.
Don’t forget to close the logic of the If…Then with an End If statement.
If the test of the cell returns a “True”, we need to keep record of that occurrence. We need to create a variable to store that occurrence. Then next time we encounter a formula, we will increment that counter by 1.
Create a new variable called “CountFormula” as a Long data type to store this value. The Long data type can hold a value in excess of 9 quadrillion, well in excess of the maximum 17 billion cells on a single sheet.
Accounting for the Presence of a Formula
If the examined cell possesses a formula, we will add 1 to the counter named “CountFormula”. This is performed by the following line of code.
When the macro begins, the value of the variable “CountFormula” is automatically set to 0 (zero). As we loop though each cell in the used range testing for the presence of a formula, if the cell contains a formula, we add 1 (one) to the variable. This overwrites the current value with the updated value.
Displaying the Final Value to the User
We want to show the user the result of the cell examinations. We want to display the value stored in the “CountFormula” variable in cell B3 of the current sheet. The following line of code will perform this action.
Adding a Macro Launch Button
Returning to the Excel workbook, we will add a macro launch button and assign the newly created macro to the button.
From the ribbon, select Developer (tab) -> Controls (group) -> Insert -> Form Controls -> Button (upper left option).
Click anywhere on the active sheet.
From the Assign Macro dialog box, select the Count_If_Formula macro from the list of available macros and select OK.
This will create the macro launch button on the sheet.
Rename the button to “Count Formula” by right-clicking on the button and selecting Edit Text.
You may need to resize the button to display the fully renamed text.
Testing the Macro
Click the Count Formula button and observe the value in cell B6. The current value is 2, since we have formulas in cells B8 and B9.
Add a formula to cell B10 (any formula will do) and execute the macro. The value of cell B6 has been updated to 3.
NOTE: In this case, since we are putting the button on the same sheet we are examining we don’t need to reference the sheet name. We can use “ActiveSheet” and directly use range and not have to specify the sheet. The assumption is that the sheet being examined is the active sheet. If you were going to put your button on a different sheet and you wanted it to count the formulas on this specific sheet, you need to reference the worksheet names.
The Completed Code
Sub Count_IF_Formula()
Dim cell As Range
Dim CountFormula As Long
For Each cell In ActiveSheet.UsedRange
If cell.HasFormula Then
CountFormula = CountFormula + 1
End If
Next cell
Range("B6").Value = CountFormula
End Sub
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
