Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Single stepping gives different result
I have a situation where I want to open a different workbook and go to the
next available row and paste some data there from the calling workbook. The following code works if I single step through it but not if it runs in real time. Why is that? Is there a better way to achieve what I am after? In real time it just opens the workbook to where it was last saved - a different sheet altogether and it doesn't select A21 in that sheet either. So that's 2 statements that do not execute in real time. If Not WBIsOpen(stExportBook) Then ' workbook isn't open so Workbooks.Open stExportFull, 0 ' so open it without updates Sheets("Capital Work (CAPEX)").Select Range("A21").Select End If TIA -- Len __________________________________________________ ____ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Single stepping gives different result
Where is the code located? If it is in the ThisWorkbook module or a Sheet
module, references resolve differently than if the code is in a standard module. You should fully qualify the objects. Also, remove any On Error statements you may have. It might be the case that perhaps the results depend on what sheet is active and an error is being ignored by an On Error Resume Next. -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "Len B" <gonehome@optusnet:con:au wrote in message ... I have a situation where I want to open a different workbook and go to the next available row and paste some data there from the calling workbook. The following code works if I single step through it but not if it runs in real time. Why is that? Is there a better way to achieve what I am after? In real time it just opens the workbook to where it was last saved - a different sheet altogether and it doesn't select A21 in that sheet either. So that's 2 statements that do not execute in real time. If Not WBIsOpen(stExportBook) Then ' workbook isn't open so Workbooks.Open stExportFull, 0 ' so open it without updates Sheets("Capital Work (CAPEX)").Select Range("A21").Select End If TIA -- Len __________________________________________________ ____ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Single stepping gives different result
Thanks Chip,
The code is in Module1. Also in Module1 is the WBIsOpen function - the only code anywhere with On Error. Module2 contains DelData() which resets certain ranges to empty. Sheet1 contains Worksheet_SelectionChange(ByVal Target As Range). All other sheets including ThisWorkbook are empty Private Function WBIsOpen(stWBName As String) As Boolean Dim w As Workbook On Error Resume Next Set w = Workbooks(stWBName) If Err = 0 Then WBIsOpen = True 'Looking fresh, I think I should have "Set w = Nothing" here??? Else WBIsOpen = False End If On Error GoTo 0 End Function As I see it, the Resume Next cannot fail to be undone by the final line of the function. In any case, I REMmed out the If WBIsOpen and matching End If so that the function would not execute at all. The result was entirely the same. Then, I even REMmed the OnError Resume Next, saved and re-launched. No change in result. Please correct me here if I misunderstand, but shouldn't the A21.Select statement operate on the active sheet whatever it is. As an experiment, I moved the cell pointer to B35 in every sheet in both workbooks and saved them both. Not one cell pointer moved when I executed the code. Initially, the code used to say Workbooks(stExportBook).Sheets(".. capex..").select and that didn't work so I pasted over it with code from the macro recorder to reassure myself about invisible (to me) typos. I am assuming that's what you mean by fully qualified. Any other thoughts? -- Len __________________________________________________ ____ "Chip Pearson" wrote in message ... Where is the code located? If it is in the ThisWorkbook module or a Sheet module, references resolve differently than if the code is in a standard module. You should fully qualify the objects. Also, remove any On Error statements you may have. It might be the case that perhaps the results depend on what sheet is active and an error is being ignored by an On Error Resume Next. -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "Len B" <gonehome@optusnet:con:au wrote in message ... I have a situation where I want to open a different workbook and go to the next available row and paste some data there from the calling workbook. The following code works if I single step through it but not if it runs in real time. Why is that? Is there a better way to achieve what I am after? In real time it just opens the workbook to where it was last saved - a different sheet altogether and it doesn't select A21 in that sheet either. So that's 2 statements that do not execute in real time. If Not WBIsOpen(stExportBook) Then ' workbook isn't open so Workbooks.Open stExportFull, 0 ' so open it without updates Sheets("Capital Work (CAPEX)").Select Range("A21").Select End If TIA -- Len __________________________________________________ ____ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Single stepping gives different result
Please correct me here if I misunderstand, but shouldn't the A21.Select
statement operate on the active sheet whatever it is. You are correct. As long as your code is in a standard code module like Module1, the references will roll up from Range to ActiveSheet to ActiveWorkbook. This is not the case with code in ThisWorkbook or a Sheet module. But since your code is in fact in a standard code module, this should not be an issue. As I see it, the Resume Next cannot fail to be undone by the final line of the function. That is correct. In fact, the final On Error Goto 0 is not necessary, but it is harmless. Initially, the code used to say Workbooks(stExportBook).Sheets(".. capex..").select You cannot select a cell on a sheet that is not the active worksheet. You must first Activate the workbook, then Select or Activate the sheeet (the difference between Select and Activate is clear when you have grouped sheets), then finally Select or Activate the Range. You can't do it all on one line of code. Just for fun, try commenting out Workbooks.Open stExportFull, 0 ' so open it without updates Sheets("Capital Work (CAPEX)").Select Range("A21").Select and replace it with the code Application.Goto _ Reference:=Workbooks(stExportBook).Worksheets("Cap ex").Range("A21"), _ Scroll:=True This *shouldn't* make a difference, but you are in an odd set of circumstances, so who knows, it might work. Beyond that, your code looks sound and I didn't see anything that looked troublesome. Again, just for fun, in VBA go to the Tools menu, choose Options, then the General tab, and change "Error Trapping" to "Break On All Errors", just to see if some error is getting ignored. The proper setting of this option for normal execution is "Break In Class Module". I'm out of ideas. -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "Len B" <gonehome@optusnet:con:au wrote in message ... Thanks Chip, The code is in Module1. Also in Module1 is the WBIsOpen function - the only code anywhere with On Error. Module2 contains DelData() which resets certain ranges to empty. Sheet1 contains Worksheet_SelectionChange(ByVal Target As Range). All other sheets including ThisWorkbook are empty Private Function WBIsOpen(stWBName As String) As Boolean Dim w As Workbook On Error Resume Next Set w = Workbooks(stWBName) If Err = 0 Then WBIsOpen = True 'Looking fresh, I think I should have "Set w = Nothing" here??? Else WBIsOpen = False End If On Error GoTo 0 End Function As I see it, the Resume Next cannot fail to be undone by the final line of the function. In any case, I REMmed out the If WBIsOpen and matching End If so that the function would not execute at all. The result was entirely the same. Then, I even REMmed the OnError Resume Next, saved and re-launched. No change in result. Please correct me here if I misunderstand, but shouldn't the A21.Select statement operate on the active sheet whatever it is. As an experiment, I moved the cell pointer to B35 in every sheet in both workbooks and saved them both. Not one cell pointer moved when I executed the code. Initially, the code used to say Workbooks(stExportBook).Sheets(".. capex..").select and that didn't work so I pasted over it with code from the macro recorder to reassure myself about invisible (to me) typos. I am assuming that's what you mean by fully qualified. Any other thoughts? -- Len __________________________________________________ ____ "Chip Pearson" wrote in message ... Where is the code located? If it is in the ThisWorkbook module or a Sheet module, references resolve differently than if the code is in a standard module. You should fully qualify the objects. Also, remove any On Error statements you may have. It might be the case that perhaps the results depend on what sheet is active and an error is being ignored by an On Error Resume Next. -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "Len B" <gonehome@optusnet:con:au wrote in message ... I have a situation where I want to open a different workbook and go to the next available row and paste some data there from the calling workbook. The following code works if I single step through it but not if it runs in real time. Why is that? Is there a better way to achieve what I am after? In real time it just opens the workbook to where it was last saved - a different sheet altogether and it doesn't select A21 in that sheet either. So that's 2 statements that do not execute in real time. If Not WBIsOpen(stExportBook) Then ' workbook isn't open so Workbooks.Open stExportFull, 0 ' so open it without updates Sheets("Capital Work (CAPEX)").Select Range("A21").Select End If TIA -- Len __________________________________________________ ____ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Single stepping gives different result
Thanks for your efforts Chip.
I have tried both "just for fun" ideas. 1. I typed the Application.GoTo line into the code in lower case as I usually do and look for anything not converted to camel case by the editor. reference:= and scroll:= remained in lower. It generated runtime error 9 - subscript out of range even after I corrected the quoted sheet name. 2. Option was initially set to "Unhandled". After I changed it to "Break All" I had to REM the call to WBIsOpen because the Set w = statement wouldn't let me past. Having avoided that, the result was unchanged. No other errors. What is diff between "Unhandled" and "Break in Class"? One other thing though - earlier in my experiments when I forgot to unREM the On Error, the Set w = threw a runtime error 9. I did the unREM, set next statement as the On Error line and pressed F5. It went on to work properly. That has been the only time it worked other than by single stepping. Is this a clue?? -- Len __________________________________________________ ____ "Chip Pearson" wrote in message ... Please correct me here if I misunderstand, but shouldn't the A21.Select statement operate on the active sheet whatever it is. You are correct. As long as your code is in a standard code module like Module1, the references will roll up from Range to ActiveSheet to ActiveWorkbook. This is not the case with code in ThisWorkbook or a Sheet module. But since your code is in fact in a standard code module, this should not be an issue. As I see it, the Resume Next cannot fail to be undone by the final line of the function. That is correct. In fact, the final On Error Goto 0 is not necessary, but it is harmless. Initially, the code used to say Workbooks(stExportBook).Sheets(".. capex..").select You cannot select a cell on a sheet that is not the active worksheet. You must first Activate the workbook, then Select or Activate the sheeet (the difference between Select and Activate is clear when you have grouped sheets), then finally Select or Activate the Range. You can't do it all on one line of code. Just for fun, try commenting out Workbooks.Open stExportFull, 0 ' so open it without updates Sheets("Capital Work (CAPEX)").Select Range("A21").Select and replace it with the code Application.Goto _ Reference:=Workbooks(stExportBook).Worksheets("Cap ex").Range("A21"), _ Scroll:=True This *shouldn't* make a difference, but you are in an odd set of circumstances, so who knows, it might work. Beyond that, your code looks sound and I didn't see anything that looked troublesome. Again, just for fun, in VBA go to the Tools menu, choose Options, then the General tab, and change "Error Trapping" to "Break On All Errors", just to see if some error is getting ignored. The proper setting of this option for normal execution is "Break In Class Module". I'm out of ideas. -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "Len B" <gonehome@optusnet:con:au wrote in message ... Thanks Chip, The code is in Module1. Also in Module1 is the WBIsOpen function - the only code anywhere with On Error. Module2 contains DelData() which resets certain ranges to empty. Sheet1 contains Worksheet_SelectionChange(ByVal Target As Range). All other sheets including ThisWorkbook are empty Private Function WBIsOpen(stWBName As String) As Boolean Dim w As Workbook On Error Resume Next Set w = Workbooks(stWBName) If Err = 0 Then WBIsOpen = True 'Looking fresh, I think I should have "Set w = Nothing" here??? Else WBIsOpen = False End If On Error GoTo 0 End Function As I see it, the Resume Next cannot fail to be undone by the final line of the function. In any case, I REMmed out the If WBIsOpen and matching End If so that the function would not execute at all. The result was entirely the same. Then, I even REMmed the OnError Resume Next, saved and re-launched. No change in result. Please correct me here if I misunderstand, but shouldn't the A21.Select statement operate on the active sheet whatever it is. As an experiment, I moved the cell pointer to B35 in every sheet in both workbooks and saved them both. Not one cell pointer moved when I executed the code. Initially, the code used to say Workbooks(stExportBook).Sheets(".. capex..").select and that didn't work so I pasted over it with code from the macro recorder to reassure myself about invisible (to me) typos. I am assuming that's what you mean by fully qualified. Any other thoughts? -- Len __________________________________________________ ____ "Chip Pearson" wrote in message ... Where is the code located? If it is in the ThisWorkbook module or a Sheet module, references resolve differently than if the code is in a standard module. You should fully qualify the objects. Also, remove any On Error statements you may have. It might be the case that perhaps the results depend on what sheet is active and an error is being ignored by an On Error Resume Next. -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "Len B" <gonehome@optusnet:con:au wrote in message ... I have a situation where I want to open a different workbook and go to the next available row and paste some data there from the calling workbook. The following code works if I single step through it but not if it runs in real time. Why is that? Is there a better way to achieve what I am after? In real time it just opens the workbook to where it was last saved - a different sheet altogether and it doesn't select A21 in that sheet either. So that's 2 statements that do not execute in real time. If Not WBIsOpen(stExportBook) Then ' workbook isn't open so Workbooks.Open stExportFull, 0 ' so open it without updates Sheets("Capital Work (CAPEX)").Select Range("A21").Select End If TIA -- Len __________________________________________________ ____ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Single stepping gives different result
I wish I had something else to offer, but I can't think of anything that
might help. What is diff between "Unhandled" and "Break in Class"? In normal code in a standard code module, "Break On Unhandled" and "Break In Class" work the same way. The difference is where the code breaks if there is an error in an object module (class module, userform, ThisWorkbook, etc). Suppose you have UserForm1 with the code Private Sub UserForm_Initialize() Debug.Print 1 / 0 ' force an error for demo End Sub Then in your regular Module1, you show the form with Sub AAA() UserForm1.Show End Sub The 1/0 code in the userform will cause an error 11 (div by 0) which Excel will treat as a trappable error. If you have "Break On Unhandled" set, the debugger will highlight the line "UserForm1.Show" as the source of the error. You could look at that line of code all day long and never find anything wrong with it. If you have "Break In Class" set, the debugger will break within the class on the line that actually caused the error, the 1/0 line. All" I had to REM the call I've noticed you use the terminology "REM" several times to mean commenting out code. Just FYI, you don't need to type the word "REM" to comment code. You can simply put an apostrophe at the start of the line of code. Also, if you need to comment out a long block of code, you can select the text and then click the Comment Block item on the edit command bar. Often, I will use conditional compilation to prevent a block of code from running. E.g., Debug.Print 1 Debug.Print 2 #If False Then Debug.Print 3 Debug.Print 4 Debug.Print 5 #End If Debug.Print 6 Here, only 1,2, and 6 will run. 3, 4, and 5 are excluded by the conditional compilation. Just FYI in case you didn't know about it. -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "Len B" <gonehome@optusnet:con:au wrote in message ... Thanks for your efforts Chip. I have tried both "just for fun" ideas. 1. I typed the Application.GoTo line into the code in lower case as I usually do and look for anything not converted to camel case by the editor. reference:= and scroll:= remained in lower. It generated runtime error 9 - subscript out of range even after I corrected the quoted sheet name. 2. Option was initially set to "Unhandled". After I changed it to "Break All" I had to REM the call to WBIsOpen because the Set w = statement wouldn't let me past. Having avoided that, the result was unchanged. No other errors. What is diff between "Unhandled" and "Break in Class"? One other thing though - earlier in my experiments when I forgot to unREM the On Error, the Set w = threw a runtime error 9. I did the unREM, set next statement as the On Error line and pressed F5. It went on to work properly. That has been the only time it worked other than by single stepping. Is this a clue?? -- Len __________________________________________________ ____ "Chip Pearson" wrote in message ... Please correct me here if I misunderstand, but shouldn't the A21.Select statement operate on the active sheet whatever it is. You are correct. As long as your code is in a standard code module like Module1, the references will roll up from Range to ActiveSheet to ActiveWorkbook. This is not the case with code in ThisWorkbook or a Sheet module. But since your code is in fact in a standard code module, this should not be an issue. As I see it, the Resume Next cannot fail to be undone by the final line of the function. That is correct. In fact, the final On Error Goto 0 is not necessary, but it is harmless. Initially, the code used to say Workbooks(stExportBook).Sheets(".. capex..").select You cannot select a cell on a sheet that is not the active worksheet. You must first Activate the workbook, then Select or Activate the sheeet (the difference between Select and Activate is clear when you have grouped sheets), then finally Select or Activate the Range. You can't do it all on one line of code. Just for fun, try commenting out Workbooks.Open stExportFull, 0 ' so open it without updates Sheets("Capital Work (CAPEX)").Select Range("A21").Select and replace it with the code Application.Goto _ Reference:=Workbooks(stExportBook).Worksheets("Cap ex").Range("A21"), _ Scroll:=True This *shouldn't* make a difference, but you are in an odd set of circumstances, so who knows, it might work. Beyond that, your code looks sound and I didn't see anything that looked troublesome. Again, just for fun, in VBA go to the Tools menu, choose Options, then the General tab, and change "Error Trapping" to "Break On All Errors", just to see if some error is getting ignored. The proper setting of this option for normal execution is "Break In Class Module". I'm out of ideas. -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "Len B" <gonehome@optusnet:con:au wrote in message ... Thanks Chip, The code is in Module1. Also in Module1 is the WBIsOpen function - the only code anywhere with On Error. Module2 contains DelData() which resets certain ranges to empty. Sheet1 contains Worksheet_SelectionChange(ByVal Target As Range). All other sheets including ThisWorkbook are empty Private Function WBIsOpen(stWBName As String) As Boolean Dim w As Workbook On Error Resume Next Set w = Workbooks(stWBName) If Err = 0 Then WBIsOpen = True 'Looking fresh, I think I should have "Set w = Nothing" here??? Else WBIsOpen = False End If On Error GoTo 0 End Function As I see it, the Resume Next cannot fail to be undone by the final line of the function. In any case, I REMmed out the If WBIsOpen and matching End If so that the function would not execute at all. The result was entirely the same. Then, I even REMmed the OnError Resume Next, saved and re-launched. No change in result. Please correct me here if I misunderstand, but shouldn't the A21.Select statement operate on the active sheet whatever it is. As an experiment, I moved the cell pointer to B35 in every sheet in both workbooks and saved them both. Not one cell pointer moved when I executed the code. Initially, the code used to say Workbooks(stExportBook).Sheets(".. capex..").select and that didn't work so I pasted over it with code from the macro recorder to reassure myself about invisible (to me) typos. I am assuming that's what you mean by fully qualified. Any other thoughts? -- Len __________________________________________________ ____ "Chip Pearson" wrote in message ... Where is the code located? If it is in the ThisWorkbook module or a Sheet module, references resolve differently than if the code is in a standard module. You should fully qualify the objects. Also, remove any On Error statements you may have. It might be the case that perhaps the results depend on what sheet is active and an error is being ignored by an On Error Resume Next. -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "Len B" <gonehome@optusnet:con:au wrote in message ... I have a situation where I want to open a different workbook and go to the next available row and paste some data there from the calling workbook. The following code works if I single step through it but not if it runs in real time. Why is that? Is there a better way to achieve what I am after? In real time it just opens the workbook to where it was last saved - a different sheet altogether and it doesn't select A21 in that sheet either. So that's 2 statements that do not execute in real time. If Not WBIsOpen(stExportBook) Then ' workbook isn't open so Workbooks.Open stExportFull, 0 ' so open it without updates Sheets("Capital Work (CAPEX)").Select Range("A21").Select End If TIA -- Len __________________________________________________ ____ |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Single stepping gives different result
I use "REM" because it is shorter than "commenting" in typing about it.
I use ' because it is shorter than REM in code. I did not know about block (un)commenting though - thanks. I did not know about #If. I presume that in real life you replace 'False' with some conditional expression. I am further on the track of this thing. <A Workbooks.Open stExportFull, 0 ' so open it without updates <B Sheets("Capital Work (CAPEX)").Select <C Msgbox "All Done" When I set breakpoint on <A then F5, it works properly. When I set breakpoint on <B or <C, it doesn't. So far, it appears that execution is abandoned after <A executes. Thanks for all your time Chip. I do appreciate it. -- Len __________________________________________________ ____ "Chip Pearson" wrote in message ... I wish I had something else to offer, but I can't think of anything that might help. What is diff between "Unhandled" and "Break in Class"? In normal code in a standard code module, "Break On Unhandled" and "Break In Class" work the same way. The difference is where the code breaks if there is an error in an object module (class module, userform, ThisWorkbook, etc). Suppose you have UserForm1 with the code Private Sub UserForm_Initialize() Debug.Print 1 / 0 ' force an error for demo End Sub Then in your regular Module1, you show the form with Sub AAA() UserForm1.Show End Sub The 1/0 code in the userform will cause an error 11 (div by 0) which Excel will treat as a trappable error. If you have "Break On Unhandled" set, the debugger will highlight the line "UserForm1.Show" as the source of the error. You could look at that line of code all day long and never find anything wrong with it. If you have "Break In Class" set, the debugger will break within the class on the line that actually caused the error, the 1/0 line. All" I had to REM the call I've noticed you use the terminology "REM" several times to mean commenting out code. Just FYI, you don't need to type the word "REM" to comment code. You can simply put an apostrophe at the start of the line of code. Also, if you need to comment out a long block of code, you can select the text and then click the Comment Block item on the edit command bar. Often, I will use conditional compilation to prevent a block of code from running. E.g., Debug.Print 1 Debug.Print 2 #If False Then Debug.Print 3 Debug.Print 4 Debug.Print 5 #End If Debug.Print 6 Here, only 1,2, and 6 will run. 3, 4, and 5 are excluded by the conditional compilation. Just FYI in case you didn't know about it. -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "Len B" <gonehome@optusnet:con:au wrote in message ... Thanks for your efforts Chip. I have tried both "just for fun" ideas. 1. I typed the Application.GoTo line into the code in lower case as I usually do and look for anything not converted to camel case by the editor. reference:= and scroll:= remained in lower. It generated runtime error 9 - subscript out of range even after I corrected the quoted sheet name. 2. Option was initially set to "Unhandled". After I changed it to "Break All" I had to REM the call to WBIsOpen because the Set w = statement wouldn't let me past. Having avoided that, the result was unchanged. No other errors. What is diff between "Unhandled" and "Break in Class"? One other thing though - earlier in my experiments when I forgot to unREM the On Error, the Set w = threw a runtime error 9. I did the unREM, set next statement as the On Error line and pressed F5. It went on to work properly. That has been the only time it worked other than by single stepping. Is this a clue?? -- Len __________________________________________________ ____ "Chip Pearson" wrote in message ... Please correct me here if I misunderstand, but shouldn't the A21.Select statement operate on the active sheet whatever it is. You are correct. As long as your code is in a standard code module like Module1, the references will roll up from Range to ActiveSheet to ActiveWorkbook. This is not the case with code in ThisWorkbook or a Sheet module. But since your code is in fact in a standard code module, this should not be an issue. As I see it, the Resume Next cannot fail to be undone by the final line of the function. That is correct. In fact, the final On Error Goto 0 is not necessary, but it is harmless. Initially, the code used to say Workbooks(stExportBook).Sheets(".. capex..").select You cannot select a cell on a sheet that is not the active worksheet. You must first Activate the workbook, then Select or Activate the sheeet (the difference between Select and Activate is clear when you have grouped sheets), then finally Select or Activate the Range. You can't do it all on one line of code. Just for fun, try commenting out Workbooks.Open stExportFull, 0 ' so open it without updates Sheets("Capital Work (CAPEX)").Select Range("A21").Select and replace it with the code Application.Goto _ Reference:=Workbooks(stExportBook).Worksheets("Cap ex").Range("A21"), _ Scroll:=True This *shouldn't* make a difference, but you are in an odd set of circumstances, so who knows, it might work. Beyond that, your code looks sound and I didn't see anything that looked troublesome. Again, just for fun, in VBA go to the Tools menu, choose Options, then the General tab, and change "Error Trapping" to "Break On All Errors", just to see if some error is getting ignored. The proper setting of this option for normal execution is "Break In Class Module". I'm out of ideas. -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "Len B" <gonehome@optusnet:con:au wrote in message ... Thanks Chip, The code is in Module1. Also in Module1 is the WBIsOpen function - the only code anywhere with On Error. Module2 contains DelData() which resets certain ranges to empty. Sheet1 contains Worksheet_SelectionChange(ByVal Target As Range). All other sheets including ThisWorkbook are empty Private Function WBIsOpen(stWBName As String) As Boolean Dim w As Workbook On Error Resume Next Set w = Workbooks(stWBName) If Err = 0 Then WBIsOpen = True 'Looking fresh, I think I should have "Set w = Nothing" here??? Else WBIsOpen = False End If On Error GoTo 0 End Function As I see it, the Resume Next cannot fail to be undone by the final line of the function. In any case, I REMmed out the If WBIsOpen and matching End If so that the function would not execute at all. The result was entirely the same. Then, I even REMmed the OnError Resume Next, saved and re-launched. No change in result. Please correct me here if I misunderstand, but shouldn't the A21.Select statement operate on the active sheet whatever it is. As an experiment, I moved the cell pointer to B35 in every sheet in both workbooks and saved them both. Not one cell pointer moved when I executed the code. Initially, the code used to say Workbooks(stExportBook).Sheets(".. capex..").select and that didn't work so I pasted over it with code from the macro recorder to reassure myself about invisible (to me) typos. I am assuming that's what you mean by fully qualified. Any other thoughts? -- Len __________________________________________________ ____ "Chip Pearson" wrote in message ... Where is the code located? If it is in the ThisWorkbook module or a Sheet module, references resolve differently than if the code is in a standard module. You should fully qualify the objects. Also, remove any On Error statements you may have. It might be the case that perhaps the results depend on what sheet is active and an error is being ignored by an On Error Resume Next. -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "Len B" <gonehome@optusnet:con:au wrote in message ... I have a situation where I want to open a different workbook and go to the next available row and paste some data there from the calling workbook. The following code works if I single step through it but not if it runs in real time. Why is that? Is there a better way to achieve what I am after? In real time it just opens the workbook to where it was last saved - a different sheet altogether and it doesn't select A21 in that sheet either. So that's 2 statements that do not execute in real time. If Not WBIsOpen(stExportBook) Then ' workbook isn't open so Workbooks.Open stExportFull, 0 ' so open it without updates Sheets("Capital Work (CAPEX)").Select Range("A21").Select End If TIA -- Len __________________________________________________ ____ |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Single stepping gives different result
I've solved it.
I added a DoEvents just before testing if the target spreadsheet is open or not. -- Len __________________________________________________ ____ "Chip Pearson" wrote in message ... I wish I had something else to offer, but I can't think of anything that might help. What is diff between "Unhandled" and "Break in Class"? In normal code in a standard code module, "Break On Unhandled" and "Break In Class" work the same way. The difference is where the code breaks if there is an error in an object module (class module, userform, ThisWorkbook, etc). Suppose you have UserForm1 with the code Private Sub UserForm_Initialize() Debug.Print 1 / 0 ' force an error for demo End Sub Then in your regular Module1, you show the form with Sub AAA() UserForm1.Show End Sub The 1/0 code in the userform will cause an error 11 (div by 0) which Excel will treat as a trappable error. If you have "Break On Unhandled" set, the debugger will highlight the line "UserForm1.Show" as the source of the error. You could look at that line of code all day long and never find anything wrong with it. If you have "Break In Class" set, the debugger will break within the class on the line that actually caused the error, the 1/0 line. All" I had to REM the call I've noticed you use the terminology "REM" several times to mean commenting out code. Just FYI, you don't need to type the word "REM" to comment code. You can simply put an apostrophe at the start of the line of code. Also, if you need to comment out a long block of code, you can select the text and then click the Comment Block item on the edit command bar. Often, I will use conditional compilation to prevent a block of code from running. E.g., Debug.Print 1 Debug.Print 2 #If False Then Debug.Print 3 Debug.Print 4 Debug.Print 5 #End If Debug.Print 6 Here, only 1,2, and 6 will run. 3, 4, and 5 are excluded by the conditional compilation. Just FYI in case you didn't know about it. -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "Len B" <gonehome@optusnet:con:au wrote in message ... Thanks for your efforts Chip. I have tried both "just for fun" ideas. 1. I typed the Application.GoTo line into the code in lower case as I usually do and look for anything not converted to camel case by the editor. reference:= and scroll:= remained in lower. It generated runtime error 9 - subscript out of range even after I corrected the quoted sheet name. 2. Option was initially set to "Unhandled". After I changed it to "Break All" I had to REM the call to WBIsOpen because the Set w = statement wouldn't let me past. Having avoided that, the result was unchanged. No other errors. What is diff between "Unhandled" and "Break in Class"? One other thing though - earlier in my experiments when I forgot to unREM the On Error, the Set w = threw a runtime error 9. I did the unREM, set next statement as the On Error line and pressed F5. It went on to work properly. That has been the only time it worked other than by single stepping. Is this a clue?? -- Len __________________________________________________ ____ "Chip Pearson" wrote in message ... Please correct me here if I misunderstand, but shouldn't the A21.Select statement operate on the active sheet whatever it is. You are correct. As long as your code is in a standard code module like Module1, the references will roll up from Range to ActiveSheet to ActiveWorkbook. This is not the case with code in ThisWorkbook or a Sheet module. But since your code is in fact in a standard code module, this should not be an issue. As I see it, the Resume Next cannot fail to be undone by the final line of the function. That is correct. In fact, the final On Error Goto 0 is not necessary, but it is harmless. Initially, the code used to say Workbooks(stExportBook).Sheets(".. capex..").select You cannot select a cell on a sheet that is not the active worksheet. You must first Activate the workbook, then Select or Activate the sheeet (the difference between Select and Activate is clear when you have grouped sheets), then finally Select or Activate the Range. You can't do it all on one line of code. Just for fun, try commenting out Workbooks.Open stExportFull, 0 ' so open it without updates Sheets("Capital Work (CAPEX)").Select Range("A21").Select and replace it with the code Application.Goto _ Reference:=Workbooks(stExportBook).Worksheets("Cap ex").Range("A21"), _ Scroll:=True This *shouldn't* make a difference, but you are in an odd set of circumstances, so who knows, it might work. Beyond that, your code looks sound and I didn't see anything that looked troublesome. Again, just for fun, in VBA go to the Tools menu, choose Options, then the General tab, and change "Error Trapping" to "Break On All Errors", just to see if some error is getting ignored. The proper setting of this option for normal execution is "Break In Class Module". I'm out of ideas. -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "Len B" <gonehome@optusnet:con:au wrote in message ... Thanks Chip, The code is in Module1. Also in Module1 is the WBIsOpen function - the only code anywhere with On Error. Module2 contains DelData() which resets certain ranges to empty. Sheet1 contains Worksheet_SelectionChange(ByVal Target As Range). All other sheets including ThisWorkbook are empty Private Function WBIsOpen(stWBName As String) As Boolean Dim w As Workbook On Error Resume Next Set w = Workbooks(stWBName) If Err = 0 Then WBIsOpen = True 'Looking fresh, I think I should have "Set w = Nothing" here??? Else WBIsOpen = False End If On Error GoTo 0 End Function As I see it, the Resume Next cannot fail to be undone by the final line of the function. In any case, I REMmed out the If WBIsOpen and matching End If so that the function would not execute at all. The result was entirely the same. Then, I even REMmed the OnError Resume Next, saved and re-launched. No change in result. Please correct me here if I misunderstand, but shouldn't the A21.Select statement operate on the active sheet whatever it is. As an experiment, I moved the cell pointer to B35 in every sheet in both workbooks and saved them both. Not one cell pointer moved when I executed the code. Initially, the code used to say Workbooks(stExportBook).Sheets(".. capex..").select and that didn't work so I pasted over it with code from the macro recorder to reassure myself about invisible (to me) typos. I am assuming that's what you mean by fully qualified. Any other thoughts? -- Len __________________________________________________ ____ "Chip Pearson" wrote in message ... Where is the code located? If it is in the ThisWorkbook module or a Sheet module, references resolve differently than if the code is in a standard module. You should fully qualify the objects. Also, remove any On Error statements you may have. It might be the case that perhaps the results depend on what sheet is active and an error is being ignored by an On Error Resume Next. -- Cordially, Chip Pearson Microsoft MVP - Excel, 10 Years Pearson Software Consulting www.cpearson.com (email on the web site) "Len B" <gonehome@optusnet:con:au wrote in message ... I have a situation where I want to open a different workbook and go to the next available row and paste some data there from the calling workbook. The following code works if I single step through it but not if it runs in real time. Why is that? Is there a better way to achieve what I am after? In real time it just opens the workbook to where it was last saved - a different sheet altogether and it doesn't select A21 in that sheet either. So that's 2 statements that do not execute in real time. If Not WBIsOpen(stExportBook) Then ' workbook isn't open so Workbooks.Open stExportFull, 0 ' so open it without updates Sheets("Capital Work (CAPEX)").Select Range("A21").Select End If TIA -- Len __________________________________________________ ____ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup multiple criteria for a single result | Excel Discussion (Misc queries) | |||
Multiple Lookups - SIngle Result | Excel Worksheet Functions | |||
Returning a result from a single charater within a cell | Excel Discussion (Misc queries) | |||
Stepping through gives different result than running | Excel Programming | |||
How to get value of variable when single stepping through code? | Excel Programming |