VBA Code running very slowly

Question

I have a listbox on a userform and when the user selects an item it will add an item to the worksheet as well as place a formula in the next 8 columns

but when I run the macro it runs terribly slow.  If I close any other programs on my computer it will run faster but not as fast as expected.  I have two different codes set up to perform the function.

The first code places the formula in the cells

Dim shname, item As String

Dim itemrng, qtyrng, toqtyrng, unirng, laborrng As Range

Dim tolaborrng, pricerng, extrng, topricerng As Range

shname = Sheet14.Range("A1").Value

Sheets(shname).Select

item = ListBox1.Value

Set itemrng = Sheets(shname).Range("B5000").End(xlUp).Offset(1, 0)

Set qtyrng = itemrng.Offset(0, 1)

Set toqtyrng = qtyrng.Offset(0, 1)

Set unirng = qtyrng.Offset(0, 2)

Set laborrng = qtyrng.Offset(0, 3)

Set tolaborrng = qtyrng.Offset(0, 4)

Set pricerng = qtyrng.Offset(0, 5)

Set extrng = qtyrng.Offset(0, 6)

Set topricerng = qtyrng.Offset(0, 7)

itemrng.Value = item

qtyrng.Value = "0"

toqtyrng.Value = ("=" & qtyrng.Address & "*$G$4")

unirng.Value = ("=Vlookup(" & itemrng.Address & ",'Mat Ext'!$A$2:$D$100000,3,FALSE)")

laborrng.Value = ("=" & toqtyrng.Address & "*" & unirng.Address)

tolaborrng.Value = ("=" & laborrng.Address & "*$G$2")

pricerng.Value = ("=Vlookup(" & itemrng.Address & ",'Mat Ext'!$A$2:$D$100000,4,FALSE)")

extrng.Value = ("=" & toqtyrng.Address & "*" & pricerng.Address)

topricerng.Value = ("=" & tolaborrng.Address & "+" & extrng.Address & "+(" & extrng.Address & "*$G$5)")

qtyrng.Select

The other codes accomplish the same task by another route

Dim itemrng, ref1, ref2, ref3, rngtocopy As Range

Dim shname, item As String

shname = Sheet14.Range("A1").Value

Sheets(shname).Select

item = ListBox1.Value

Set ref1 = itemrng.Offset(-1, 2)

Set ref2 = itemrng.Offset(-1, 8)

Set rngtocopy = Range(ref1, ref2)

Set ref3 = itemrng.Offset(0, 2)

rngtocopy.Select

Selection.copy

ref3.Select

ActiveSheet.Paste

I have no idea why either of these codes run so slow, I mean maybe maybe a few seconds, I need it to happen really fast because the user will be using the arrow keys to select data and load to sheet.

Any help would be awesome!!

Answer 1

First thing: when you declare varibales and objects, you have to specify the type each time otherwise they are just declared as Variants.  You should do:

    Dim itemrng As Range, qtyrng As Range, toqtyrng As Range, unirng As Range, laborrng As Range

not:

    Dim itemrng, qtyrng, toqtyrng, unirng, laborrng As Range

Not sure whether that would cause a noticable drop-off in speed though (but doing it correctly wil help you by providing the correct intellisense dropdowns each time you type a dot).

Secondly, to enter a formula in a cell you should use:

    toqtyrng.Formula = "=" & qtyrng.Address & "*$G$4"

not:

toqtyrng.Value = ("=" & qtyrng.Address & "*$G$4")

Thirdly, I notice you're doing a VLOOKUP over 100,000 rows (x4 columns).  Do you really need that many rows?  Could you find the last row by doing something like:

Dim lastRow As Long

    lastRow = Range("A" & Sheets(shname).Rows.Count).End(xlUp)

?

Final point.  Best way to speed up the code is to suspend screen updating and calculations.  top and tail your code with:

Application.ScreenUpdating = False

    Application.Calculation = xlCalculationManual

    'Do stuff...    Application.ScreenUpdating = True

    Application.Calculation = xlCalculationAutomatic

Please give that a try and post back if it's still really slow.

Hope that helps.

Cheers

Rich

Answer 2

I'll focus on your second code instead of the first since it should be a bit faster. There are a number of things that will speed things up.

1. Your variables are declared improperly.

Dim itemrng, ref1, ref2, ref3, rngtocopy As Range

is equivalent to

Dim itemrng as Variant, ref1 as Variant, ref2 as Variant, ref3 as Variant, rngtocopy As Range

You should use

Dim itemrng As Range, ref1 As Range, ref2 As Range, ref3 As Range, rngtocopy As Range

Variants are slow. Same issue for your strings.

2. Don't select things. Change

rngtocopy.Select

Selection.copy

ref3.Select

ActiveSheet.Paste

To

rngtocopy.copy Destination:= ref3

3. Turn off calculations and events

With Application

.Calculation = xlCalculationManual

.EnableEvents = False

End With

'***your code here

With Application

.Calculation = xlCalculationAutomatic

.EnableEvents = True

End With

Point 3 is probalby the item that will yeild the greatest improvement.

Answer 3

Wow guys these suggestions seem awesome, as soon as I get a change I will look at my program and see what happens.  I think your suggestions can speed up my entire app.