"Bob Phillips" wrote in message
...
My biggest problem is that I couldn't get it to take a long time, 1/8th
second was all it took.
Bob, perhaps you have a fast 'puter and it goes that quickly. On my puter
it is quite slow.. I am using my 'puter at work, it's around 7 years old or
so.. I am sure my home puter (3 Ghz) would be much faster. It probably
takes around 20 seconds or so on each file at work.. after doing that for
16 files or so it's painful slow. As I said before if I put the code in a
local file it was blazing fast, my problem was only when using Excel as an
automation object from inside a
VB app. I also was using it as a bound
object using the "new" operator, not by using "CreateObject()" to gain
access to Excel.
I actually found my own remedy.. Your remedy really did not change
anything as far as the speed goes, if it did the change was very tiny. In
fact when I took one line I was uisng to replace the text if the condition
was met...and also turning everthing off (calculations, and updating) like
you suggested.. I was then only reading a value out of a cell and
comparing it to a hard coded string value.. But the routine still took
almost the same time.
The remedy I decided on:
I remembered this problem is highlighted in my excel 97 developers'
handbook (Wells, Harshbanger).. The trick is to assign a range to an
array, then compare and alter the array.. then finally reassign the array to
the range. End result is that it's so fast I don't even notice a pause
happening between routines. Guess I could have made my code slightly more
efficient if I "Set" the range at the start of my code below.. but it
works just fine now.. it's plenty fast enough, so I don't care to worry
about that.
thanks for your comments.
'################################################# ################################################## #####
Dim RG_Array As Variant
call xlPause( ExclObj, True )
Set WS = WBmain.Worksheets("Plots")
With WS
RG_Array = .Range(Cells(500, 2), Cells(2502,
17)).Value
For cnt = 2 To UBound(RG_Array, 1) - 1
For Y = 1 To UBound(RG_Array, 2)
If (RG_Array(cnt, Y) = "0") Then
RG_Array(cnt, Y) = RG_Array((cnt -
1), Y)
End If
Next
Next
.Range(Cells(500, 2), Cells(2502, 17)).Value =
RG_Array
End With
call xlPause( ExclObj, False )
'################################################# ################################################## #####
sub xlPause( xl as object, bVal as boolean)
bVal = Not bVal
xl.ScreenUpdating = bVal
xl.EnableEvents = bVal
if bVal = true then
xl.Calculation = xlCalculationAutomatic
else
xl.Calculation = xlCalculationManual
end if
end sub
'################################################# ################################################## #####