Unpleasant VBA Agility
Besides the mentioned screen updating, you may do the same with
calculations:
Application.Calculation = xlCalculationManual
You can reset this to:
Application.Calculation = xlCalculationAutomatic
after your macro runs.
Use early binding, and work with objects. This allows you to set With
blocks, but also improves performance. One example of this is
CreateObject() function. Whenever possible, include a reference to a
library instead and do a strict type. Instead of this:
dim objWord as Object
set objWord = CreateObject("Word.Application")
use this:
dim objWord as Word.Application
set objWord = new Word.Application
A similar principle applies to using Selection, ActiveCell,
ActiveSheet, etc. If you need to do something with the current
selection (assuming on a worksheet), do this:
dim rngSelection as Excel.Range
set rngSelection = Application.Selection
Or, if you need to use active sheet, do this:
dim wshSheet as Excel.Worksheet
set wshSheet = Application.ActiveSheet
As mentioned above, do not "select" stuff to work on it. Macro
recorder will generate code like this:
ActiveSheet.Range("A1:D10").Select
Selection.Font.Bold = True
Instead, do this:
ActiveSheet.Range("A1:D10").Font.Bold = True
Use With blocks whenever possible. For instance, you might be tempted
to write code like this:
objWorksheet.Name = "My Worksheet"
objWorksheet.Visible = xlSheetHidden
objWorksheet.EnableAutoFilter = False
objWorksheet.PageSetup.FitToPagesTall = 2
objWorksheet.PageSetup.FitToPagesWide = 1
objWorksheet.PageSetup.Zoom = 70
This is not efficient, because the objWorksheet is resolved each
time. Here's how it ought to be done:
With objWorksheet
.Name = "My Worksheet"
.Visible = xlSheetHidden
.EnableAutoFilter = False
With .PageSetup
.FitToPagesTall = 2
.FitToPagesWide = 1
.Zoom = 70
End With
End With
Those are some main beginner inefficiencies (not sure of your level).
Of course, code optimization is a complex subject, so it's difficult
to tell which suggestions will work best for your particular
situation. Perhaps if you describe some of the functionalities
provided by your add-in some more examples specific to your situation
can be provided.
Good luck.
-Ilia
On May 20, 8:58 pm, Varne wrote:
Hi
Withholding screen updating helps. Thank you Bernard and N10.
Whiz
Could you please send 2 examples of best practice codes and corresponding
untrained programmer's entries to
Many Thanks
Varne M
"N10" wrote:
Hi
Try putting
"application.screenupdating = false"
at the starts of macros for which you wish to hide the "agility" factor.
This command hides changes of sheets,data updating visually and a host of
others actions
You may turn on screenupdating gain with
"application.screenupdating = true"
Happy hunting
Regards N10
"Varne" wrote in message
...
Hi Chip
Thanks
If you click for action or report in an accounting package it comes to
your
screen with just one change. However underneath the dbms must be gathering
data or consolidating data from many tables. You do not see it.
Now VBA does it in front of you running to and from pages, fields and
cells.
Cannot this be hidden?
Varne M
"Chip Pearson" wrote:
Just what does "agile" mean in the context of VBA?
"Varne" wrote in message
...
Hi
My VBA applications are too agile. I am trying to sell a few VBA
applications on accounting but am afraid the said problem will be a
negative
factor. Cannot VBA made to perform below the surface with an inbuilt
macro
screening it?
Varne M- Hide quoted text -
- Show quoted text -
|