MS Excel Tips & Tricks 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. Grau Andreas

Andreas Grau is a passionate financial engineer with the drive to improve the tools for complex economic or financial models. Therefore, he coinvented the methodology Computer Aided Finance, the Monte Carlo Simulation Tool Theta Suite and the MS Excel Speed-up tool Theta Proxy.

 https://computeraidedfinance.wordpress.com 30 11 2011 MS ExcelUDFVBA 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:

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

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

Open the Developer tab for Microsoft Excel 2010

Start MS Excel.

Click theFiletab.

Click Options.

In the categories pane, clickCustomize Ribbon.

In the list of main tabs, selectDeveloper.

Click OK to close theOptionsdialog box.

Open the Developer tab for Excel 2007

Start the application.

Click the Microsoft Office Button.

Click Excel Options.

In the categories pane, click Popular.

Select Show Developer tab in the Ribbon.

Click OK to close the Optionsdialog box.

Now, Insert the VB Code

Create a new Module:Create a new module clicking on "Module" in the Visual Basic for Applications Editor. Module creation

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":Use MySquare in the same way as any other build-in Excel function. MS Excel UDF

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.

29 4 2015

nice.expect more

30 7 2014

[…] […]

7 1 2012

At last! Someone who understands! Thanks for posintg!