Very simple question on functions

[buffalo]

Hi,

I have still not figured out where to put the VBA code for functions in excel.

I click Alt + F11 and get to the VBA code part, I can write the Sub's and when i click the Run button they run well.

But not so for functions. ONLY when I open a new 'Module' and write my function in that, then i can work the fucntion from the worksheet.

QUESTIONS:
....Where is the right place to write fucntions?
....What is module useful for?

thanks

 

[earlyd]

I get the same thing with Alt F11.

To write a function, you can choose Tools...macro....macros.....then type in a macro name, click create and change the sub to a function.

Not very elegant but it works.

 

[phantom1975]

Hi,

I have still not figured out where to put the VBA code for functions in excel.

I click Alt + F11 and get to the VBA code part, I can write the Sub's and when i click the Run button they run well.

But not so for functions. ONLY when I open a new 'Module' and write my function in that, then i can work the fucntion from the worksheet.

QUESTIONS:
....Where is the right place to write fucntions?
....What is module useful for?

thanks

I guess the first thing you need to understand is what a module is. Think of a module as a container. There are worksheet modules, the ThisWorkbook module, and then the...um....module modules. I would recommend that you use the worksheet and ThisWorkbook modules for EVENT macros (macros that automatically run when a particular event occurs.) If you want a macro or function to be available to the entire workbook, you will put it in a module module. You might ask, "Why not put it in the ThisWorkbook module if you want code to be run anywhere in the workbook?" The ThisWorkbook module is designed for workbook events. The ThisWorkbook is something specific to Excel VBA. Regular VB wouldn't have a ThisWorkbook sort of thing. It would have the module modules though.

Did I make this more confusing than it needs to be?

 

[Iridium]

A bit of info here on UDF's:

http://www.ozgrid.com/VBA/Functions.htm