Excel Macro, slow performance on terminal server...

[Lars Gaarde]

I'm having some trouble with a Excel macro, which transfers data from
one worksheet to another in the same workbook.

The macro is embedded in the workbook.

It is a rather large workbook with several, big worksheets.

On my own PC i open 10 copies of the same workbook and run the macro
it takes less than 1 second (Excel 2003)
If i open 20 workbooks it still takes less than a second.

On a terminal server, where i am the only user i open 10 copies of the
same workbook and run the macro it takes less than 1 second (Excel
2003)
If i open 20 workbooks it still takes less than a second.

No problem so far.

But on another terminal server (Excel 2003), the macro just runs
slower and slower the more workbooks i open.

For example:
- 1 workbook, execution time less than 1 second as expected.
- 5 workbooks, execution time around 3 seconds
- 10 workbooks, execution time around 9 seconds
- 20 workbooks, execution time around 23 seconds

If i log in locally on this terminalserver performance is fast even
with 20 workbooks as i would expect.

Does anyone have any idea what's going on?
Why is the second terminal slowing down so dramatically?

A copy of the Workbook can be found at:
http://home19.inet.tele.dk/skankkid/slow_ts.zip

The Macro in question is module1.WriteToTablesOut.

I don't think that it is the macro itself which is the problem, i
think it is some setting in either Excel or on the terminalserver.

Any ideas??

Lars G.

This is the macro itself:

Public Function WriteToTablesOut() As Boolean

Application.ScreenUpdating = False
Application.Calculation = xlManual

'Define first line in TABLE_OUT Features table
line_cnt = 28
'Delete items in table
For i = line_cnt To 56
For j = 1 To 10
ThisWorkbook.Worksheets("TABLES_OUT").Cells(line_cnt, j + 1)
= ""
Next
line_cnt = line_cnt + 1
Next

line_cnt = 28
'Write active feature dimensions from 'DRAWING' to features table
'TABLES_OUT'
For i = 18 To 105
checkval = ThisWorkbook.Worksheets("DRAWING").Cells(i, 24)
If checkval = 1 Then
For j = 1 To 10
If ThisWorkbook.Worksheets("DRAWING").Cells(i, 24 + j) = 0
And (j = 3 Or j = 4 Or j = 5) Then
ThisWorkbook.Worksheets("TABLES_OUT").Cells(line_cnt, j +
1) = " "
Else
ThisWorkbook.Worksheets("TABLES_OUT").Cells(line_cnt, j +
1) = ThisWorkbook.Worksheets("DRAWING").Cells(i, 24 + j)
End If
Next
line_cnt = line_cnt + 1
End If
Next

'Calculate and set X-Y coordinates for cutouts in plank surface where
there is a flag
Cutout_Centered = 0
'Plank center X/Y coords
Center_Xcoord = (ThisWorkbook.Worksheets("INVENTOR
PARAMETER").Cells(2, 2) / 2) - 0.1
Center_Ycoord = (ThisWorkbook.Worksheets("INVENTOR
PARAMETER").Cells(3, 2) / 2) - 0.1

'If no centering is required then skip
For i = 561 To 566
Cutout_Centered = ThisWorkbook.Worksheets("INVENTOR
PARAMETER").Cells(i, 2)
If Cutout_Centered = 1 Then
GoTo Centered_Found
End If
Next

Centered_Found:

If Cutout_Centered <> 1 Then
GoTo CenteringEnd
End If

'RNDHL_01_PLNK_CENTERED
'Change RNDHL_01_PLNK_H on Line 80 of INVENTOR PARAMETER to Plank
Center Y-coord
'Change RNDHL_01_PLNK_W on Line 81 of INVENTOR PARAMETER to Plank
Center X-coord
If i = 561 And Cutout_Centered = 1 Then
ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(80, 2) =
Center_Ycoord
ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(81, 2) =
Center_Xcoord
End If

'RNDHL_02_PLNK_CENTERED
'Change RNDHL_02_PLNK_H on Line 89 of INVENTOR PARAMETER to Plank
Center Y-coord
'Change RNDHL_02_PLNK_W on Line 90 of INVENTOR PARAMETER to Plank
Center X-coord
If i = 562 And Cutout_Centered = 1 Then
ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(89, 2) =
Center_Ycoord
ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(90, 2) =
Center_Xcoord
End If

'RCTNGL_01_PLNK_CENTERED
'Change RCTNGL_01_PLNK_H on Line 97 of INVENTOR PARAMETER to Plank
Center Y-coord
'Change RCTNGL_01_PLNK_W on Line 98 of INVENTOR PARAMETER to Plank
Center X-coord
If i = 563 And Cutout_Centered = 1 Then
ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(97, 2) =
Center_Ycoord
ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(98, 2) =
Center_Xcoord
End If

'RCTNGL_02_PLNK_CENTERED
'Change RCTNGL_02_PLNK_H on Line 107 of INVENTOR PARAMETER to Plank
Center Y-coord
'Change RCTNGL_02_PLNK_W on Line 108 of INVENTOR PARAMETER to Plank
Center X-coord
If i = 564 And Cutout_Centered = 1 Then
ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(107, 2) =
Center_Ycoord
ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(108, 2) =
Center_Xcoord
End If

'INFRM_CENTERED
'Change INFRM_W on Line 119 of INVENTOR PARAMETER to Plank Center
Y-coord
'Change INFRM_H on Line 120 of INVENTOR PARAMETER to Plank Center
X-coord
If i = 565 And Cutout_Centered = 1 Then
ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(119, 2) =
Center_Xcoord
ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(120, 2) =
Center_Ycoord
End If

'DMPL_CENTERED
'Change DMPL_H on Line 128 of INVENTOR PARAMETER to Plank Center
Y-coord
'Change DMPL_W on Line 129 of INVENTOR PARAMETER to Plank Center
X-coord
If i = 566 And Cutout_Centered = 1 Then
ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(128, 2) =
Center_Ycoord
ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(129, 2) =
Center_Xcoord
End If

CenteringEnd:
'Update Inner and Outer Frame calculations
ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(13, 2) =
ThisWorkbook.Worksheets("NTI Calculation").Cells(45, 2)
ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(14, 2) =
ThisWorkbook.Worksheets("NTI Calculation").Cells(46, 2)
ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(15, 2) =
ThisWorkbook.Worksheets("NTI Calculation").Cells(47, 2)
ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(16, 2) =
ThisWorkbook.Worksheets("NTI Calculation").Cells(48, 2)
ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(18, 2) =
ThisWorkbook.Worksheets("NTI Calculation").Cells(49, 2)
ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(19, 2) =
ThisWorkbook.Worksheets("NTI Calculation").Cells(50, 2)
ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(20, 2) =
ThisWorkbook.Worksheets("NTI Calculation").Cells(51, 2)
ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(21, 2) =
ThisWorkbook.Worksheets("NTI Calculation").Cells(52, 2)
'Update flatpattern dims
ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(5, 2) =
ThisWorkbook.Worksheets("DRAWING").Cells(9, 3)
ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(6, 2) =
ThisWorkbook.Worksheets("DRAWING").Cells(9, 2)
ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(7, 2) =
ThisWorkbook.Worksheets("DRAWING").Cells(12, 3)
ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(8, 2) =
ThisWorkbook.Worksheets("DRAWING").Cells(12, 2)
'Update plank type data - Work order & Perf type
ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(567, 2) =
ThisWorkbook.Worksheets("NTI Calculation").Cells(6, 5)
ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(568, 2) =
ThisWorkbook.Worksheets("NTI Calculation").Cells(7, 5)
ThisWorkbook.Worksheets("INVENTOR PARAMETER").Cells(569, 2) =
ThisWorkbook.Worksheets("NTI Calculation").Cells(4, 5)

WriteToTablesOut = True

Application.ScreenUpdating = True
Application.Calculation = xlAutomatic
ThisWorkbook.Worksheets("INVENTOR PARAMETER").Calculate
ThisWorkbook.Worksheets("TABLES_OUT").Calculate
End Function

 

[Joel]

Check the memory usege when you run on both servers. the slowerr server
probably is doing a lot of memory swapping beause it doesn't have enough
memory to run that many copies of the program.

 

[Lars Gaarde]

Unfortunately this is not the case.

We are still in the test phase and i am the only one using the server,
The server has 2GB RAM and there is loads of free physical memory when
running.

Lars G.

 

[Joel]

It doesn't matter how much memory is availabble, it matters how much is
assigned to the users.

 

[Lars Gaarde]

OK i didn't realise that.

I'll look into it and check if it helps.

Lars G.