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
__________________________________________________ ____