slow macros when sharing workbook

[puba]

Hi,
I have a very simple two sheet Excel file with two macros to act as a
basic helpdesk database. They work fine and fast - but when I share the
workbook, the macros become terribly slow. I don't know why sharing the
workbook slow down so much the macros.

As I don't know VBA, most of the code is "recorded".
I've heard that the "select" command is slowing down execution of the
code.

Perhaps, someone could simply tell me by what to replace the recorded
select code by a smarter alternative.

The NEWLINE macro copies the last existing row, then paste below the
formatting and the validation (drop down lists). Each line in numbered,
so row 30 will be referenced as "30" in column A. Then, the current
time and date is inserted, as well as the "no" marker in the "closed"
column to indicate the call is open.

The SHOWOPEN macro filters on calls that are marked as "not closed",
using autofilter.

The second sheet is a list of names or items for the drop down lists.

Here are the headers for the 'database' sheet.

Ref Date Time Name Incident type Ware Details Description Helpdesk
1 Escalation Helpdesk 2 Solution Closed Date closed Duration Same day


Thanks.
Jim

Here are the macros.

--------------------------------


Sub newline()
'
' newline Macro
' New incident line
'
' Keyboard Shortcut: Ctrl+Shift+N

'Select database sheet
Sheets("database").Select

'Remove auto-filter, if any. (the SHOWOPEN macro will use autofilter to
display open calls)
Range("A11").Select
ActiveSheet.AutoFilterMode = False

' Sort by incident number

Columns("A").Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending,
Key2:=Range("B2") _
, Order2:=xlAscending, Key3:=Range("C2"), Order3:=xlAscending,
Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal,
DataOption3:= _
xlSortNormal

' Select column A
Range("A1").Select

' count number of lines
n = Selection.CurrentRegion.Rows.Count
Rows(n).Copy

' Go to last line and copy it
Rows(n).Select
Selection.Copy

' Go to new line below and paste the validation (time, names...)
Rows(n + 1).Select

Selection.PasteSpecial Paste:=xlPasteValidation, Operation:=xlNone,
_
SkipBlanks:=False, Transpose:=False

'and paste formats
Selection.PasteSpecial Paste:=xlPasteFormats,
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

' Send ESC key to deselect row
SendKeys ("{ESCAPE}")


'Increment incident number and type it as the reference number
ActiveCell.Value = n + 1


'Move to the right to Date column
ActiveCell.Offset(0, 1).Select

'insert today's date
ActiveCell.Value = Date

'move to the time cell
ActiveCell.Offset(0, 1).Select

'insert time
ActiveCell.Value = Now


'move to the "closed" column and enter "No" by default (call is
therefore 'open' by default)
ActiveCell.Offset(0, 10).Select
ActiveCell.FormulaR1C1 = "No"

'then return to Name cell
ActiveCell.Offset(0, -9).Select



'
End Sub

Sub SHOWOPEN()
'
' SHOWOPEN Macro
' Macro recorded 24/08/2006 by PE
'Ctrl Shift I

'
'force auto-filter
Range("A11").Select
Selection.AutoFilter

' filter for NO in closed column

Worksheets("database").Range("A1").AutoFilter _
field:=13, _
Criteria1:="no"

'Sort by date (descending) and time, then by status (closed/open)

Columns("A").Select
Range("P1").Activate
Selection.Sort Key1:=Range("B2"), Order1:=xlDescending,
Key2:=Range("C2") _
, Order2:=xlDescending, Key3:=Range("M2"), Order3:=xlAscending,
Header _
:=xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom _
, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal,
DataOption3:= _
xlSortNormal

' scroll to top of the screen

Range("A1").Select
End Sub

 

[Jim Rech]

I'm guessing but if you have 'track changes' on, that would presumably slow
a macro down. Perhaps you should unshare the workbook before running a
macro if possible.

--
Jim
| Hi,
| I have a very simple two sheet Excel file with two macros to act as a
| basic helpdesk database. They work fine and fast - but when I share the
| workbook, the macros become terribly slow. I don't know why sharing the
| workbook slow down so much the macros.
|
| As I don't know VBA, most of the code is "recorded".
| I've heard that the "select" command is slowing down execution of the
| code.
|
| Perhaps, someone could simply tell me by what to replace the recorded
| select code by a smarter alternative.
|
| The NEWLINE macro copies the last existing row, then paste below the
| formatting and the validation (drop down lists). Each line in numbered,
| so row 30 will be referenced as "30" in column A. Then, the current
| time and date is inserted, as well as the "no" marker in the "closed"
| column to indicate the call is open.
|
| The SHOWOPEN macro filters on calls that are marked as "not closed",
| using autofilter.
|
| The second sheet is a list of names or items for the drop down lists.
|
| Here are the headers for the 'database' sheet.
|
| Ref Date Time Name Incident type Ware Details Description Helpdesk
| 1 Escalation Helpdesk 2 Solution Closed Date closed Duration Same day
|
|
| Thanks.
| Jim
|
| Here are the macros.
|
| --------------------------------
|
|
| Sub newline()
| '
| ' newline Macro
| ' New incident line
| '
| ' Keyboard Shortcut: Ctrl+Shift+N
|
| 'Select database sheet
| Sheets("database").Select
|
| 'Remove auto-filter, if any. (the SHOWOPEN macro will use autofilter to
| display open calls)
| Range("A11").Select
| ActiveSheet.AutoFilterMode = False
|
| ' Sort by incident number
|
| Columns("A").Select
| Selection.Sort Key1:=Range("A2"), Order1:=xlAscending,
| Key2:=Range("B2") _
| , Order2:=xlAscending, Key3:=Range("C2"), Order3:=xlAscending,
| Header:= _
| xlGuess, OrderCustom:=1, MatchCase:=False,
| Orientation:=xlTopToBottom, _
| DataOption1:=xlSortNormal, DataOption2:=xlSortNormal,
| DataOption3:= _
| xlSortNormal
|
| ' Select column A
| Range("A1").Select
|
| ' count number of lines
| n = Selection.CurrentRegion.Rows.Count
| Rows(n).Copy
|
| ' Go to last line and copy it
| Rows(n).Select
| Selection.Copy
|
| ' Go to new line below and paste the validation (time, names...)
| Rows(n + 1).Select
|
| Selection.PasteSpecial Paste:=xlPasteValidation, Operation:=xlNone,
| _
| SkipBlanks:=False, Transpose:=False
|
| 'and paste formats
| Selection.PasteSpecial Paste:=xlPasteFormats,
| Operation:=xlNone, _
| SkipBlanks:=False, Transpose:=False
|
| ' Send ESC key to deselect row
| SendKeys ("{ESCAPE}")
|
|
| 'Increment incident number and type it as the reference number
| ActiveCell.Value = n + 1
|
|
| 'Move to the right to Date column
| ActiveCell.Offset(0, 1).Select
|
| 'insert today's date
| ActiveCell.Value = Date
|
| 'move to the time cell
| ActiveCell.Offset(0, 1).Select
|
| 'insert time
| ActiveCell.Value = Now
|
|
| 'move to the "closed" column and enter "No" by default (call is
| therefore 'open' by default)
| ActiveCell.Offset(0, 10).Select
| ActiveCell.FormulaR1C1 = "No"
|
| 'then return to Name cell
| ActiveCell.Offset(0, -9).Select
|
|
|
| '
| End Sub
|
| Sub SHOWOPEN()
| '
| ' SHOWOPEN Macro
| ' Macro recorded 24/08/2006 by PE
| 'Ctrl Shift I
|
| '
| 'force auto-filter
| Range("A11").Select
| Selection.AutoFilter
|
| ' filter for NO in closed column
|
| Worksheets("database").Range("A1").AutoFilter _
| field:=13, _
| Criteria1:="no"
|
| 'Sort by date (descending) and time, then by status (closed/open)
|
| Columns("A").Select
| Range("P1").Activate
| Selection.Sort Key1:=Range("B2"), Order1:=xlDescending,
| Key2:=Range("C2") _
| , Order2:=xlDescending, Key3:=Range("M2"), Order3:=xlAscending,
| Header _
| :=xlGuess, OrderCustom:=1, MatchCase:=False,
| Orientation:=xlTopToBottom _
| , DataOption1:=xlSortNormal, DataOption2:=xlSortNormal,
| DataOption3:= _
| xlSortNormal
|
| ' scroll to top of the screen
|
| Range("A1").Select
| End Sub
|

 

[puba]

Jim Rech a écrit :

I'm guessing but if you have 'track changes' on, that would presumably slow
a macro down. Perhaps you should unshare the workbook before running a
macro if possible.

Thanks for your reply. "Track changes" is not on, but it is a "shared"
workbook. Unsharing the workbook before running the macro definitely
does make the macros lightening fast. However, this is not an option.

The code can probably be tuned to make the running faster as the "macro
recorder" generates somehow crude code, I believe.

For example, I'm guessing that these two lines could possibly rewritten
better.
They select the entire columns from A to P in order to sort the rows by
date. It may be possible to ONLY select the existing rows (currently
only less than 50) and thus do a sorting on a much smaller scale.

Columns("A").Select
| Range("P1").Activate

 

[puba]

Replaced before the "sort" instruction the following lines

Columns("A").Select
Range("P1").Activate

by

ActiveCell.CurrentRegion.Select

That way, only the current cells are sorted instead of the 65000 rows.

 

[Jim Rech]

But what does the workbook being shared have to do with this? I said the
problem occurs only when shared.

--
Jim
Replaced before the "sort" instruction the following lines

Columns("A").Select
Range("P1").Activate

by

ActiveCell.CurrentRegion.Select

That way, only the current cells are sorted instead of the 65000 rows.

 

[puba]

Good question. Not sure why. I suppose that Excel can fairly quickly
sort through the empty cells when not shared. But when it is shared, I
guess it makes all sort of comparaison for each cell.
The smaller selection makes the macro run faster when not shared too,
but the huge difference is when it is shared.


Jim Rech a écrit :