Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old October 14th 05, 05:22 AM
James4U2enjoy
 
Posts: n/a
Default Changing worksheet cells from within a function

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 noting 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()
Application.Worksheets("Sheet1").Cells(1, 1).Value = 100
End Sub

Sub Test2()
MsgBox (Test(123))
End Sub


  #2   Report Post  
Old October 14th 05, 02:16 PM
Dave Peterson
 
Posts: n/a
Default

A UDF called from a worksheet can't change a different cell. It can return a
value to the cell with the formula--and that's about all.

A function called not originated from a UDF in a cell can modify other cells.



James4U2enjoy wrote:

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 noting 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()
Application.Worksheets("Sheet1").Cells(1, 1).Value = 100
End Sub

Sub Test2()
MsgBox (Test(123))
End Sub


--

Dave Peterson


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Offset function referencing worksheet Jeff Excel Discussion (Misc queries) 1 September 11th 05 07:01 PM
excel contents cells on different worksheet like a comment Church Administrator Excel Discussion (Misc queries) 1 August 31st 05 08:48 PM
Worksheet VBA function - not recognized G Lykos Excel Worksheet Functions 4 August 23rd 05 05:50 AM
Excel 2003 worksheet name function? eddie_zoom Excel Worksheet Functions 3 June 16th 05 11:46 PM
Date & Time mully New Users to Excel 4 May 23rd 05 11:56 AM


All times are GMT +1. The time now is 08:10 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017