VBA Debugging Tips Needed
-
2015 is my year for finally getting into development that my employer wants me to learn...been troubleshooting a VBA heavy sheet and got a few questions about debugging:
A lot of these VBA projects have many For/Next loops that take an eternity to step through (F8)...I thought STEP OVER (SHIFT-F8) would get me through these but alas, doesn't seem to do it...either I am impatient for these For/Next loops take forever to get through, even holding down F8. Is there a faster way to get through these?
Also, this sheet has a calendar object where you select a start and end date. Variable is called dteEnd. Somewhere in the code, it changes it's value to 12:00AM instead of the actual date (ex. 1/6/2015)...I've put in stop points, stepped through the code and cannot catch where this would be happening. There are only three references to dteEnd: the variable deceleration, at the end of a loop:
Loop Until CDate(GetDate(sTmp)) >= dteEnd
And one point where it actually becomes equal to something:
dteEnd = Application.Sheets("Report").Range("$J$27").Value (this cell is the actual date end, in this example, 1/6/2015.
I am assuming it being assigned 12:00AM is stopping this from actually pulling data from that date because this sheet has always needed the user to put in the next day past the actual date the needed...
Any thoughts?
-
@garak0410 said:
A lot of these VBA projects have many For/Next loops that take an eternity to step through (F8)...I thought STEP OVER (SHIFT-F8) would get me through these but alas, doesn't seem to do it...either I am impatient for these For/Next loops take forever to get through, even holding down F8. Is there a faster way to get through these?
Been a while since I was in VBA but I think it is F5 to run to cursor? Stick the cursor in front of the next line after the loop and hit F5 (or whatever run to cursor is).
@garak0410 said:
dteEnd = Application.Sheets("Report").Range("$J$27").Value
This is a logic error. A cell can always be converted to a date if it is 0. it converts to midnight like that if you dump it into a date variable.
I would need to see the logic to troubleshoot more. Been a while since i used VBA.
-
@JaredBusch said:
@garak0410 said:
A lot of these VBA projects have many For/Next loops that take an eternity to step through (F8)...I thought STEP OVER (SHIFT-F8) would get me through these but alas, doesn't seem to do it...either I am impatient for these For/Next loops take forever to get through, even holding down F8. Is there a faster way to get through these?
Been a while since I was in VBA but I think it is F5 to run to cursor? Stick the cursor in front of the next line after the loop and hit F5 (or whatever run to cursor is).
@garak0410 said:
dteEnd = Application.Sheets("Report").Range("$J$27").Value
This is a logic error. A cell can always be converted to a date if it is 0. it converts to midnight like that if you dump it into a date variable.
I would need to see the logic to troubleshoot more. Been a while since i used VBA.
I'll give F5 a try.
What is interesting about this dteEnd thing, is at some breakpoints, it does show 1/6/2015 but near the end of the Macro, it gets assigned the 12:00 AM. And because someone else wrote this like 7 years ago and without knowing their logic, this has been a chore.
