Excel 2007 : User Defined Functions

I don't understand your question. Do you know how to write a UDF?

Yes, you can write any function you want in VBA:

Please Login or Register  to view this content.

=AddEm(1, 3.5)

UDFs in a workbook are stored in a standard code module, which is (are) part of the workbook. Other code can be in a sheet module or the ThisWorkbook module, or in a forms module.

My working definition of a macro is a VBA sub that takes no arguments, i.e., one that can be selected and run from the macros dialog (via Alt+F8).

For a basic place to start, google introduction to excel vba

VBA is the native automation language for Office -- it's what you use to write macros, UDFs, ...

I think any basic book would help you get started. I've used Walkenbach's VBA for Dummies for a class -- it wasn't bad.

Just start writing code and ask questions when you get stuck.

There should be no Function and End Function, thats makes no since with it popping up automatically.

Why not?

Have you never seen any programming language?

Why is it indented anyway ...

Indenting is purely cosmetic to improve readability.

... and you could leave it all in massive functions in Excel.

For this simple example, you certainly could.

Originally Posted by Rblack40

I am prepping for the Excel 2007 Expert Exam and need help in this area.

Who is prepping you for this exam? You're enrolled in a course somewhere? Based on your questions and frustrations, your comfort level with Excel and VBA terms is below Novice level, so an Expert Exam would be premature, surely.

Your course materials should walk you through learning these things slowly, step by step. It sounds like you're diving in all at once trying to absorb the entire environment and getting frustrated by it. I remember that's how I felt when I was first using the VBEditor.

But your frustration will subside over time as you spend more time in there working with more and more examples from your course materials. This is going to be a long process to get to the point you're ready for an expert's exam, so relax and keep working at it.


Here, we are more suited to assist you in understanding a specific thing, Ask a specific question (like you started to do originally). "I have no clue"... that's just you frustrating yourself. Give it time.

Hi RBlack40 - let me jump in here...
Reading the code given above - It is a User Defined Function called Discount. It takes two parameters or arguments to make it work. These are the quantity and price values. You know it is a function because it says "Function" instead of "Sub". Functions return stuff and Subs don't return anything. You need to have "Discount = something" later in the UDF to know what is returned.

The code starts and says if the quantity is greater than or equal to 100 then give the customer a discount. If that fails then the discount is zero. The amount of the discount is the quantity times the price times .1. It looks like this function gives a discount of 10% if the customer buys 100 or more of something.

The way you then use this FUNCTION in excel is to put something like this is a single Cell.
= Discount(200, 4.50)
press enter after putting this in a cell and see if it calculates correctly. then change the 200 to 50 in the above to see if it returns zero.

You can also put cell ranges in a UDF. You could have 200 in A1 and 4.50 in B1 and you could do...
=Discount(A1,B1)
This would give you the same answer as above..

User Defined Functions don't recalculate automatically unless you use Application.Volitile in the first line after the function statement.

I hope the above gets you over some of the learning problems with VBA and UDFs.

VBA is confusing to you because it's unfamiliar. Remember the first time you sat in a driver's seat? That unfamiliar feeling took some time to get over. But the feeling is irrelevant, ultimately. If you feel you must pass this test then you'll have to sit there and read through the codes and examples and ask questions and digest answers until it starts to click. You just do it.

There is a structure to writing VBA and some of it is absolute. Every sub or function will start with a declaration of it's name and possible required parameters.... then there will activity code, and the sub will end with a clearly marked "END SUB" or "END FUNCTION". Like a train has an engine in front starting you out and a caboose saying "it's over", and a random number of cars in between that is your activity code.

It's the same.