×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Creating a function

Creating a function

Creating a function

(OP)
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

RE: Creating a function

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

RE: Creating a function

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.

RE: Creating a function

(OP)
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

RE: Creating a function

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

RE: Creating a function

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") 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

RE: Creating a function

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Resources

Low-Volume Rapid Injection Molding With 3D Printed Molds
Learn methods and guidelines for using stereolithography (SLA) 3D printed molds in the injection molding process to lower costs and lead time. Discover how this hybrid manufacturing process enables on-demand mold fabrication to quickly produce small batches of thermoplastic parts. Download Now
Design for Additive Manufacturing (DfAM)
Examine how the principles of DfAM upend many of the long-standing rules around manufacturability - allowing engineers and designers to place a part’s function at the center of their design considerations. Download Now
Taking Control of Engineering Documents
This ebook covers tips for creating and managing workflows, security best practices and protection of intellectual property, Cloud vs. on-premise software solutions, CAD file management, compliance, and more. Download Now

Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close