Single stepping gives different result

[Len B]

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

 

[Chip Pearson]

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]

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?

 

[Chip Pearson]

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("Capex").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]

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??

 

[Chip Pearson]

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]

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 B]

I've solved it.

I added a DoEvents just before testing if the target spreadsheet is open or
not.