VBA Counter

Excel VBA Counter

There is the various function in MS Excel to count values, whether it is a string, numbers. Counting can be done based on some criteria. Functions include COUNT, COUNTACOUNTAThe COUNTA function is an inbuilt statistical excel function that counts the number of non-blank cells (not empty) in a cell range or the cell reference. For example, cells A1 and A3 contain values but, cell A2 is empty. The formula “=COUNTA(A1,A2,A3)” returns 2. read more, COUNTBLANK, COUNTIFCOUNTIFThe COUNTIF function in Excel counts the number of cells within a range based on pre-defined criteria. It is used to count cells that include dates, numbers, or text. For example, COUNTIF(A1:A10,”Trump”) will count the number of cells within the range A1:A10 that contain the text “Trump” read more, and COUNTIFS in excelCOUNTIFS In ExcelThe COUNTIFS excel function counts the values of the supplied range based on one or multiple criteria (conditions). The supplied range can be single or multiple and adjacent or non-adjacent. Being a statistical function of Excel, the COUNTIFS supports the usage of comparison operators and wildcard characters. read more. However, these functions cannot do some tasks like counting the cells based on their color, counting only bold values, etc. That is why we will create a counter in VBA so that we can count for these types of tasks in excel.

VBA-Counter

You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: VBA Counter (wallstreetmojo.com)

Let us create some counter in excel VBA.

Examples of Excel VBA Counter

You can download this VBA Counter Excel Template here – VBA Counter Excel Template

Below are examples of the counter in VBA.

Example #1

VBA Counter Example 1

Suppose we have data like above for 32 rows. We will create a VBA counter, which will count the values, which are greater than 50 and one more counter to count the values, which are less than 50. We will create the VBA codeCreate The VBA CodeVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more in this way so that the user can have data for unlimited rows in excel.

To do the same, steps would be:

Make sure the Developer tab Excel is visible. To make the tab visible (if not), steps are:

Click on the ‘File’ tab in the ribbon and choose ‘Option’ from the list.

VBA Counter Example 1-1

Choose ‘Customize Ribbon’ from the list, check the box for ‘Developer,’ and click on OK.

VBA Counter Example 1-2

Now the ‘Developer’ tab is visible.

VBA Counter Example 1-3

Insert the command button using the ‘Insert’ command available in the ‘Controls’ group in the ‘Developer’ tab.

VBA Counter Example 1-4

While pressing the ALT key, create the command button with the mouse. If we keep pressing the ALT key, then the edges of the command button go automatically with the border of the cells.

VBA Counter Example 1-5

Right-click on the command button to open the contextual menu (make sure ‘Design Mode’ is activated; otherwise, we will not be able to open the contextual menu).

VBA Counter Example 1-6

Choose ‘Properties’ from the menu.

VBA Counter Example 1-7

Change the properties of the command button, i.e., Name, Caption, and Font, etc.

VBA Counter Example 1-8

Right-click again and choose the ‘View Code’ from the contextual menu.

VBA Counter Example 1-9

Visual Basic Editor is opened now, and by default, a subroutine is already created for the command button.

VBA Counter Example 1-10

We will write code now. We will declare 3 variables. One for loop purpose, one to count, and one to store the value for the last row.

VBA Counter Example 1-11

We will use the code to select cell A1 and then the current region of cell A1 and then get down to the last filled row to get the last filled row number.

VBA Counter Example 1-12

We will run a ‘for’ loop in VBA to check the values written in the A2 cell to the last filled cell in the A column. We will increase the value of the ‘counter’ variable by 1 if the value is greater than 50 and will change the font color of the cell to ‘Blue,’ and if the value is less than 50, then the font color of the cell would be ‘Red.’

VBA Counter Example 1-13

After checking and counting, we need to display the values. To do the same, we will use VBA MsgBoxVBA MsgBoxVBA MsgBox function is an output function which displays the generalized message provided by the developer. This statement has no arguments and the personalized messages in this function are written under the double quotes while for the values the variable reference is provided.read more.’

VBA Counter Example 1-14

Code:

Private Sub CountingCellsbyValue_Click()

Dim i, counter As Integer
Dim lastrow As Long

lastrow = Range("A1").CurrentRegion.End(xlDown).Row

For i = 2 To lastrow

If Cells(i, 1).Value > 50 Then
counter = counter + 1
Cells(i, 1).Font.ColorIndex = 5
Else
Cells(i, 1).Font.ColorIndex = 3
End If

Next i

MsgBox "There are " & counter & " values which are greater than 50" & _
vbCrLf & "There are " & lastrow - counter & " values which are less than 50"

End Sub

Deactivate the ‘Design Mode’ and click on the ‘Command button.’ The result would be as follows.

vba counter example 1

Example #2

Suppose we want to create the time counter using excel VBA as follows:

If we click on the ‘Start’ button, the timer starts, and if we click on the ‘Stop’ button, the timer stops.

To do the same, steps would be:

Create a format like this in an excel sheet.

VBA Counter Example 2

Change the format of the cell A2 as ‘hh:mm: ss.’

VBA Counter Example 2-1

Merge the cells C3 to G7 by using the Merge and Center ExcelMerge And Center ExcelThe merge and center button is used to merge two or more different cells. When data is inserted into any merged cells, it is in the center position, hence the name merge and center. read more command in the ‘Alignment’ group in the ‘Home’ tab.

VBA Counter Example 2-2

Give the reference of cell A2 for just merged cell and then do the formatting like make the font style to ‘Baskerville,’ font size to 60, etc.

VBA Counter Example 2-3

Create two command buttons, ‘Start’ and ‘Stop’ using the ‘Insert’ command available in the ‘Controls’ group in the ‘Developer’ tab.

VBA Counter Example 2-4

Using the ‘Properties’ command available in the ‘Controls’ group in the ‘Developer’ tab, change the properties.

VBA Counter Example 2-5

Select the commands buttons one by one and choose the ‘View Code’ command from the ‘Controls’ group in the ‘Developer’ tab to write the code as follows.

VBA Counter Example 2-6

Choose from the drop-down the appropriate command button.

VBA Counter Example 2-7
VBA Counter Example 2-8

Insert a module into ThisWorkbookThisWorkbookVBA ThisWorkbook refers to the workbook on which the users currently write the code to execute all of the tasks in the current workbook. In this, it doesn't matter which workbook is active and only requires the reference to the workbook, where the users write the code.read more by right-clicking on the ‘Thisworkbook’ and then choose ‘Insert’ and then ‘Module.’

Example 2-9

Write the following code in the module.

Code:

Sub start_time()
Application.OnTime Now + TimeValue("00:00:01"), "next_moment"
End Sub
Sub end_time()
Application.OnTime Now + TimeValue("00:00:01"), "next_moment", , False
End Sub

Sub next_moment()
If Worksheets("Time Counter").Range("A2").Value = 0 Then Exit Sub
Worksheets("Time Counter").Range("A2").Value = Worksheets("Time Counter").Range("A2").Value - TimeValue("00:00:01")
start_time
End Sub

We have used the ‘onTime‘ method of the Application object, which is used to run a procedure at a scheduled time. The procedure, which we have scheduled to run, is “next_moment.”

Save the code. Write the time in the A2 cell and click on the ‘Start’ button to start the time counter.

vba counter example 2

Example #3

Suppose we have a list of students along with marks scored by them. We want to count the number of students who passed and who failed.

Example 3

To do the same, we will write the VBA code.

Steps would be:

Open Visual Basic editor by pressing shortcut in excelShortcut In ExcelAn Excel shortcut is a technique of performing a manual task in a quicker way.read more Alt+F11 and double click on ‘Sheet3 (Counting Number of students)’ to insert a subroutine based on an event in Sheet3.

Example 3-1

Choose ‘Worksheet’ from the dropdown.

Example 3-2

As we pick ‘Worksheet’ from the list, we can see, there are various events in the adjacent dropdown. We need to choose ‘SelectionChange’ from the list.

Example 3-3
Example 3-4

We will declare the VBA variableDeclare The VBA VariableVariable declaration is necessary in VBA to define a variable for a specific data type so that it can hold values; any variable that is not defined in VBA cannot hold values.read more ‘lastrow’ for storing last row number as a list for students can increase, ‘pass’ to store a number of students who passed, and ‘fail’ to store a number of students who failed.

Example 3-5

We will store the value of the last row number in ‘lastrow.’

Example 3-6

We will create the ‘for’ loop for counting based on condition.

Example 3-7

We have set the condition if the total marks are greater than 99, then add the value 1 to the ‘pass’ variable and add one value to the ‘fail’ variable if the condition fails.

The last statement makes the heading ‘Summary’ bold.

To print the values in the sheet, the code would be:

Example 3-8

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim lastrow As Long
Dim pass As Integer
Dim fail As Integer

lastrow = Range("A1").CurrentRegion.End(xlDown).Row

For i = 2 To lastrow

If Cells(i, 5) > 99 Then
pass = pass + 1
Else
fail = fail + 1
End If
Cells(1, 7).Font.Bold = True

Next i

Range("G1").Value = "Summary"
Range("G2").Value = "The number of students who passed is " & pass
Range("G3").Value = "The number of students who failed is " & fail

End Sub

Now whenever there is a change in selection, values will be calculated again as below:

VBA Counter Example 3-9

Things to Remember

  1. Save the file after writing code in VBA with .xlsm excel extensionExcel ExtensionExcel extensions represent the file format. It helps the user to save different types of excel files in various formats. For instance, .xlsx is used for simple data, and XLSM is used to store the VBA code.read more; otherwise, the macro will not work.
  2. Use the ‘For’ loop when it is decided already for how many times the code in the VBA loopCode In The VBA LoopA VBA loop in excel is an instruction to run a code or repeat an action multiple times.read more will run.

Recommended Articles

This has been a guide to VBA Counter. Here we discuss how we will create a counter in excel VBA so that we can count cells based on their color, counting only bold values, etc. Below you can find some useful excel VBA articles –

  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>