Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Still trying to return the progress
Hello all,
HOW DO I GET THIS CODE TO RUN FROM WITHIN THE ERROR HANDLER? For background on what I'm trying to do and why, scroll all the way down and read my previous posts. I came up with this code: Sub DisplayProgress() Dim plngFirstRow As Long Dim plngFirstCol As Long Dim plngCurrRow As Long Dim plngCurrCol As Long Dim plngCurrCell As Long Dim plngTotalCells As Long plngTotalCells = Selection.Cells.Count plngFirstRow = Selection.Cells(1).Row plngFirstCol = Selection.Cells(1).Column plngCurrRow = Selection.Find("NULL", ActiveCell, xlFormulas).Row plngCurrCol = Selection.Find("NULL", ActiveCell, xlFormulas).Column plngCurrCell = ((plngCurrRow - plngFirstRow) * Selection.Columns.Count) + (plngCurrCol - plngFirstCol) + 1 MsgBox "Progress: " & FormatPercent(plngCurrCell / plngTotalCells, 2, vbTrue) End Sub Now after interrupting code execution ([Ctrl] + [Pause/Break]) on another sub routine (the PasteReplaceNulls sub in the orig. posts below), if I run this one all by itself, it appears that it runs just fine and reports the information I'm looking for. BUT if I try to call this sub routine from an error handler in the other sub like so: PasteReplaceNulls_Err: DisplayProgress Exit Sub End Sub OR if I try to put these statements in the error handler like so: PasteReplaceNulls_Err: plngTotalCells = Selection.Cells.Count plngFirstRow = Selection.Cells(1).Row plngFirstCol = Selection.Cells(1).Column plngCurrRow = Selection.Find("NULL", ActiveCell, xlFormulas).Row plngCurrCol = Selection.Find("NULL", ActiveCell, xlFormulas).Column plngCurrCell = ((plngCurrRow - plngFirstRow) * Selection.Columns.Count) + (plngCurrCol - plngFirstCol) + 1 MsgBox "Progress: " & FormatPercent(plngCurrCell / plngTotalCells, 2, vbTrue) Exit Sub End Sub (Variable names were declared at the top of the sub) I get the following error message: http://home.att.net/~ctbarbarin/file...ress_error.jpg HOW DO I GET THIS CODE TO RUN FROM WITHIN THE ERROR HANDLER? Thanks for any help anyone can provide, Conan Kelly ---- Original Posts Below ---- Hello all, 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 Worksheet Functions | |||
How do I return the progress | Excel Programming | |||
Progress Bar | Excel Programming | |||
Progress Bar | Excel Programming | |||
Progress bar in VBE | Excel Programming |