Getting started programming a UDF.
 

I am trying to write some DLL's to be accessed by Excel.
But I have never before even written an UDF for Excel
and cannot get the most basic stuff to work.

I opened a VB code window from Excel and copied into it the code below
(from a post by Kevin Stecyk).

Option Explicit

Public Function Test(dAmt As Double) As Double
Test = dAmt * 2
End Function

In the spreadsheet, I entered in a cell, =Test(5)

The answer was #Name?

What am I missing?

TIA, JohnH

Getting started programming a UDF.
 

John

Make sure it's in a standard module, and not the ThisWorkbook or Sheet
module.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"John Herbster" wrote in message
...

I am trying to write some DLL's to be accessed by Excel.
But I have never before even written an UDF for Excel
and cannot get the most basic stuff to work.

I opened a VB code window from Excel and copied into it the code below
(from a post by Kevin Stecyk).

Option Explicit

Public Function Test(dAmt As Double) As Double
Test = dAmt * 2
End Function

In the spreadsheet, I entered in a cell, =Test(5)

The answer was #Name?

What am I missing?

TIA, JohnH

Getting started programming a UDF.
 

"Dick Kusleika" wrote
Make sure it's in a standard module,
and not the ThisWorkbook or Sheet module.

When I open the saved XLS "book", I enable macros.
When I press Alt-F11, I see my Function Test code and
I see that the left-hand combobox shows "(General)".
I do not see ThisWorkbook or Sheet anywhere.

What else should I be looking for. Is there any registration
step required.

TIA, JohnH

Public Function Test(dAmt As Double) As Double
Test = dAmt * 2
End Function

In the spreadsheet, I entered in a cell, =Test(5)

The answer was #Name?

Getting started programming a UDF.
 

John

Press Cntrl+R to make the Project Explorer visible. In the PE, find your
project and expand it if it isn't already. Underneath the project name
should be a Microsoft Excel Objects folder which contains ThisWorkbook and a
module for each sheet in the workbook. You may also have a folder called
Modules. Under that should be any standard modules that you have like
Module1, Module2, etc. If you don't have this, then your code isn't in a
standard module. To create a standard module, right click the project name
and choose Insert-Module. Put your code in the resulting module and delete
it from wherever it was.

You can also look at the title bar in the VBE (Alt-F11 to open the VBE). If
it says something like

Microsoft Visual Basic - MyBook.xls - [Sheet1 (Code)]

then the macro is in the Sheet1 module and needs to be moved.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"John Herbster" wrote in message
...

"Dick Kusleika" wrote
Make sure it's in a standard module,
and not the ThisWorkbook or Sheet module.

When I open the saved XLS "book", I enable macros.
When I press Alt-F11, I see my Function Test code and
I see that the left-hand combobox shows "(General)".
I do not see ThisWorkbook or Sheet anywhere.

What else should I be looking for. Is there any registration
step required.

TIA, JohnH

Public Function Test(dAmt As Double) As Double
Test = dAmt * 2
End Function

In the spreadsheet, I entered in a cell, =Test(5)

The answer was #Name?

Getting started programming a UDF.
 

"Dick Kusleika" wrote
Press Cntrl+R to make the Project Explorer visible.

Dick, Thank you. I see that my code was under Sheet1. ...

To create a standard module, right click the project name
and choose Insert-Module.

I just now discovered this from reading Rubin's
http://www.Exceltip.com/ng-102.html
When I moved my code from Sheet 1 to Modules | Module 1,
it works.

Thanks again, JohnH