Problem using newly created user function in Excel 2003

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

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

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

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

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

You need to understand that the rules were not written specifically to
please one user.

A UDF can only go in a regular module which you may rename, if desired.
A worksheet event macro can only go into the SHEET module of the desired
sheet. Or, it might be able to be put into the ThisWorkbook module if for
many sheets.
--
Don Guillett
SalesAid Software

"Richard" wrote in message
ups.com...
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

Richard

Sheet event code does not go into a general module.

With your workbook open and visible, right-click on the sheet tab and "View
Code"

Paste the worksheet_change code into that sheet module.


Gord

On 20 Jan 2007 23:08:16 -0800, "Richard"
wrote:

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

Hi Don,

Thanks for weighing in on my problem.

You need to understand that the rules were not written specifically to
please one user.

I acknowledge that. But I'm innocent :-) I was just trying to get
this functionality working.

A UDF can only go in a regular module which you may rename, if desired.
A worksheet event macro can only go into the SHEET module of the desired
sheet. Or, it might be able to be put into the ThisWorkbook module if for
many sheets.

When I read this, I didn't understand where the "SHEET module of the
desired sheet" was. It was only when I followed the directions from
the final response that I understood what you meant.

Even following those directions gave me a problem because I didn't
immediately know where the "sheet tab" was (because I use Excel so
infrequently and so sparingly.) But when I finally opened Sheet1's
context menu and then saw Sheet1 highlighted in under Microsoft Excel
Objects in VBA, all the "lights" went on!

And the Worksheet_Change module worked. Hallelujah!!

Again, thanks for your help.

Best wishes,
Richard

Hi Gord,

You got me started in the right direction and you came through now with
the last piece of the puzzle.

As I told Don in a preceding reply, I had problems following your very
clear guidance, but then everything cleared up for me.

Finally, I combined your ideas with some ideas I picked up from
Microsoft's documentation and wound up with the code below, which does
exactly what I wanted. Pretty nice code for something trivial, eh?
:-)

I greatly appreciate the fact that you hung around on this thread until
I got this going.

Yours truly,
Richard Muller

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 1 Then
n = Target.Row
Cells(n, "D").Value = Format(Now, "mm-dd-yyyy")
End If
enditall:
Application.EnableEvents = True
End Sub


Gord Dibben wrote:
Richard

Sheet event code does not go into a general module.

With your workbook open and visible, right-click on the sheet tab and "View
Code"

Paste the worksheet_change code into that sheet module.


Gord

On 20 Jan 2007 23:08:16 -0800, "Richard"
wrote:

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

Thanks for the feedback.

When entering sheet code I generally do not open the VBE.

I just right-click on the sheet tab and "View Code" as I explained in earlier
post.

Some people prefer to go to the VBE using Alt + F11 and finding the
workbook/project then expanding the MS Excel Objects, double-clicking on the
sheet module and pasting the code.

Seems like a long-way around to meg

And yes, the event code for entering the date is good.

Continue experimenting and never hesitate coming here for assistance.


Gord

On 21 Jan 2007 12:09:29 -0800, "Richard"
wrote:

Hi Gord,

You got me started in the right direction and you came through now with
the last piece of the puzzle.

As I told Don in a preceding reply, I had problems following your very
clear guidance, but then everything cleared up for me.

Finally, I combined your ideas with some ideas I picked up from
Microsoft's documentation and wound up with the code below, which does
exactly what I wanted. Pretty nice code for something trivial, eh?
:-)

I greatly appreciate the fact that you hung around on this thread until
I got this going.

Yours truly,
Richard Muller

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 1 Then
n = Target.Row
Cells(n, "D").Value = Format(Now, "mm-dd-yyyy")
End If
enditall:
Application.EnableEvents = True
End Sub


Gord Dibben wrote:
Richard

Sheet event code does not go into a general module.

With your workbook open and visible, right-click on the sheet tab and "View
Code"

Paste the worksheet_change code into that sheet module.


Gord

On 20 Jan 2007 23:08:16 -0800, "Richard"
wrote:

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