Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
My VBA code works in the step though mode but not at full speed
I may be having the same problem. I'm having problem with the
EditShippedMaterials macro. It works if I run it in 2 parts, but I have to actually manuall step through. I usually debug-run to cursor at the first asterisk in my EditShippedMaterials sub (at the for loop) and then step through the rest. Basicially, I have to interrupt the code for it to work, as far as I can tell. It won't work if I just run the macro. Actually, if I just step through it, without interrupting the flow and restarting the debugging at the asterisk, it skips the where loop altogether. Here's my code (it's a little complex, but here goes:) (I've included the two macros that are called by it in case it helps). Public Sub EditShippedMaterials() Application.ScreenUpdating = False Call WorkbookUnprotect Application.ScreenUpdating = False Call modFilterEstimateData.RemoveEstimateFilter Application.ScreenUpdating = False Dim LastCell As String, LastCellNext As String, ShippedQty As String Index = 0 * For Index = 2 To 12 LastCell = Worksheets(Index).Range("l65536").End(xlUp).Offset (-3, 1).Address ' MsgBox LastCell 'MsgBox i & Worksheets(Index).Name ShippedQty = ActiveSheet.Range(LastCell, "m10").Address 'MsgBox ShippedQty & Worksheets(Index).Name LastCellNext = Range(LastCell).Offset(3, -1).Address 'MsgBox "Sheet " & Index & ":" & Worksheets(Index).Name & LastCellNext Do While Worksheets(Index).Range(LastCellNext).HasFormula Range(ShippedQty).Locked = False ' If Range(ShippedQty).Locked = False Then MsgBox Index & " " & Worksheets(Index).Name & " " & ShippedQty & " unlocked" ShippedQty = Range(ShippedQty).Offset(0, 3).Address ' MsgBox ShippedQty LastCellNext = Range(LastCellNext).Offset(0, 3).Address ' MsgBox LastCellNext Loop '?? Next Index If wkshtLumberShores.Range("m11").Locked = False Then MsgBox "The Shipped cells are ready for editing." End Sub Public Sub FilterEstimateData() If wkshtLumberShores.FilterMode = False Then Application.StatusBar = "Filtering Estimate Data" With wkshtLumberShores.Range("TLumberEstQty", "BLumberEstQty") .Locked = True .Offset(0, -1).Locked = True End With With wkshtLumberShores.Range("TShoresEstQty", "BShoresEstQty") .Locked = True .Offset(0, -1).Locked = True End With wkshtLumberShores.Range("TLumberEstQty", "BShoresEstQty") _ .AutoFilter Field:=1, Criteria1:="0", Operator:=xlOr, _ Criteria2:="<", VisibleDropDown:=False With wkshtPlywoodHardware.Range("TPlywoodEstQty", "BPlywoodEstQty") .Locked = True .Offset(0, -1).Locked = True End With With wkshtPlywoodHardware.Range("THardwareEstQty", "BHardwareEstQty") .Locked = True .Offset(0, -1).Locked = True End With wkshtPlywoodHardware.Range("TPlywoodEstQty", "BHardwareEstQty") _ .AutoFilter Field:=1, Criteria1:="0", Operator:=xlOr, _ Criteria2:="<", VisibleDropDown:=False With wkshtScaffold.Range("TScaffoldEstQty", "BScaffoldEstQty") .Locked = True .Offset(0, -1).Locked = True .AutoFilter Field:=1, Criteria1:="0", Operator:=xlOr, _ Criteria2:="<", VisibleDropDown:=False End With With wkshtPurlinsRunners.Range("TPurlinsRunnersEstQty", "BPurlinsrunnersEstQty") .Locked = True .Offset(0, -1).Locked = True .AutoFilter Field:=1, Criteria1:="0", Operator:=xlOr, _ Criteria2:="<", VisibleDropDown:=False End With With wkshtHVDoka.Range("THVDokaEstQty", "BHVDokaEstQty") .Locked = True .Offset(0, -1).Locked = True .AutoFilter Field:=1, Criteria1:="0", Operator:=xlOr, _ Criteria2:="<", VisibleDropDown:=False End With With wkshtCecoWallParts.Range("TCecoWallsEstQty", "BCecoWallsEstQty") .Locked = True .Offset(0, -1).Locked = True .AutoFilter Field:=1, Criteria1:="0", Operator:=xlOr, _ Criteria2:="<", VisibleDropDown:=False End With With wkshtBurkeHandset.Range("TBurkeHandsetEstQty", "BBurkeHandsetEstQty") .Locked = True .Offset(0, -1).Locked = True .AutoFilter Field:=1, Criteria1:="0", Operator:=xlOr, _ Criteria2:="<", VisibleDropDown:=False End With With wkshtBurkeCraneset.Range("TBurkeCranesetEstQty", "BBurkeCranesetEstQty") .Locked = True .Offset(0, -1).Locked = True End With With wkshtPansEquipment.Range("TPansEquipEstQty", "BPansEquipEstQty") .Locked = True .Offset(0, -1).Locked = True End With With wkshtTrussParts.Range("TTrussPartsEstQty", "BTrussPartsEstQty") .Locked = True .Offset(0, -1).Locked = True .AutoFilter Field:=1, Criteria1:="0", Operator:=xlOr, _ Criteria2:="<", VisibleDropDown:=False End With With wkshtTools.Range("TToolsShippedQty", "BToolsShippedQty") .Locked = True .Offset(0, -1).Locked = True .AutoFilter Field:=1, Criteria1:="0", _ VisibleDropDown:=False End With wkshtTools.Range("ToolsLegend").EntireRow.Hidden = True End If Application.ScreenUpdating = True Application.StatusBar = False End Sub Public Sub WorkbookUnprotect() If wkshtInventoryHome.ProtectContents = True Then Application.ScreenUpdating = False ActiveWorkbook.Unprotect Password:="ceco24" wkshtInventoryHome.Activate For Index = 1 To 13 Worksheets(Index).Unprotect Password:="password" Next Application.ScreenUpdating = True End If End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
My VBA code works in the step though mode but not at full spee
your code is impossible to replicate without the actual sheets
however: i'm wondering about the necessity of repeating application.screenupdating=false also the repeating msgbox lines (commented) indicate that you haven't studied vba end skipped any chapters on debugging. Ever heard of VBE's local window? -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Nicole B wrote : I'm still looking for a response if anyone can help me. Thanks! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
My VBA code works in the step though mode but not at full spee
OK, since my code cannot be replicated, I'll rephrase. Is it possible to
write something that interrupts a procedure and then starts back at a different point in a procedure? I think that that would work, if it can be done. I had to repeat application.screenupdating = false because the procedures that I'm calling turn it back on at the end of the procedure. I guess I could set it up so that those procedures only turn it back on if it was on at the beginning of the other procedure. Would that be more efficient? vbe's local window won't tell me what sheet it's on, or at least I don't know how to make it do so, so it wasn't helpful while debugging. the msgboxes were more explicit. Unless I've missed something. And no, I haven't studied VBA properly, that's why I'm asking questions. "keepITcool" wrote: your code is impossible to replicate without the actual sheets however: i'm wondering about the necessity of repeating application.screenupdating=false also the repeating msgbox lines (commented) indicate that you haven't studied vba end skipped any chapters on debugging. Ever heard of VBE's local window? -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Nicole B wrote : I'm still looking for a response if anyone can help me. Thanks! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
My VBA code works in the step though mode but not at full spee
please read back.. why should I answer this -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Nicole B wrote : Unless I've missed something. And no, I haven't studied VBA properly, that's why I'm asking questions. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
My VBA code works in the step though mode but not at full spee
just kidding. i think your code probably goes wrong because you use unqualified addressing. also it is unclear which is the activesheet and you mix Activesheet.Range Worksheets(index).range Range when LastCell is an unqualifeed address (just a simple $A$1) Range(lastcell) will get you a range on the activesheet But I'm not sure what Range(lastcell,"m10") will get you. this one is interesting: LastCell = Worksheets(Index).Range("l65536"). _ End(xlUp).Offset(-3,1).Address ShippedQty = ActiveSheet.Range(LastCell, "m10").Address LastCellNext = Range(LastCell).Offset(3, -1).Address Do While Worksheets(Index).Range(LastCellNext).HasFormula Range(ShippedQty).Locked = False ShippedQty = Range(ShippedQty).Offset(0, 3).Address LastCellNext = Range(LastCellNext).Offset(0, 3).Address Loop your addresses come from a mix of cells on the activeworksheet and on worksheets(index), therefor you may get unpredictable results and anyone will have a hard time figuring out what your doing. if I read and interpret your code following should do the same dim wks,rng set wks = worksheets(index) set rng= wks.Range("l65536").end(xlUp) while rng.offset(0,3).hasformula set rng=rng.offset(0,3) wend wks.range(rng,"m10").locked = false -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam keepITcool wrote : please read back.. why should I answer this -- keepITcool www.XLsupport.com | keepITcool chello nl | amsterdam Nicole B wrote : Unless I've missed something. And no, I haven't studied VBA properly, that's why I'm asking questions. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
My VBA code works in the step though mode but not at full spee
Thanks, that was helpful! Oh, I read the chapter on debugging. I'm using
the watch window now, and it's much betterthan a thousand message boxes! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
full screen mode | Excel Discussion (Misc queries) | |||
My VBA code works in the step though mode but not at full speed | Excel Programming | |||
Works if I single step | Excel Programming | |||
Runs Fine In Step Mode But Crashes Excel Otherwise | Excel Programming | |||
Debug Step vs Run Mode | Excel Programming |