Solved

code only works in debugger

Posted on 2012-03-14
7
202 Views
Last Modified: 2012-11-21
Hi Experts,

This is my VBA code. A part of it only works in debugger and not when run normally. That part has been bolded.

PFA the code.
I am trying to copy from 1 sheet to another.
Please help.

Thanks

Shilpi
Sub-bootstrap.docx
0
Comment
Question by:camper12
7 Comments
 
LVL 46

Expert Comment

by:Martin Liss
ID: 37722488
Put a DoEvents line after the first Selection.Copy and if that doesn't work put one after the second Selection.Copy
0
 
LVL 14

Accepted Solution

by:
Zack Barresse earned 500 total points
ID: 37722535
Hello,

No, you do not need a Do Events in your code anywhere.  You should, however, explicitly reference your worksheets, and I wouldn't rely on selecting anything, as it's not needed at all.  I've amended your code with such, but have no data to test with....


Sub bootstrap_new()
'
' bootstrap_new Macro
'
    Dim iRow                    As Integer
    Dim wsHistData              As Worksheet
    Dim wsInterZero             As Worksheet
    Dim wsBoot                  As Worksheet

    Set wsHistData = Sheets("Hist_DATA")
    Set wsInterZero = Sheets("Interpolated_Zero_Rate_new")
    Set wsBoot = Sheets("Bootstrapping")

    Application.ScreenUpdating = False
   
    For iRow = 5 To wsHistData.UsedRange.Rows.Count
   
        wsHistData.Cells(iRow, 1).Copy Destination:=wsBoot.Range("Date_Bootstrap")
        Calculate
        Range("P21:P43").Copy

        wsInterZero.Range("A5:A75").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
        wsInterZero.Range("A5:A75").Copy
        wsInterZero.Cells(iRow, 2) = wsBoot.Range("Date_Bootstrap")
        wsInterZero.Cells(iRow, 3).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Transpose:=True
       
    Next iRow
   
    Application.ScreenUpdating = True
    Application.StatusBar = False
    Application.CutCopyMode = False
   
End Sub

HTH

Regards,
Zack Barresse
0
 
LVL 33

Expert Comment

by:Norie
ID: 37722552
The ranges here have no worksheet references.
Range("P21:P43").Select
Selection.Copy

Sheets("Interpolated_Zero_Rate_new").Select
Range("A5:A75").Select

Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
  xlNone, SkipBlanks:=False, Transpose:=False

Open in new window

Which sheet(s) are you copying from/pasting to?

If you are copying from 'BootStrapping' to 'Interpolated_Zero_Rate_new' try this.
Sheets("BootStrapping").Range("P21:P43").Copy

Sheets("Interpolated_Zero_Rate_new").Range("A5:A75").PasteSpecial Paste :=xlPasteValuesAndNumberFormats

Open in new window

0
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 
LVL 46

Expert Comment

by:Martin Liss
ID: 37722955
Dear Mr. firefytr, it's yet to be seen if a DoEvents() is needed (note it's not Do Events), but in case you're not aware, what DoEvents does, among other things, is to temporarily yield to the processor within an event procedure. This allows the processor to catch up when it has to do external processing and it is not given enough time to complete the external process before the code moves on. It is often the reason that code will work when run in Debug but will not work when run normally.
0
 
LVL 14

Expert Comment

by:Zack Barresse
ID: 37723080
I know what DoEvents does,Martin, and how it's spelled.  Thanks.  :)  While DoEvents (I have taken my misplaced space out, just for you! :) ) might be preferrable in some cases, this is not one of them.  It's just a simple copy/paste routine.  Better to turn off some application attributes and let it run.  We're not letting other processes run outside the scope of the application and need to wait for the code to finish, so it's not an issue here, thus DoEvents is not needed.

Regards,
Zack Barresse
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37723337
Just so the whole back of 'tricks' is on the table :P

I can't say I disagree with anyone's comments.  I've found occassions when DoEvents/Wait works in special cases, as opposed to normal steps that shouldn't require it.

As I recently had this experience, I would suggest one more thing to do, and that's run Code Cleaner on the project.  Not only did my debugger kick in but when I hit F5 it was executing different lines than what was highlighted yellow, lol.

I don't know if Code Cleaner is need, but thought to tee it up if the other suggestions didn't get you there:
http://www.appspro.com/Utilities/CodeCleaner.htm

Dave
0
 
LVL 14

Expert Comment

by:Zack Barresse
ID: 37725105
Hi Dave.  I think it's a safe a assumption that 90%+ of the code we all see that *magically works* when you use DoEvents is poorly written code, and not actually due to what is being processed at that time.

In addition to Bovey's CodeCleaner, MZ-Tools is another fantastic add-in for the VBIDE (http://www.mztools.com/index.aspx).

In the above code, since it is a simple loop, copy, paste, we are not waiting on other processes or applications, thus DoEvents would not be warranted.  In fact, you'd probably see a performance hit instead.  Because we want the code to execute as efficiently as possible (I hope that's what we all want!), we'll want these processes and commands to go quickly, thus execute and move on, and not yield any execution to any process.  We don't want to yield to the operating system, we want control, as we're not opening any outside processes which may take up any CPU speed/efficiency.  It might be advantageous if the OP were doing extreme multi-tasking, running multiple forms/code at once, or accessing other applications/processes which needed to run/load during run-time.  But as I said, this is not the case here.  In short - leave DoEvents out of it.  :)

Regards,
Zack Barresse
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

806 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question