Hi Mike,

You can re name the modules. In the VB editor select the module you want to
re name and look at the properties [snip]

You're so right, Mike. Except the guys at Microsoft didn't follow the
nearly universal standard in Microsoft products and 3rd party Windows
applications: Instead of display "Properties" at the end of the context
menu, they stuck "VBAProject properties" near the top. No wonder I
didn't notice any "Properties" offering.

If I may, can I ask an additional question? On a different thread
today, somebody told me about "Private Sub Worksheet_Change(ByVal
Target As Range)" for automating some data-entry tasks. I then found
that documented in
http://www.microsoft.com/office/comm...2fsettings.xml

I copied the latter routine as the third procedure in Module1
associated with my worksheet, modified slightly to make column D as the
target for date, contingent on some of the columns A-C of the row being
populated. It doesn't seem to work ... not even if I format the D
column as Date.

How can I put some debugging code in there to determine whether that
procedure is even being invoked when I enter data in columns A-C of row
and then select a subsequent row? The code follows.

Thanks in advance for any additional help you may offer.

Best wishes,
Richard

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("A:C")) Is Nothing Then
Exit Sub
End If
n = Target.Row
Cells(n, "D").Value = Format(Now, "mm-dd-yyyy")
End Sub

Mike Rogers wrote:
Richard

You can re name the modules. In the VB editor select the module you want to
re name and look at the properties for that module and you can re name it
there.

Mike Rogers

"Richard" wrote:

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