How To Create Custom Function In Excel: VBA Code for Personalized Formulas

I still remember the first time I needed Excel to do something it couldn’t out of the box. That frustration led me to VBA, where I turned a 10-minute manual process into a one-click function.

Creating custom functions is simpler than most tutorials make it sound. You write a small piece of VBA code, save it in a module, and suddenly your spreadsheets can do exactly what you need. I’ve built dozens for clients over the years.

No special tools required—just Excel and a bit of curiosity. The first function I wrote handled currency conversions, and it still runs perfectly today. You’ll be surprised how much time you save once you start.

Works in any modern Excel version, and the skills transfer to other Office apps. Here’s how to get started.

What Youll Need

Instructions

Here’s the procedure I follow to get custom formulas working reliably in any workbook.

Open Excel and click File, then Options. In the left sidebar, select Customize Ribbon. On the right side, check the box for Developer under the Main Tabs list.

Click OK to save. The Developer tab now appears in the ribbon. This is your gateway to VBA and custom functions—without it, you can’t access the editor.

Click the Developer tab, then select Visual Basic. This launches the VBA editor in a separate window. Here’s the thing—if this is your first time, you’ll see an empty project tree on the left.

In the editor, click Insert from the menu bar, then choose Module. This creates a new module where your custom function will live. The module appears as Module1 under the Modules folder.

In the empty module window, type your function code. Start with Function followed by your function name and parentheses. For example: Function DOUBLEIT(num As Double) As Double. Press Enter to begin the function body.

Add your logic between the lines. For the example above, you’d type DOUBLEIT = num 2 and then End Function. Save the module by pressing Ctrl + S or clicking the save icon.

Click File, then Save As. In the save dialog, choose Excel Macro-Enabled Workbook (.xlsm) from the Save as type dropdown. This preserves your VBA code.

Name your file and click Save. If you save as a regular .xlsx, Excel will strip out all your VBA code, and your custom function will disappear. Real talk: I’ve lost hours of work by forgetting this step.

Tips and Common Mistakes

Small details can make or break your custom function—here’s what I’ve learned from years of trial and error.

Developer Tab First: Don’t skip Step 1. I’ve seen people waste hours trying to access VBA without enabling the Developer tab first. This single checkbox is the foundation for everything that follows—without it, you won’t even see the Visual Basic option in Step 2.

Case Sensitivity Matters: In Step 4, when testing your function, remember that Excel treats DOUBLEIT and doubleit as completely different functions. The #NAME? error you’ll see is often just a case mismatch. I keep a sticky note with my function names in all caps to avoid this exact problem.

Save Correctly: Step 5 is where most people lose their work. Saving as a regular .xlsx file will permanently delete your VBA code. Always choose .xlsm format—the macro-enabled version. Confession: I learned this the hard way after losing an entire afternoon’s work to a muscle-memory “Save As” click.

Module Organization: In Step 2, when you create Module1, give it a descriptive name by right-clicking and selecting Rename. Instead of Module1, Module2, use names like CustomFunctions or MathUtilities. This makes your code much easier to navigate as your project grows.

Frequently Asked Questions

Creating custom functions in Excel with VBA unlocks powerful flexibility, letting you tailor formulas to your exact needs. Start small—experiment with simple functions before tackling complex logic. Ready to dive deeper? Try building a function for a repetitive task and watch your efficiency soar! 🚀