Code Running Slowly - Help Me Optimize?

Olson14

New Member
Joined
Apr 23, 2020
Messages
9
Office Version
  1. 2016
New to VBA and using a lot of the Macro Recorder. Tried my best to speed up this code, but it still takes a long time. Hoping someone has some advice for me? This code is unloading a userform. I appreciate the help in advance!

VBA Code:
Private Sub SubmitButton_Click()

    Application.ScreenUpdating = False
    Application.CutCopyMode = False

If ComboBox1.value = "" Then
    MsgBox "Enter Manager Name", , "Error"
    Exit Sub
End If

If ComboBox4.value = "" Then
    MsgBox "Enter Employee Name", , "Error"
    Exit Sub
End If

If TextBox3.value = "" Then
    MsgBox "Enter Date", , "Error"
    Exit Sub
End If

If ComboBox3.value = "" Then
    MsgBox "Enter Details About Topics Discussed", , "Error"
    Exit Sub
End If

    Sheets("Entries").Visible = True
    Sheets("Entries").Range("A2").End(xlDown).Select
    Selection.Offset(1, 0).value = ComboBox1.value
    Selection.Offset(1, 1).value = ComboBox4.value
    Selection.Offset(1, 2).value = TextBox3.value
    Selection.Offset(1, 3).value = ComboBox3.value
    Selection.Offset(1, 4).Formula = "=DATE(YEAR(C" & ActiveCell.Offset(1, 0).Row & "),FLOOR(MONTH(C" & ActiveCell.Offset(1, 0).Row & ")+2,3)+1,0)"
    Selection.Offset(1, 5).Formula = "=VLOOKUP($B" & ActiveCell.Offset(1, 0).Row & ",EmpData!$A:$E,5,True)"
    Selection.Offset(1, 4) = Selection.Offset(1, 4).value
    Selection.Offset(1, 5) = Selection.Offset(1, 5).value
    Range(Range("A2"), Selection.End(xlToRight).End(xlDown)).Select
    ActiveWorkbook.Worksheets("Entries").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Entries").Sort.SortFields.Add2 Key:=Range("C2:C1000" _
        ), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Entries").Sort
        .SetRange Range("A2:F1000")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Sheets("Home").Select
    Sheets("Entries").Visible = False
    Unload Take30
    ThisWorkbook.Save
    MsgBox "Entry Complete!"
End Sub
 
Last edited by a moderator:

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Welcome to the Forum!

How long is a long time?

There are many little things here that could be tidied up - the macro recorder doesn't produce efficient code - but there's nothing obvious that should produce slow run time.

Is your workbook big and slow already, before you start running code?

Do you have other code that could be being triggered, e.g. do you have a Sub Worksheet_Change that will run if changes are made to Worksheets("Entries")?
 
Upvote 0
Thanks for your post. My code is taking roughly 5-10 seconds to run. It really isn't too bad right now. But the workbook is used for record keeping and I am afraid that the larger it grows, the longer it may take? I did a little bit of digging and found that array formulas may be an issue? I do have several but am not sure of a good work around. As I mentioned the speed right now, isn't too bad but am hoping to avoid future problems. Here is my name manager...

1587737136862.png
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,114,002
Members
448,543
Latest member
MartinLarkin

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