Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro statement won't execute
I have a simple statement in a function that attempts to set the value in a
particular cell. Instead, the macro appears to just stop executing when I try to step into or over the statement with the debugger. Here's the function in a simplified form: Function aa_test_set(nsheet As Integer) As Integer Dim isheet As Integer isheet = nsheet Worksheets(3).Cells(10, 2).Value = isheet aa_test_set = 3 End Function The offending statement seems to be the one where ...Value = isheet. The function arguments are just used to allow stepping through the function, and have no particular significance (I think). When I enter the function into the calling cell, I provide an integer, such as 10, as the function argument. When I step into or over this statement, the debugger just quits doing anything (apparently the function returned prematurely). The highlighted statement in the debugger returns to white. The targeted cell value never gets set. The cell containing the function call displays "#VALUE!". The workbook has many worksheets, so 5 should not be out of range. (It doesn't help to refer to the target sheet as a quoted name instead of an index.) The target cell has format "General". If I try to run the macro without a breakpoint set, the VB window stops with the function name highlighted. I can step forward to the statement above, but the same behavior occurs. The problem occurs in more than one version of Excel (2002 is the latest I've tried). I've found an example statement that's very similar in the VBA help under the topic "Referring to Cells by Using Index Numbers". There is an error message in Excel 2002 next to the cell into which I entered the function saying "Error in value." However, none of the help suggestions helps me to recognize the problem. I'm probably missing something obvious (?!?), but I'm stuck. I'd greatly appreciate your help! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro statement won't execute
Hi,
A function can't directly change a cell other than the one it is called from so something like this works but isn't very useful perhaps you could expand on what you expect to be returned if you call your function with (say) 10 Function aa_test_set(nsheet As Integer) As Integer Dim isheet As Integer isheet = nsheet aa_test_set = isheet End Function Mike "w_c_mead" wrote: I have a simple statement in a function that attempts to set the value in a particular cell. Instead, the macro appears to just stop executing when I try to step into or over the statement with the debugger. Here's the function in a simplified form: Function aa_test_set(nsheet As Integer) As Integer Dim isheet As Integer isheet = nsheet Worksheets(3).Cells(10, 2).Value = isheet aa_test_set = 3 End Function The offending statement seems to be the one where ...Value = isheet. The function arguments are just used to allow stepping through the function, and have no particular significance (I think). When I enter the function into the calling cell, I provide an integer, such as 10, as the function argument. When I step into or over this statement, the debugger just quits doing anything (apparently the function returned prematurely). The highlighted statement in the debugger returns to white. The targeted cell value never gets set. The cell containing the function call displays "#VALUE!". The workbook has many worksheets, so 5 should not be out of range. (It doesn't help to refer to the target sheet as a quoted name instead of an index.) The target cell has format "General". If I try to run the macro without a breakpoint set, the VB window stops with the function name highlighted. I can step forward to the statement above, but the same behavior occurs. The problem occurs in more than one version of Excel (2002 is the latest I've tried). I've found an example statement that's very similar in the VBA help under the topic "Referring to Cells by Using Index Numbers". There is an error message in Excel 2002 next to the cell into which I entered the function saying "Error in value." However, none of the help suggestions helps me to recognize the problem. I'm probably missing something obvious (?!?), but I'm stuck. I'd greatly appreciate your help! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro statement won't execute
In the example with the statement
aa_test_set = isheet I'd expect return of the integer isheet, which is 10. In the function I specified, with aa_test_set = 3 I'd expect a return value of (integer) 3, and I'd like to have the outlying cell addressed by the Worksheets...Value statement to be set to the integer isheet. It's true the help pages showed this statement in a sub rather than a function. But, I thought a function was the same as a sub, except that the function is called from within a spreadsheet instead of from within a function? I didn't see anything pithy on this subject in Help, but there are so many usages of the term function that I could be missing it. If the distinction between sub and function is important, can I change a "third party" cell by calling the sub within a function? That seems odd. But, there must be some way to do this kind of thing? Thanks, - Bill - Bill "Mike H" wrote: Hi, A function can't directly change a cell other than the one it is called from so something like this works but isn't very useful perhaps you could expand on what you expect to be returned if you call your function with (say) 10 Function aa_test_set(nsheet As Integer) As Integer Dim isheet As Integer isheet = nsheet aa_test_set = isheet End Function Mike "w_c_mead" wrote: I have a simple statement in a function that attempts to set the value in a particular cell. Instead, the macro appears to just stop executing when I try to step into or over the statement with the debugger. Here's the function in a simplified form: Function aa_test_set(nsheet As Integer) As Integer Dim isheet As Integer isheet = nsheet Worksheets(3).Cells(10, 2).Value = isheet aa_test_set = 3 End Function The offending statement seems to be the one where ...Value = isheet. The function arguments are just used to allow stepping through the function, and have no particular significance (I think). When I enter the function into the calling cell, I provide an integer, such as 10, as the function argument. When I step into or over this statement, the debugger just quits doing anything (apparently the function returned prematurely). The highlighted statement in the debugger returns to white. The targeted cell value never gets set. The cell containing the function call displays "#VALUE!". The workbook has many worksheets, so 5 should not be out of range. (It doesn't help to refer to the target sheet as a quoted name instead of an index.) The target cell has format "General". If I try to run the macro without a breakpoint set, the VB window stops with the function name highlighted. I can step forward to the statement above, but the same behavior occurs. The problem occurs in more than one version of Excel (2002 is the latest I've tried). I've found an example statement that's very similar in the VBA help under the topic "Referring to Cells by Using Index Numbers". There is an error message in Excel 2002 next to the cell into which I entered the function saying "Error in value." However, none of the help suggestions helps me to recognize the problem. I'm probably missing something obvious (?!?), but I'm stuck. I'd greatly appreciate your help! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro statement won't execute
It might be useful to back up and pose the problem I'm trying to solve. I'd
like to be able to use the sheet number (index) of a sheet as a function argument so that the function can refer to cells using a Sheets(nsheet).Cells(i,j).Value construct. This seems to work if I specify nsheet explicitly. But that is not general enough to survive if a new sheet is added to the workbook. I can't find a property or method that allows me to set nsheet to the current sheet number so that I could offset it to refer to the previous or next sheet. I've got a kluge that works if I store the sheet number in a cell on each sheet, but this value has to be reset by a "renumber" operation if a sheet is inserted. Yuk! How would you go about this? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro statement won't execute
Take a look at this function. It returns the value from cell A1 of the next
sheet. Public Function Test() As Variant Application.Volatile Test = Application.Caller.Parent.Next.Cells(1, 1) End Function If there is no next sheet then it returns #Value -- HTH... Jim Thomlinson "w_c_mead" wrote: It might be useful to back up and pose the problem I'm trying to solve. I'd like to be able to use the sheet number (index) of a sheet as a function argument so that the function can refer to cells using a Sheets(nsheet).Cells(i,j).Value construct. This seems to work if I specify nsheet explicitly. But that is not general enough to survive if a new sheet is added to the workbook. I can't find a property or method that allows me to set nsheet to the current sheet number so that I could offset it to refer to the previous or next sheet. I've got a kluge that works if I store the sheet number in a cell on each sheet, but this value has to be reset by a "renumber" operation if a sheet is inserted. Yuk! How would you go about this? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro statement won't execute
Wow! Thanks for the info. That'll take me awhile to chew on...
- Bill "Jim Thomlinson" wrote: Take a look at this function. It returns the value from cell A1 of the next sheet. Public Function Test() As Variant Application.Volatile Test = Application.Caller.Parent.Next.Cells(1, 1) End Function If there is no next sheet then it returns #Value -- HTH... Jim Thomlinson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro statement won't execute
What Mike said is true, you can only return a value and NOT affect the
contents of another cell :-( I wish this were possible as I'm sure many have had this need. Anyway, here is a way to get the sheet info: --- Code Start --- Option Explicit Public Function aatestset(Optional DisplayIndex As Boolean) As Variant Application.Volatile If DisplayIndex Then aatestset = ActiveSheet.Index Else aatestset = ActiveSheet.CodeName End If End Function --- Code End --- Not sure if you want the Application.Volatile but if so, the function will be executed whenever there's a change event on the sheet (basically, though probably not the correct verbage) like changing a cell's contents or hitting the F9 function key (calculate sheet). Though you may think your method is kludgy, it's probably the only way you can accomplish it. Have you thought about having a VeryHidden sheet to use as a reference sheet? That way you can use it for storing information that the user doesn't neet to see nor allow them the ability to alter it ;-) Simply create a sheet and name it, then in the VBA Editor select the sheet and change its Visible property to xlSheetVeryHidden (use the Properties Window). -- Toby Erkson http://excel.icbm.org/ "w_c_mead" wrote: It might be useful to back up and pose the problem I'm trying to solve. I'd like to be able to use the sheet number (index) of a sheet as a function argument so that the function can refer to cells using a Sheets(nsheet).Cells(i,j).Value construct. This seems to work if I specify nsheet explicitly. But that is not general enough to survive if a new sheet is added to the workbook. I can't find a property or method that allows me to set nsheet to the current sheet number so that I could offset it to refer to the previous or next sheet. I've got a kluge that works if I store the sheet number in a cell on each sheet, but this value has to be reset by a "renumber" operation if a sheet is inserted. Yuk! How would you go about this? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro statement won't execute
Application.caller is the cell that the formula is in.
Parent is the worksheet that holds that cell. Next is the next sheet (that one is kinda obvious) Now that you have the next sheet you can grab a cell value from it. Application.volatile forces the formula to re-calc each time a calcualtion runs (this is necessary as there are no precedents to the formula). I personally would not use a formula like this as it changes as sheets are moved, added or deleted which could make the results seem unpredicatable especially since you can not readliy audit the formula to see what it is up to... But to each his own. -- HTH... Jim Thomlinson "w_c_mead" wrote: Wow! Thanks for the info. That'll take me awhile to chew on... - Bill "Jim Thomlinson" wrote: Take a look at this function. It returns the value from cell A1 of the next sheet. Public Function Test() As Variant Application.Volatile Test = Application.Caller.Parent.Next.Cells(1, 1) End Function If there is no next sheet then it returns #Value -- HTH... Jim Thomlinson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro statement won't execute
Thanks to both Jim and Toby... This works. The volatile might not be needed,
but it does no harm except perhaps to over-calculate the function. The VeryHidden sheet is an interesting thought, too, and one I'd never run across. - Bill "Air_Cooled_Nut" wrote: What Mike said is true, you can only return a value and NOT affect the contents of another cell :-( I wish this were possible as I'm sure many have had this need. Anyway, here is a way to get the sheet info: --- Code Start --- Option Explicit Public Function aatestset(Optional DisplayIndex As Boolean) As Variant Application.Volatile If DisplayIndex Then aatestset = ActiveSheet.Index Else aatestset = ActiveSheet.CodeName End If End Function --- Code End --- Not sure if you want the Application.Volatile but if so, the function will be executed whenever there's a change event on the sheet (basically, though probably not the correct verbage) like changing a cell's contents or hitting the F9 function key (calculate sheet). Though you may think your method is kludgy, it's probably the only way you can accomplish it. Have you thought about having a VeryHidden sheet to use as a reference sheet? That way you can use it for storing information that the user doesn't neet to see nor allow them the ability to alter it ;-) Simply create a sheet and name it, then in the VBA Editor select the sheet and change its Visible property to xlSheetVeryHidden (use the Properties Window). -- Toby Erkson http://excel.icbm.org/ |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro statement won't execute
This code is tied to the active sheet. What happens when you put this
function into sheet 1 but run a calculation from Sheet 2. It will look at the wrong sheet I suspect. The answer given will depend on what sheet is active the last time a calc was run. That could result in some very unpredictable behavior. -- HTH... Jim Thomlinson "Air_Cooled_Nut" wrote: What Mike said is true, you can only return a value and NOT affect the contents of another cell :-( I wish this were possible as I'm sure many have had this need. Anyway, here is a way to get the sheet info: --- Code Start --- Option Explicit Public Function aatestset(Optional DisplayIndex As Boolean) As Variant Application.Volatile If DisplayIndex Then aatestset = ActiveSheet.Index Else aatestset = ActiveSheet.CodeName End If End Function --- Code End --- Not sure if you want the Application.Volatile but if so, the function will be executed whenever there's a change event on the sheet (basically, though probably not the correct verbage) like changing a cell's contents or hitting the F9 function key (calculate sheet). Though you may think your method is kludgy, it's probably the only way you can accomplish it. Have you thought about having a VeryHidden sheet to use as a reference sheet? That way you can use it for storing information that the user doesn't neet to see nor allow them the ability to alter it ;-) Simply create a sheet and name it, then in the VBA Editor select the sheet and change its Visible property to xlSheetVeryHidden (use the Properties Window). -- Toby Erkson http://excel.icbm.org/ "w_c_mead" wrote: It might be useful to back up and pose the problem I'm trying to solve. I'd like to be able to use the sheet number (index) of a sheet as a function argument so that the function can refer to cells using a Sheets(nsheet).Cells(i,j).Value construct. This seems to work if I specify nsheet explicitly. But that is not general enough to survive if a new sheet is added to the workbook. I can't find a property or method that allows me to set nsheet to the current sheet number so that I could offset it to refer to the previous or next sheet. I've got a kluge that works if I store the sheet number in a cell on each sheet, but this value has to be reset by a "renumber" operation if a sheet is inserted. Yuk! How would you go about this? |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro statement won't execute
You are correct. Just throwing out ideas.
-- Toby Erkson http://excel.icbm.org/ "Jim Thomlinson" wrote: This code is tied to the active sheet. What happens when you put this function into sheet 1 but run a calculation from Sheet 2. It will look at the wrong sheet I suspect. The answer given will depend on what sheet is active the last time a calc was run. That could result in some very unpredictable behavior. -- HTH... Jim Thomlinson "Air_Cooled_Nut" wrote: What Mike said is true, you can only return a value and NOT affect the contents of another cell :-( I wish this were possible as I'm sure many have had this need. Anyway, here is a way to get the sheet info: --- Code Start --- Option Explicit Public Function aatestset(Optional DisplayIndex As Boolean) As Variant Application.Volatile If DisplayIndex Then aatestset = ActiveSheet.Index Else aatestset = ActiveSheet.CodeName End If End Function --- Code End --- Not sure if you want the Application.Volatile but if so, the function will be executed whenever there's a change event on the sheet (basically, though probably not the correct verbage) like changing a cell's contents or hitting the F9 function key (calculate sheet). Though you may think your method is kludgy, it's probably the only way you can accomplish it. Have you thought about having a VeryHidden sheet to use as a reference sheet? That way you can use it for storing information that the user doesn't neet to see nor allow them the ability to alter it ;-) Simply create a sheet and name it, then in the VBA Editor select the sheet and change its Visible property to xlSheetVeryHidden (use the Properties Window). -- Toby Erkson http://excel.icbm.org/ "w_c_mead" wrote: It might be useful to back up and pose the problem I'm trying to solve. I'd like to be able to use the sheet number (index) of a sheet as a function argument so that the function can refer to cells using a Sheets(nsheet).Cells(i,j).Value construct. This seems to work if I specify nsheet explicitly. But that is not general enough to survive if a new sheet is added to the workbook. I can't find a property or method that allows me to set nsheet to the current sheet number so that I could offset it to refer to the previous or next sheet. I've got a kluge that works if I store the sheet number in a cell on each sheet, but this value has to be reset by a "renumber" operation if a sheet is inserted. Yuk! How would you go about this? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto Execute Macro | Excel Worksheet Functions | |||
the macro don't execute | Excel Programming | |||
execute a macro | Excel Programming | |||
Execute Macro | Excel Discussion (Misc queries) | |||
Excel 2002 Macro to Execute SQL Statement | Excel Programming |