Basic VBA questions....

[Eric @ BP-EVV]

1. Do you have to "select" a worksheet or cell (or range of cells) before you
can copy a cell (or range of cells) from that worksheet ?
2. Do you have to "select" a worksheet or cell before you can assign a
formula to a cell in that worksheet ?
3. Do you have to "select" a row in a worksheet before you delete that row ?

These may seem like very basic VBA questions....and in reality they
are....what I am trying to get at is efficiency. I have some code in a
workbook that pulls data from a SQL table on an AS/400 system and them
performs several calcuations, sorts, etc. on that data. When I run this
routine for all 17 locations at one time, it takes nearly 10 minutes to run
to completion. What I'm really after here is suggestions on how to make my
code more efficient....what can I do to speed up this process ?

Thanks !

 

[Gary Keramidas]

other more knowledgeable will probably respond, but to answer all 3 questions,
no.

in fact, it's preferable not to,.

make sure you turn off screen updating and set calculation to manual while your
code executes and then set calculation back to automatic when it's finished.

 

[paul.robinson]

Hi
No to all three.
If tou use the macro recorder you typically get

Something.Select
Selection.DoSomething

You can always cut out the middle bit

Something.DoSomething

Have a look at your code and see how many of these selections you can
remove.
regards
Paul

 

[Eric @ BP-EVV]

Gary - thanks for the reply.... I have already got screen updating turned
off, as well as having set calculation to manual. I'm sure I can elminate
some code by not selecting cells first, which I would think can only help the
speed of this routine.

Does anyone else have other suggestions for improving speed or efficiency ?

Thanks !

 

[Don Guillett]

No to all

range("a2:a22").copy sheets("destshtname").range("a1")

sheets("destshtname").range("a1").formula="a1*a2"

sheets("destshtname").rows(3).delete

 

[Dave Peterson]

#1. Nope.

dim RngToCopy as range
dim DestCell as range

set rngtocopy = worksheets("Sheet999").range("x1:z99")
set destcell = worksheets("sheet888").range("u71")

rngtocopy.copy _
destination:=destcell

#2. Nope.

worksheets("sheet999").range("z99").value = "what you want here"

#3. Nope.

worksheets("sheet999").rows(17).delete


==========
I'd turn calculation to manual, then do the work, then change the calculation
back to whatever it was before.

In fact, there are a few things that can slow down macros. I do this kind of
thing to speed them up:

Option Explicit
Sub testme()

Dim CalcMode As Long
Dim ViewMode As Long

Application.ScreenUpdating = False

CalcMode = Application.Calculation
Application.Calculation = xlCalculationManual

ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

ActiveSheet.DisplayPageBreaks = False

'do the work

'put things back to what they were
Application.Calculation = CalcMode
ActiveWindow.View = ViewMode

End Sub

Your code will replace the "'do the work" line.

When/if you're hiding rows/columns, excel wants to figure out where to draw
those lines each time you change the layout.

 

[Dave Peterson]

#2. Nope.

worksheets("sheet999").range("z99").formula = "=sum(a:a)"

(using a formula)

 

[Eric @ BP-EVV]

Based on your reply, Dave, along with those of Paul and Don, it's
unanimous....I'm an amateur at this VBA coding stuff ! None the less,
this forum has always been of great help to me and I am grateful for the
responses I get to my questions. I will implement the suggestions from y'all
and see how my routine's performance changes.

One more question: what does the "Option Explicit" do / mean ?

Thanks again !

 

[JLGWhiz]

Option Explicit means that all of your variables have to be declared. If
they are not, the first one you try to use that is not declared will generate
an error. It also helps you to catch typos because VBA will see the typo as
a new undeclared variable and send you a message. For short procedures it is
not that important, but it can save you a lot of grief in long and complex
procedures.

 

[Eric @ BP-EVV]

Thanks for that information....it sounds like I need to be using "Option
Explicit" for future coding projects.

 

[Dave Peterson]

I think it's very useful for short code, too.

As well as making typos easier to find, if you declare your variables correctly:

Dim wks as worksheet
not
dim wks as variant 'or as object

Then you'll get VBAs helpful intellisense--that's the popup that you see after
typing the dot in:

wks.

You'll see a list of all the properties and methods that you could use.

 

[Rick Rothstein]

You can turn it on automatically for all your code windows by clicking
Tools/Options on the VB editor's menu bar and then putting a check mark in
the Code Setting checkbox labeled "Require Variable Declaration". Note, this
only affects new (empty) code windows that you call up... if you have
existing code windows with code already in them, you have to add the Option
Explicit manually to them.

 

[Eric @ BP-EVV]

Just for the record, I have added "Option Explicit" and the compiler found 3
variables I had not defined...I fixed that...I commented out all the
unnecessary select statements, and revamped my delete, copy and paste type
statements.

I timed the performance with a stop watch before and after making these
changes, with the same "other applications" open on my computer and the
performance did improve a little....went from 9 minutes 40 seconds down to 9
minutes 20 seconds, which equates to almost a 3.5 % increase in speed....I
was hoping for more, so now I am going to study hard on the use of
calculation statements in the routine. I have it turned to manual at the
beginning...I need to make sure I am only forcing the recalculation when
absolutely necessary.

 

[Dave Peterson]

One of the best things that comes out of your clean up isn't the (slightly)
improved execution speed. I think that you'll find the code easier to
understand and easier to update.

Those worksheets().select and range().selects can get really confusing really
fast.

And some things that may improve speed:

Option Explicit
Sub testme()

Dim CalcMode As Long
Dim ViewMode As Long

Application.ScreenUpdating = False

CalcMode = Application.Calculation
Application.Calculation = xlCalculationManual

ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView

ActiveSheet.DisplayPageBreaks = False

'do the work

'put things back to what they were
Application.Calculation = CalcMode
ActiveWindow.View = ViewMode

End Sub

Just for the record, I have added "Option Explicit" and the compiler found 3
variables I had not defined...I fixed that...I commented out all the
unnecessary select statements, and revamped my delete, copy and paste type
statements.

I timed the performance with a stop watch before and after making these
changes, with the same "other applications" open on my computer and the
performance did improve a little....went from 9 minutes 40 seconds down to 9
minutes 20 seconds, which equates to almost a 3.5 % increase in speed....I
was hoping for more, so now I am going to study hard on the use of
calculation statements in the routine. I have it turned to manual at the
beginning...I need to make sure I am only forcing the recalculation when
absolutely necessary.

 

[Don Guillett]

There could be other reasons. If desired, you may send your workbook to my
address below and I'll take a look. Please make sure I know who you are and
what you are talking about.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

Just for the record, I have added "Option Explicit" and the compiler found
3
variables I had not defined...I fixed that...I commented out all the
unnecessary select statements, and revamped my delete, copy and paste type
statements.

I timed the performance with a stop watch before and after making these
changes, with the same "other applications" open on my computer and the
performance did improve a little....went from 9 minutes 40 seconds down to
9
minutes 20 seconds, which equates to almost a 3.5 % increase in speed....I
was hoping for more, so now I am going to study hard on the use of
calculation statements in the routine. I have it turned to manual at the
beginning...I need to make sure I am only forcing the recalculation when
absolutely necessary.

 

[Eric @ BP-EVV]

Don,

Thanks for the offer to review my workbook/code...I appreciate it...and I
may take you up on in shortly. I'm down to the last three days on my current
contract, and I would hate to ask for your help with such a short deadline,
and since the application I have developed works, just slower than I would
like, I'm willing to leave it as is as I depart, and should you be able to
help with improvements after the fact, I'll email the company and give them
the benefits of the improvements....heck...that may land me another contract
with them !

I will email you the workbook from my personal email....it will be coming
from (e-mail address removed)

Thanks again !
Eric

 

[Eric @ BP-EVV]

Just an FYI for anyone interested....I put in several breakpoints to my code
and used a stopwatch to time things....the sections of code that are slowing
down this process are the sections where Excel is calculating. I'm working on
trying to modify this application so that all the work is done on the AS/400
and only launch the execution and have the reporting in Excel for the users.
The AS/400 queries took about 2 minutes in total for 6 different queries,
while Excel took over 7 minutes to do the rest of the work when 17 facilitles
were selected simultaneously....about 120,000 records in Excel with between 8
and 10 columns being calculated for each record in order to complete the
analysis.

Don,

Thanks for the offer to review my workbook/code...I appreciate it...and I
may take you up on in shortly. I'm down to the last three days on my current
contract, and I would hate to ask for your help with such a short deadline,
and since the application I have developed works, just slower than I would
like, I'm willing to leave it as is as I depart, and should you be able to
help with improvements after the fact, I'll email the company and give them
the benefits of the improvements....heck...that may land me another contract
with them !

I will email you the workbook from my personal email....it will be coming
from (e-mail address removed)

Thanks again !
Eric