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

342 notes

University of Colorado Boulder

Excel/VBA for Creative Problem Solving, Part 1

342 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:50

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 a lots of built in functions in Excel.

For example, I can write in the square root,

there's the square root built in function.

There is 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 ConVol and

type in the radius of maybe 4, and the height of maybe 6.

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'm going to go ahead and insert a module.

Just like subs, functions are placed in new modules, so I'm going to.

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 ConeVol.

It's going to have three arguments.

It's going to have R, h, sorry, it's only going to 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 parentheses here, in the ConeVol function,

this is where you deem things.

This is where we 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 ConeVol function, we want that to be a Double.

So we can put Double at the end.

So the output's going to be double.

And you don't ReDim, some people are tempted to

write Dim R as Double inside the function.

But that actually wont work and it'll give you an error.

And if 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.

And I'm going to, for now,

I'm going to define pi as equal to 4 times arctangent of 1.

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, 4 * Atn(1) 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 in Excel, that has to be named,

there has to be a variable named ConVol 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 our pi, that we've defined,

* R squared * h, that's input by the user, / 3.

And there is our cone volume function.

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

let's say radius of 4 and a height of 6.

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 ConeVol function with the radius that I've defined down here.

I want to keep a constant radius of 4, but

I want to do a case study on the height changing from 0.5 to 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 have 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.

[SOUND] It gives us an error that says duplicate declaration in 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.

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

Télécharger dans l'App Store

Disponible sur Google Play