Problem with macro speed when placed on network drive

M

madchemist

Board Regular
Joined
Jul 10, 2006
Messages
198
The following macro takes less than 2 seconds to execute when stored on my local machine. Once I load it onto the network drive and open it, it takes over 30 seconds. It even takes forever if I then take it off the network drive and open it locally on my machine. Even the original file that wasn't loaded onto the network takes forever.

Does anyone have any idea why it would do this? I really need to get around this problem. This thing works so beautifully minus this issue. Thanks!

Code: 
Application.ScreenUpdating = False
    Sheets(PIA).Select
    [H2].Select
    Selection.NumberFormat = "@"
    frmAdd.Show
    [B2].FormulaR1C1 = PIA
    Sheets("Action Item Trends").Select

        
    [C9].FormulaR1C1 = "=SUMPRODUCT(--(INT('" & PIA & "'!R2C8:R2500C8)=RC[-1]),--('" & PIA & "'!R2C16:R2500C16<>""Closed""))"
    [C10].FormulaR1C1 = "=SUMPRODUCT(--(INT('" & PIA & "'!R2C8:R2500C8)=RC[-1]),--('" & PIA & "'!R2C16:R2500C16<>""Closed""))"
    [C11].FormulaR1C1 = "=SUMPRODUCT(--(EXACT('" & PIA & "'!R2C[5]:R2500C[5],2.06)),--('" & PIA & "'!R2C[13]:R2500C[13]<>""Closed""))"
    [C12].FormulaR1C1 = "=SUMPRODUCT(--(INT('" & PIA & "'!R2C8:R2500C8)=RC[-1]),--('" & PIA & "'!R2C16:R2500C16<>""Closed""))"
    [C13].FormulaR1C1 = "=SUMPRODUCT(--(EXACT('" & PIA & "'!R2C[5]:R2500C[5],3.01)),--('" & PIA & "'!R2C[13]:R2500C[13]<>""Closed""))"
    [C14].FormulaR1C1 = "=SUMPRODUCT(--(EXACT('" & PIA & "'!R2C[5]:R2500C[5],3.02)),--('" & PIA & "'!R2C[13]:R2500C[13]<>""Closed""))"
    [C15].FormulaR1C1 = "=SUMPRODUCT(--(INT('" & PIA & "'!R2C8:R2500C8)=RC[-1]),--('" & PIA & "'!R2C16:R2500C16<>""Closed""))"
    [C16].FormulaR1C1 = "=SUMPRODUCT(--(INT('" & PIA & "'!R2C8:R2500C8)=RC[-1]),--('" & PIA & "'!R2C16:R2500C16<>""Closed""))"
    [C17].FormulaR1C1 = "=SUMPRODUCT(--(EXACT('" & PIA & "'!R2C[5]:R2500C[5],5.02)),--('" & PIA & "'!R2C[13]:R2500C[13]<>""Closed""))"
    [C18].FormulaR1C1 = "=SUMPRODUCT(--(EXACT('" & PIA & "'!R2C[5]:R2500C[5],5.05)),--('" & PIA & "'!R2C[13]:R2500C[13]<>""Closed""))"
    [C19].FormulaR1C1 = "=SUMPRODUCT(--(INT('" & PIA & "'!R2C8:R2500C8)=RC[-1]),--('" & PIA & "'!R2C16:R2500C16<>""Closed""))"
    [C20].FormulaR1C1 = "=SUMPRODUCT(--(INT('" & PIA & "'!R2C8:R2500C8)=RC[-1]),--('" & PIA & "'!R2C16:R2500C16<>""Closed""))"
    [C21].FormulaR1C1 = "=SUMPRODUCT(--(EXACT('" & PIA & "'!R2C[5]:R2500C[5],7.08)),--('" & PIA & "'!R2C[13]:R2500C[13]<>""Closed""))"
    [C22].FormulaR1C1 = "=SUMPRODUCT(--(INT('" & PIA & "'!R2C8:R2500C8)=RC[-1]),--('" & PIA & "'!R2C16:R2500C16<>""Closed""))"
    [C23].FormulaR1C1 = "=SUMPRODUCT(--(INT('" & PIA & "'!R2C8:R2500C8)=RC[-1]),--('" & PIA & "'!R2C16:R2500C16<>""Closed""))"
    [C24].FormulaR1C1 = "=SUMPRODUCT(--(EXACT('" & PIA & "'!R2C[5]:R2500C[5],9.01)),--('" & PIA & "'!R2C[13]:R2500C[13]<>""Closed""))"
    [C25].FormulaR1C1 = "=SUMPRODUCT(--(INT('" & PIA & "'!R2C8:R2500C8)=RC[-1]),--('" & PIA & "'!R2C16:R2500C16<>""Closed""))"
    [C26].FormulaR1C1 = "=SUMPRODUCT(--(EXACT('" & PIA & "'!R2C[5]:R2500C[5],10.01)),--('" & PIA & "'!R2C[13]:R2500C[13]<>""Closed""))"
    [C43].FormulaR1C1 = "=SUMPRODUCT(--('" & PIA & "'!R2C16:R2500C16=""Open""),'" & PIA & "'!R2C17:R2500C17)/COUNTIF('" & PIA & "'!R2C16:R2500C16,""Open"")"
    [C44].FormulaR1C1 = "=SUMPRODUCT(--('" & PIA & "'!R2C16:R2500C16=""Closed""),'" & PIA & "'!R2C17:R2500C17)/COUNTIF('" & PIA & "'!R2C16:R2500C16,""Closed"")"
    [C45].FormulaR1C1 = "=COUNTIF('" & PIA & "'!R2C16:R2500C16,""Closed"")"

    Sheets(PIA).Select
    [C2].Select

Code from frmadd

Code: 
Rows("2:2").Select
    Selection.Insert Shift:=xlDown
    Selection.Rows.AutoFit

    [H2].Select
    Selection.NumberFormat = "@"
    
    Range("A2:D2,F2:J2,L2:P2,R2:S2").Select
    Selection.Locked = False
    Selection.FormulaHidden = False
    
    [C2].Select
    ActiveCell.Value = txtSubject.Value
    ActiveCell.Offset(0, 1) = txtDescription.Value
    ActiveCell.Offset(0, 3) = txtDCF.Value
    ActiveCell.Offset(0, 9) = Application.UserName
    ActiveCell.Offset(0, 4) = ComboBox_Responsible.Value
    ActiveCell.Offset(0, 5) = txtCode.Value
    ActiveCell.Offset(0, 6) = ComboBox_Severity.Value
    ActiveCell.Offset(0, 7) = ComboBox_Frequency.Value
    ActiveCell.Offset(0, 10) = txtDue.Value
    ActiveCell.Offset(0, 16) = txtComments.Value
    
    Range("A2:S2").Select
    Selection.Font.Bold = False
    Selection.Interior.ColorIndex = xlNone
    With Selection.Font
        .Name = "Arial"
        .Size = 8
    End With
    With Selection
        .VerticalAlignment = xlBottom
        .WrapText = True
    End With
    [A2:C2,E2:R2].Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = True
    End With
    [D2,S2].Select
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = True
    End With

    [A2:M2:N2:R2].Select
    Selection.NumberFormat = "[$-409]d-mmm-yy;@"
    [B2:L2,O2:Q2,S2].Select
    Selection.NumberFormat = "General"
    [H2].Select
    Selection.NumberFormat = "@"
    [K2].FormulaR1C1 = "=INDEX({2,3,5},1,MATCH(RC[-2],{""Low"",""Mod"",""High""},0))*INDEX({1,2,3},1,MATCH(RC[-1],{""Low"",""Mod"",""High""},0))"
    [P2].FormulaR1C1 = "Open"
    [P2].Select
    With Selection.Interior
        .ColorIndex = 6
        .Pattern = xlSolid
    End With
    [Q2].FormulaR1C1 = "=NETWORKDAYS(RC[-16],IF(RC[-1]=""open"",TODAY(),IF(RC[-1]=""escalated"",RC[1],RC[-3])))"
    
    [A2].FormulaR1C1 = "=TODAY()"
    [A2].Select
    Selection.Copy
    [A2].Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    [K2].Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual, Formula1:="9"
    Selection.FormatConditions(1).Interior.ColorIndex = 7
    [H2].Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=ISBLANK(H2)"
    Selection.FormatConditions(1).Interior.ColorIndex = 3
    
    Casegenerater
    Unload Me

Code from Casegenerater

Code: 
Sub Casegenerater()
    Prefix = Strings.Left(ActiveSheet.Name, 4)
    Count = 0
    For i = 1 To Cells(Rows.Count, "E").End(xlUp).Row
        If Strings.Left(Cells(i, "E"), 4) = Prefix Then Count = Count + 1
    Next i
    CaseCount = Count + 1
    Cells(2, "E").Value = Prefix & "-" & Format(CaseCount, "0000")
End Sub
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Sort by date Sort by votes
You'll have better results if you tweak your code to eliminate all of the select statements.

Here's an example:

Code: 
With Rows("2:2")
    .Insert Shift:=xlDown
    .Rows.AutoFit
End With

    [H2].NumberFormat = "@"
    
    With Range("A2:D2,F2:J2,L2:P2,R2:S2")
        .Locked = False
        .FormulaHidden = False
    End With
    
    With Range("C2")
        .Value = txtSubject.Value
        .Offset(0, 1) = txtDescription.Value
        .Offset(0, 3) = txtDCF.Value
        .Offset(0, 9) = Application.UserName
        .Offset(0, 4) = ComboBox_Responsible.Value
        .Offset(0, 5) = txtCode.Value
        .Offset(0, 6) = ComboBox_Severity.Value
        .Offset(0, 7) = ComboBox_Frequency.Value
        .Offset(0, 10) = txtDue.Value
        .Offset(0, 16) = txtComments.Value
    End With
    
    With Range("A2:S2")
        .Font.Bold = False
        .Interior.ColorIndex = xlNone
            With .Font
                .Name = "Arial"
                .Size = 8
            End With
        .VerticalAlignment = xlBottom
        .WrapText = True
    End With

As for the speed issue you can try to set caculation to manual until everything's done and calculate at the end of the code.

HTH,

Smitty
 
Upvote 0
wow, the manual calculation made it instantaneous. So could I change it from manual to automatic at the beginning of the code and then change it back and have it calculate at the end of the code? how exactly would I do this? Is this going to take a ton of time to calculate everything then?


Thanks Smitty!
 
Upvote 0
wow, the manual calculation made it instantaneous. So could I change it from manual to automatic at the beginning of the code and then change it back and have it calculate at the end of the code? how exactly would I do this? Is this going to take a ton of time to calculate everything then?
Click to expand...

Just use Application.Calculation = xlManual and set it back to automatic at the end.

Smitty
 
Upvote 0
You might also want to stick a Calculate in there too.
 
Upvote 0
You might also want to stick a Calculate in there too.
Click to expand...

So just put the command calculate somewhere within the code is what you are saying?


Thanks Smitty and Norie. The code is working much better now. I have gone through and cleaned it all up and done the switch to manual and then back to automatic.

Where would you put the calculate command? Thanks!
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Locked
  • Question Question
Help Cleaning Up Code
Replies
2
Views
601
RoryA
RoryA
I
  • Question Question
Is there an easy way to add formulaR1C1 into VBA arrays to speed up calculation time?
Replies
4
Views
1K
ianawwalker
I
L
  • Solved
VBA remove leading zeros
2
Replies
14
Views
2K
Joe4
Joe4
E
  • Solved
Macro Speed Improvement
2
Replies
17
Views
1K
Peter_SSs
Peter_SSs
E
  • Question Question
VBA code clean up
Replies
9
Views
604
mumps
mumps

Forum statistics

Threads
1,224,975
Messages
6,182,112
Members
453,089
Latest member
boonga

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top