Hello Excellers and welcome back to another #macromonday. Creating your own Excel VBA function can be very useful. Don’t get me wrong most of our calculations needs can be achieved with what is provided already in Excel. But it isn’t long before you find yourself wishing that there was a function that did a particular job. You search and it is not on the function list. You need a UDF. (User Defined Function). We can achieve this with some simple VBA code. Then save it as an Excel Add-in. In this Excel VBA tip, we will create a new Excel Function which will count coloured cells in Excel.
Now, these UDF’s can be easy to the most complex, but the theory is the same, you want Excel to do a calculation to return a single value back. Today we are going to create our own UDF to count cells by their fill colour. We then will save this as an Add-In. It can then be used in ANY Excel workbook. But before we get into some cool Excel VBA code, let’s take a step back and look at what the heck a UDF is.
UDF Step Back.
When we talk about VBA Macros in Excel we normally or at least most of the time are talking about Sub procedures. Most of the macros that we record or write in VBA are Sub procedures. Sub is basically a command. So, by executing a Sub procedure and what happens absolutely depends on the code that you write as part of that Sub procedure.
But, we also have a Function which is also a procedure, but quite different from its brother or sister the Sub. You are probably already aware of the concept of an Excel Function. For example a VLOOKUP, SUM, INDEX, MIN, MAX. These are all Functions that usually take at least one argument and do some calculations in the background to return a single value. That is a Function.
So, just to clarify the difference between a Sub Procedure and a Function.
SUB v FUNCTIONS
⇒A Sub procedure is a group of VBA statements that will perform a single or group actions in Excel.
⇒A Function procedure is a group of VBA statements that perform a calculation and like the definition of an Excel Function will return a single value.
- If you want to browse through all of the Macro Monday series of blog posts you can do so at the link below.
Every Sub procedure begins with the work Sub, so, it makes sense that function begins with the Function.
Prepare To Write Your UDF
Open the Visual Basic Editor. You can do this either by hitting ALT+F11 or hit the Developer Tab | Code Group | Visual Basic.
Insert a new Module to store your VBA function code.
Select the Insert Menu | Select Module to create a New Module.
How Does The Macro Work?
Start the code by typing Function then type the name of your UDF. I have chosen to name it CellByColour. We also need to set out the arguments that the Function will take. Notice that Excel automatically put the End Function at the end of the code for us.
range_date is the first argument in the Function as is the range of cells we are checking for colour.
criteria – this is the second argument and the cell with the colour we are checking
[stextbox id=’info’]Function CellByColour(range_data As Range, criteria As Range) As Long[/stextbox]
Declaring Variables
Next, we need to declare 2 variables. This action simply creates a memory container in Excel for them.
[stextbox id=’info’]
Dim datarnge As Range
Dim colourx As Long
[/stextbox]
Checking For Fill Colours
Excel then executes this piece of code that checks the background colour or fill-colour of a cell. Subsequently the colour of our criteria cell is assigned to the variable colourx by the following code.
[stextbox id=’info’]colourx = criteria.Interior.ColorIndex[/stextbox]
Searching For The Coloured Cells
We use the For Next loop to check each cell in the range_data. The code counts the number of cells that match our criteria range. ie count coloured cells.
[stextbox id=’info’]For Each datarnge In range_data
If datarnge.Interior.ColorIndex = colourx Then
CellByColour = CellByColour + 1
End If
Next datarnge[/stextbox]
Finally, Ending The VBA Code
Our code then ends with the small end code
[stextbox id=’info’]End Function[/stextbox]
Ok, we have our code, why don’t we test it. My favourite part of the code.
Let’s Create The Add-In To Count Coloured Cells In Excel.
First Things First…What Is An Excel Add-In?.
An Excel add-in is simply a File with an extension of .xlam or .xll. Files are loaded up when Excel starts up and contain VBA code (.xlam extensions) that add extra functionality. They usually save you time, and or help you avoid errors. While there are Add-Ins that are already available, and there are a lot of Add-Ins that are a lot of third party Add-Ins – a lot of them free to download. Just take a look around. So, just follow the next steps to convert the Macro to reuse as an Add-In
- Click on the File Tab
- Select Show All Properties
- Next, click on the Title and Comments – These are the pieces of information that a user sees and uses when they are loading the Add-In.
- Fill in these fields with something appropriate and informative to the user
- Click Save As and select the file extension .xlam
- The name you choose here will be the name of the Add-In
- The file path automatically is changed, you can keep this location or change it
Install the Add-In
- Open a new workbook
- Developer Tab – Add-Ins – Excel Add-Ins
- Select your Add-In and hit Ok in the dialog box
- The Add-In will now be activated. We can easily now count coloured cells in Excel.
That’s it. A simple piece of VBA can turn into something really useful. We can now count coloured cells in our worksheet. Easy as that. Would you find this useful? Let me know in the commnets below.
Do You Need Help With An Excel Problem?.
At Last, I am pleased to announce I have teamed up with Excel Rescue, where you can get help FAST.
