Creating Custom Functions in Excel - v14 (Course Id 2426)

• Who is this course for?
  This course is designed for CPAs, accountants, and other professionals needing Continuing Professional Education (CPE), especially those who use Excel in their daily work and have a basic understanding of IF statements.

• What is this course about or what problem does this course solve?
  The course teaches how to create and manage custom functions (User-Defined Functions) in Excel using the Visual Basic Editor, providing solutions for users who need to automate and reuse custom calculations without diving deep into programming.

• How can the knowledge from this course be used?
  Learners can apply the knowledge to build reusable, personalized Excel functions and create Add-ins so that these functions are available across multiple workbooks, improving efficiency and consistency.

• Why is this course important to a CPA, Accountant, or IRS Enrolled Agent?
  Understanding how to create custom Excel functions allows accounting professionals to streamline complex calculations, enhance productivity, and tailor Excel to specific business needs without relying heavily on external tools or manual processes.

• When is this course relevant or timely?
  This course is relevant for users of Excel 2010 and newer, particularly Excel 2019 and Excel 365, making it timely for professionals looking to leverage the latest Excel features as of 2024 and beyond.

• How is a course like this consumed or used?
  It is delivered as a NASBA QAS self-study text-based course that includes reading material, review questions, and a final exam, all accessible online without needing additional materials.

This course covers how to create a custom function (sometimes called an UDF or user-defined function) in Excel.  A custom function is a calculation that the user creates and names. This custom function is then stored in Excel and can recalled when it is needed. In this course, we will be using the Visual Basic Editor and using a couple of VBA keywords; however, this is NOT a VBA or programming course.  You will create a couple of different custom functions and then see how to create an Add-ins file so that these functions can be used in all your workbooks.

NASBA QAS - Text - NASBA Registry - 2426

Course Learning Objectives

• Recognize how to find and access the Visual Basic Editor
• Identify the components of custom functions and how to use them
• Recognize how to create, save and remove custom functions
• Recognize the implications of creating custom functions and how they work

Chapter 1 - Creating Custom Functions In Excel

OVERVIEW

DEVELOPER TAB

BASIC RULES

VISUAL BASIC EDITOR

CREATING A CUSTOM FUNCTION

CREATING A CUSTOM FUNCTION- Sales Variance PY to CY

DOCUMENTATION

SAVING CUSTOM FUNCTIONS

LET’S TRY-IT- ON YOUR OWN REVIEW

Solution

CREATING A CUSTOM FUNCTION – IF STATEMENT

COMPARING SYNTAX

EDITING AND DELETING A CUSTOM FUNCTION

EDIT

DELETE

Add-ins

CONCLUSION

Review Questions

Appendix A

Glossary