Running line-by-line is part of the process of debugging your code and is an
EXTREMELY valuable tool!!!
In the VBEditor, right-click in the icon area and select the Debug and Edit
icon sets.
The first time, they will appear "floating" around the window.
I usually "park" or "dock" them on a line below the standard icons.
Now, once you're looking at the macro/sub you're interested in running, I
usually selct one line and either click the "hand" icon in the tool bar, or
click in the border at the left of the line.
This will highlight the line, put a circle in the border and set a "breakpoint"
in the code.
Now, run the macro by launching it however you normally would, or click the
"play" button in the tool bar.
it should stop execution at the breakpoint.
You can then use the icons or F8 to "step into" the macro one line at a time.
by right-clicking a variable and selecting "add watch", you can continuously
minitor the value of variables, and even change the values for testing purposes!
give it a try and see how it works.
Paul
-----------------------------------------
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
-----------------------------------------
>________________________________
> From: Shrinivas Shevde <shrinivas...@gmail.com>
>To: excel-macros@googlegroups.com
>Sent: Thursday, January 15, 2015 1:14 AM
>Subject: Re: $$Excel-Macros$$
>
>
>
>Dear Paul
>Till date code was runing fine I added one column after Column B and made
>required changes .Now results are showing ok but its shows run time error 13
>Type mistatch and when I try to debug its highlighted following line can you
>please help
>
> If (Sheets("Blends Procesed").Cells(Data.Row, "Y").Value <> "") Then
>
>Is there any way I can run the code line by line and see understand every line
>
>Waiting for reply
>
>
>
>
>
>On Thu, Dec 11, 2014 at 6:52 PM, Paul Schreiner <schreiner_p...@att.net> wrote:
>
>Dear Shrini,
>>
>>As Anil implied, the problem with your macro is with the "overhead" with
>>copying the data.
>>When you use:
>>Sheets("Blends Procesed").UsedRange.Copy Destination:=ws1.Range("a1")
>>
>>not only are you copying the data, but you're copying all of the formatting
>>too.
>>Not only that, you are copying it TWICE.
>>It copies it once to the clipboard, then it copies the clipboard to your
>>desitinatio worksheet.
>>
>>I took your macro and added a "timer" function to it.
>>I then ran it on my workstation and it took 3 minutes and 41 seconds.
>>
>>I then wrote the macro below in which, instead of clearing sheet3, I simply
>>delete it and create it new.
>>I then only copy the columns that you resulting macro had.
>>
>>This macro ran so fast, the timer shows 0 minutes, 0 seconds!
>>
>>Now, you may wish to add some formatting, but that would still be better than
>>3 minutes!
>>
>>BTW: Excel will attempt to recognize any entry in a date format as a "date"
>>and convert it to an official "Excel date".
>>That is, the number of days since 1/1/1900.
>>That way, it can be displayed in a variety of formats.
>>It also will automatically convert the display to a "regional" format.
>>For instance: my dates are in m/d/yyyy format.
>>While your Regional date format may be dd-mm-yyyy, and excel will change the
>>display to suit.
>>
>>I noticed that some dates (for records 140923-140931 and 141143) aren't
>>recognized as dates.
>>
>>Here's my macro:
>>
>>Sub BALANCE()
>> Dim wb As Workbook
>> Dim ws As Worksheet
>> Dim ws1 As Worksheet
>> Dim Data
>> Dim oRow As Integer
>> tstart = Timer
>> '------------------------------
>> Application.ScreenUpdating = False
>> Set wb = ThisWorkbook
>> Sheets("Blends Procesed").Select
>> On Error Resume Next
>> Application.DisplayAlerts = False
>> Sheets("Sheet3").Delete
>> Sheets.Add after:=Sheets(Sheets.Count)
>> ActiveSheet.Name = "Sheet3"
>>
>> Set ws = wb.Sheets("Blends Procesed")
>> Set ws1 = wb.Sheets("sheet3")
>> On Error GoTo 0
>> ws1.Cells(1, "B").Value =
ws.Cells(1, "B").Value
>> oRow = 1
>> For Each Data In Sheets("Blends Procesed").UsedRange.Rows
>> If (Sheets("Blends Procesed").Cells(Data.Row, "Y").Value <> "") Then
>> oRow = oRow + 1
>> ws1.Cells(oRow, "A").Value = ws.Cells(Data.Row, "A").Value
>> ws1.Cells(oRow, "B").Value = ws.Cells(Data.Row, "B").Value
>> ws1.Cells(oRow, "C").Value = ws.Cells(Data.Row, "C").Value
>> ws1.Cells(oRow, "D").Value = ws.Cells(Data.Row, "Y").Value
>> End If
>> Next Data
>>
'------------------------------
>> tstop = Timer
>> tElapsed = tstop - tstart
>> tMin = tElapsed \ 60
>> tSec = tElapsed Mod 60
>> MsgBox "Finished" & Chr(13) & tMin & " min " & tSec & " sec"
>>End Sub
>>
>>Paul
>>-----------------------------------------
>>“Do all the good you can,
>>By all the means you can,
>>In all the ways you can,
>>In all the places you can,
>>At all the times you can,
>>To all the people you can,
>>As long as ever you can.” - John Wesley
>>-----------------------------------------
>>
>>
>> From: Shrinivas Shevde <shrinivas...@gmail.com>
>>>To: अनिल नारायण गवली <gawlianil8...@gmail.com>
>>>Cc: excel-macros@googlegroups.com
>>>Sent: Thursday, December 11, 2014 6:28 AM
>>>Subject: Re: $$Excel-Macros$$
>>>
>>>
>>>
>>>Dear Anil
>>>Thanks for reply I was out of station for 2 week so I could not reply
>>>Please find attached workbook.
>>>When I run the micro it works well but very slow takes 3 to 4 mins for
>>>results
>>>Please go through it and let me know where is problem?
>>>As I Said earlier it was working fine before.
>>>Open file select STOCK sheet click on CTS Stock/Grade Stock button It will
>>>work but will take a tim
>>>
>>>Waiting for reply
>>>
>>>Regards
>>>Shrini
>>>
>>>
>>>On Thu, Nov 27, 2014 at 2:58 PM, Anil Gawli <gawlianil8...@gmail.com> wrote:
>>>
>>>Pl share the workbook with us
>>>>
>>>>
>>>>On Thu, Nov 27, 2014 at 2:56 PM, Shrinivas Shevde
>>>><shrinivas...@gmail.com> wrote:
>>>>> Dear All
>>>>> Please find below macro.
>>>>> Previously it was running very fine but from last few days it takes time
>>>>> (2
>>>>> to 3 min).I could not trace out can any one help me for finding out
>>>>> problem.
>>>>>
>>>>> Macro is just for copying some data from one sheet and paste to other
>>>>> shhet
>>>>> and removes unwanted column
>>>>>
>>>>> Sub BALANCE()
>>>>> Application.ScreenUpdating = False
>>>>> Dim wb As Workbook
>>>>> Dim ws As Worksheet
>>>>> Dim ws1 As Worksheet
>>>>> Dim rawdata
>>>>>
>>>>> Set wb = ThisWorkbook
>>>>> Sheets("Blends Procesed").Select
>>>>> Set ws1 = wb.Sheets("sheet4")
>>>>> ws1.UsedRange.Clear
>>>>> Sheets("Blends Procesed").ListObjects("Table3").Range.AutoFilter
>>>>> Field:=25,
>>>>> Criteria1:= _
>>>>> "<>"
>>>>> Sheets("Blends Procesed").UsedRange.Copy Destination:=ws1.Range("a1")
>>>>> ws1.Select
>>>>> Columns("D:x").Select
>>>>> Selection.Delete Shift:=xlToLeft
>>>>> Cells.Select
>>>>> Cells.EntireColumn.AutoFit
>>>>>
>>>>> Sheets("Blends Procesed").Select
>>>>> Range("A2").Select
>>>>> Selection.AutoFilter
>>>>> Sheets("Sheet4").Select
>>>>> Range("K3").Select
>>>>> ws1.Select
>>>>> End Sub
>>>>>
>>>>>
>>>>> --
>>>>> Shrini
>>>>>
>>>>> --
>>>>> Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
>>>>> =TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
>>>>> https://www.facebook.com/discussexcel
>>>>>
>>>>> FORUM RULES
>>>>>
>>>>> 1) Use concise, accurate thread titles. Poor thread titles, like Please
>>>>> Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
>>>>> will
>>>>> not get quick attention or may not be answered.
>>>>> 2) Don't post a question in the thread of another member.
>>>>> 3) Don't post questions regarding breaking or bypassing any security
>>>>> measure.
>>>>> 4) Acknowledge the responses you receive, good or bad.
>>>>> 5) Jobs posting is not allowed.
>>>>> 6) Sharing copyrighted material and their links is not allowed.
>>>>>
>>>>> NOTE : Don't ever post confidential data in a workbook. Forum owners and
>>>>> members are not responsible for any loss.
>>>>> ---
>>>>> You received this message because you are subscribed to the Google Groups
>>>>> "MS EXCEL AND VBA MACROS" group.
>>>>> To unsubscribe from this group and stop receiving emails from it, send an
>>>>
> email to mailto:excel-macros%2bunsubscr...@googlegroups.com.
>>>>
>>>>> To post to this group, send email to excel-macros@googlegroups.com.
>>>>> Visit this group at http://groups.google.com/group/excel-macros.
>>>>> For more options, visit https://groups.google.com/d/optout.
>>>>
>>>
>>>
>>>--
>>>
>>>Shrini
>>>--
>>>Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
>>>=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
>>>https://www.facebook.com/discussexcel
>>>
>>>FORUM RULES
>>>
>>>1) Use concise, accurate thread titles. Poor thread titles, like Please
>>>Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
>>>not get quick attention or may not be answered.
>>>2) Don't post a question in the thread of another member.
>>>3) Don't post questions regarding breaking or bypassing any security measure.
>>>4) Acknowledge the responses you receive, good or bad.
>>>5) Jobs posting is not allowed.
>>>6) Sharing copyrighted material and their links is not allowed.
>>>
>>>NOTE : Don't ever post confidential data in a workbook. Forum owners and
>>>members are not responsible for any loss.
>>>---
>>>You received this message because you are subscribed to the Google Groups
>>>"MS EXCEL AND VBA MACROS" group.
>>>To unsubscribe from this group and stop receiving emails from it, send an
>>>email to excel-macros+unsubscr...@googlegroups.com.
>>>To post to this group, send email to excel-macros@googlegroups.com.
>>>Visit this group at http://groups.google.com/group/excel-macros.
>>>For more options, visit https://groups.google.com/d/optout.
>>>
>>>
>>>
>>--
>>Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
>>=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
>>https://www.facebook.com/discussexcel
>>
>>FORUM RULES
>>
>>1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
>>Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not
>>get quick attention or may not be answered.
>>2) Don't post a question in the thread of another member.
>>3) Don't post questions regarding breaking or bypassing any security measure.
>>4) Acknowledge the responses you receive, good or bad.
>>5) Jobs posting is not allowed.
>>6) Sharing copyrighted material and their links is not allowed.
>>
>>NOTE : Don't ever post confidential data in a workbook. Forum owners and
>>members are not responsible for any loss.
>>---
>>You received this message because you are subscribed to the Google Groups "MS
>>EXCEL AND VBA MACROS" group.
>>To unsubscribe from this group and stop receiving emails from it, send an
>>email to excel-macros+unsubscr...@googlegroups.com.
>>To post to this group, send email to excel-macros@googlegroups.com.
>>Visit this group at http://groups.google.com/group/excel-macros.
>>For more options, visit https://groups.google.com/d/optout.
>>
>
>
>--
>
>Shrini
--
>Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
>=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
>https://www.facebook.com/discussexcel
>
>FORUM RULES
>
>1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
>Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
>quick attention or may not be answered.
>2) Don't post a question in the thread of another member.
>3) Don't post questions regarding breaking or bypassing any security measure.
>4) Acknowledge the responses you receive, good or bad.
>5) Jobs posting is not allowed.
>6) Sharing copyrighted material and their links is not allowed.
>
>NOTE : Don't ever post confidential data in a workbook. Forum owners and
>members are not responsible for any loss.
>---
>You received this message because you are subscribed to the Google Groups "MS
>EXCEL AND VBA MACROS" group.
>To unsubscribe from this group and stop receiving emails from it, send an
>email to excel-macros+unsubscr...@googlegroups.com.
>To post to this group, send email to excel-macros@googlegroups.com.
>Visit this group at http://groups.google.com/group/excel-macros.
>For more options, visit https://groups.google.com/d/optout.
>
>
>
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
