Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|