Four User Defined Functions - probably one similar answer

[Paul Moles]

I want a set of "function" for convertions, eg if I enter =MPH() in a cell it
will assume the current value or referenced cell is KPH and will enter the
corrent MPH figure, conversely I would like to be able to do the reverse
=KPH(). Saves me having to enter conversion rate calculation each time. Also
would it be possible for =Centigrade() and =Farenheit(), although here the
conversion is more complex. (I generally use /5*9 +32).
Please advise whereabouts in the VBA these would need to be placed
Many Thanks
Paul

 

[Mike H]

Hi,

These should do

Function kphmph(rng As Range)
kphmph = rng.Value * 0.62137119
End Function

Function mphkph(rng As Range)
mphkph = rng.Value * 1.609344
End Function

Function CF(rng As Range)
CF = (rng.Value * 1.8) + 32
End Function

Function FC(rng As Range)
FC = (rng.Value - 32) * 0.555556
End Function


Mike

 

[Paul Moles]

Thanks Mike
Have stored in my Personal Workbook
Thanks
Paul

 

[Mike H]

I missed the 'where'.

Alt +F11 to open VB editor. Right click 'This Workbook' and insert module.
Paste them in on the right.

Mike

 

[Paul Moles]

Do you know how/where to enter the "Help" text that explains what a function
does?
Cheers
Paul

 

[Mike H]

Paul,

This is for Excel 2003 and you must be in the workbook where the macro is

Tools|Macro|Macros
You get a list of macros but don't worry that these UDF's don't appear
because Excel is aware of them
Type the name of the UDF you want help for and click Options and enter your
description. Click OK
Now you have to click Cancel (Yes Cancel) and your done.

Next time you use the function wizard you should get your help text

Mike

 

[Paul Moles]

Thanks Mike
That needs to be a highlighted answer as much of my searching said it wasn't
possible. Great.

 

[Gord Dibben]

Paul

If all you need is a line or two to describe the UDF try this.

Tools>Macro>Macros

Type in the name of the UDF and "Options"

You can type in a couple of descriptive lines and OK then Cancel

...........................................

To fully customize your User Defined Functions....

See Laurent Longre's website for FUNCUSTOMIZE add-in.

ZIP file also includes a demo file and a how-to-use file.

http://longre.free.fr/english/index.html

Ballon Tips are not available with Laurent's method.


Gord Dibben MS Excel MVP