After a recent thread
, I noticed that /excel
's guides tend to focus on the specifics of a particular tool or technique, so I wrote this guide focusing on the big ideas that I've found help people develop their working knowledge of Excel. It is meant for near-beginners who have some familiarity with navigating Excel, however it may also be useful to those who are a bit more experienced, but don't feel they have a strong enough grasp to develop solutions on their own.  Although I review some of the most basic elements of a spreadsheet, I recommend that complete beginners jump to one of the first three items on my list of further resources at the bottom of this guide, since those are much better suited to show you around Excel with pictures or videos. Additionally, I deliberately stay away from many details covered by other resources, as well as from uninstructive exceptions to the core concepts presented, though I include some non-essential but useful tidbits in the end-notes. Feedback is welcome and encouraged.
The most fundamental concept is that Excel is just a big calculator  with a few major advantages over your desk calculator:
- Excel can easily chain multiple calculations together
- Excel can do more complex operations
- Excel can easily store data to use in calculations
- Excel can do operations on things other than numbers, such as text and cell ranges.
The guiding principle is to let Excel do your work for you as much as possible. If you find yourself doing a lot of work and it feels like Excel should be able to do it more efficiently, do what you can to find out how. Spreadsheets have been around for a long time, and Excel has many users, so it is very unlikely you're the first to want to do something.
Like learning a language, you need a basic vocabulary to be able to learn and to find help when you need. There are two aspects to the vocabulary: the elements of Excel that you work with, and the tasks you're looking to accomplish through formulas or other Excel features. Basic tasks include things like importing, cleaning, formatting, sorting, filtering, merging, summarizing, or charting data. Many tasks can be accomplished by finding the right feature in the ribbon, or the right function in the Function Library (see the Formulas ribbon), and the linked resources at the end of this guide also have great explanations and tutorials for the multitude of tasks. This guide instead focuses on explaining the basic elements.
What's a spreadsheet made of?
Excel files (called workbooks
) are made of worksheets
) each of which contain a grid of cells
. You can reference
a cell by its letter-number address, where the letter represents the column and the number represents the row. For example, D6 is the address of the sixth row of the fourth column on the sheet. A group of one or more cells is called a range.
You can reference a contiguous range using the range operator :, e.g., A1:C4 represents the rectangular range of cells from A1 through C4. You can also refer to cells on another sheet, or even in another workbook. The important concept is that when you refer to a range, you are generally referring to the values contained in the specified range. The range address is just a convenient way to point to the values it contains.
Though it is seemingly the basic building block of a spreadsheet, a cell has several distinct properties which are useful to understand. Every cell you use has a value
, which is either static data or the calculated result of the cell's formula
. Cells also have formatting properties, such as the border size and style, cell shading, text alignment, number formatting, and font styling (bold, italics, underlines, and so on). If you refer to the cell in a formula, you will almost always be retrieving the cell's value.
Formulas and Functions
A cell's formula
contains one or more operations
, which can include the basic mathematical operators (addition, subtraction, multiplication, division, exponentiation), logical operators (testing for equality and comparing values), or more complex functions.
On a semantic note, like the parts of speech in English, it's good to learn about the distinction between functions and formulas, but few people actually care, so the terms are often used interchangeably.
To tell Excel you are entering a formula, start the cell with a =.  A very simple (and useless) formula would be entering =42 into A1, which means, the value of this cell, i.e., A1, is equal to 42. (For convenience, any time I mention a formula that starts with a cell address should be understood to be located in that cell.) A more interesting formula may be B1 =A1^2 which squares A1's value. If A1 is changed to be some other value, B1 will change to reflect that  because A1 in the formula just means the value in A1. As long as A1 contains something that can be squared, B1 will show the squared value. B1's value is thus dynamic
,rather than static
. Likewise, C1 =B1+5 will add 5 to the value of B1, and will change when B1 changes, which in this example, means when A1 changes. If you want to make the result of a calculated formula static, you can copy the cell and paste values only.
Relative versus Absolute References
When you copy and paste a cell with a formula , each cell reference will shift by the distance between the original and copy: D10 =D8+D9 copied to E15 will be =E13+E14 because the formula used relative references
. However, you can create absolute references
the row and/or column by adding a $ before the column letter or row number , so that the column/row of that reference doesn't change when the cell is copied. So D10 =$D8+D$9 copied to E15 will be =$D13+E$9. If you're having difficulty following, check out this page on relative versus absolute references
which has some great illustrations of this concept. For a basic exercise showing the usefulness of this feature, try making a multiplication table by writing one formula and copying it to the rest of the table.
A function is a named command that takes some inputs (aka arguments
), does something to them, and returns
some outputs (usually just one ) as its value. Functions are particularly useful when the "something" being done is complicated. In Excel, the function name is always followed by parentheses between which you provide the inputs. For example, SUM(…) take one or more numbers, adds them up, and returns the total. To make functions easier to use, Excel helpfully tells you the names of the inputs expects. (Function inputs are also usually restricted to certain data types
, which are discussed in more detail below. In essence, the type determines what you can do with a piece of data.) A general philosophy regarding functions is that they should do one thing well—but that doesn't mean that one thing has to be simple. You'll quickly find that many of your tasks can't be accomplished by using a built-in function on its own, which brings us back to formulas.
In addition to the series of chained calculations across multiple cells like the above example in the "Formulas" section, formulas let you build chains of calculations by nesting
functions so that one function's output is directly used as the input to another function all in the same cell. For example, the earlier example's formulas in B1 and C1 could have been nested in a single formula as =A1^2 + 5. Technically, a cell reference takes the result of whatever calculations get the value of that cell, but once it has that value, the calculation that led to the resulting value is unimportant. What this means is that a function input can either be something entered directly, or anything that calculates to the expected data type.
This concept is key to become comfortable building formulas.
There are several categories of functions, which you can see through the Insert Function
button (the little "fx" next to the formula bar) or on the Formulas
ribbon under Function Library
. Some common categories include Math & Trig, Lookup & Reference, Text, Date & Time, and Logical. I highly recommend glancing through categories that look relevant to help you get a feel for what tasks are common enough to have a function. Knowing what's available can easily help you learn a new way to do something that saves you a lot of time and effort.
As you get proficient at writing complicated formulas, it's worth keeping in mind that it's sometimes easier to build a large formula spread over a few separate helper
cells, and then decide how much to combine them by placing the formula from helper directly where the helper is referenced down the chain. It can often be easier to leave them separate to help with finding errors, to aid in understanding what you're doing, and even to help keep calculation time down. More specifics on these topics are beyond the scope of this guide.
A note on order of operations
You may recall from math class that 1+2*3 = 7 as a result of our conventions about the order in which we apply the mathematical operations in this calculation chain. (This is usually taught as PEMDAS
.) If we want to calculate in the order it's written, we'll need to group it as (1+2)*3 = 9.
Excel maintains this convention, with functions being treated as parentheses groupings. When the calculation order brings us to a function, its arguments are first calculated according to the normal PEMDAS rules, and then the function output is used in the next operation in formula. This is similar to the way that referencing a cell will just use its value regardless of what formula is in the cell. Here are two examples of how I've seen an unclear understanding of this topic manifest:
- At a very basic level, it's not uncommon to see formulas like =SUM(A1+D6+J8+X15), but you can see why the SUM is redundant. SUM's arguments are separated by commas, so here there is only one argument, which is the total of adding the values in the four cells. Say those cells had the values 1, 2, 3, and 4, then this would be evaluated as =SUM(1+2+3+4) which is =SUM(10). This is a completely valid formula, but not a very helpful one since by the time it is calculated, you already have the desired result. For this particular example, you could instead write it = A1+D6+J8+X15 or =SUM(A1,D6,J8,X15). 
- A more advanced but less obvious example is related to the tiresome VLOOKUP versus INDEX-MATCH debate. Setting aside each option's relative performance (and others), one commonly claimed advantage for INDEX-MATCH is that the column number of the desired value can be dynamic, while VLOOKUP needs a hardcoded column number which is annoying to change. However, there's nothing stopping you from using MATCH to dynamically return the desired column number in a VLOOKUP, same as in INDEX-MATCH.
When dealing with a spreadsheet and calculations in formulas, it's a good idea to understand the types of input and output you are dealing with. As mentioned above, the datatype
determines what you can do with a piece of data. It wouldn't make sense to add the number 149 and the text "yellow", for example. Most functions are built to expect certain types for their inputs. Spreadsheets have a few important data types that you as an end user should worry about:
- Numbers : pretty self-explanatory. Note that dates and times are actually stored as numbers (more below).
- Text : also called strings, it refers to any alphanumeric text that isn't a boolean or error value, though those types as well as numbers can be treated as text.
- Boolean : this just means logical values, TRUE and FALSE, and are most commonly used for conditions. They are typically calculate by comparing values, e.g., is A1 equal to B1?
- Error values : when you run into some problems, functions may return different error codes, starting with a # and often ending with !. Be sure to know what they mean because they'll help you figure out what went wrong.
- Ranges and other references can be thought of as a data type since they can be the input or output to some functions.
A source of frustration for novice users is that values that look the same may be treated differently if they have different data types. Almost universally, this will be a number stored as text not being recognized as a number in your formula. You can see this by trying =1="1" which equals FALSE — Excel treats the number 1 and text string "1" differently. This type of mismatch may show up when dealing with dates stored as text, or when using a lookup function and the lookup value is a different type than the data in the lookup range.
Dates and Times
Excel stores dates and times as a number (called serial date-times
), counting the number of days since January 0, 1900 (yes, 0). So a value of 1 is equal to January 1, 1900 and 42675 is November 1, 2016. Since whole numbers are days, decimals are part of a day: i.e., times. For example, 42675.75 is November 1, 2016 at 6:00 PM. Because dates are stored as numbers, you can use them in mathematical calculations, such as subtracting two dates to find the number of days between them, or adding some number of days to the current date. Although dates are stored as numbers, there are a variety of number formats designed for dates so that you can look at something meaningful. Additionally, Excel helpfully (or sometimes unhelpfully) lets you enter a date in a text-like format and automatically changes it to the serial date number, so you don't have to know what the serial numbers are. There's plenty more to know about working with dates, but knowing just this is an important step to Excel fluency.
Other Useful Information
Navigation and Keyboard Shortcuts
Keyboard shortcuts are great, but they are covered elsewhere so much that I won't spend much time on them. Rather than memorizing every single shortcut, know that with the Ribbon interface introduced in Excel 2007, it becomes really easy to learn to access any ribbon item: press Alt once, and the hotkeys for the Ribbons pop up. Press the desired Ribbon's hotkey, and you'll see hotkeys for each item on that Ribbon pop up. Eventually, you'll learn the keystrokes for your most-used features, and if those keystrokes are still too annoying, then look up alternatives. Excel maintains many ways to get to the same feature from the different shortcuts used in earlier versions. For example, you can get to the Paste Special dialog by Alt+H+V+S (Ribbon keystrokes), Alt+E+S+V (the pre-2007 menu keystrokes), or my favorite Ctrl-Alt+V (pressed simultaneously).
Get to know the tools in the ribbon
Like with the function categories, knowing what's there can help significantly, even if you don't know how to do it yet. In terms of working with formulas, the Formula Ribbon has a couple of tools that are very useful in becoming advanced. First, Named Ranges let you make your formulas much easier to understand if certain ranges you use make sense to name — just make sure to give descriptive names. Second, the Formula Auditing tools seem to be vastly underrated, and using them can help you learn how to work with formulas very quickly. A related feature not in the ribbon is that you can highlight a portion
of a formula while in edit mode and press the F9 key to evaluate just that portion of the formula in-place. You can hit Esc to cancel edit mode and revert to the full formula.
Further Resources for the Beginner
I have no affiliation with any of the following resources, but here are popular recommendations plus a few other links:
- I haven't looked at all of it, but Excel Exposure seems to be the best free source out there. It provides a regularly-updated master workbook that has references of functions and keyboard shortcut, and examples of useful features, as well as extensive video lessons online.
- Many like the ExcelIsFun YouTube channel. Thousands of videos covering pretty much any Excel topic you can think of.
- ExcelFrog is a recently-introduced newcomer with practical instructions and demonstrations for beginners.
- The Microsoft page Overview of formulas in Excel contains much more detail on how to work with formulas, including topics I didn't cover such as 3D references, array constants, and formula limitations.
- Earlier guides from /excel contain some great in-depth tutorials on particular topics.
- There's a white paper from 2005 I've posted in the comments before: How Do You Know Your Spreadsheet Is Right? [PDF link] all about overall good spreadsheet design, despite its age. It's slightly advanced, but you can still get value out of it even if you skip the VBA-related parts.
- Armed with the proper vocabulary, Google is your best friend.
This guide is based on the US Locale of Excel 2013 for Windows, so my function names are in English and arguments are separated by commas rather than semicolons. Other versions of Excel (or non-Excel spreadsheet software) may have different terminology, but will have the same main concepts.  Note that I'm all for avoiding re-inventing the wheel, but if you regularly find yourself unable to figure out how your found solutions work, this is for you.
 Yes, you could say this about any computer, but bear with me here.
 You can actually also start a formula with a + or - which are allowed for backwards compatibility with older spreadsheet software, though Excel will add the = once you enter the formula.
 By default, Excel automatically calculates all formulas, but you can change it to manual calculation, which is sometimes helpful. I'll be assuming it is set to automatic.
 You should know that this applies when you copy the cell versus copying the text of the formula. If you are able to edit the formula and see a text cursor (a blinking vertical line), you're just copying the text. If you don't see that, but do see an outline around the current cell(s), you are copying the cell, and the following description applies. When you have cells copied, Excel should show a moving dotted line on the border of the copied range.
 When editing the formula, you can cycle through the various combinations of anchored/non-anchored column and row by hitting the F4 key. You can also select more text in the formula to cycle multiple references at once, though they'll be set to the same combination.
 Some functions can return an array of multiple values, and would typically be entered in an array formula. This is beyond the scope of this guide.
 When the input cells all contain numbers, these two would be equal, but if one cell may end up with text, the SUM will add up the other cells while the plus formula would return an error. This is because SUM also contains instructions what to do when an argument is not a number, while using a plus instructs the two values surrounding it to be added together without checking whether each is a number.
Hi All. In this post I’m going to be describing how to create function procedures to create your own functions in VBA. Note 1
: This is an intermediate level VBA post. I assume that you know how to use variables, arrays and loops in this post. If you do not, it would probably help to read the writeup guides we have on the subject. You can see those guides here
in the VBA section. Note 2
: The terms "parameter" and "argument" are not the same and refer to different things. This post uses those terms interchangeably, which is fairly common.
Functions are one of two types of procedures in VBA: the other are subroutines, which are occasionally called macros. There are two types of functions: Worksheet functions and VBA functions. I will be discussing both in this post.
If you have even some of the most basic knowledge of Excel, you’ve used functions before: They’re what you use in your formulas. Functions typically take a number of parameters (although some take none) and return a value. So a user-defined worksheet function is a function that you can call from the worksheet (or VBE) in the same way that you do with native Excel worksheet functions.
Excel has so many functions available to you. You may wonder what the point would be to create your own functions. There are several reasons for creating your own functions:
- You have complicated formulas that, perhaps invoke several different formulas and parameters. You can simplify this by putting all of the logic in your own user-defined function.
- Once you create a function, you can make use of it in multiple cells, worksheets, workbooks, etc. Just like you can with native worksheet functions (if they’re in an addin)
- You want to get the result of a value on the worksheet, perhaps once or several times, without deleting your undo stack
- You want to assign a value to a variable or array in VBA that is processed in some way.
Now that we have an introductions to functions and why you’d want to create your own, let’s look at function scope.
Public and private functions
Just like subroutines (macros), functions are public by default. For functions, this means that they’re available for use within the Excel worksheet, and within other modules in VBA. You can make the function private by using the private keyword before the function keyword like so:
Private Function ADDSFIVE() ‘code goes here End Function
A private function is only available for use within the same module where it’s defined and is not displayed by the worksheet intellisense.
Differences between function and subroutines
There are a number of differences between subroutines and functions. Let’s take a look at some of them below:
- Only function procedures can return values. Note the use of my word can here. Function procedures are not required to return values. In Excel, if I call a function with no return value, it just returns the default value of whatever its return type is (e.g. false for bool, 0 for long, etc.)
- You can call function procedures to return values without deleting your undostack in the worksheet. This can’t be done with macros.
- Functions need to be defined in the current workbook or an addin to be called from the worksheet / VBE. They cannot be used if defined in your personal macro workbook. You can also qualify the workbook name with the function name to call them. (One thing you can do however, is define them in a module in your personal macro workbook, and then just drag that module into the VBProject in the workbook you want those functions in.)
- Any code which makes any modification to Excel is disabled in a function when called from a worksheet cell. Such code works when called from VBE.
- Typically, macros crash and break on the line where a runtime error occurred. This does not happen in functions and can make them harder to debug.
- When you provide arguments to a subroutine, they are passed by reference by default. This means that VBA modifies the value of the original variable that was provided to the subroutine. In functions, they are passed by value. This means that a copy of the function uses a copy of the original value. You cannot pass a value in a function by reference with the exception of arrays, which must be passed by reference. However, you can work around this easily by assigning a variable to the value that’s returned by the processed function of itself. You can see an example of that below:
dim A as long A = processFunction(A) Note
: this does not work with arrays
So now we have some background with functions, let’s start with some examples:
First function example: ADDFIVE()
Option Explicit Function ADDFIVE(val As Long) ADDFIVE= val + 5 End Function
In this example, a function is created which accepts one parameter. In the function, there is just one line of code. The single line of code is an expression. If you notice, the name that we’re assigning the value of val + 5, ADDSFIVE, has the same name as the name of the function.
In the body of this function, ADDFIVE is essentially just a special type of variable.
Since the ADDFIVE variable has the same name as the function, it is the value that’s ultimately returned by the function.
So if you use this function in the Excel worksheet, the ADDFIVE function would return the value of the val parameter + 5, because that’s the value we assigned to the function.
One thing you may notice is that option explicit is enabled, but there’s no dim statement in the function to declare the variable ‘val’. The dim statement is not required for variables declared in the parentheses next to the function name. It is however required for all variables declared between the function and end function statements.
Another thing you may notice is that, like native Excel worksheet functions, ADDFIVE is in all capitals. You can name your functions in lowercase, capitals, or use mixed case.
One thing we may wonder about ADDFIVE is, what if we assign the value to val to be something that’s not numeric, like a string? Let’s take a look at an example in the next section.
The error Excel gives me is a #VALUE! Error when I use this formula in the worksheet. As I said before, functions do not break on the line where the error occurred. So, if you’re function was relatively long, it can be difficult to find where the error is.
One thing we can do is check for the value type supplied and run different code depending on what is supplied. Let’s take a look at the ADDFIVE function with additional logic to check user input:
Function ADDFIVE(val As variant) dim temp as variant If IsNumeric(val) Then temp = val + 5 Else temp = “Error: val Parameter Is Not numeric” End If ADDFIVE = temp End Function
This function is a bit more complicated than the first function, so let’s start breaking it down. The first thing that's done is declare a temp variable of the variant data type (variant is used because we can return a number type or string type.) Unlike our first function, which just returned an expression, this function does some type checking. The first thing this function does is check if the val variable is numeric. If it isn’t, then the temp variable is assigned the error message and returned by the function. If it is, then the temp variable is assigned the expression val + five and the function returns this value.
It can be helpful to use a temp variable like this in case you ever need to rename a function. If so, you only need to replace the function name in one place, which will be at the end of the procedure.
Once you have this function entered in a module in VBA, you can call this function like a regular Excel function in the worksheet. You can also call this function in VBA. With ADDSFIVE defined in a module, you can run the following code in the immediate window:
debug.print ADDFIVE(5) ‘returns 10
Now that we have an idea of how functions work, let’s take a look at return types.
Function return types
By default, if you don’t declare datatypes for variables, they can be set to any type. Similarly, for functions, if you don’t declare a return type, they can return any type. Because of this, it’s recommended that you explicitly return the datatypes for your functions. This can be done like so:
Function ADDFIVE(val As Long) As String ‘code goes here End Function
The ‘as string’ declaration sets the return type of the function to a string. So whether add five returns the sum of two numbers, or an error, it will return a string either way.
One thing that’s important to note about function return types is that they do not require the function to return values. So you can write code like so:
Function ADDSFIVE() As String ‘no code in this function End Function
Now, what happens if we run this function? Will it return an error? Unfortunately, no error is returned. There is no error because functions do not require return types. So what does the function return? Well, as I said earlier, what the function returns depends on depends on the datatype the function is set to return. Since we’re saying the function return type will be a string, if no value is returned, the value will be set to an uninitialized string which is “”.
Because of this, you should always
have option explicit turned on when you write function procedures. A simple typo can change your function from having a return value to having no return value at all. And if you don’t have option explicit turned on, VBE will not warn you of this mistake.
Just like you can with Excel worksheet functions, you can also nest function procedures you from your own user-defined functions. In VBA, this is done by passing one function as the parameter to another function. You can see an example below
Function HW() HW = "Hello world" End Function Function EXCLAM(val As String) EXCLAM = val & "!!" End Function ‘in immediate window Debug.print debug.Print EXCLAM(HW()) ‘prints Hello world!!
Exit function statement
One thing that’s important to note about functions is that, even if you return a value, this does not necessarily exit a function. You may, for example, be in a loop, and the loop may continue processing even after you assign the value of a function. To deal with this, we have the exit function statement which allows us to exit at a point of our choosing. Let’s take a look at such a function in an example below:
Function INRANGE(val As String, rang As Range) As Boolean Dim cell As Range For Each cell In rang If val = cell.Value Then INRANGE = True Exit Function End If Next cell INRANGE = False End Function
The INRANGE() function accepts two parameters: a val parameter and a range parameter. And the INRANGE function will ultimately return a Boolean value. A variable named cell is declared of the range datatype. Using this variable, a for-each-next loop is done that compares the value of the cells in the range to val. If any of the cells have a value that matches val, INRANGE is set to true, and the function exits. Since the value of INRANGE is set to true if the function exits using the exit function statement, true is returned by the function. If no such match is found, and the loop exits, INRANGE is set to a value of false before it exits the function in a natural way. And since false is the value its set to before the function exits, that’s the value that’s returned by the function.
Functions that have optional parameters
When you write functions, sometimes you want to provide optional parameters that can be utilized as necessary when the function is called. Optional parameters are specified with the optional keyword. Their data type must be variant
. And they must be provided after all mandatory parameters. When you use optional parameters, you have to check whether they’re provided with this ismissing function as you can see below as you can see below:
Function BILINGUALGREET(lang As String, Optional name As Variant) Dim greet As String If lang = "E" Then greet = "Hello" ElseIf lang = "S" Then greet = "Hola" End If If IsMissing(name) Then greet = greet & "!" Else greet = greet & " " & name & "!" End If BILINGUALGREET = greet End Function
This function, BILINGUALGREET specifies a greeting either in English or Spanish. The optional parameter “name”, of the variant type, comes after the mandatory parameter “lang”. If “name” is provided an argument, that name is also included in the greeting.
Functions that can take varying parameters
All of the examples so far have looked at functions that have a fixed
amount of parameters. With certain functions, you don’t know how many arguments will be provided until it’s called. Sometimes you may want a function that takes one parameter, sometimes three, sometimes six, etc. If you had a function with a fixed amount of parameters, the function would fail when provided with too few or too many arguments. In VBA, you can create functions that can take dynamic parameters. You can create a function that takes a dynamic number of parameters using the paramarray keyword, which you can see used in the example below:
Function SUMMY(ParamArray args() As Variant) Dim temp As Long, i As Long temp = 0 For i = LBound(args) To UBound(args) If IsNumeric(args(i)) Then temp = temp + args(i) End If Next i SUMMY = temp End Function
The paramarray keyword is specified before the array named args, which is a dynamic array. Once the dynamic array is provided, the array is iterated with using a for loop. A conditional statement then checks whether the current element of the array is numeric. And if it is, it is added to the temp variable. One the loop finishes, the temp variable is assigned to the function name. And this returns the value from the function.
A volatile function is a function that recalculates automatically whenever any change is made is the workbook. This also includes stepping into a cell, even if you don’t make any changes. A few examples of functions that work this way are RAND(), RANDBETWEEN(), and NOW(). You can create your volatile functions by using the volatile property of the application object as you can see below:
Function DYNAMICRAND(Optional vol As Variant) Dim temp As Double Application.volatile If Not IsMissing(vol) Then If vol = False Then Application.volatile False End If End If temp = Rnd DYNAMICRAND = temp End Function
This function works similarly to the RAND() function in Excel. It is a volatile function and recalculates any time any change is made in the workbook. The main difference is that it accepts an optional boolean parameter which allows you to turn volatility off. So if you pass in FALSE argument to the DYNAMICRAND() function, it will not be volatile
One thing that’s important to note is that, if you make use of a volatile function in your own code (e.g. worksheetfunction.randbetween()), this can make your function volatile.
Once your function is volatile, it can not be made non-volatile by passing false parameter to application.volatile.
All of the examples we’ve used so far only return a single value. However, functions can also return an array. You can take a look at an example below:
Function ADDFIVEARR(ByRef arr() As Long) As Variant Dim i As Long For i = LBound(arr) To UBound(arr) If IsNumeric(arr(i)) Then arr(i) = arr(i) + 5 End If Next i ADDFIVEARR = arr End Function Sub subby() Dim numsArr(2) As Long, funcArr() As Long, i As Long numsArr(0) = 1 numsArr(1) = 2 numsArr(2) = 3 funcArr = ADDFIVEARR(numsArr) For i = LBound(funcArr) To UBound(funcArr) Debug.Print numsArr(i) Next i 'debug.print 6, 7, 8 End Sub
The function ADDSFIVEARR takes an array parameter. It goes through each element in the array. If the element is numeric, five is added to the value of that array element. And once all of the elements in the array have been iterated, the array is returned from the function. In this example, the array is called from the subroutine Subby that provides the argument for the function. There are a few things to note about this function:
- Once an array’s elements have been given values (like numsArr’s have) you can’t reassign those values to the array without clearing them. So, we use the dynamic array funcArr to get past this.
- The arr() parameter in ADDFIVEARR is passed byref explicitly. As I noted earlier, if arrays are passed as arguments, they must be passed by reference. If not you’ll get a syntax error (VBE notifies that, matter-of-factly, “Array argument must be ByRef”)
- Because arrays are passed as reference, the array that is passed (i.e. numsArr) is also modified. So in the subby macro, it doesn’t matter whether iterate through the numsArr or the funcArr arrays. They essentially point to the same array. So I can iterate through and debug.print either of them and get the same values.
As I stated earlier, debugging functions can be difficult. Unlike macros, the functions don’t break at the line where the error occurred when called from the worksheet. So how do you debug them? Let’s look at some examples below
Runtime errors are errors that cause a procedure to crash at runtime. This is what happens when a macro encounters such an error. It breaks and allows you to see which line of code the error occurred on. You can do this with function procedures as well. Let’s take a look at a few different options: Immediate window:
Using the immediate window (view -> immediate window), you can call the function like so: “debug.print ADDFIVE("hello world")” If no error ocurrs, the function will return the result in the immediate window. If one does occur, it will break on the line where the error occurred in VBE. Test Macro:
Another option you have is to run a test macro that calls the function like so:
Sub Test() Range("A1").Value = ADDFIVE("hello world") End Sub Function ADDFIVE(val) As Long ADDFIVE = val + 5 End Function
Running this code will give you an error in a similar error to using the immediate window. Now that we discussed some solutions for runtime errors, let’s discuss semantic errors.
Semantic errors are errors that happen when a function returns a value, and so doesn’t have a runtime error, but does not return the value it should. These errors are the result of a bug in your code. Because there’s no runtime error, the code does not crash at any particular line. That makes these type of bugs significantly harder to find and debug. Let’s look at a strategy for finding such errors below: Immediate window with debug.print statements
: Like runtime errors, the immediate window can also be useful for looking at semantic errors. One strategy is to open the immediate window and use several debug.print statements in your code. You can use these statements to print the value of several variables as they’re occurring in your code to see if they have the value that they should. The good thing about this method is that these statements will be ignored when the function is called from the worksheet. So you can add them and not have to worry about your function not working as it should when it’s called from the worksheet. Test macro with watches
Let’s take a look at another test macro below:
Sub test() Debug.Print funky() End Sub Function funky() As Boolean Dim a As Long, b As Long, c As Long a = -1 b = 2 c = 3 If a + b = c Then funky = True Else funky = False End If End Function
From here, we can click the debug window and click “watch window”. With the watch window, you can create watches. Watches allow us to provide an expression and allows us to take certain action based on the options we select. Let’s assume that all variables in the funky function should have a value greater than zero. We can test for this by providing an expression like “a < 0”. For the procedure, select funky. And finally, select the option “break when true”. After we add this watch, we can run the Test procedure. This procedure will break at the line b = 2 because the value of a is less than zero. You can also add additional watches to check for other variables and other procedures. Using watch windows in this way lets you work with semantic errors in the same way that you’d work with runtime errors. And it can be very useful, especially for larger functions.
Advanced function topics
Private functions that can be used in different modules
If a function is private, it doesn’t appear as a function that you can use in the worksheet. Unfortunately, this also prevents you from using it in other modules other than the one it’s defined in. You can get around this by defining the function in a class module. While class modules sound advanced, and this may seem difficult, it’s actually pretty easy to do.
You start by inserting a class module into your project (Insert -> class module). The first thing we’re going to do in the properties window (view -> properties window) is change the name to UDF. Next, we’ll just recreate our initial ADDFIVE function in the class module below:
Function ADDFIVE(val As Long) ADDFIVE = val + 5 End Function
With the function defined, open or insert a module in VBA. You can now create an instance of the UDF object with an object variable as you can see below:
Sub subby() Dim u As UDF Set u = New UDF Debug.Print u.ADDFIVE(15) 'prints 20 Set u = Nothing End Sub
A few things to note about this example:
- Since the u variable is an object, it must be instantiated. Objects are instantiated using the ‘new’ keyword. You can do this in the variable declaration like so: “Dim u As New UDF”. Using this syntax, you don’t need to set the variable to the object using the ‘set’ keyword. This syntax is more convenient but is not recommended.
- Since the u variable is an object, it should be terminated by being set to nothing when you’re done with it.
You can use the methods defined in this object in multiple modules without ever appearing in the Excel worksheet.
Windows API functions
You can functions to encapsulate the logic to utilize the Windows API to do a number of really advanced things, such as detecting the state of certain keys on the keyboard (e.g. numlock). This is really complicated though and out of the scope of this discussion. I did feel the need to mention that it was possible though. If you’re interested, you can google ‘Windows api VBA” to see some examples)
Thanks for reading! I hope you’ve learned by reading this post that functions can be very useful in VBA. I hope this post helps you write some of your own functions in the future!