creating functions

[Guest]

I am missing something fundamental in creating functions in Excel. Does
somebody have step by step, comprehensive instructions?

At this point, I somehow have a simple function that is recognized and
returns a 0 value, but does not appear in the VB Editor or in the list of
functions. Two other simple functions - same as the first with slightly
different names - appear in the list of functions and in the VB Editor, but
are not recognized when I insert the names into the corresponding spread
sheet.

Help would be appreciated,

Jim

 

[Ron Rosenfeld]

I am missing something fundamental in creating functions in Excel. Does
somebody have step by step, comprehensive instructions?

At this point, I somehow have a simple function that is recognized and
returns a 0 value, but does not appear in the VB Editor or in the list of
functions. Two other simple functions - same as the first with slightly
different names - appear in the list of functions and in the VB Editor, but
are not recognized when I insert the names into the corresponding spread
sheet.

Help would be appreciated,

Jim

Make sure your functions have unique names. (You can also use fully-qualified
names, but this is a PITA).

Make sure they are in a regular module, and not a workbook or worksheet module.
--ron

 

[Guest]

Generally speaking, the best way to make functions (UDFs) "findable" from the
VBE environment and "reachable" from the worksheet is to:

1. Insert them in standard modules
2. Declare them Public

Public Function bumpit(i As Integer) As Integer
bumpit = i + 1
End Function

 

[Guest]

Hi,

Thanks. Sorry if I sound dim, but how do I insert functions into standard
modules rather than workbook or worksheet modules?

Jim

 

[Guest]

Hi,

Thanks. Sorry if I sound dim, but how do I insert functions into standard
modules rather than workbook or worksheet modules?

Jim

 

[Guest]

User Defined Functions (UDFs) are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it.

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the UDF from Excel:

=myfunction(A1)

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Chip Pearson]

Jim,

See http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx for a
step-by-step guide to writing User Defined Functions in VBA.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"Creating Functions in Excel" <Creating Functions in
(e-mail address removed)> wrote in message
news:[email protected]...

 

[Ron Rosenfeld]

Hi,

Thanks. Sorry if I sound dim, but how do I insert functions into standard
modules rather than workbook or worksheet modules?

Jim

After opening the VB Editor, and selecting your project, you select
Insert/Module and enter your code into the window that opens.

In the subtree under your project, you will see something like:

VBAProject(Book2)
Microsoft Excel Objects
Sheet1
Sheet2
ThisWorkbook
Modules
Module1

Your code should be in "Module1"
--ron