Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to make a function change other cells in worksheet
I have placed "=Test(123)" in cell B2. The function is supposed to return
the value 200 in cell B2 and put the value of 100 in cell A1, but instead returns the "#VALUE!" error and does nothing to cell A1. However, when the same function is called from within a sub (Test2), it works properly. How can I get the function to work properly when called from a worksheet? The function and subs appear below: Function Test(X As Double) Application.Worksheets("Sheet1").Cells(1, 1).Value = 100 Test = 200 End Function Sub Test1() 'This shows that the code below works from within a sub initiated by a command ' button Application.Worksheets("Sheet1").Cells(1, 1).Value = 100 End Sub Sub Test2() MsgBox (Test(123)) End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to make a function change other cells in worksheet
Hello James4U2enjoy, You need to move the function into a VBA module. Add a VBA module to your project using the VB editor. Under Insert choose Module, the default name is Module1. You can rename this to something more appropriate. The function will now be seen by Excel as a worksheet macro. Sincerely, Leith Ross -- Leith Ross ------------------------------------------------------------------------ Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465 View this thread: http://www.excelforum.com/showthread...hreadid=476109 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to make a function change other cells in worksheet
Another reason it won't work is that Function procedures aren't allowe to alter other cells in the way you're trying to do. The statemen "Application.Worksheets("Sheet1").Cells(1, 1).Value = 100" is no allowed in Function procedure, though it is a perfectly valid statemen in a Sub procedure. A Function procedure is allowed to 1) read values. These values can either be passed to the function fro the argument list or a statement such a "myvar=Application.Worksheets("Sheet1").Cells( 1, 1).Value" 2) perform calculations, make decisions, and otherwise process th information. It cannot alter cells in the worksheet or otherwise alte the operating environment (like setting cell format properties). 3) Then return a value to the calling cell via the function name, a you've done. I don't know specifically what you are trying to do, but it looks lik you either need to use a Sub procedure or use a different approach i your Function procedure -- MrShort ----------------------------------------------------------------------- MrShorty's Profile: http://www.excelforum.com/member.php...fo&userid=2218 View this thread: http://www.excelforum.com/showthread.php?threadid=47610 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to make a worksheet non editable ...even file extension change | Excel Discussion (Misc queries) | |||
IF statement to make cells change shading | Excel Discussion (Misc queries) | |||
How to make IF function not change cell value? | Excel Worksheet Functions | |||
How do i make a change in one worksheet affect the others | Excel Worksheet Functions | |||
How do I make Excel worksheet tabs change appearance when chosen . | Excel Discussion (Misc queries) |