Excel VBA Tutorial – #10 Using built-in Excel functions
Last updated on February 1, 2019 By Victor Chan
Summary: Wouldn’t it be great to run your favorite Excel functions with macros? Like SUM, COUNTIF, VLOOKUP and SUMIF…
In this tutorial, I’ll walk you through code that will allow you to do just that. After we’re finished, you’ll have flexibility you could never achieve with formulas alone!
Difficulty: Intermediate
Download the Sample Workbook
Download the sample file with VBA code
Excel-VBA-Built-In-Functions.xlsm (119 Kb)
#1 – The most often used Excel functions
If we’re honest, we use only a handful of Excel functions daily. But these functions form the foundation for an Excel power-user.
SUM, SUMIF, VLOOKUP, and COUNTIF are a few of my go-to functions. Without these functions, my office life would be much more difficult. These functions are fast, reliable, and most of my tasks can be completed using only them.
When we use such functions in worksheets, they all require us to input ranges of cells where they will read or write data. More robust functions require even more complex criteria to work properly.
You may wonder why I even mention the inputs required to use basic Excel functions. Since the functions won’t work without the right inputs, when using functions in macros we’ll need to provide the inputs via VBA code.
Also, in our code we’ll need to declare a range type variable to represent each input range. We’ll also need to declare a variable to represent every other required input value for the function(s) we’re using.
Now that we’ve reviewed the basics, let’s start coding!
Variables needed
Considering all the information the functions need, here’s how we’ll declare the necessary variables:
Public rng As Range 'To store the function's range
Public SumRng As Range 'To store the range of cells to be summed
Public criteria As Variant 'To store other function criteria
Public ResultValue As Long 'To store the function's result
#2 – SUM
I’ll start with the SUM function, as it’s the simplest of the ones we’re going to discuss.
I copied the payroll calculating macro from the previous tutorial. We can use its table to experiment with these Excel functions. As you can see, MaxSalary is 1200, and StateTax is set to 0.2.
Let’s say we want to SUM the salary in column “B”. We will store the result in the “ResultValue” variable.
This is how the code should look:
Sub SumFunction()
ResultValue = Application.Sum(Range(Cells(2, 2), Cells(6, 2)))
MsgBox ResultValue
End Sub
The third line displays the result in a Msgbox. We could also write the result in a cell – it’s your responsibility, as the programmer, to display the result in the best location.
If you want to put your knowledge to the test, feel free to modify the code as follows:
- Find the last row in column “B”
- Modify the macro to write the result of the SUM function in the cell below the last row in column “B”
I’m confident you can make these changes with the help of the previous tutorials! Use the power of your knowledge to customize the code the way you want!
#3 – COUNTIF
Using COUNTIF is a bit more complicated, but it’s nothing we can’t solve with VBA!
We’ll use our previously declared variable “rng” for the range. The variable “ResultValue” will again store the result.
Sub CountIfFunction()
Set rng = Range("A1:D6")
ResultValue = Application.WorksheetFunction.CountIf(rng, "960")
MsgBox ResultValue
End Sub
This sub will count the occurrences of the number “960” in the dataset. We set “rng” first because the function needs it as input.
We hard-code the searched value (960) in the code. We could’ve used our variable “criteria” instead, but I think it’s more straightforward to show you the code with the hard-coded value.
If you’re performing a COUNTIF on a range of cells formatted as text, you can also add wildcard characters to allow you to count instances of a subset of characters. For example, if you want to count the number of names in column “A” that include the letter “o”, you must put “*” before and after the variable (“search”) that represents the letter “o” in the code.
The code below does just this. As you can see, the code captures the subset of characters for which it’s searching via an InputBox, and it saves that search criteria in the “search” variable.
Dim search As String
Dim occurrence As Integer
Set rng = Range("A2:A6")
search = InputBox("Search for", "Search Term")
occurrence = WorksheetFunction.CountIf(rng, "*" & search & "*")
MsgBox occurrence
#4 – VLOOKUP
Another useful function I tend to use daily is VLOOKUP. In many situations, VLOOKUP can save you a significant amount of time. As you might know, you can use it to pair data between sheets and to find the first occurrence of a value.
Here’s an example of how to implement VLOOKUP in VB code:
Sub VlookupFunction()
criteria = "Amy"
Set rng = Range("A1:D6")
ResultValue = Application.WorksheetFunction.VLookup(criteria, rng, 2, False)
MsgBox ResultValue
End Sub
Let’s say we want to find Amy’s salary. Therefore, we set the “criteria” variable equal to “Amy”. The range we search will be the same as it was in the original COUNTIF function.
In the third parameter of the function, we set the index to the number of the column (in this case, column 2) in which we find our search result. We set the fourth parameter to “False” to indicate we’re looking for an exact match.
Running this code returns the expected result of “960”.
#5 – SUMIF
SUMIF may be the best of all these functions – I use it often due to its flexible parameters.
It isn’t necessary to use the SUMIF function on the limited payroll dataset, but we can still demonstrate that it works. The below code sums the salaries of employees with the same first name “John”:
Sub SumifFunction()
Set rng = Range("A1:D6")
Set SumRng = Range("B:B")
criteria = "John"
ResultValue = Application.WorksheetFunction.SumIf(rng, criteria, SumRng)
MsgBox ResultValue
End Sub
The “rng” variable represents the table and “criteria” now represents “John”.
In this code snippet we finally use the variable “SumRng”. It represents the column for which the function sums the cells that meet the criteria.
The expected result is again displayed via a Msgbox.
#6 – Summary
In this tutorial, we used VBA code to run some of the most commonly used Excel functions (SUM, COUNTIF, VLOOKUP, SUMIF). You can, of course, execute your favorite functions with VBA code, too!
Feel free to experiment. If you make mistakes when coding, the VBA Editor frequently lets you know.
Add Excel functions to your other VBA knowledge and build macros that allow you to perform your job in much less time and with fewer mistakes!
P.S. There’s a helpful article on Wall Street Oasis that gives a good idea of the kinds of problem you can solve with VBA, together with some code samples. It’s a good read if you’re new to Excel Macros & VBA.
#7 – About the Author
Daniel Lajosbanyai – I work as a controller and accountant for a company with a bunch of international subsidiaries. In my daily job I work A LOT with Excel and my tasks are quite repetitive (and sometimes boring!)
To boost my processes and spare some time and energy, I started learning Excel Macros. My first teacher was a university professor, who showed me how to get started. I am really thankful to him, because without this knowledge I might have quit my job years ago.
Now I enjoy writing macros for every task I can automate and feel really happy to have learned this skill! Why should we do repetitive things, when our computers can do them quicker for us? We only need to learn how to give them instructions to follow!
Related Posts:
Hey, I'm Victor Chan
Are you struggling with complex Excel tasks? Feeling overwhelmed by spreadsheets that are hard to use?
Many people believe mastering Excel is about learning shortcuts, functions, and formulas. But this overlooks the importance of building practical, real-world applications. It's not just about knowing the tools. It's about using them effectively.
That's where I come in. You'll get a unique perspective to Excel training from me. I have over 20 years of experience at Deloitte and two global tech companies. And I know what can make a difference in your career.
Let me help you integrate Excel into your professional life. Starting today. Read one of my articles, watch one of my videos. Then apply the new technique to your work. You'll see the difference immediately!
Recommended Posts
Discover the PROVEN Blueprint for transforming your Excel skills, supercharging your productivity, and standing out in your career! My course helps you to learn Excel VBA and save hours of time even if you have zero prior experience with programming.
Solve tricky Excel problems and take your work to the next level! Get customized solutions for your unique needs. Save time and gain insights with truly expert Excel solutions from only $97 per task.
Get a clear overview of your project progress using the Excel project timeline. Use it to communicate the big picture, track task progress, and stay on top of your project goals. Stay organized with our project timeline!
Our cheat sheets provide quick and easy reference to commonly used Excel VBA concepts and code snippets.
Unlock new levels of productivity and efficiency with our cheat sheets, and write VBA code like a pro in no time.
RECOMMENDED READING
Are you looking to upskill and stay ahead of the curve? Excel is a powerful tool that keeps growing in demand. We round up the best online courses for learning Excel.
Are you looking to up your spreadsheet game? Excel is an invaluable tool that can help you stay organized and save time. From data analysis to budgets, Excel can do it all!
Today, having Excel skills is more critical than ever. Those who know how to use Excel are more likely to find higher-paying jobs. And get promoted faster.