The COUNTA Function – Use It In Your Own VBA Code!
Contents
In this post, we will review how to use the COUNTA Function in your VBA code.
Excel allows you to use many of the built-in worksheet functions in VBA, which are on the syllabus of our Excel VBA courses.
This means that you don’t have to write the code for the entire function yourself and can instead access worksheet functions that already exist in Excel.
The Excel COUNTA Function
A quick reminder:
The COUNT Function counts all the values in a range that are numbers.
The COUNTA Function however, counts the number of cells that are not blank in a range. This includes numbers, text, Boolean values, errors and empty text strings (“”).
The syntax of the COUNTA Function is shown below.
- Where value1 is required and represents the first argument. This is the range or cell reference that you would like to count the cells that are not empty
- Where value2 is optional. This is the range or cell reference that you would like to count the cells that are not empty
The function can take a maximum of 255 arguments.
Both the COUNT and the COUNTA Function returns a numeric value.
So, let’s see how to use the COUNTA Function.
We have the following data set shown below. In cell B10, we’d like to use the COUNTA Function to count the number of cells that are not empty in range B2:B9.
To do this enter the following formula in cell B10.
=COUNTA(B2:B9)
Press Enter and you should see the following.
There are 6 cells that are not empty.
If you would like to learn more, about another useful counting function then please visit our COUNTIF Function post.
Getting Started with VBA
We have already covered some of the basics that will assist you, as you embark on your VBA learning journey, in previous tutorials.
- In our Introduction to Macros tutorial, we go over how to add the Developer Tab to the Ribbon. You will learn all about how to create simple macros in Excel. In addition, you will learn how to assign shortcut keys to macros and how to assign macros to buttons.
- Our Introduction to VBA tutorial discusses the VBA object model in Excel, and reviews what objects, properties and methods are. You will also learn about how to access the Visual Basic Editor. Furthermore, you will discover how to change the properties of objects.
- In our Mastering VBA Special Cells In Excel tutorial, we go over the Special Cells method in VBA, as well as the Activate, Select and Copy methods. This post will help you to get comfortable with using methods in your code. We also introduce you to Error Handling.
How to Use the Excel COUNTA Function in VBA – Simple Example
You can access many of the standard Excel worksheet functions in your VBA code.
The only worksheet functions that are not available for use in VBA, are those that already have a built-in VBA equivalent or a VBA operator.
We have the data set shown below.
We would like to use the COUNTA Function in our VBA code, to return all the cells in range B2:B12 that are not empty. This number should be returned in cell B13.
Understanding Variables
The first thing we have to start thinking about, is the concept of variables and how to utilise them. Variables are utilised in most programming languages, including VBA.
A variable can be regarded as a container. A variable is used to store a value that is allocated a place in your computer’s memory.
It is good practice to declare the variables that you intend to use in your code.
The way one usually does this in VBA, is with a Dim statement. This involves declaring a name for your variable and a type.
Data Types
The common data types that you will utilise in your code, when getting started, are given below.
In Excel VBA, you can declare a variable as a Range object. The range object represents a cell or multiple cells.
Step 1:
So, to use the COUNTA Function in VBA code, the first thing we need to do is access the Visual Basic Editor. To do this press ALT-F11 on your keyboard.
Go to the Insert Tab and choose Module.
Step 2:
Enter the following code.
Sub UsingCOUNTAInVBA()
‘Declaring the variable named inputRange as a Range object
Dim inputRange As Range
‘Using the Set keyword to assign an object reference to the variable inputRange
Set inputRange = Range(“B2:B12”)
‘Specifying that cell B13 will output the number of non-blank cells in range B2:B12 (our inputRange),
‘by utilising the COUNTA method of the WorksheetFunction object
Range(“B13”) = Application.WorksheetFunction.CountA(inputRange)
End Sub
Now let’s look at the code in greater detail.
We start off by using the Dim statement to declare the name of the variable which is inputRange in this case, and the type.
We then use the Set statement to assign an object reference to the variable, we declared previously.
In the last part of the code, we are using the WorksheetFunction object in order to utilise the COUNTA method of this object. So, this is how one accesses an Excel worksheet function in VBA.
When looking at it from the VBA perspective, one must remember that the WorksheetFunction is an object and COUNTA is a method of that object.
Step 3:
Now to run the code, place the cursor in your sub procedure. Press F5 on your keyboard.
You should see the following.
How to Use the Excel COUNTA Function in VBA – More Complex Example
We will now look at a more complex example involving the COUNTA Function in VBA code. The data set is shown below.
We would like to have a button on the worksheet.
When the user clicks on the button then a message box will appear telling them how many cells in the specified range contains data.
However, if the values are cleared, then when the user clicks on the button then a message box will appear stating that no cells in the specified range contain data.
Step 1:
The first step involves adding a button to the worksheet. To do this go to the Developer Tab (Step 1 in the image) and in the Controls Group click on the Insert drop down arrow (Step 2 in the image).
In the ActiveX Controls section, select the Command button.
Drag to create a button on the sheet.
Step 2:
Ensuring that Design Mode is activated, click on the button and with the button selected go to the Developer Tab and in the Controls Group, select Properties.
Using the Properties Dialog Box change the:
-
- Name of the button to cmdCountNonBlankCells
- Caption to Click to see the number of cells that contain data in the range
- WordWrap option to True
Close the Properties Dialog Box.
Step 3:
Right-click the button and select View Code.
This will take us to the button click event, where we will specify what happens when the button is clicked.
Step 4:
In the sub procedure enter the following code.
‘Declaring the variable named theinputRange as a Range object
Dim theinputRange As Range
‘Using the Set keyword to assign an object reference to the variable theinputRange
Set theinputRange = Range(“C2:C8”)
‘We are declaring a variable called theResult which will be a whole number
Dim theResult As Integer
‘We are saying, by using the WorksheetFunction object and the COUNTA method that theResult variable should be
‘the count of all the non-blank cells i.e the cells with data in the range C2:C8
theResult = Application.WorksheetFunction.CountA(theinputRange)
‘We are now using conditional logic with our If Statement
‘This line states the first condition if theResult is greater than zero
If theResult > 0 Then
‘If theResult is greater than zero then we want a message box to appear that
‘gives the theResult i.e the number of cells that have data in the specified range
MsgBox “We have data for” & ” ” & theResult & ” ” & “cell(s) in the range”
‘This line states the alternative condition if theResult is zero
ElseIf theResult = 0 Then
‘If theResult is zero, then we want a message box to appear that
‘states that there is no data for any of the cells in the specified range and
‘that the range should be populated
MsgBox “We do not have data for any of the cells in the range. Please populate the range with data.”
End If
Step 5:
Now return to the worksheet and make sure Design Mode is not activated and click on the button and the following message will appear.
Click Ok.
Now let’s see what happens if there is no data in cell range C2:C8.
So, we can remove the data from the cells, and then click on the button again.
Troubleshooting
You may encounter instances where your code is not working the way you expected it to and an error is generated.
If we look at our first COUNTA example in VBA above, and leave out the Set statement then we will get the following error.
Now this is not a very user-friendly, or informative error message. Instead, when the error is generated in this case, we want a message box that explains what it is likely due to and how to fix it.
So, to do this we will use the following code.
Sub UsingCOUNTAInVBA()
‘We are saying that if an error occurs then go the ErrorHandler Label
On Error GoTo ErrorHandler
‘Declaring the variable named inputRange as a Range object
Dim inputRange As Range
‘Specifying that cell B13 will output the number of non-blank cells in range B2:B12 (our inputRange),
‘by utilising the COUNTA method of the WorksheetFunction object
Range(“B13”) = Application.WorksheetFunction.CountA(inputRange)
Exit Sub
‘This part of the code says that if there is an error a message box, should appear that
‘states what the error is likely due to and how to fix it
ErrorHandler:
MsgBox “A line of code is missing, please ensure that you did not forget the Set statement in your actual VBA code”
Exit Sub
End Sub
When we run the sub procedure, this time we will see the following message.
Learning Objectives
You now know how to:
- Use the COUNTA Function in your VBA code
- Access Excel worksheet functions in VBA
- Declare a variable in VBA
- Use a Message Box
Additionally, you have an understanding of:
- What a variable is
- Some of the common VBA data types
Conclusion
Excel has more than 400 built-in worksheet functions. It is a great time saver to simply access these worksheet functions in your VBA code instead of having to write the function yourself.
Knowledge of how to use the COUNTA Function in VBA, is very useful for advanced Excel users to know about.
Special thank you to Taryn Nefdt for collaborating on this article!
