How do I return the progress

[Conan Kelly]

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

 

[Jake Marx]

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]

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

 

[Conan Kelly]

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

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]

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