How do I write a simple User Defined Function (UDF) in Excel with VBA?

UDFs are a powerful tool customizing Excel. VBA is the easiest way to create them.

·
Citation
, XML
Authors

MS Excel 2010 – UDF in VBA:Preparation

First we need to open the Visual Basic for Applications Editor. Choose either ALT + F11 or to press the “Visual Basic” button of the Developer tab in the Ribbon:

MS Excel 2010 Developer tab

Fig. 1: MS Excel 2010 Developer tab

Visual Studio Editor in MS Excel 2010 Developer tab. Sometimes, this tab is not presented in Excel by default.


If the Developer tab is not enabled, you can open it following these instructions:

Open the Developer tab for Microsoft Excel 2010

  1. Start MS Excel.

  2. Click theFiletab.

  3. Click Options.

  4. In the categories pane, clickCustomize Ribbon.

  5. In the list of main tabs, selectDeveloper.

  6. Click OK to close theOptionsdialog box.

Open the Developer tab for Excel 2007

  1. Start the application.

  2. Click the Microsoft Office Button.

  3. Click Excel Options.

  4. In the categories pane, click Popular.

  5. Select Show Developer tab in the Ribbon.

  6. Click OK to close the Optionsdialog box.

Now, Insert the VB Code

Create a new Module:

Module creation

Fig. 2: Module creation in MS Excel 2010

Create a new module clicking on “Module” in the Visual Basic for Applications Editor.


Insert the following VBA Code

Function MySquare(X As Double)

MySquare = X * X

End Function

And you are done: You created your first MS Excel UDF and you can now start “MySquare”:

MS Excel UDF

Fig. 3: The first MS Excel UDF: MySquare

Use MySquare in the same way as any other build-in Excel function.


My function is different: It Will Return a Different Value for Every Call

What can you do if your function should return a different value for every calculation? Well, you can mark the function as volatile and it will be recalculated every time, e.g. every time you press F9. As an example, we want to look at a function which returns the time in seconds with millisecond accuracy:

Function PreciseTime()

 Application.Volatile

 PreciseTime = Timer

End Function

Just add Application.Volatile to the second line of your UDF and it will be recalculated every time.

1 Comment

Comments RSS
  1. Hayle

    At last! Someone who understands! Thanks for posintg!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 80 other followers