Creating Custom Functions in Excel 2010

Excel provides many built-in functions that help you accomplish a lot of work. There are times that the built-in excel functions are not enough. If you ever hit that roadblock then you must actually create your own function. In order to do this you must have VBA knowledge. Below are instructions on how to do this with Excel 2010.

First you must enable the Developer menu. To do this, perform these steps:

1.Click on the “File” menu
2.Click “Options”
3.On the left hand side look for “Customize Ribbon”
4.You will now see two columns towards the left. On the right hand column look for the “Developer” check box and check it.
5.Click “Ok” at the bottom.
6.You will now see a “Developer” menu at the top

Now that the Developer menu has been enabled you must now open up the Visual Basic editor to create your new function.

1.Click on the “Developer” menu\
2.Click on “Visual Basic” on the left hand side of the Ribbon

The visual basic editor is now open. Here you will want to create a new module where you will write your own functions.

To create a new module do this:

1.Right click on any area in the section where you see “Microsoft Excel Objects.” This is located on the left, towards the top.
2.Click “Insert”
3.Click “Module”

A new window will appear where you can now start to write the code for you excel function. Let’s now create a simple function that will double the value of a number. Let’s call this function “doubleNum.” On the window that just appeared type the below text.

Function doubleNum (num)
doubleNum = num * 2
End Function

Save the document. You will be asked to rename the document to have an xlsxm extension because Microsoft does not allow custom code on regular documents anymore. After saving close the Visual Basic editor and try out your new function by click on a cell and enter “=doubleNum(5).” You will see the function as part of Excel’s auto complete. After entering the function you will see that the number 5 was doubled to 10.

Advertisements
This entry was posted in Software and tagged , , . Bookmark the permalink.

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