Excel VBA Formulas, Functions, UDFs + Create Your Own AddIn!

Name: Excel VBA Formulas, Functions, UDFs + Create Your Own AddIn!
Rating: 4.3 (122 reviews)

Learn Built-In VBA Functions, Build Your Own Custom UDF (User-Defined Functions) and Learn To Share Them!

Created byDaniel Strong

Last updated 4/2019

English

English [Auto],

What you'll learn

Create custom Excel Functions
How To use parameters to allow users to specify how they want to use your functions
Understand real-life uses of user-defined functions (UDFs)
Learn Special built-in Functions that are only available in the Visual Basic editor
Utilize Regular Worksheet Functions in your VBA code
Breeze through Advanced Functions like VLookup and HLookup in VBA with our Troubleshooting tips
Master String manipulation & Randomization techniques in VBA
Discover the 3 Distinct Methods of sharing your functions and macros with coworkers, friends and family
Create your very own Excel Add-In that will make your formulas available to others.

Course content

4 sections • 25 lectures • 2h 22m total length

Introduction - Basic Excel Functions versus UDFs4:54
Compare basic Excel functions with user defined functions and learn to create your own VBA-based functions for use in worksheet cells and the VBA editor.
Resources Download0:03
LEFT, RIGHT Functions2:53
Explore the left and right functions in Excel and VBA, extracting the first or last characters from a string, with practical examples and basic variable use.
MID Function2:47
Explore how the mid function extracts a substring from a string in excel and vba. Use a starting position and length to grab the middle initial or other characters.
Your First Custom Function!4:46
Create your first custom function in Excel VBA, return a value, and use it in a worksheet cell; learn concatenation with ampersand and placing code in a standard module.
Calling a Custom Function in VBA Instead of In A Cell2:43
Learn to call a custom VBA function from code in the Visual Basic Editor rather than in a worksheet cell, and display the result with a message box.

What are Parameters?4:58
Explore how parameters drive Excel VBA functions by accepting a name as a string input and returning a greeting. Learn passing cell values and declaring string or variant parameters.
If you run into a Links Error...2:03
Resolve a links error by using the section 1 clean and section 2 finished files, enabling the add-in, editing links, and updating values.
Find Nth Word Function - Fun!12:39
Learn to build a custom VBA function that returns the nth word from a string using split, arrays, and a space delimiter in Excel VBA.
Optional Parameters and How to Tell If They're Present6:41
Explore optional parameters in Excel VBA, learn to use the IsMissing function to detect omitted arguments, and create an optional welcome function that gracefully handles missing inputs.
Optional Parameters Pt. 2 - Using Specific Params In Any Order7:58
explore optional parameters and named arguments in Excel VBA by using specific parameters in any order, testing with macros, and building readable code with explicit field names.
Custom LastRow Function (Works Dynamically for Any Sheet)8:14
Create a dynamic Excel VBA function that returns the last available row on any sheet by passing the sheet name as a parameter, using column A and xlUp.
Get The Last Row Dynamically Pt 2 - Which Column Parameter5:16
Advance a dynamic last row function in Excel VBA by adding an optional column parameter, defaulting to column A, to target different data columns.
Getting The Row Where Employees End and Subtotals Begin Plus Extra Function!14:54
Learn to locate the morning's row where subtotals begin using a VBA function. Build nested functions to calculate the number of employees, enabling dynamic, reusable Excel automation.
Using Worksheet Functions in VBA6:21
Explore using built-in worksheet functions in VBA through Application.WorksheetFunction, pulling numbers such as a random value between 1 and 1000 and the weekday of a date.
Evaluate Function5:59
Discover how the Excel VBA evaluate function unlocks powerful runtime calculations. Use brackets as a short key, compute sums, and work with named ranges for dynamic range references.
Sum Function in VBA3:14
Learn to implement the Excel sum function in VBA by calling Application.WorksheetFunction.Sum on a range (such as column C), using design mode and a button, with optional last-row logic.
Count Function in VBA2:13
Discover the count function in VBA for Excel, counting numbers and nonblanks. Compare count and counta, test results, and apply when building add-ins and UDFs.
VLookup and HLookup Tips in VBA13:02
Explore vlookup and hlookup in vba to retrieve table data using exact and approximate matches, including annual revenue. Learn to name ranges and handle errors to avoid disruptive popups.

Sharing Functions With Yourself or Other Computers!4:29
Learn practical ways to share VBA functions with yourself or colleagues, from copy-paste macros and exporting modules to using add-ins or a personal workbook for universal access.
Using Your Personal.XLSB Workbook To Share With Yourself - Plus Reference Trick9:24
Discover how to reveal the personal.xlsb personal workbook, name it for easy access, and reference it in formulas via the references feature to use the concat all function.
Creating An Excel AddIn and Ways To Use It!10:34
Learn to create an Excel add-in, save it as an xlam, and enable it in Excel to share macros and functions with coworkers, using intellisense and Application.Run.

Randomization with Rnd() or Randbetween() functions4:16
Discover how Excel's random and randbetween functions work, generating values from 0 to 1 or within set bounds, and implement them in VBA using application.worksheetfunction.randbetween with practical examples.
Thank You and Final Thoughts1:45
Join this journey into creating custom functions and add-ins in Excel, engage with a growing community by sharing tips, answering questions, and posting user-submitted innovations.
BONUS LECTURE0:03

Requirements

Need to Have Excel

Description

The last thing you want to do is waste your own time! Unfortunately, most Excel users find themselves creating humongous formulas that are prone to errors, nested if statements that will drive you nuts and generally looking for more efficient ways to perform routine tasks or look up data.

Microsoft Excel has some amazing built-in formulas, but they don't always have the special customizations that you need.

[Enter custom functions and formulas.]

With this course, you're going to master creating your own customized formulas using Excel VBA, the hidden programming language behind Microsoft Excel. You'll learn the ins and outs of using VBA to supercharge your productivity in Excel. Regardless of programming experience, if you're ready to step up your game and increase productivity and efficiency in your Excel usage, this course is for you.

We'll show you:

How To use parameters to allow users to specify how they want to use your functions as well as
Show you multiple examples of real-life uses of user-defined functions. We'll also show you
Special built in functions that are only available in the Visual Basic editor as well as
How you can use regular worksheet functions in your VBA code. We'll also review
Troubleshooting tips for advanced functions like VLookup and HLookup,
String manipulation & Randomization techniques and much more! We'll also show you
Three distinct methods of sharing your functions and macros with coworkers, friends and family Including
How to create your very own Excel Add-In that will make your formulas available to others.
Take the next step in your Excel mastery and grab your lifetime access for Excel VBA formulas functions and UDF's-and create your own ad in today!

Take the next step in your Excel mastery and grab your lifetime access pass for "Excel VBA Formulas, Functions & UDFs - and create your own Add-In today!

Who this course is for:

Beginners with Excel VBA wanting to expand their skills
Anyone wanting to learn how to create your own Excel Functions/Formulas
Do not take this course if you hate learning awesome stuff

Excel VBA Formulas, Functions, UDFs + Create Your Own AddIn!

What you'll learn

Explore related topics

Course content

Introduction6 lectures • 18min

Digging Deeper...13 lectures • 1hr 34min

Sharing Custom Macros3 lectures • 24min

Logical Functions3 lectures • 6min

Requirements

Description

Who this course is for: