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

**johnw993** · 02-08-2015, 01:56 AM

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

**stnkynts** · 02-08-2015, 06:13 PM

Can you submit your workbook? I am a little curious about the interaction of cboDept1_Change() and the UserForm_Initialize processes and how they may be affecting each other. Also, this is beyond reason:

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

**johnw993** · 02-08-2015, 07:25 PM

stnkynts,

Thanks for your reply. See attached my workbook (in zip file).

The Workbook is 'Menu driven' and will only show the sheets you've opened. To see all the sheets in the workbook, from the Main Menu click on the Administration button, use Admin as user name and the letter a (small caps) as the password. Then click on "Show All Sheets" button in the upper right.
The password to un-protect any or all sheets is the letter j (small caps).

I'm no expert in Excel, and am trying to learn VBA as I go, so I'm sure you'll find some illogical processes in the workbook.

Thank you for your willingness to take a look.

John

**stnkynts** · 02-08-2015, 09:21 PM

Please remove from zip file and attach directly.

**johnw993** · 02-09-2015, 02:43 AM

Please advise me how to. The file is too large (4.5 MB) to be accepted as an attachment.

**johnw993** · 02-11-2015, 09:34 PM

I've been asked to show my workbook, which I'm glad to do, but the forum doesn't allow any workbook larger than 1K to be shared as an attachment. When I send the Workbook in a zip file, I'm being asked to please unzip and send it as a workbook. I'm confused. Please advise what I should do.
This site has been a great learning experience and I really, really would like some feedback on the issues I'm trying to solve in my workbook.

Thank you,

John

**Leith Ross** · 02-11-2015, 09:53 PM

Hello johnw993,

Leave your workbook zipped. You were correct in following the forum's rule for posting. If a member is unable to open the workbook because the file is corrupted then you should repost. However, if a member does do not possess the needed software to open the file, that is not your problem.

**johnw993** · 02-11-2015, 10:18 PM

Thank you for your reply Leith. I was starting to wonder what I was doing wrong.
I guess if no-one else replies to my post/question, my question must not be enticing enough.
It's a challenge to find a VBA expert who can look at my code and explain to me why my code is slowing things down (because I suspect I wrote it very poorly).
I'll post my message/question again.

Regards,

John

**johnw993** · 02-08-2015, 05:51 PM

Alan,

My apologies for the mistake. Is the moderation request fulfilled correctly?

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

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

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

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Similar Threads

The runtime for my code I wrote takes too long, is there a way tocan you condense?

Wrote code to print worksheets based on numbers in cells and rows, but is it efficient?

VBA code that I wrote seems to be looping without a Do...Loop statement. Why?

QUERY &amp; HELP: so slow executing VBA code... :S

[SOLVED] QUERY & HELP: so slow executing VBA code... :S

Bookmarks

Bookmarks

Posting Permissions