Create Function
By Richard Rost

6 years ago

Create Your Own Custom Function in Microsoft Access VBA

S M L XL FS | Slo Reg Fast 2x | Bookmark

In this video, I will show you how to create your own custom, user-defined functions in Microsoft Access VBA (Visual Basic for Applications). We will start with a subroutine, and then create a couple functions. We will create a random number generator, and functions to convert Celsius to Fahrenheit and vice versa. You will learn how to pass parameter values to functions, and have them return a value.

Michael in Louisville KY (a Gold Member) asks: I'm familiar with the built-in Access functions like Date() and Now(). Is there a way I can create my own custom functions to perform the specific calculations I need?

Members

I'll show you how to create public functions and global modules so your functions can be used in every form, report, and query in your database. You will see how to set a lower bound for your random numbers, so you can, for example, generate random numbers from 10 to 20. We will build a dice roller where you can roll any number of multi-sided dice in sets. Very handy for you gamers and D&D players who want to generate character stats. (This one's for the nerds like me).

Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!

Links

Intro to VBA: https://599cd.com/IntroVBA
Custom Functions: https://599cd.com/acd2
Random Numbers: https://599cd.com/RandomNumbers

Learn More

FREE Access Beginner Level 1
FREE Access Quick Start in 30 Minutes
Access Level 2 for just $1

Free Templates

TechHelp Free Templates
Blank Template
Contact Management
Order Entry & Invoicing
More Access Templates

Resources

Diamond Sponsors - Information on our Sponsors
Mailing List - Get emails when new videos released
Consulting - Need help with your database
Tip Jar - Your tips are graciously accepted
Merch Store - Get your swag here!

Questions?

Please feel free to post your questions or comments below or post them in the Forums.

Comments for Create Function

Age	Subject	From
3 years	Awesome	Dan Jackson
4 years	Custom Function	David Clement

Subscribe to Create Function
Get notifications when this page is updated

Intro

In this video, I will show you how to create your own custom functions in Microsoft Access using VBA. We'll talk about the difference between subroutines and functions, how to pass parameters, return values, and see practical examples including doubling a number, generating random numbers, and converting between Celsius and Fahrenheit. You'll also learn how to call these functions from form controls and use them to perform calculations in your Access database.

Transcript

Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost. In today's video, I'm going to show you how to create your own custom functions in Access VBA.

Today's question comes from Michael from Louisville, Kentucky, a Gold Member. Michael asks, "I'm familiar with the built-in Access functions like Date and Now. Is there a way I can create my own custom functions to perform the specific calculations I need?"

Of course, Michael. You can definitely create your own functions in Microsoft Access VBA. Let me show you how.

Here I am in my basic Access database template that I use for a lot of my classes. Let's go into Design View. I'm going to drop another command button on this main menu form. Go up to Design, find the Controls box, find a button, and drop it right there. When the wizard starts up, just hit Cancel; we're not going to use the wizard, we're going to make our own custom stuff.

It says Command13. Just put "My Button" in here, or whatever you want to call it. Now right-click on the button and go to Build Event. You'll get this little dialog. Pick Code Builder, which allows us to go into the VBA editor, the Visual Basic for Applications editor. Click OK.

Now the VBA editor comes up. You may see some other stuff, some other panes on the left or on the top. Ignore those for now. Right here you'll see that I'm inside "Private Sub Command13_Click." That's the button's event that I just built.

In here, I can put something like "MsgBox 'Hello World'." If I save this (Ctrl+S), come back over here, close this form, and reopen it, then hit the button, I get "Hello World."

That's a basic subroutine. Now that subroutine is right here as "Private Sub Command13_Click."

If I want to create my own subroutine so I can do this stuff from anywhere, I can come right above here and say "Private Sub HelloWorld." That's the name of it. Hit Enter, and it puts a little open and close parentheses after it—don't worry about that, I'll teach you about that in just a minute. In here, I'll put the "Hello World" code. Watch this: copy this and paste it up here.

Now I've got my own subroutine. "Private" just means only this form can use it, versus "Public" where other forms and reports and stuff can call it. Let me get rid of some empty spaces in here.

Now, what I can do is call "HelloWorld" in the Command13_Click event. What will happen is I'll click on the button, which calls Command13_Click. It will then call HelloWorld, which is my private subroutine, which pops up the message box. Click the button and there you go.

Now it looks the same to you and me on the outside, but under the hood what's happening is we now have our own subroutine. It seems kind of silly to do that for only one line of code, but this could be 50 lines of code. This could be a long procedure that does a bunch of stuff. You don't want to repeat that in multiple places inside your database, so you make your own subroutine for it.

I could come down here and now say HelloWorld in other controls as well. For example, that's the button that opens up the customer form, so it will open up the customer form and then call HelloWorld.

Customers—look, it opened up the customer form and then it called HelloWorld.

That's a subroutine. A subroutine does some stuff. A function, on the other hand, returns a value. We're going to have the function give me some information back and then I'll do something with it.

You're probably familiar with a lot of Access's built-in functions like Date. For example, I can come up here and say MsgBox "Hello World. Today is " & Date.

Access gets rid of the open and close parentheses around some of the built-in functions like Date, but Date is a function. Date returns a value; it gives you the current system date.

If I run this now, it says "Hello World. Today is 10/10/2020." It returns a value.

In both subs and functions, you can use things called parameters. You can send information to this subroutine. For example, I can send it myDate as a Date.

Actually, let's do something simple first. Let me send it myName as a String. Instead of saying "Hello World," I'm going to say "Hello Rick." So I'm going to put myName in here.

Down here, when I call HelloWorld, I have to send it myName. Since HelloWorld's myName comes in as myName, myName goes in here. See that? That's called a parameter. You can send multiple parameters if you want: comma, myDate as Date, for example. You can use Dates, Integers, Long Integers, all kinds of different types. I cover all of them in my Developer classes.

Now, down here, I can send the Date value. Date values have to be enclosed inside of hashtags (#, pound symbols). I'll send the date—say, #10/23/1972#. I'll say, "Your birthday is " & myDate, like that. I'm sending it my birthday. Yeah, I'm old. Ready, go. See?

That's all stuff with subroutines.

How do I do a function? What do I use a function for? A function is good to do some calculation and return a value.

Let's come up top here and I'm going to say "Private Function MyNumber." I'm not going to take any information in, so the parentheses will be empty there. But I'm going to return a value; let's say it's a Long Integer: "As Long." That's how you declare that.

MyNumber as Long. Right in here, I'm going to say MyNumber = 42.

Now let's get rid of the HelloWorld example, and I'm going to say: MsgBox "My number is: " & MyNumber

You should always end a function call with the open and closed parentheses. It's just good programming. Sometimes you don't need it, but you should get in the habit of using it for functions.

Go over here, hit the button. "My number is: 42."

Let's examine what happened. I clicked the button. It's going to MsgBox the actual text, "My number is: " and then it's going to say go out here and get MyNumber.

MyNumber is a function returning a Long Integer. Right in here, we say MyNumber = 42. This "MyNumber" here is the same as the name of the function. That's how you return the value.

Now let's say MyNumber's job is to take a number in from the user and return double that number.

So, I'm going to have it take "x As Long" as a parameter, and then MyNumber = x * 2. So MyNumber's job is to return double whatever x is.

We could change the name of the function if you want. We could say DoubleTheNumber is the name of the function; just change it here and change it everywhere it's called. The name of the function should represent what the function does. So, what are we doing? We're doubling the number. So, DoubleTheNumber will be our function. We have to give it a value—let's give it 42.

Save that, Ctrl+S, come over here, and run it. "My number is: 84." It doubled the number. That's what a function does. Usually you give it some data, and it returns some other value. You don't have to give it arguments, you can just go out and do stuff like the first example did.

How about something a little more useful? How about returning a random number?

Let's change this to RandomNumber. Then we'll have "maxNum" as the maximum number, so it could be like 1 to 6 if you want to roll a 6-sided die, or 1 to 20 for your D&D nerds rolling hit rolls. I used to be a D&D nerd when I was younger. We can make a little dice roller here.

First, you have to call Randomize. That initializes the random number seed in the database—it makes sure that you get a truly random number every time. Well, there is no such thing as a truly random number with computers, but that's a different lesson.

Now we've changed the name of our function, so it's going to be RandomNumber = some code.

How do you generate a random number? It's going to be

Int(Rnd * maxNum) + 1

Why? Rnd returns a random floating point number from 0 to just less than 1: 0.999999... So, you take that value and multiply it by your max, say 1 to 10. It's still a floating point number, so you could have 3.625, whatever. But it's 0 to 9 in this example. So you use Int, which chops off the decimal part and makes it an integer; now it's 0 to 9. But we really want 1 to 10, so we add 1 to it.

I'll do a whole separate TechHelp lesson on random numbers, including how to set the lower and upper bounds.

This is where you come in. Random numbers is on my list; I've gotten questions about it before and want to do a TechHelp video on it. I like to make videos based on what's popular. If you want to see a lesson on random numbers, please drop me a comment, send feedback, or email me. All my contact information is below and at the end of the video. I'm going to make videos based on what you tell me you want to see.

Now I can call RandomNumber down here: give me a random number from 1 to 6. Maybe change this to "6-Sided Die Roll is ..." Ready?

Sub or function not defined. I was too busy talking and typed "Maximize" instead of "Randomize." Hit Stop to reset the code.

I'm going to leave that error in the video. Usually I go back and edit out errors in my TechHelp videos because I want you to think that I'm perfect. No. I was busy talking, so I typed in "Maximize" instead of "Randomize." You see what you have to do when you get errors—you get that dialog, hit the Stop button, and reset the code.

Now, there we go. 6-sided die roll is 6. Hit it again, 6. Hit it again, 4. I thought for a minute something was wrong, but got another 6. Why do I want to use this die roll? If we're generating stats: 3, 5, 6, and so on.

In the members-only video, the extended cut coming up next, I'm going to show you how to make a list of these. If you want six dice rolls, it'll give you six of them in a row. I'll also show you how to set the lower bounds, so you can say "Give me a number from 1 to 6," for example.

Let's do another example. People always ask me about Celsius to Fahrenheit conversion.

Private Function: we're going to call it C2F (Celsius to Fahrenheit). I'm going to send it the C value (we'll call it "c"), as Double (a floating-point number), and we'll return a Double ("As Double").

How do I convert Celsius to Fahrenheit? The formula is:

C2F = (c * 9 / 5) + 32

You don't need the parentheses because of the order of operations, but they are a good habit to have.

Let's put this functionality on the form. Design View. We'll put "C2F" as a label, with Celsius and Fahrenheit fields beside each other. Grab a couple text boxes, delete the labels that come with them, and position them. Name the boxes "Celsius" and "Fahrenheit."

Now, in the code, right-click, Build Event. In here, I'll say:

Fahrenheit = C2F(Celsius)

So what's going to happen? We've got a text box called Fahrenheit, we've got a text box called Celsius. C2F will send Celsius up as a Double, evaluate it, convert it to Fahrenheit, and then set it in the Fahrenheit box.

Try it: close and reopen the form, put a value in Celsius (like 0), press Convert—boom, 32. Try 20—boom, 68. Try 30—boom, working. Try -5—boom, 23. Beautiful. Try 5.5—got 41.9. Good, because we're using Doubles for floating-point support.

Want to go the opposite way? I'll leave this for homework, but here's how you do it:

Private Function F2C, takes "f As Double," returns "As Double."

F2C = (f - 32) * 5 / 9

That's the reverse of the other one.

A trick I learned living in Buffalo, New York, which is across the border from Canada: when you see a temperature in Celsius, just double it and add 30. If it's 10 degrees Celsius: double it (20), add 30, so it's 50 (Fahrenheit). It's not exact, but it's close enough.

Now, let's build a button for the opposite conversion: Celsius to Fahrenheit and Fahrenheit to Celsius. Copy one button and its handler, update its caption, and update the code.

Right-click, Build Event, Code Builder. In there, you can set

Celsius = F2C(Fahrenheit)

Save it, close and open, and test it: enter 90 in Fahrenheit, go to Celsius—works; enter 18 in Celsius, go to Fahrenheit—works.

You could have used a subroutine for this, since we have actual fields on the form, but I wanted to teach you how to do your own custom functions.

Hope you learned something. Want to learn more about functions? In the extended cut for members only, you get public functions, where we can take those functions and put them in global modules, and then every form, report, and query in your database can use them, not just the form they're built in.

I will show you how to set a lower bound for your random numbers, so you can roll dice, for example, not just from 1 to 6 or 1 to 100, but from 10 to 15, for example. We'll build an actual dice roller, like the picture shows there.

I'll show you some loop tricks, where you can do a loop x times (like 3d6, rolling three six-sided dice), and nested loops so you can roll sets of those (like six sets of 3d6 to generate character stats).

That's all in the extended cut for members only, Silver Members and up.

How do you become a member? Click on the Join button below the video. Silver Members and up will get access to all of my extended cut TechHelp videos, live video and chat sessions, and other perks. After you click the Join button, you'll see a list of all the different perks that are available: Silver, Gold, Platinum, and Diamond.

Don't worry, these TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making them, and they'll always be free.

If you enjoyed this video, please give me a like and share. Click on the Subscribe button to subscribe to my channel and be notified of any new releases.

Check for additional resources down below the video. Click the Show More button and you'll see links to other videos, downloads, resources, lessons, and lots more.

If you have not yet tried my free Access Level 1 course, it's three hours long. You can find it on my website or my YouTube channel. If you like Level 1, Level 2 is just $1, and that's free for my members.

Want to have your question answered in a video just like this one? Visit my TechHelp page and you can post your question there. Also, be sure to stop by my Access Forum on my website, and look for me on Facebook, Twitter, and of course, YouTube.

Once again, my name is Richard Rost with AccessLearningZone.com. Thanks for learning with me, and I'll see you next time.

Quiz

Q1. What is the main difference between a subroutine (Sub) and a function in Access VBA?
A. Subroutines return values, while functions do not
B. Functions return values, while subroutines do not
C. Both subroutines and functions return values
D. Neither subroutines nor functions can accept parameters

Q2. What keyword would you use to allow a function or subroutine to be called from other forms or modules?
A. Local
B. Private
C. Static
D. Public

Q3. Which of the following is true about parameters in VBA functions or subroutines?
A. Parameters can only be strings
B. Parameters allow values to be passed into the routine for use
C. Parameters are always optional
D. Only subroutines can have parameters

Q4. How do you return a value from a custom VBA function in Access?
A. Use the Return statement
B. Use the Yield keyword
C. Assign the value to the function's name
D. Use the Print command

Q5. What is the purpose of using the Randomize statement before generating random numbers?
A. To set the minimum value
B. To initialize the random number generator for more randomness
C. To reset the database settings
D. To stop code execution

Q6. Why is the Int function used when creating a random number within a desired range?
A. To round up the number to the next integer
B. To convert the floating point random number into an integer
C. To add 1 to the result
D. To generate negative numbers

Q7. Given the function C2F = (c * 9 / 5) + 32, what does this function do?
A. Converts a Fahrenheit value to Celsius
B. Converts a Celsius value to Fahrenheit
C. Multiplies a number by 32
D. Adds 9 and 5 together

Q8. What is the advantage of using custom functions in Access VBA?
A. To avoid using built-in functions
B. To repeat the same code in multiple places
C. To encapsulate re-usable calculations and logic
D. To prevent code from being modified

Q9. If you want a function in VBA to take an input parameter and return double its value, how should you write it?
A. Private Function DoubleIt(x As Long) As Long: DoubleIt = x * 2: End Function
B. Private Sub DoubleIt(x): DoubleIt = x * 2: End Sub
C. Private Function DoubleIt() As Long: DoubleIt = x: End Function
D. Private Sub DoubleIt(): DoubleIt = x * 2: End Sub

Q10. If you want to call a custom function from a form event handler, what do you need to do?
A. Just reference the function name and provide necessary parameters
B. Convert the function to a macro
C. Use the RunCode command only
D. Functions cannot be called from event handlers

Q11. What type of value should you use for a temperature conversion function dealing with decimal numbers?
A. Integer
B. String
C. Double
D. Byte

Q12. Why is it good practice to use parentheses after function names when calling them, even if not technically required by VBA?
A. Parentheses are always required and it's a syntax rule
B. It helps to avoid ambiguity and is good programming practice
C. Parentheses increase performance
D. Parentheses are only required for subs

Q13. What happens if you mistype a built-in VBA function or statement in your code (such as writing Maximize instead of Randomize)?
A. The code runs, but with a warning
B. VBA automatically corrects it
C. You get a "Sub or function not defined" error
D. Nothing happens

Q14. What is suggested as a homework exercise in the video?
A. Writing a function to square a number
B. Making a Fahrenheit to Celsius conversion function
C. Creating a complex database without forms
D. Writing code to generate reports

Q15. What is a good reason to encapsulate code in a subroutine or function instead of repeating it in multiple places?
A. It makes the code shorter
B. It helps centralize logic, making maintenance and updates easier
C. It increases the chance of errors
D. It prevents the use of parameters

Answers: 1-B; 2-D; 3-B; 4-C; 5-B; 6-B; 7-B; 8-C; 9-A; 10-A; 11-C; 12-B; 13-C; 14-B; 15-B

DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.

Summary

Today's video from Access Learning Zone is focused on showing you how to create your own custom functions in Access VBA. This is something anyone working in Microsoft Access will find invaluable as it allows you to tailor the database to perform the specific calculations or actions you need, moving well beyond the basics offered by built-in functions such as Date and Now.

Let's begin with the basics in my standard Access database template. When you need to add a new action, you can place a command button on your form and modify its event code in the VBA editor—Visual Basic for Applications. Instead of using the Command Button Wizard, I recommend canceling out and manually setting up the button with your own custom code. Once you give your button a proper label, you can move on to editing its click event by choosing the Code Builder, which launches the VBA editor.

Within the editor, you'll see the event procedure for that particular button. To start, you can place a simple message box command so that, when you click the button, it displays a message. This is an example of a subroutine: a set of instructions that runs and performs an action, but doesn't return a value.

But let's talk about pulling that logic out into its own subroutine so you can call it more easily from multiple places in your project. By creating a private subroutine above the button's click event, you can then call this subroutine from the click event or even from other controls or events. This is good practice when you want to centralize frequently repeated processes, keeping your code organized and minimizing repetition.

Parameters can be added to both subroutines and functions, allowing you to pass information in or out. For example, you might pass a user's name into a greeting subroutine, or provide a date as an argument. Parameters make your code far more flexible.

Now, to move from subroutines to functions: the main difference is that a function returns a value. Built-in Access functions like Date do this—when you use Date, it gives back the current system date. You can create your own custom functions in VBA just as easily. For example, you could write a function that returns a constant number each time you call it, or better yet, a function that takes one or more arguments and returns a calculated result.

It is always a good habit to call your functions with parentheses, even if no arguments are required. This makes your code clearer and avoids confusion between functions and subroutines.

You can further expand your custom functions to do useful work such as doubling a value that's passed to it, converting temperatures, or generating random numbers. For instance, you might want a function to double whatever number the user provides. It's best to name your function according to its behavior, like DoubleTheNumber, so its purpose is clear.

One especially handy use case for custom functions is generating a random number, such as simulating a dice roll. To do this effectively, initialize the random seed with the Randomize command, then use the Rnd function to produce a random value. It's important to note how integer conversion and range adjustment work to ensure the output falls within the desired boundaries.

Beyond these basics, another common request is converting Celsius to Fahrenheit and vice versa. You can accomplish this with two custom functions: one for each conversion. By tying these functions to form controls, you can set up a simple calculator that updates values automatically when the user enters a new temperature.

The advantage of doing all this in function form, rather than as business logic tied to the form's events, is that you can easily reuse your functions wherever needed—ensuring consistency and reducing the chance of duplicated or inconsistent logic.

If you're interested in taking these concepts further, the Extended Cut for members covers several advanced topics. In the extended session, I will show you how to make your functions public so they can be shared across all forms, reports, and queries in your database. I'll also show you how to set both lower and upper bounds for random number generation, as well as how to roll multiple dice with code loops to simulate more complex scenarios, such as Dungeons & Dragons character generation. You'll also learn how to integrate these functions efficiently throughout your solution.

If you'd like to learn more or get step-by-step instructions for everything covered here, you can find the complete video tutorial on my website at the link below. Live long and prosper, my friends.

Topic List

Creating subroutines in Access VBA
Using the VBA editor and Code Builder
Assigning code to button click events
Understanding private vs public subroutines
Passing parameters to subroutines
Understanding functions and returning values
Using parameters in custom functions
Returning values from functions
Creating a function to double a number
Writing a function to generate random numbers
Using Randomize and Rnd in VBA
Building a simple dice roller function
Building a Celsius to Fahrenheit conversion function
Building a Fahrenheit to Celsius conversion function
Assigning custom functions to form controls
Using text boxes and buttons to trigger functions on forms