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