Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I return the progress
Hello all,
(sorry for the double post--using Outlook Express, it showed up as a reply to my post when I was trying to start a new thread) First, below is my first post closely related to this question. Jim Rech already answered this question for me. Thanks Jim. I have a macro that I use to replace all "NULL" 's with "". On a small amount of data, everything works fine. But currently I'm working on an amount of data about 45,000 rows x 92 columns. I usually let it run for 10 seconds and then do a [Ctrl] + [Pause/Break], otherwise it gets bogged down and then I get "Out of memory" errors and message boxes. The problem is when I do these [Ctrl] + [Pause/Break]'s, I get just the generic "Code execution has been interrupted. Continue? End? Debug? Help?" message box. I would like to replace that generic message box with my own custom one that will show my progress through the selected range. Can this be done with On Error Goto? Can I check for the [Ctrl] + [Pause/Break] key combo another way? What I would like to do is display a message box displaying my progress through the selection. I can easily figure out the total number of rows/cell in the selection, but is there any way to figure out what row/cell the code left off on when it was canceled? Now for the code (I don't like using line continuation characters in my code--hopefully this is readable): Sub PasteReplaceNulls() On Error GoTo PasteReplaceNulls_Err Application.EnableCancelKey = xlErrorHandler Dim pmbrResponse As VbMsgBoxResult Dim pmbrAnswer As VbMsgBoxResult Dim pstrPasteCell As String pstrPasteCell = "A2" If Application.Calculation = xlCalculationAutomatic Then ToggleAutoCalc pmbrResponse = MsgBox("Do you want to Paste?", vbYesNoCancel + vbDefaultButton2 + vbQuestion, "Paste?") If pmbrResponse = vbCancel Then Exit Sub ElseIf pmbrResponse = vbYes Then pmbrAnswer = MsgBox("Data will be pasted starting in cell A2" & vbCrLf & vbCrLf & "Is this correct?", vbYesNoCancel + vbQuestion, "Paste in A2") If pmbrAnswer = vbCancel Then Exit Sub ElseIf pmbrAnswer = vbNo Then pstrPasteCell = InputBox("Please enter the cell you would like pasting to begin:", "Paste Cell", pstrPasteCell) End If Range(pstrPasteCell).Select ActiveSheet.Paste End If ' Selection.Replace "NULL", Null, xlPart, xlByRows, False, False, False Selection.Replace "NULL", "", xlPart, xlByRows, False, False, False If Application.Calculation = xlCalculationManual Then ToggleAutoCalc PasteReplaceNulls_Exit: Exit Sub PasteReplaceNulls_Err: MsgBox "Need to enter progress info here." Exit Sub End Sub PS. If any one has a more efficient way of doing this, I'm all ears. Thanks for any help anyone can provide, Conan Kelly |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I return the progress
Hi Conan,
You can cause a trappable error when the user cancels by using the following code: On Error Goto ErrHandler Application.EnableCancelKey= xlErrorHandler Then in your error handler: ErrHandler: If Err.Number = 18 Then '/ user interrupt '/ show progress Else MsgBox "Unexpected Error: " & Err.Number & " - " & Err.Description End If But I think you'll have a problem showing a progress indicator with your current code. The issue is that you're using the Replace method on a range, and there's no iteration you can latch onto. If you cancel in the middle of its operation, you will not be able to tell (without querying the range again) how far along it got. You could try looping with the Find method - that way, you can tell what number you're on. However, you'd have to get a count of the number of NULL valued cells first so you can calculate the percentage. And it would likely be much slower than the one call to the Replace method. Whatever you decide, you could use Application.StatusBar to keep the user updated: Dim lTot As Long Dim lCurr As Long lTot = 100000 For lCurr = 1 To lTot Application.StatusBar = "Replacing NULL values: " & _ Format$(lCurr, "#,##0") & " of " & _ Format$(lTot, "#,##0") & " completed..." lCurr = lCurr + 1 Next lCurr Application.StatusBar = False Although you'll probably want to use the Mod statement to only update the user every n steps so there's less flicker and less interruption. -- Regards, Jake Marx www.longhead.com [please keep replies in the newsgroup - email address unmonitored] Conan Kelly wrote: Hello all, (sorry for the double post--using Outlook Express, it showed up as a reply to my post when I was trying to start a new thread) First, below is my first post closely related to this question. Jim Rech already answered this question for me. Thanks Jim. I have a macro that I use to replace all "NULL" 's with "". On a small amount of data, everything works fine. But currently I'm working on an amount of data about 45,000 rows x 92 columns. I usually let it run for 10 seconds and then do a [Ctrl] + [Pause/Break], otherwise it gets bogged down and then I get "Out of memory" errors and message boxes. The problem is when I do these [Ctrl] + [Pause/Break]'s, I get just the generic "Code execution has been interrupted. Continue? End? Debug? Help?" message box. I would like to replace that generic message box with my own custom one that will show my progress through the selected range. Can this be done with On Error Goto? Can I check for the [Ctrl] + [Pause/Break] key combo another way? What I would like to do is display a message box displaying my progress through the selection. I can easily figure out the total number of rows/cell in the selection, but is there any way to figure out what row/cell the code left off on when it was canceled? Now for the code (I don't like using line continuation characters in my code--hopefully this is readable): Sub PasteReplaceNulls() On Error GoTo PasteReplaceNulls_Err Application.EnableCancelKey = xlErrorHandler Dim pmbrResponse As VbMsgBoxResult Dim pmbrAnswer As VbMsgBoxResult Dim pstrPasteCell As String pstrPasteCell = "A2" If Application.Calculation = xlCalculationAutomatic Then ToggleAutoCalc pmbrResponse = MsgBox("Do you want to Paste?", vbYesNoCancel + vbDefaultButton2 + vbQuestion, "Paste?") If pmbrResponse = vbCancel Then Exit Sub ElseIf pmbrResponse = vbYes Then pmbrAnswer = MsgBox("Data will be pasted starting in cell A2" & vbCrLf & vbCrLf & "Is this correct?", vbYesNoCancel + vbQuestion, "Paste in A2") If pmbrAnswer = vbCancel Then Exit Sub ElseIf pmbrAnswer = vbNo Then pstrPasteCell = InputBox("Please enter the cell you would like pasting to begin:", "Paste Cell", pstrPasteCell) End If Range(pstrPasteCell).Select ActiveSheet.Paste End If ' Selection.Replace "NULL", Null, xlPart, xlByRows, False, False, False Selection.Replace "NULL", "", xlPart, xlByRows, False, False, False If Application.Calculation = xlCalculationManual Then ToggleAutoCalc PasteReplaceNulls_Exit: Exit Sub PasteReplaceNulls_Err: MsgBox "Need to enter progress info here." Exit Sub End Sub PS. If any one has a more efficient way of doing this, I'm all ears. Thanks for any help anyone can provide, Conan Kelly |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I return the progress
Jake,
Thanks for the feed back. I'm not looking for a continually updating progress bar, just a message box that shows my progress at the instance that I canceled code (just so I have an idea as to how many more times I will need to run the code). And like you mentioned, there is no iteration I can latch onto using the Replace method on a range. Thanks again for all of your help, Conan "Jake Marx" wrote in message ... Hi Conan, You can cause a trappable error when the user cancels by using the following code: On Error Goto ErrHandler Application.EnableCancelKey= xlErrorHandler Then in your error handler: ErrHandler: If Err.Number = 18 Then '/ user interrupt '/ show progress Else MsgBox "Unexpected Error: " & Err.Number & " - " & Err.Description End If But I think you'll have a problem showing a progress indicator with your current code. The issue is that you're using the Replace method on a range, and there's no iteration you can latch onto. If you cancel in the middle of its operation, you will not be able to tell (without querying the range again) how far along it got. You could try looping with the Find method - that way, you can tell what number you're on. However, you'd have to get a count of the number of NULL valued cells first so you can calculate the percentage. And it would likely be much slower than the one call to the Replace method. Whatever you decide, you could use Application.StatusBar to keep the user updated: Dim lTot As Long Dim lCurr As Long lTot = 100000 For lCurr = 1 To lTot Application.StatusBar = "Replacing NULL values: " & _ Format$(lCurr, "#,##0") & " of " & _ Format$(lTot, "#,##0") & " completed..." lCurr = lCurr + 1 Next lCurr Application.StatusBar = False Although you'll probably want to use the Mod statement to only update the user every n steps so there's less flicker and less interruption. -- Regards, Jake Marx www.longhead.com [please keep replies in the newsgroup - email address unmonitored] Conan Kelly wrote: Hello all, (sorry for the double post--using Outlook Express, it showed up as a reply to my post when I was trying to start a new thread) First, below is my first post closely related to this question. Jim Rech already answered this question for me. Thanks Jim. I have a macro that I use to replace all "NULL" 's with "". On a small amount of data, everything works fine. But currently I'm working on an amount of data about 45,000 rows x 92 columns. I usually let it run for 10 seconds and then do a [Ctrl] + [Pause/Break], otherwise it gets bogged down and then I get "Out of memory" errors and message boxes. The problem is when I do these [Ctrl] + [Pause/Break]'s, I get just the generic "Code execution has been interrupted. Continue? End? Debug? Help?" message box. I would like to replace that generic message box with my own custom one that will show my progress through the selected range. Can this be done with On Error Goto? Can I check for the [Ctrl] + [Pause/Break] key combo another way? What I would like to do is display a message box displaying my progress through the selection. I can easily figure out the total number of rows/cell in the selection, but is there any way to figure out what row/cell the code left off on when it was canceled? Now for the code (I don't like using line continuation characters in my code--hopefully this is readable): Sub PasteReplaceNulls() On Error GoTo PasteReplaceNulls_Err Application.EnableCancelKey = xlErrorHandler Dim pmbrResponse As VbMsgBoxResult Dim pmbrAnswer As VbMsgBoxResult Dim pstrPasteCell As String pstrPasteCell = "A2" If Application.Calculation = xlCalculationAutomatic Then ToggleAutoCalc pmbrResponse = MsgBox("Do you want to Paste?", vbYesNoCancel + vbDefaultButton2 + vbQuestion, "Paste?") If pmbrResponse = vbCancel Then Exit Sub ElseIf pmbrResponse = vbYes Then pmbrAnswer = MsgBox("Data will be pasted starting in cell A2" & vbCrLf & vbCrLf & "Is this correct?", vbYesNoCancel + vbQuestion, "Paste in A2") If pmbrAnswer = vbCancel Then Exit Sub ElseIf pmbrAnswer = vbNo Then pstrPasteCell = InputBox("Please enter the cell you would like pasting to begin:", "Paste Cell", pstrPasteCell) End If Range(pstrPasteCell).Select ActiveSheet.Paste End If ' Selection.Replace "NULL", Null, xlPart, xlByRows, False, False, False Selection.Replace "NULL", "", xlPart, xlByRows, False, False, False If Application.Calculation = xlCalculationManual Then ToggleAutoCalc PasteReplaceNulls_Exit: Exit Sub PasteReplaceNulls_Err: MsgBox "Need to enter progress info here." Exit Sub End Sub PS. If any one has a more efficient way of doing this, I'm all ears. Thanks for any help anyone can provide, Conan Kelly |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Progress bar | Excel Discussion (Misc queries) | |||
Progress Bar | Excel Programming | |||
Progress YTD | Charts and Charting in Excel | |||
progress bar | Excel Programming | |||
Progress Bar | Excel Programming |