Lesson#10 Functions in VBA

Question 1

What are the 3 types of functions you can use in VBA?

Answer 1

VBA Built-in, Excel Worksheet and User-Defined.

Question 2

How do you use a VBA built-in function?

Answer 2

By calling it.

Question 3

What types of arguments can you use when working with VBA built-in functions?

Answer 3

Named and Unnamed.

Question 4

How do you separate an argument’s name from it’s value when working with named arguments? (VBA Built-in)

Answer 4

:=

Question 5

What statement do you use to call a worksheet function?

Answer 5

Application.WorksheetFunction

Question 6

What are function procedures commonly known as?

Answer 6

User-Defined Functions (UDFs)

Question 7

What is the statement used for creating a UDF?

Answer 7

Function UDFName()
Statements
End Function

Question 8

When creating a UDF what must you always do?

Answer 8

Assign a value, because name of UDF is also name of variable.

Question 9

What is the statement used for assigning a data type to a UDF?

Answer 9

Function UDFName() As DataType

Question 10

Where should you generally store a UDF?

Answer 10

Within a standard module

Question 11

What are the 3 ways you can store a UDF?

Answer 11

• Calling from another procedure.
• Using a UDF in a worksheet/conditional formatting formula.
• Calling UDF from VBE window.

Question 12

What is difference between a normal UDF and a volatile one?

Answer 12

Volatile functions are recalculated whenever a cell changes.

Question 13

How do you mark a UDF as volatile?

Answer 13

By setting the Application.Volatile Method to True.

Question 14

How do you create UDFs that return errors?

Answer 14

Use the CVErr built-in function to convert error numbers to real formula errors.