Why I get #Name? When I use a sub
 

From the Excel, I tried to use Visual Basic Edit to create some sub/functions.

Private Sub Worksheet_Calculate()
Worksheets("Sheet1").Activate
ActiveSheet.Cells(2, 1).Formula = "=Sum(A1:A5)"
End Sub

From the Sheet1, I enter "Worksheet_Calculate()" in a cell.
#Name? displays in the cell where I entered the sub name.
Why?

Why I get #Name? When I use a sub
 

you have a circular reference. it is putting your formula in a2 and your
wanting the sum of a1:a5

--


Gary


"ciccia" wrote in message
...
From the Excel, I tried to use Visual Basic Edit to create some
sub/functions.

Private Sub Worksheet_Calculate()
Worksheets("Sheet1").Activate
ActiveSheet.Cells(2, 1).Formula = "=Sum(A1:A5)"
End Sub

From the Sheet1, I enter "Worksheet_Calculate()" in a cell.
#Name? displays in the cell where I entered the sub name.
Why?

Why I get #Name? When I use a sub
 

Hi Ciccia,

A user defined function (UDF) can be entered in a worksheet cell. A sub can
not. Additionally, a UDF can only return a value to the cell in which it is
entered and can perform no other action.

You may wish to visit David McRitchie's 'Getting Started With Macros And
User Defined Functions' at:

http://www.mvps.org/dmcritchie/excel/getstarted.htm


---
Regards,
Norman



"ciccia" wrote in message
...
From the Excel, I tried to use Visual Basic Edit to create some
sub/functions.

Private Sub Worksheet_Calculate()
Worksheets("Sheet1").Activate
ActiveSheet.Cells(2, 1).Formula = "=Sum(A1:A5)"
End Sub

From the Sheet1, I enter "Worksheet_Calculate()" in a cell.
#Name? displays in the cell where I entered the sub name.
Why?

Why I get #Name? When I use a sub
 

You created a macro that can put a formula into a cell.
Unfortunately the formula includes itself in the calculation since
you are putting it into A2.

And the worksheet doesn't recognize it as anything, giving you #Name

What you want is a function that would work from the sheet

Try:
Function MyFunction()
' steps to perform
End Function

Remember that a function can only return a calculated value and will not
make any changes to the sheet.

(I don't have any hard experience writing functions - so can't help much
there)

--
steveB

Remove "AYN" from email to respond
"ciccia" wrote in message
...
From the Excel, I tried to use Visual Basic Edit to create some
sub/functions.

Private Sub Worksheet_Calculate()
Worksheets("Sheet1").Activate
ActiveSheet.Cells(2, 1).Formula = "=Sum(A1:A5)"
End Sub

From the Sheet1, I enter "Worksheet_Calculate()" in a cell.
#Name? displays in the cell where I entered the sub name.
Why?

Why I get #Name? When I use a sub
 

I didn't get an #name? error when I tried your formula in xl2003.

But I bet Steve Bell isolated the problem because your sub puts in a formula
that needs to be calculated.

Since I didn't get the error, I'm not sure if this helps:

Option Explicit
Private Sub Worksheet_Calculate()
Application.EnableEvents = False
Worksheets("Sheet1").Cells(2, 2).Formula = "=Sum(A1:A5)"
'or
'me.Cells(2,2).formula = "=sum(a1:a5")
Application.Calculate
Application.EnableEvents = True
End Sub

Is this event behind Sheet1 or a different worksheet?

If it's behind sheet1, then use the me.cells(...) line and delete line with
worksheets("sheet1").cells(...).

If it's behind a different sheet, then delete the me.cells(...) line. Notice
that I dropped the .activate line and worked directly on the range.

(Ps. I changed the .cells(2,1) to .cells(2,2) to avoid the circular reference
while testing. Change it to what you need.)



ciccia wrote:

From the Excel, I tried to use Visual Basic Edit to create some sub/functions.

Private Sub Worksheet_Calculate()
Worksheets("Sheet1").Activate
ActiveSheet.Cells(2, 1).Formula = "=Sum(A1:A5)"
End Sub

From the Sheet1, I enter "Worksheet_Calculate()" in a cell.
#Name? displays in the cell where I entered the sub name.
Why?

--

Dave Peterson