Creating a function 1

[GregLocock]

Please don't hurt me, I'm a VBA newbie.

Is there a way that I can write my own functions in VBA?

eg, suppose I wanted to define a function GREGFUN(x1,x2,x3)=x1*x2+x3 for use in a spreadsheet.

My guess is no, a new dll and/or AddIn would have to be written, but perhaps someone here knows different.







Cheers

Greg Locock

 

[gearguru]

Greg,
in Excel you open or create new "module" - look in the help menu or in some book how to - and you can write there your own functions. (For example I use my "inv" and "arcinv" functions when working with gear calculations)
But we have better experts in this forum, so let's see what they'll tell us.
gearguru

 

[Mutt]

I believe the user defined functions are what make Excel come alive!

In VBasic, insert a module. Then define your function :

Function GregFun(x1,x2,x3)
GregFun = x1*x2+x3
End Function

Passing in a range of values:

Function GregFun(xx as a Range)
Dim x(100)
For each one in xx
i=i+1
x(i)=one
next one
GregFun = x(1)*x(2)+x(3)
end function

[To the experts out there - yes, I know the code above is a bit sloppy - no variable definitions etc but still works and just to give an idea of how to use function]

Now can be called up from that workbook. I keep a whole library of commonly used functions in one workbook. Save it as a .xla file and install into Excel via the tools - addin - browse Then the functions are available for all your future workbooks. Add new functions by editing the .xla file in VBasic - just remember to save while in VBAsic mode. Lots of help in the VBAsic help file.

 

[GregLocock]

Ah, what I mean is, I'd like to be able to use GREGFUN in my spreadsheet, ie be able to call call GREGFUN from a cell. As I write this I realise it is unlikely.

eg

cell A1 contains =GREGFUN(A2,A3,A4)



Cheers

Greg Locock

 

[jproj]

GregLocock:

Mutt is right... you have to open VBA (new module) and create your own function in able to use it as you want:

Function GregFun(x1,x2,x3)
GregFun = x1*x2+x3
End Function

Then, you can enter GregFun(A2,A3,A4) in your spreadsheet and it will work like a built in function. If you want to make the function available to all workbooks, you need to save the macro into your "Personal Macro Workbook", which is hidden until you save something to it (easiest way to find it is to click on Tools, Macro, Record New Macro. A dialog box will pop up asking for a macro name and a location that you want to save it to. Save the macro in the personal macro workbook then click ok. Next, go to tools, macro, and click "Stop Recording". Then open your VBA editor (Alt-F11) and select the module in your "personal" workbook. Delete whatever is there and insert the function Mutt wrote above and save it... after that, you should be able to find your function listed in Excel's "paste function" dialog box.

Good Luck!

jproj

 

[pmover]

Greg,

in addition to jproj statements:

fyi, i've created several custom functions (i.e. recip compressor flow & hp calcs, EOS calcs, orifice flow calcs, centrifugal compressor head, cent. comp. hp, etc.) that i use to simplify workbook development. i make the functions available when needed.

the custom (ms calls them "user defined&quot functions are available when the module or add-in is loaded (such as personal module). the user can see the user-defined functions from the functions dialog box, scrolling down in the category side to user-defined, and the user-defined functions will be listed to the right.

the function creator can also create help files or descriptive text for the function and variable(s) when selected.
-pmover

 

[tomatge]

A word of warning to you all - whilst user defined functions can be very flexible and powerful, they can also be VERY SLOW.

By way of demonstration, try recreating a standard Excel function (e.g. x^3) as a user defined function, and pasting it through a worksheet. Then measure the performance of your own version as opposed to the native Excel formula.

The moral is that if you can do what you want with native Excel functions then do so, even if the resulting formula looks messy.