Results 1 to 9 of 9

Why is this VBA code (that I wrote) executing so slow?

Threaded View

  1. #1
    Forum Contributor
    Join Date
    11-30-2012
    Location
    Colorado, US
    MS-Off Ver
    MS Prof. 2010 and 2013 + 365
    Posts
    113

    Why is this VBA code (that I wrote) executing so slow?

    I've created a user form for people to enter data into. All is working well in the workbook, except this form is slowing things down. It takes WAY too long to process the data that is entered (and thus allocated).
    Any suggestions on how to speed this up?

    Private Sub cboDept1_Change()
    Dim idx As Long
    Dim I As Long
    
    idx = cboDept1.ListIndex
    
    If idx <> -1 Then
    With Sheet3
    For I = 3 To 22
    Me.Controls("Label" & I + 12).Caption = .Range("C" & I).Offset(0, idx).Text
    Next I
    End With
    End If
    
    End Sub
    
    Private Sub cmdCancel_Click()
    Unload Me
    End Sub
    
    Private Sub cmdClearForm_Click()
    Call UserForm_Initialize
    End Sub
    
    Private Sub cmdOK4_Click()
    Application.ScreenUpdating = False
    Dim rng As Range, wd As Worksheet, r As Long
    Set wd = ActiveWorkbook.Sheets("IndicatorDB")
    r = wd.Range("A" & Rows.Count).End(xlUp).Row
    For I = 1 To 20
    If Me.Controls("TextBox" & I).Value <> "" Then
    
    r = r + 1: Set rng = wd.Range("A" & r)
    rng.Offset(-1, 0).EntireRow.Copy: rng.PasteSpecial xlPasteFormulas
    
    rng.Value = cboName1.Value
    rng.Offset(0, 1) = cboDept1.Value
    rng.Offset(0, 2).Value = Me.Controls("Label" & I + 14).Caption
    rng.Offset(0, 3).Value = DTPicker4.Value
    rng.Offset(0, 4).Value = Me.Controls("TextBox" & I).Value
    Set rng = rng.Offset(1)
    End If
    Next I
    
    
    Sheets("Forms").Select
    Unload Me
    UserForm2.Show
    Application.ScreenUpdating = True
    End Sub
    
    Private Sub UserForm_Initialize()
    With Worksheets("ADMIN")
    cboDept1.List = .Range("F4", .Range("F" & Rows.Count).End(xlUp)).Value
    End With
    
    With Worksheets("Employees")
    cboName1.List = .Range("A1", .Range("A" & Rows.Count).End(xlUp)).Value
    End With
    
    End Sub
    
    Sub SlowWithFlicker()
    
    Dim rCell As Range
    
    For Each rCell In Range("A1:A15000")
    
    rCell.Select
    
    Next rCell
    
    End Sub
    
    
    
    Sub FastWithNoFlicker()
    
    Dim rCell As Range
    
    Application.ScreenUpdating = False
    
    For Each rCell In Range("A1:A15000")
    
    rCell.Select
    
    Next rCell
    
    Application.ScreenUpdating = True
    
    End Sub
    Last edited by johnw993; 02-08-2015 at 02:20 AM. Reason: didn't clearly show message and code

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. The runtime for my code I wrote takes too long, is there a way tocan you condense?
    By dnice0123 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-18-2014, 03:33 AM
  2. Replies: 10
    Last Post: 11-13-2013, 09:01 PM
  3. VBA code that I wrote seems to be looping without a Do...Loop statement. Why?
    By carpe.cervisiam in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-07-2012, 12:08 AM
  4. QUERY &amp;amp; HELP: so slow executing VBA code... :S
    By KevinGPO in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-13-2006, 11:50 AM
  5. QUERY & HELP: so slow executing VBA code... :S
    By KevinGPO in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-13-2006, 11:50 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1