Excel VBA - Why does a function have to be in a module?

brothertruffle880 used Ask the Experts™
I created a function in a worksheet object and couldn't see it in the Intellisense (auto-list members).
But when I created it in a module, I saw it.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Martin Liss"Life would be infinitely happier if we could only be born at the age of eighty and gradually approach eighteen." - Mark Twain
Most Valuable Expert 2017
Distinguished Expert 2018
If it starts with Private Function. remove the "Private".
Lee W, MVPTechnology and Business Process Advisor
Most Valuable Expert 2013
You can get a fairly good explanation here:
Kesavan JeganarayananIT Consultant
Hope below explains your Question:

Modules are placeholders where you can write code that is accessible by any part of the Office application the module belongs to.

For example if you have a Form you can write VBA code that applies/functions on that form and that form only.
But if you want to write something (a Sub, a Function) that can be used by any Form or any object in your Office file/application then you add a module, place the Sub/Function there and make it Public to define it’s accessible by any other object.

VBA code is typed and viewed in the VBA Editor in what are called modules. A collection of modules is what is called a VBA project. In the VBA Editor, a VBA module when viewed resembles and behaves like a Word document in both basic organization and typing. When viewed, a VBA module will appear in its own window within the VBA Editor. Think of modules as organizational folders for your code, you add VBA modules as needed to a project to organize and run code.

Modules are made up of elemental building blocks called procedures. Procedures are used to organize and run your code in a module. Think of a paragraph in a word document and you are on the right track. You type Excel commands, variables, arrays, loops, logic, functions,... in your VBA module procedures and then run the procedure to control Microsoft Excel and perform various other tasks
Hardware Tester and Debugger
Hi there! :)

Functions are used to return calculated values and results based on the procedures in the function. Furthermore, If a Function procedure is typed into a Module in the Visual Basic Editor, it will then be available to be called from other VBA procedures or to be used in the worksheets of your Excel workbook.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial