Excel VBA Functions
Learn how to create custom functions in Excel using VBA to enhance spreadsheet capabilities
Lesson Overview
Key Takeaway
Excel VBA functions allow you to create custom calculations that extend Excel's built-in capabilities, enabling powerful solutions tailored to your specific needs.
Welcome to Lesson 15 of our Visual Basic 6 Tutorial! In this lesson, you'll learn how to create custom functions in Excel using Visual Basic for Applications (VBA). These user-defined functions complement Excel's built-in functions and provide flexible solutions to complex problems.
15.1 Creating Excel VBA Functions
We can enhance the capabilities of MS Excel spreadsheets by creating customized functions to complement the built-in functions. While the built-in functions have certain limitations, these user-defined functions, also known as Visual Basic for Applications (VBA) functions aka macros, provide flexible and powerful solutions.
Commission Payment Structure
Table 15.1 shows the commission rates based on sales volume
| Sales Volume($) | Commissions |
|---|---|
| <500 | 3% |
| <1000 | 6% |
| <2000 | 9% |
| <5000 | 12% |
| >5000 | 15% |
For example, when a salesman attains a sale volume of $6000, they will be paid $6000 × 15% = $720.00.
Commission Calculation Function
A VBA function to calculate commissions based on the above structure can be written as follows:
Function Comm(Sales_V As Variant) As Variant If Sales_V < 500 Then Comm = Sales_V * 0.03 ElseIf Sales_V >= 500 And Sales_V < 1000 Then Comm = Sales_V * 0.06 ElseIf Sales_V >= 1000 And Sales_V < 2000 Then Comm = Sales_V * 0.09 ElseIf Sales_V >= 2000 And Sales_V < 5000 Then Comm = Sales_V * 0.12 ElseIf Sales_V >= 5000 Then Comm = Sales_V * 0.15 End If End Function
Commission Calculator:
Function Structure
VBA functions follow the same structure as regular VB functions but are specifically designed to work within Excel. They can accept parameters and return values that can be used directly in Excel formulas.
15.2 Using Microsoft Excel Visual Basic Editor
To create VBA functions in MS Excel, you can click on Tools, select Macro and then click on Visual Basic Editor as shown in Figure 15.1:
Upon clicking the Visual Basic Editor, the VB Editor windows will appear as shown in Figure 15.2. To create a function, type in the function as illustrated in section 15.1 above. After typing, save the file and then return to the Excel windows.
Using the Custom Function in Excel
In the Excel window, type in the titles Sales Volume and Commissions in any two cells. By referring to figure 15.3, key-in the Comm function at cell C4 and by referencing the value in cell B4, using the format Comm(B4). Any value appearing in cell B4 will pass the value to the Comm function in cell C4.
For the rest of the rows, just copy the formula by dragging the bottom right corner of cell C4 to the required cells. A nice and neat table that shows the commissions will automatically appear. It can also be updated anytime.
1 Create Function
Write your custom function in the VBA Editor using the same syntax as VB6 functions.
2 Save Workbook
Save your Excel workbook as a macro-enabled file (.xlsm) to preserve your VBA code.
3 Use in Spreadsheet
Call your function in any cell just like a built-in Excel function (e.g., =Comm(B4)).
Lesson Summary
In this lesson, you've learned how to create and use custom Excel VBA functions:
Custom Functions
Create VBA functions to extend Excel's capabilities
VB Editor
Access and use the Visual Basic Editor in Excel
Practical Application
Implement custom functions in spreadsheets
Maintenance
Update and maintain custom functions
Best Practice
Always save your Excel workbooks with custom VBA functions as macro-enabled files (.xlsm) to preserve your code. Use descriptive function names and include comments to make your code maintainable.
Practice Exercises
Test your understanding of Excel VBA functions with these exercises:
Exercise 1: Tax Calculator
Create a VBA function that calculates tax based on income brackets.
Exercise 2: Discount Function
Develop a function that applies tiered discounts based on purchase amount.
Exercise 3: Grade Calculator
Create a function that converts numerical scores to letter grades.
Exercise 4: Shipping Cost Calculator
Build a function that calculates shipping costs based on weight and destination.
Exercise 5: Date Formatter
Create a function that formats dates in a custom format for reports.
Further Learning
To learn more about Excel VBA, check out our comprehensive Excel VBA Tutorial for in-depth guides and examples.
Next Lesson
Continue your VB6 journey with Lesson 16: Arrays.
Related Resources
Visual Basic 6 Made Easy
Start your programming journey with Visual Basic 6. Learn how to build Windows applications step-by-step using an easy and beginner-friendly approach.
- Perfect for beginners
- Learn core programming concepts
- Build real VB6 applications
Visual Basic 2026 Made Easy
Upgrade to modern Visual Basic with VB.NET, .NET 10, and Visual Studio 2026. Build real-world applications with modern tools and AI-powered development.
- Modern VB.NET development
- Hands-on projects and real apps
- GitHub Copilot & AI integration
🚀 Ready for the Next Level?
After learning the basics with VB6 Made Easy and upgrading to VB2026 Made Easy, continue your journey into advanced professional development.
Advanced VB.NET Programming
Take your VB.NET skills to the next level. Learn advanced programming techniques, real-world architectures, and professional development practices using modern .NET.
Best For:
- After VB6 or VB.NET fundamentals
- Intermediate to advanced learners
- Developers building real-world systems
🚀 Migrating from VB6 to VB.NET
Still using or learning classic Visual Basic 6? This practical step-by-step guide shows you how to migrate legacy VB6 applications to modern VB.NET with Visual Studio 2026 and .NET 10.
VB6 to Modern VB.NET Made Easy
A practical step-by-step guide to migrating legacy Visual Basic 6 applications to VB.NET with Visual Studio 2026 and .NET 10.
- Designed for VB6 programmers and legacy app maintainers
- Clear migration guidance from classic VB to modern .NET
- Perfect bridge between VB6 fundamentals and VB.NET development
🚀 Move to Modern VB.NET
Visual Basic 6 is your foundation — but modern development uses VB.NET with .NET and Visual Studio 2026.
Start VB.NET Tutorial →