Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error writing value to cell - continued
I have a continuing perplexing problem. Using Excel 2000, I created a
test routine, in a module in Personal.xls: Sub TestAssign() ActiveSheet.Cells(17, 10).Value = "TT" End Sub When I run it in my currently open spreadsheet, it changes the value of cell J17 to "TT" and the subroutine finishes normally. That's as expected. I have another routine, a function defined in a module in the spreadsheet. It includes the same statement: ActiveSheet.Cells(17, 10).Value = "UU" There is a breakpoint on this line. Before execution, the arguments have the values 17 and 10, same as above. When I press F8 to execute that step, execution does NOT continue to the same line. Nothing changes in the active sheet (there is only one sheet in the file.) There's no error, it just stops. The only thing that seems to be different is that the assignment to ..Value works if the function is stored in a module in Personal.xls, and does NOT work if the function is in a module in the current spreadsheet. Is this some sort of bug in VB? -- Steven M - lid (remove wax and invalid to reply) A fool and his money are soon elected. -- Will Rogers |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error writing value to cell - continued
Argument seems to work fine for me!
"Steven M (remove wax and invalid to repl" wrote: I have a continuing perplexing problem. Using Excel 2000, I created a test routine, in a module in Personal.xls: Sub TestAssign() ActiveSheet.Cells(17, 10).Value = "TT" End Sub When I run it in my currently open spreadsheet, it changes the value of cell J17 to "TT" and the subroutine finishes normally. That's as expected. I have another routine, a function defined in a module in the spreadsheet. It includes the same statement: ActiveSheet.Cells(17, 10).Value = "UU" There is a breakpoint on this line. Before execution, the arguments have the values 17 and 10, same as above. When I press F8 to execute that step, execution does NOT continue to the same line. Nothing changes in the active sheet (there is only one sheet in the file.) There's no error, it just stops. The only thing that seems to be different is that the assignment to ..Value works if the function is stored in a module in Personal.xls, and does NOT work if the function is in a module in the current spreadsheet. Is this some sort of bug in VB? -- Steven M - lid (remove wax and invalid to reply) A fool and his money are soon elected. -- Will Rogers |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error writing value to cell - continued
You write function in your text.
Do you really mean that it's a function Function Test(somethingpassed as sometype) End function If you do mean that, then how is this function executed? If you're calling it from a worksheet cell, then these functions can only return values to the cell that holds the formula. If you're calling it from a Sub, then this isn't the problem. "Steven M (remove wax and invalid to reply)" wrote: I have a continuing perplexing problem. Using Excel 2000, I created a test routine, in a module in Personal.xls: Sub TestAssign() ActiveSheet.Cells(17, 10).Value = "TT" End Sub When I run it in my currently open spreadsheet, it changes the value of cell J17 to "TT" and the subroutine finishes normally. That's as expected. I have another routine, a function defined in a module in the spreadsheet. It includes the same statement: ActiveSheet.Cells(17, 10).Value = "UU" There is a breakpoint on this line. Before execution, the arguments have the values 17 and 10, same as above. When I press F8 to execute that step, execution does NOT continue to the same line. Nothing changes in the active sheet (there is only one sheet in the file.) There's no error, it just stops. The only thing that seems to be different is that the assignment to .Value works if the function is stored in a module in Personal.xls, and does NOT work if the function is in a module in the current spreadsheet. Is this some sort of bug in VB? -- Steven M - lid (remove wax and invalid to reply) A fool and his money are soon elected. -- Will Rogers -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error writing value to cell - continued
Just like I answered in the original thread:
A user defined function called by a worksheet cell can not change the value of any cell or alter the environment in any way. It can only return a value. (it can read other values and so forth, but can't change anything that could be visible). -- Regards, Tom Ogilvy "Steven M (remove wax and invalid to reply)" wrote in message ... I have a continuing perplexing problem. Using Excel 2000, I created a test routine, in a module in Personal.xls: Sub TestAssign() ActiveSheet.Cells(17, 10).Value = "TT" End Sub When I run it in my currently open spreadsheet, it changes the value of cell J17 to "TT" and the subroutine finishes normally. That's as expected. I have another routine, a function defined in a module in the spreadsheet. It includes the same statement: ActiveSheet.Cells(17, 10).Value = "UU" There is a breakpoint on this line. Before execution, the arguments have the values 17 and 10, same as above. When I press F8 to execute that step, execution does NOT continue to the same line. Nothing changes in the active sheet (there is only one sheet in the file.) There's no error, it just stops. The only thing that seems to be different is that the assignment to .Value works if the function is stored in a module in Personal.xls, and does NOT work if the function is in a module in the current spreadsheet. Is this some sort of bug in VB? -- Steven M - lid (remove wax and invalid to reply) A fool and his money are soon elected. -- Will Rogers |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error writing value to cell - continued
Thanks, it's much clearer now. And thinking about it this way, I can
see why -- If the worksheet had 100 cells that called this function, and any of them could change the value of cell A1, all of the values would be overwritten except the last one. Excel uses its own algorithms to set the order in which cells are recalculated, and the user can't control this, correct? So the results would be unpredictable and essentially random. Let me back up a level of abstraction. There is a range of variable size (now 10 x 10 for testing), with a value in each cell. Another 10x10 range contains a function that computes a result, which depends on the values in the first range, plus some other user-entered information. The function that is called 100 times in the second range generates some intermediate results. I wanted to "write" that data as a string into a third range and display it visually, but it doesn't work for the reason you describe. (I'm assuming that the restriction on changing values or the environment extends to all functions or subroutines that are called by the user-defined function, correct?) In effect, what I need is to return a matrix or array, containing more than one value as a result of the function call. The only alternative I see now is to write a second function and call it from the 100 cells in the third range. I hoped to take advantage of the intermediate calculations that are performed in the first function. Repeating these calculations in two functions would be less efficient. Can you suggest another way to store these intermediate results? Thanks again, Steven Je Sat, 15 Apr 2006 11:01:10 -0400, "Tom Ogilvy" skribis: Just like I answered in the original thread: A user defined function called by a worksheet cell can not change the value of any cell or alter the environment in any way. It can only return a value. (it can read other values and so forth, but can't change anything that could be visible). -- Steven M - lid (remove wax and invalid to reply) A fool and his money are soon elected. -- Will Rogers |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error writing value to cell - continued
You can't trick excel by having your function call a sub - that is correct.
Possibly you could have a static array (10x10) and store the values there. You could then possibly use the Calculate event to write that array to the worksheet (turn off events while writing to eliminate recursion). The reason this restiction is imposed is so Excel can build an optimal calculation sequence. If it calculated A1, then later calculated your cell/function which changed a value A1 was dependent on, then it would have to keep calculating until nothing else changed. The Excel calculation engine doesn't go in and analyze what your function does to figure out how to calculate it. Another alternative is to calculate your 10x10 result matrix with the calculate event (but turn off events while writing to eliminate recusion) -- Regards, Tom Ogilvy "Steven M (remove wax and invalid to reply)" wrote in message ... Thanks, it's much clearer now. And thinking about it this way, I can see why -- If the worksheet had 100 cells that called this function, and any of them could change the value of cell A1, all of the values would be overwritten except the last one. Excel uses its own algorithms to set the order in which cells are recalculated, and the user can't control this, correct? So the results would be unpredictable and essentially random. Let me back up a level of abstraction. There is a range of variable size (now 10 x 10 for testing), with a value in each cell. Another 10x10 range contains a function that computes a result, which depends on the values in the first range, plus some other user-entered information. The function that is called 100 times in the second range generates some intermediate results. I wanted to "write" that data as a string into a third range and display it visually, but it doesn't work for the reason you describe. (I'm assuming that the restriction on changing values or the environment extends to all functions or subroutines that are called by the user-defined function, correct?) In effect, what I need is to return a matrix or array, containing more than one value as a result of the function call. The only alternative I see now is to write a second function and call it from the 100 cells in the third range. I hoped to take advantage of the intermediate calculations that are performed in the first function. Repeating these calculations in two functions would be less efficient. Can you suggest another way to store these intermediate results? Thanks again, Steven Je Sat, 15 Apr 2006 11:01:10 -0400, "Tom Ogilvy" skribis: Just like I answered in the original thread: A user defined function called by a worksheet cell can not change the value of any cell or alter the environment in any way. It can only return a value. (it can read other values and so forth, but can't change anything that could be visible). -- Steven M - lid (remove wax and invalid to reply) A fool and his money are soon elected. -- Will Rogers |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error writing value to cell | Excel Programming | |||
Cell to Show "Continued" only if > Page 1 | Excel Worksheet Functions | |||
Error 50290: Error writing to Worksheet while using an ActiveX Control | Excel Programming | |||
Continued Cell Formulas Across an ODBC | Excel Programming | |||
Cell Formatting - Continued | Excel Programming |