How to make your own user-defined function

Loading...

En provenance du cours de University of Colorado Boulder

Excel/VBA for Creative Problem Solving, Part 1

654 notes

University of Colorado Boulder

Excel/VBA for Creative Problem Solving, Part 1

654 notes

Cours 1 sur 3 dans Specialization Excel/VBA for Creative Problem Solving

"Excel/VBA for Creative Problem Solving, Part 1" is aimed at learners who are seeking to augment, expand, optimize, and increase the efficiency of their Excel spreadsheet skills by tapping into the powerful programming, automation, and customization capabilities available with Visual Basic for Applications (VBA). This course is the first part of a three-part series and Specialization that focuses on the application of computing techniques in Excel/VBA to solve problems. In this course (Part 1), you will: 1) create macros to automate procedures in Excel; 2) define your own user-defined functions; 3) create basic subroutines to interface with the user; 4) learn the basic programming structures in VBA; and 5) automate Excel’s Goal Seek and Solver tools and use numerical techniques to create “live solutions” to solve targeting and optimization problems. New to computer programming? The extremely intuitive and visual nature of VBA lends itself nicely to teaching and learning - what a fun way to learn to code! No prior knowledge in programming nor advanced math skills are necessary yet seasoned programmers will pick up new and creative spreadsheet problem solving strategies. After you have learned the basics of VBA, each module will introduce foundational and broad problems inspired by situations that you might encounter in the real world. To pass each module, you'll need to pass a mastery quiz and complete a problem solving assignment. This course is unique in that the weekly assignments are completed in-application (i.e., on your own computer in Excel), providing you with valuable hands-on training.

À partir de la leçon

User-Defined VBA Functions

In Week 2, you will learn basic VBA expression entry, how to create user-defined functions, how to convert functions to Add-Ins in Excel, how to borrow Excel's built-in functions, how to troubleshoot your VBA functions when they aren't working, and you'll learn about how to design your procedures in a modular fashion.

Week 2 preview1:54

How to write basic VBA expressions and use built-in functions6:51

How to make your own user-defined function4:35

Example of a user-defined function3:19

How to convert a user-defined function to an Excel Add-In4:24

Borrowing Excel functions4:07

How to troubleshoot your user defined functions1:06

Why you don't use input and message boxes in functions2:58

An introduction to modular programming5:34

Running a subroutine that resides in another file4:17

Rencontrer les enseignants

Charlie Nuttelman
Instructor
Chemical and Biological Engineering

There are lots of built-in functions in Excel.

For example, I can write in the square root,

there's a square root built-in function,

there's the average function, there's lots of these.

So in this screencast,

I'm going to show you how we can make a user-defined function.

So, a custom function in Excel using VBA.

So, we have a cone here and we want to compute the volume of this cone.

We want to be able to type in something like cone volume and

type in the radius of maybe four and the height of maybe six,

press enter, and it'll compute the volume using this formula.

So, right now there is no built-in function in Excel to do that.

So, let me show you how to do that.

I go ahead and insert a module.

Just like subs, functions are placed in new modules,

and they don't have to be new modules,

they're just placed in modules.

You can have multiple procedures within the same module.

So, I'm going to call this function cone volume.

It's going to only have two arguments.

So, it's going to have R and H. Now,

you don't dim your arguments inside the function as you do in subroutines.

Instead, in the parentheses here,

in the cone volume of function,

this is where you dim things.

So, this is where we would declare the radius as a double.

We could also do the same thing for the height as a double

and then the output of the cone volume function we want that to be a double.

So, we can put double at the end.

So, the output is going to be double and you don't redim.

Some people are tempted to write Dim R are as double inside the function,

but that actually won't work and it'll give you an error.

At the end of this screencast,

I'll show you what happens when you do that.

But if you need to define new variables inside the function,

for example I have pi in my cone volume function,

I can do that here.

So, just like we would in a subroutine.

So, Dim pi as double.

For now, I'm going to define pi is equal to four times the arc tangent of one.

There is no built in function for pi like there is in Excel.

You can borrow the pi function as I'll show in a subsequent screencast.

You can borrow it from Excel.

But for now, four times the arc tangent of one is a good way to get pi.

The output of a function is always the name of the function.

So, whatever you want to be output in the cell that we type the formula in Excel,

that has to be named,

that has to be a variable named cone volume inside of our ConeVol function.

So, I can do ConeVol equals,

now this is where I'm going to put the right hand side of the volume equation.

This is just equal to R pi that we've defined times radius

squared times R height that's input by the user,

divided by three, and there is our cone volume function.

Now, I can go over here and I can type in ConeVol of

let's say radius of four and a height of six and when I press enter,

it'll calculate the volume using this formula.

So, this works just like other functions in Excel.

I could do a case study using it,

I can drag it just like other formulas.

I can name this cell Rad for radius.

Then I can go up here and I can type in

my cone volume function with the radius that I've defined down here.

I want to keep a constant radius of four,

but I want to do a case study on the height changing from 0.5 to 0.4.

The second argument I can just use as my height there

and I can press Enter and then I can drag this down,

just like you would other formulas in Excel.

So, let me show you what happens if we dim R inside of the function.

Some people think that they to redim the arguments as I alluded to earlier,

dim R as double.

But when we try to do that then,

let me go back over here and press Enter,

it gives us an error and it says,

"Duplicate declaration and current scope."

So, you can't redim the arguments that you put into the function.

So, we need to go ahead and delete that.

So, this is how you make just a basic user-defined function in VBA.

Explorer notre catalogue

Rejoignez-nous gratuitement et obtenez des recommendations, des mises à jour et des offres personnalisées.

Coursera propose un accès universel à la meilleure formation au monde, en partenariat avec des universités et des organisations du plus haut niveau, pour proposer des cours en ligne.

© 2019 Coursera Inc. Tous droits réservés.

Télécharger dans l'App Store

Disponible sur Google Play