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:
Fig. 1: 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:
Fig. 2: Module creation in MS Excel 2010
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”:
Fig. 3: The first MS Excel UDF: MySquare
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 = TimerEnd FunctionJust add Application.Volatile to the second line of your UDF and it will be recalculated every time.
Computer Aided Finance - Money and Math 
Hayle
At last! Someone who understands! Thanks for posintg!