Excel Function returns #NAME? in worksheet

S

Stevie D

Hi, I'm trying to teach my self how to write functions in Excel VBA.

In a new workbook I've opened the VBA editor (ALT + F11)

After double clicking Sheet 1 in Project Explorer I've entered the following
code:

Function Multiply(a, b)
Multiply = a * b
End Function

After switching to Sheet 1 in the workbook and in cell A1 entering:

=Multiply(3,4)

I am getting #NAME? returned instead of 12.

Obviously the function is not being recognised. Can anyone please explain
what I'm doing wrong?
 
D

Dr. Stephan Kassanke

Stevie D said:
Hi, I'm trying to teach my self how to write functions in Excel VBA.

In a new workbook I've opened the VBA editor (ALT + F11)

After double clicking Sheet 1 in Project Explorer I've entered the
following
code:

Function Multiply(a, b)
Multiply = a * b
End Function

After switching to Sheet 1 in the workbook and in cell A1 entering:

=Multiply(3,4)

I am getting #NAME? returned instead of 12.

Obviously the function is not being recognised. Can anyone please explain
what I'm doing wrong?
Click to expand...

Stevie,

Functions have to be coded in modules, not in modules for sheets
(Insert/Module). The sheet modules are intended for catching events which
occur in the specific sheet.

Additionally it's good style to declare your variables, results and make the
function public. Your example would look like this:

Option Explicit

Public Function Multiply(a As Double, b As Double) As Double
Multiply = a * b
End Function

cheers,
Stephan
 
Top