Hi Gord,

Thanks for your response. I was just discovering the "module" issue by
reading Microsoft's tutorial on Excel. As I opened my worksheet to
follow Microsoft's advice, I checked the Newsgroup and saw your
response. It worked great.

But where did my original function definition go? It seems like poor
software engineering to allow a function to be defined and saved by
user but have that function unavailable to the user. Do you have any
idea why Microsoft cooked up that scheme. Or am I missing something?

It also seems strange to me that the context menu for "Module1" allows
no renaming. Who wants Module1, Module2, ... ?

Nevertheless, your post explains why I could never see any evidence or
my original automation code being invoked. And that was the point of
posting this my question. I'm glad to have a solution.

Again, thank you, and

Best wishes,
Richard


Gord Dibben wrote:
Richard

Your workbook is open and active.

You opened VBE using Alt + F11.

OK so far.

Now, where did you place the Function?

It will go into a general module in your workbook.

Select your workbook/project in the Project Explorer and InsertModule.

Place the UDF in that module.

Your usage is correct =Area(A1,A2) in A3 returns 12


Gord Dibben MS Excel MVP


On 20 Jan 2007 20:57:00 -0800, "Richard"
wrote:

Hi,

I'm a retired Windows software developer, so I *shouldn't* be having
this problem, but ...

I had help in automating an Excel task, but I couldn't get that
procedure to work. Finally, I decided to test whether my problem was
inability to invoke user-defined functions. So I followed the tutorial
at http://www.fontstuff.com/vba/vbatut01.htm for creating a simple
multiplication function.

After

1. Creating a new Excel worksheet;
2. Accessing the Visual Basic Editor with Alt-F11;
3. Creating the following function:
Public Function Area(Length As Double, Width As Double)
Area = Length * Width
End Function
4. Saving the function;
5. Entering the following:
3, 4, =Area(A1, A2)
in cells 1, 2 and 3 of row A, respectively
6. Pressing TAB to cause cell A3 to be evaluated;

cell A3 gets replaced with:
#NAME?

I had reduced the security setting to Low, using Tools | Macro |
Security, before I started this test, and I run under an Administrator
account, so I don't think there's a security problem in this
situation.

I'm running WindowsXP-Pro/SP2 amd Office 2003 with up-to-date Microsoft
Windows Update and plenty of security software, so I don't think
there's a chance that there's a virus at play in this problem. I've
got 0.5GB RAM and over 100GB of free hard drive space, so I don't
think there's a resource problem either.

I think I'm missing something simple.

Any ideas?

Thanks in Advance,
Richard