Unpleasant VBA Agility

[Guest]

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

 

[Chip Pearson]

Just what does "agile" mean in the context of VBA?

 

[Guest]

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

 

[Bernard Liengme]

Perhaps this helps:
at start of macro: Application.ScreenUpdating = False
at end: Application.ScreenUpdating = True
best wishes

 

[Guest]

The code writing tecnique can also eliminate a lot of the flutter and
flicker. The more you eliminate the select and activate methods, the less
you see the sheets jumping on the screen. I don't know if it can be
completely eliminated, but it can be greatly reduced.

 

[N10]

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

 

[Guest]

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 (e-mail address removed)

Many Thanks


Varne M

 

[ilia]

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("A110").Select
Selection.Font.Bold = True

Instead, do this:

ActiveSheet.Range("A110").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

 

[RB Smissaert]

But is this:

objWorksheet.Name = "My Worksheet"
objWorksheet.Visible = xlSheetHidden
objWorksheet.EnableAutoFilter = False

Any slower than this:

With objWorksheet
.Name = "My Worksheet"
.Visible = xlSheetHidden
.EnableAutoFilter = False
End With

I thought it was the number of dots that mattered.
Just wondering.

RBS

 

[NickHK]

AFAIK, yes, With only make an impact on performance if the number of dots is
reduced, so the object does not have to be fully resolved each time.
However, I tend to use With to improve readability anyway.

NickHK

 

[ilia]

From how I understand it, the fewer dots the better - unless you're
in .NET, in which case it doesn't matter. Page Setup is a bad
example, because that accesses printer drivers and those are usually
slow anyway.

Here's a crude test. Put this code into a new workbook and run it.

Public Sub tryWith()
Dim d1 As Double, d2 As Double
Dim objWorksheet As Excel.Worksheet
Dim rngRange As Excel.Range

Const iterations As Double = 30000

Set objWorksheet = Application.ActiveSheet

Application.ScreenUpdating = False

objWorksheet.Range("C1").Value = "Not using with"
objWorksheet.Range("D1").Value = "Using with"

d1 = Now
For Each rngRange In objWorksheet.Range("A1:A" & iterations)
rngRange.Value = rngRange.Row()
rngRange.Font.Bold = True
rngRange.Font.Italic = True
Next rngRange
d2 = Now
objWorksheet.Range("C2").Value = (d2 - d1) * 86400

d1 = Now
For Each rngRange In objWorksheet.Range("B1:B" & iterations)
With rngRange
.Value = rngRange.Row()
With .Font
.Bold = True
.Italic = True
End With
End With
Next rngRange
d2 = Now
objWorksheet.Range("D2").Value = (d2 - d1) * 86400

Application.ScreenUpdating = True

End Sub

Theoretically, C2 should be greater than D2 (that's in seconds).
Change the constant at the top for more iterations.