Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2007 VBA setting cells(x,y).value causes abnormal exit fromsubroutine/functions
Hi All,
I am having a difficult time trying to resolve this issue. I have a simple workbook, with a simple active x button. The onclick subroutine for the button is very simple. Private Sub CommandButton1_Click() Sheets("Sheet1").Cells(1, 1).Value = 12345 Sheets("Sheet1").Cells(1, 2).Value = "test" Sheets("Sheet1").Cells(11, 10).Value = "more testing" End Sub My problem is that when I click the button, only the first statement is executed, Sheets("Sheet1").Cells(1, 1).Value = 12345, then nothing. If I step through with debug, it highlights the first statement then when I step through (F8) it does set the value but then it immediately exits the subroutine. I can use other statements and functions with no problem, but the moment I try to alter the value of a cell it causes the sub or function to exit. Is there some configuration issue I am missing? I can't find any from google or the news groups about anyone having problem like this. Any help anyone can provide is greatly appreciated. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2007 VBA setting cells(x,y).value causes abnormal exit fromsubroutine/functions
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2007 VBA setting cells(x,y).value causes abnormal exit from subroutine/functions
Does this problem occur only when calc mode is automatic? If so and if you
have any UDFs it might be an interaction with them. -- Jim wrote in message ... | Hi All, | | I am having a difficult time trying to resolve this issue. | | I have a simple workbook, with a simple active x button. The onclick | subroutine for the button is very simple. | | Private Sub CommandButton1_Click() | Sheets("Sheet1").Cells(1, 1).Value = 12345 | Sheets("Sheet1").Cells(1, 2).Value = "test" | Sheets("Sheet1").Cells(11, 10).Value = "more testing" | End Sub | | My problem is that when I click the button, only the first statement | is executed, Sheets("Sheet1").Cells(1, 1).Value = 12345, then nothing. | If I step through with debug, it highlights the first statement then | when I step through (F8) it does set the value but then it immediately | exits the subroutine. | | I can use other statements and functions with no problem, but the | moment I try to alter the value of a cell it causes the sub or | function to exit. | | Is there some configuration issue I am missing? I can't find any from | google or the news groups about anyone having problem like this. | | Any help anyone can provide is greatly appreciated. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2007 VBA setting cells(x,y).value causes abnormal exit fromsubroutine/functions
On Mar 27, 3:33 pm, "Jim Rech" wrote:
Does this problem occur only when calc mode is automatic? If so and if you have any UDFs it might be an interaction with them. -- wrote in message ... | Hi All, | | I am having a difficult time trying to resolve this issue. | | I have a simple workbook, with a simple active x button. The onclick | subroutine for the button is very simple. | | Private Sub CommandButton1_Click() | Sheets("Sheet1").Cells(1, 1).Value = 12345 | Sheets("Sheet1").Cells(1, 2).Value = "test" | Sheets("Sheet1").Cells(11, 10).Value = "more testing" | End Sub | | My problem is that when I click the button, only the first statement | is executed, Sheets("Sheet1").Cells(1, 1).Value = 12345, then nothing. | If I step through with debug, it highlights the first statement then | when I step through (F8) it does set the value but then it immediately | exits the subroutine. | | I can use other statements and functions with no problem, but the | moment I try to alter the value of a cell it causes the sub or | function to exit. | | Is there some configuration issue I am missing? I can't find any from | google or the news groups about anyone having problem like this. | | Any help anyone can provide is greatly appreciated. I am not doing anything special, just a simple new excel workbook. I have never set calc mode so it is still what it is defaulted to from the base installation. There are no other user defined functions firing, I believe that to be true for two reasons. One it is a new blank workbook and I have defined none. Two, the subroutine aborts execution right after I set the value of the cell. Isn't VBA an event driven procedural language? meaning that two events cant really fire at the exact same time? When this subroutine fails it is if it is executing the statement "exit sub", at least as far as the debugger shows. I tried to do a simple test to see if it was exiting the subroutine or just crashing with no error. I took and made a simple subroutine and then called the offending subroutine. If the subroutine was exiting gracefully then the next statement after the call function would be executed. (see code) Private Sub CommandButton1_Click() MsgBox "Calling Subroutine" Call test MsgBox "Completed Subroutine" End Sub Private Sub test() Sheets("Sheet1").Cells(11, 10).Value = 54321 Sheets("Sheet1").Cells(12, 10).Value = 12345 End Sub When I ran this I only got the first msgbox, so what ever is interrupting the code is failing after the first statement in the test() subroutine: Sheets("Sheet1").Cells(11, 10).Value = 54321. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2007 VBA setting cells(x,y).value causes abnormal exit fromsubroutine/functions
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2007 VBA setting cells(x,y).value causes abnormal exit from subroutine/functions
One it is a new blank workbook
That rules out my idea. One thing I would try is starting Excel in safe mode just to be sure no add-in or something else is involved. From Start-Run run "Excel.exe /s" (no quotes, there is a space before the slash). Then try your code. -- Jim wrote in message ... On Mar 27, 3:33 pm, "Jim Rech" wrote: Does this problem occur only when calc mode is automatic? If so and if you have any UDFs it might be an interaction with them. -- wrote in message ... | Hi All, | | I am having a difficult time trying to resolve this issue. | | I have a simple workbook, with a simple active x button. The onclick | subroutine for the button is very simple. | | Private Sub CommandButton1_Click() | Sheets("Sheet1").Cells(1, 1).Value = 12345 | Sheets("Sheet1").Cells(1, 2).Value = "test" | Sheets("Sheet1").Cells(11, 10).Value = "more testing" | End Sub | | My problem is that when I click the button, only the first statement | is executed, Sheets("Sheet1").Cells(1, 1).Value = 12345, then nothing. | If I step through with debug, it highlights the first statement then | when I step through (F8) it does set the value but then it immediately | exits the subroutine. | | I can use other statements and functions with no problem, but the | moment I try to alter the value of a cell it causes the sub or | function to exit. | | Is there some configuration issue I am missing? I can't find any from | google or the news groups about anyone having problem like this. | | Any help anyone can provide is greatly appreciated. I am not doing anything special, just a simple new excel workbook. I have never set calc mode so it is still what it is defaulted to from the base installation. There are no other user defined functions firing, I believe that to be true for two reasons. One it is a new blank workbook and I have defined none. Two, the subroutine aborts execution right after I set the value of the cell. Isn't VBA an event driven procedural language? meaning that two events cant really fire at the exact same time? When this subroutine fails it is if it is executing the statement "exit sub", at least as far as the debugger shows. I tried to do a simple test to see if it was exiting the subroutine or just crashing with no error. I took and made a simple subroutine and then called the offending subroutine. If the subroutine was exiting gracefully then the next statement after the call function would be executed. (see code) Private Sub CommandButton1_Click() MsgBox "Calling Subroutine" Call test MsgBox "Completed Subroutine" End Sub Private Sub test() Sheets("Sheet1").Cells(11, 10).Value = 54321 Sheets("Sheet1").Cells(12, 10).Value = 12345 End Sub When I ran this I only got the first msgbox, so what ever is interrupting the code is failing after the first statement in the test() subroutine: Sheets("Sheet1").Cells(11, 10).Value = 54321. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2007 VBA setting cells(x,y).value causes abnormal exit fromsubroutine/functions
When I tried safe mode it worked, but it prompted me to enable active
x controls. It is good that it worked but now I need to find out why it does work in normal mode. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2007 VBA setting cells(x,y).value causes abnormal exit from subroutine/functions
So the next step is to find what workbook or add-in that loads with Excel is
causing the problem. You should look at Add-ins under Excel Options on the Office button menu. If you see any add-ins loading there you could unload them all or one at a time and test your macro. Also look in your XLSTART folder(s). Most anything there loads with Excel also. You could move them out (with Excel closed) and test your macro. -- Jim wrote in message ... When I tried safe mode it worked, but it prompted me to enable active x controls. It is good that it worked but now I need to find out why it does work in normal mode. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Page display is abnormal | Excel Discussion (Misc queries) | |||
How do I exit a cells edit mode? | Excel Discussion (Misc queries) | |||
JIS / ASC Functions in Excel 2007 | Excel Worksheet Functions | |||
Excel 2007 very slow to save files and cannot exit | Excel Discussion (Misc queries) | |||
Excel 2007 Exit Error | Excel Discussion (Misc queries) |