Long Time to Enter Cell Values

[Joe Adams]

I have a large 2.736 Meg workbook, “WorkBook A”, into
which I enter data from a SHRARED 2.531 Meg workbook via
VBA code similar to the following:

Dim wB_PN2 As Workbook
Dim wS_PN2_PurParts As Worksheet
…
With wS_PN2_PurParts
.Range("QW_PurP_PackTot_Name").Value = arrDataList(1, 3)
End With

With the result that it takes 0.562 seconds to accomplish
the assignment.

We are all using the same Compaq computers:
733Mhz w/ 256Meg RAM with
Windows 2000 Pro Version 5 (5.0.2195 SR-4 Build 2195) and
Excel 2000 (9.0.3821 SR-1)
on a 15 person LAN.

In order to speedup the execution of the Sub-routine
(macro), I wrote a test workbook where I have
A range, rng_Entry, in cells D8:M8
range, rng_Arr, in cells D9:M9
range, rng_Test, in cells E12:E21
and individual ranges, Test_1 - Test_10 in cells E12 – E21

and the following Sub-routine

Sub PushDataIn()
Dim sw As New StopWatch 'Stop Watch Object
Dim TestWS As Worksheet
Dim arrDataList() As Variant
Set TestWS = Worksheets("Sheet1")

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'
arrDataList = Range("rng_Entry").Value
sw.StartTimer
TestWS.Activate
With TestWS
.Range("Test_1").Value = arrDataList(1, 1)
.Range("Test_2").Value = arrDataList(1, 2)
.Range("Test_3").Value = arrDataList(1, 3)
.Range("Test_4").Value = arrDataList(1, 4)
.Range("Test_5").Value = arrDataList(1, 5)
.Range("Test_6").Value = arrDataList(1, 6)
.Range("Test_7").Value = arrDataList(1, 7)
.Range("Test_8").Value = arrDataList(1, 8)
.Range("Test_9").Value = arrDataList(1, 9)
.Range("Test_10").Value = arrDataList(1, 10)
End With
Debug.Print "" & sw.EndTimer / 1000 & " Seconds"
Application.ScreenUpdating = True
MsgBox "Half-way Home"
Application.ScreenUpdating = False

arrDataList = Range("rng_Arr").Value
'
sw.StartTimer
TestWS.Activate
With TestWS
.Range("rng_Test").Value = Application.Transpose
(arrDataList)
.Calculate
.Range("Box_1").Copy
.Range("Box_1").PasteSpecial Paste:=xlValues
.Range("Box_2").Copy
.Range("Box_2").PasteSpecial Paste:=xlValues
.Range("Box_3").Copy
.Range("Box_3").PasteSpecial Paste:=xlValues
.Range("Box_4").Copy
.Range("Box_4").PasteSpecial Paste:=xlValues
.Range("Box_5").Copy
.Range("Box_5").PasteSpecial Paste:=xlValues
.Range("Box_6").Copy
.Range("Box_6").PasteSpecial Paste:=xlValues
.Range("Box_7").Copy
.Range("Box_7").PasteSpecial Paste:=xlValues
.Range("Box_8").Copy
.Range("Box_8").PasteSpecial Paste:=xlValues
.Range("Box_9").Copy
.Range("Box_9").PasteSpecial Paste:=xlValues
.Range("Box_10").Copy
.Range("Box_10").PasteSpecial Paste:=xlValues
End With
Debug.Print "" & sw.EndTimer / 1000 & " Seconds to
array fill Test cells data"
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

When this test workbook is the only workbook open, it
takes between 0 – 0.015 sec. for the first half to execute
and 0.031 sec for the second portion to execute.

If I also open the large “WorkBook A”, the test workbook
takes between 5.703 – 5.765 sec. for the first half to
execute and 1.560 sec. for the second portion to execute.

If I change the name references to cell references, i.e.
E12, the execution time changes to 5.500 – 5.641 sec. for
the first half to execute and 1.140 sec. for the second
portion to execute.

If I have the test workbook and another 604k workbook open
while “WorkBook A” is not open, the test workbook takes
0.015 sec. for the first half to execute and 0.031 sec.
for the second portion to execute. Same as it runs by
itself.
This 604 workbook is mostly sub-routines.
If I have the test workbook and another 2.531 Meg SHARED
workbook (Also see: “Re-Post: Long and Varying Worksheet
Save Times” posted earlier) open while “WorkBook A” is not
open, the test workbook takes between 0 - 0.015 sec. for
the first half to execute and 0.031 sec. for the second
portion to execute. Again, same as it runs by itself.

If I have the test workbook, another 2.531 Meg SHARED
workbook and “WorkBook A” open, the test workbook takes
between 5.797 sec. for the first half to execute and 1.203
sec. for the second portion to execute. Again, same as if
only “WorkBook A” is open with it..

Any ideas why it takes so long to execute the assignments
in and with “WorkBook A”? Too many macros, too many named
ranges (628)? Why does only “WorkBook A” appear to effect
execution time and not other workbooks?

Thanks for your help in advance.

 

[John H W]

Just a guess, but do you have the same size virtual memory on each computer (To test: set the same size virtual memory and make it large enough to take the called workbook), then rerun the tests

John H W

 

[Joe Adams]

Yes, they are all the same.

256 Meg RAM installed

In the Control Panel, System, Advanced – the Virtual Memory
Total paging file size for all drives: 384mB

With all the desired workbooks open the system has:
Total Physical Memory: 261,552kb
Available Physical Memory: 122,592kb
Total Virtual Memory: 894,768kb
Available Virtual Memory: 636,828kb
Page File Space: 633,216kb

I am having difficulty convincing the Data Administrator
to increase the Virtual Memory size for a test (because
the “experts” – those more than 50 mile away and haven’t
looked at the problem) don’t believe that is a solution.
However, if you do, I will continue to try to test.

Thanks for the input,

Joe

-----Original Message-----
Just a guess, but do you have the same size virtual

memory on each computer (To test: set the same size
virtual memory and make it large enough to take the called
workbook), then rerun the tests.

 

[John H W]

There are two other things which would influence the speed

1. LAN - its speed, number of persons accessing it at the moment, and also the server, if the file being updated is located on it

2. Programming calls. Everytime you call a function (written in VB), there is overhead, which can equate to time (and memory) loss or usage. Therefore, making fewer calls will result in less overhead. Also, calling or using function that come with Excel will cut down the time since they are compiled already while VB is compiled just before it is called (thereby taking up more time). Using "named" locations will also use up time because Excel has to do a lookup to see just where the name is pointing

Hope this helps -- I am late leaving the office

John H W