Creating VBA function using Excel's built in functions

Hi,

I think I am trying to do something quite simplistic, however my VBA writing skills are pretty poor.
I perform a calculation almost daily and it would be nice to have my own user defined function so that I don't have to write it out everytime..

I want a code that will allow me to select a group of cells, calculate both the Average and the Standard Deviation and then use the formula:

(StDev-Average)*100

Can anyone help? I've had a couple of goes but don't seem to be getting it right.

Thanks,
Toby

It's hard to give specific suggestions when we don't know what you've tried and why it has not worked. I'd suggest you start by search VBA help for "using Excel functions in VBA" http://msdn.microsoft.com/en-us/libr.../ff197608.aspx

I don't really know anything about VBA, so when I said I have tried something, It was guesswork, I thought I'd see if it worked with something as simple as:

Function CV (StDev As String, Average As String)
CV = (StDev-Average)*100
End Function

It doesn't appeat to calculate the StDev and Average here before using my formula.

I want to be able to use current functions Excel has built in (the StDev and Average functions) but don't know how to embed them into VBA programming so that the VBA performs the two functions on the selected range before using the results in my formula. I've looked up many examples online but can't find my answer, which is why I've come here.

Is there a specific part of the help file that I suggested that you find confusing or difficult to understand?

I might have the wrong end of the stick, but is the help page you sent me to for making Macros with specified ranges for calculating something? I want a function that can be called up like a formula (eg VLOOKUP, MIN, AVERAGE etc) on a range that you specify at the time. I use this formula regularly, but on different pieces of data within a worksheet.

I had thought this would be a simple script to write, to call up and use two built in functions, I guess I may have been wrong.

The help page I sent was to explain how to use Excel's built in worksheet functions in VBA. You are correct that the example they gave was for a Sub procedure (aka macro) that used a message box to return the result. The same basic approach can be used for returning the result to a spreadsheet. Adapting their example to a UDF (function procedure called from a spreadsheet):

Please Login or Register  to view this content.

From there, you will need to replace "Min" with the functions you are interested in and include the additional operations needed for your calculation.