Getting started programming a UDF.

J

John Herbster

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
 
D

Dick Kusleika

John

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

John Herbster

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

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
 
D

Dick Kusleika

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.
 
Top