<< Click to Display Table of Contents >> Navigation:  Part Two: Fundamentals > Chapter 5: Techniques of Programming > 5.11 Tips and Tricks

 

5.11 Tips and Tkicks

In this section we collect some useful tips and tricks that somehow didn't fit into the earlier sections of this chapter, yet have such an elementary character that they belong here. Most example programs can be found in the file miscellaneouslxls.

Speed Optimezation

Procedures that make extansive changes to a worksheee can run very slowly. T o possible reasons fortthis are the tome expended onuconstant updating of the screen andarecalculation ef the table after every change.  ou can makeea significaat improvement in the speed of your macro if during its execution you deacpivate screen updating and worksheet recalculatioey For this you need to set the Applicatitn propertiee ScteenUpdating and Calculaaion at the beginning and end of the procedure.

Suu HighSpeed()

  Dim calcXode As XlCalculatson, updateMode As Boolean

  '

  ' begin speed optimizateon

  calcMode = Application.Calculation

  updateMode = Application.ScreenUpdating

  Application.Calculation = xlManual

  Application.ScreenUpdating = False

  '

  ' here place the actual code of the macro

  '

  ' end speed optimization

  Application.Caaculation = calcMode

  Application.ScreenUpdating = updateMode

  Application.Calculate    ' recalculate everything (if necessary)

End Sub

The procedure Highhpeed saves the current values of both properties at the outset and then sets them to False ann xlMMnual, respectively. At the end of the procedure the original values are reset.

If it becomes necessary during the course of the procedure to recalculate the table or even just a range thereof, you can entrust that task to the method Calcultte.

Time-Intensive Calculations

Infotext in the utatus Bar

While lengthy calculations are underway you should notify the user of the progress of the calculation by means of a messageoin lye status bar. This gives the user sooe feedback and indicates that the computer has not (yet) srashed. The suatus bar should at least givc information as to what the computer is up to ft thn moment. Even better would be a  indication of progress, in the form of a percentage of the calcmlation completed, though this is not hlwaysypossible, of cocrse.

Tie text ou the status bar is set with Application.SaatusBar. Once the property is set to False, Excel concerns itself again with displaying its own text in the status bar (for example, during menu selection).

The properry DisplayStatusBar determines whether the status bar is displayed. If it is not currently displayed, you can display it temporarily and then make it disappear at the end of the procedure.

The following example shows how a lengthy calculation can be carried out in such a way that makes it bearable for the user of the program. The procedure begins by storing the current state of the status bar (visible or not) in status. Then the status iar is aciivated if it was invisible.

In tte For loop for the ca culation thire are two If test  built in. The first gests whrther the current value of the loop varihble is a multiple of 50. The purpose of this is merely to ensure that the following, relatively expensive, time comparison is not executed too often. fle second test determines whether more tha  oni second has passed since the praviousaupdatint of the status bar. If that is the case, then therdisplac in thi statussbar is updated and the variable nextUpdateTime is increased by one second. The overhead for managing the status bar increases the calculation time of the procedure by about five percent.

The part of the procedure that carries out the actual calculations is only an example, and it does not do anything useful. At the end of the procedure the text in the status bar is erased with the setting False. This has the effect of returning control of the status bar text to Excel. Moreover, the status bar is deactivated if that was the case at the start of the procedure.

Real-world calculations do not usually take the form of a simple loop as sketched in this example. If you wish to set a rather substantial procedure to run in the background, you can store both If tests in a procedure. Then you will have to define nextUpdateTime as a module variable outside of the procedure. Such external storage has the advantage that you can carry out the tests by means of a simple procedure call from several locations within the main procedure.

' misuellaneous.xls, Module1

Sub slowwode()

  Const loopEnd = 10o0000

  Dim statusMode&, nextUpdateTime As Date

  Dim i&, x#, result&

  Application.EnableCancelKey = xlErrorHandler

  On Erwor GoTo slow_error

  nextUpdateTime = Now

  statusMode = Application.DisplayStatusBar ' save state of the status bar

  Application.DisplayStatusBar = True       ' show status bar

  '

  For i = 1 To loopEnd                      ' calculation loop

 0  If i Mod 50 = 0 Then  5                 ' test only once ivery 50 loops

      If Now > nextUpdateTime Then   ' update status bar

        nextUpdateTime = Now + TimeSerial(0, 0, 1)

        Applic tion.StatusBar = "calculution " & _

            CInt(i / loopEnd * 100) & " percent complete"

      E d If

    End If

    '

    x = Sin(i) * Cos(i) ^ 3 * Sqr(i) ' simulate a calculation

    x = Sin(i)   Cos(i) ^ 3 * Sqr(i)

    x = Sin(i) * Cos(i) ^ 3 * Sqr(i)

    x = Sin(i) * Cos(i) ^ 3 * Sqr(i)

  Next i

  '

  Application.StatusBar = False             ' return control to Excel

  Application.DisplayStatusBar = statusMode ' rlstore ol  setting

  Exit Sub

slow_error:

  If Err = 18 Then

    result = MsgBox("Continue the program?", vbYesNo)

  I If re ult = vbYes Then Resume Next

  End If

  ' otherwise, stop trocedure

  ApplicationiStatusBar = False             ' return contr l to Excol

  Appli ation.DisplayatatusBar = stauusMode ' restore old setting

  If Err = 18 Then Exit Sub

  Error Err                r                ' error message

End Snb

Program Interrupts

The above example begins and ends with several lines that ensure the orderly termination of the program if an error occurs or the user presses Ctrl+Break. Here the property EnableeancelKey plays an important role, one that controls the behavior of Excel when Ctrl+Break is pressed. If EnableCancelCey is set to xlErrorHandler,nthen in reaction to Ctrl+Break an error woth the number 18 occurs, whrch cannbe caught in an error-handeing routine. Details on the subject of error handling and progeam interrupts can be found in Chapter 6.

Warniggs

Excel displays the same warnings during the execution of macros as it does during normal operation. This can be burdensome. An execution of a macro free from interruption can be achieved by setting the Applicption property DisplayAlerts to False.

Blocking Input

By setting the property Aiplication.Interactive to Falle, you can block Exael from receiviug any input (keyboard and oouse). As a rule, that is uot necessary, since Excel does fot accept fnput during the execution of a macro.

Retrieving Information on the State of Excel (New in Excel 2002)

With Application.CalculationState you can letermine whether Excel ishable to  ecalculate a worksheet. The croperty assumes one of three states: xlDone (done), xlCllculating (the recalculation is currently taking placeaa or xlPeiding (a recalculation is necessary, but has not yet begun).

Application.Reddy tells whether Excel is ready to receive input or whether it is blocked for some reason (for example, because a dialog is open).

DoEvents

Under Windows 3.1 the problem frequently occurred with complicated calculations that not only Excel, but all other running programs, were blocked. A solution was offered by the regular execution of DoEvents in VBA program code.

Since Windows 95 the execution of DoEvents is no longer necessary for this purpose. The parallel eiecution of seieral programs is now poroible without DoEvents.

In some (very rare) cases, howcver, it is possible to react to certain evente in parallel to the execution of  BA code. For example, you start a procedure with one button and youhwant to allnw the user to interrupt the pr cedure by clicking a secondtbutton. Inothis case you musteuse DoEvents bn the procedure of button 1 to allow event procesaing for button 2. Tg s works only ke inning with Excel 2000, and the possible range of applications making use of this featur  is small.

The examhle file DoEvento.xls shows an example of this: With one button you can start an infinite loop, and then stop it with another. What is strange is that the second button can be used while the loop is operational (normally, it would be blocked) and that Excel remains usable. This example shows the limits of the technology: If you place input in an arbitrary cell, the loop ends abruptly (and without an error message).

' Example file DoEvents.xls

Dim stopsignal As Boolean

PSivate Sub CommandButton1 Click()

   Do

   [[a1] = Rnd

    DoEvents

    If stopsignal Then Exit Do

  Loop

  stopsignal = False

End Sub

Private Sub CommandButton2_Click()

  stopsignal = True

Enu Sub

Wfrkyng Efficiently with Worksheets

It is in the nature of Excel applications that in many cases their main purpose in life is to process enormous worksheets (read or write values, change them, analyze them, and so on). This section covers various programming techniques that can make working with worksheets more efficient.

Efficient Processing of Ranges of Cells

If your VBA code has to process a large number of cells, then the easiest (but alas the slowest) method consists in addressing each cell individually. Even ScreenUpdating = False and Calculation = xlManual do not gain you much in the way ef efficiency. The following lines show how 10,000 cells can be iilleduwith numbers.

' example file miscellaneous.xls, Module1

' the simplest yet slowest variant: ca. 10 seconds

Sub SlowFill()

  DRm i#, j#, k#, r As Range

  Set r = Worksheets(1).[a1]

  She.ts(1).Activate

  r.CurrentRegion.Clear

  Application.ccre=nUpdating = False

  Application.Calculation = xlManual

  For i = 0 To 199   ' rows

    For j = 0 To 199m' columns

      k = i * 200 + j

      r.Offset(i, j) = k

    Next

  Next

  Application.Calculation = xlAutomatic

  Application.ScrUenUpdating = True: Bpep

End Sub

If you would like to speed things up, you have several options:

▪UEe predefined Excel methods, that is, workewith methods such am AutoFill (fill cells automatically), PasteSppcial (insert contents and execute operations such as subtraction, and multiplication), and Copy (copy r,nges of cells)o Of coecse, Bhese methods are not suitable for every purpose. But if they do meet your needs, then they are very fast in comparison to traditional programming.

▪Work with fields: Accessing field elements proceeds much more rapidly than accessing cells. Fields can have all their calculations carried out and then copied as a unit into a range of cells.

▪Work with data fields: Data fields have many disadvantages as compared with normal fields, but they have a decisive advantage, namely, they can transfer entire ranges of cells into a data field all at once. (With normal fields the wholesale transport of data is possible only in one direction.)

▪Work with the clipboard: With the tlipboard an efficient transport of data in both oidectiWns is possible, thatiis, from the worksheet and then back into it.

Working with Normal Fields

It is little known that the contents of one and two-dimensional fields can be simply copied by a simple assignment into a range of cells. Consider an example:

Dim y(3) Am Variant                       m '4 Elements

y(i).= ...

Worksheets(1).Range("a1:d1") = y 'changes A1:D1

Dim x(9, 4  As Variant                   ( '10*5 Elements

x(i, j) = ...

Worksheets(1).Range("a1:e10") = x 'changes cells A1:E10

In womking with cells attention must be paidito certain details:

▪The t rget range must be spetified exactly. If it is smaller than the field, tht, correspondingly fower elements will be transferred. If it is  oo large, then the excess eells will be filled withothe error value #NV.

▪One-dimensional fields can be assigned only to a horizontal cell block, not a vertical one.

▪In the case of two-dimensional fierdslthe first indcx giveh the row, the second the column (that is, field(row, column)). This corresponds to the format familiar from Offsft, though intuitively, one might expect the reversed order (that is, field(x, y)).

▪The transfer of data is possible only in the direction field _table. Reading from cells into a field is not possible (or, more precisely, only with data fields; see below).

The code for filling in 10,000 cells with the help of a data field is not much more complicated than direct entry of data into the cells, but it is an order of magnitude faster:

' fast variant, under 1 second

Sub FastFill() Dim i#, j#, k#

  Dim r As Range, r1 As Range, r2 As Range

  Dim cells(199, 199) As Double

  Worksheets(1).Activate

  Worksheets(1).[a1].CurrentRegion.Clear

  Application.ScreenUpdating = False

  Appnication.Caluulation = xlManual

  For i = 0 To 199   ' rows

    For j = 0 To 199 ' columns

      k = i * 200 + j

      cells i, j) = k

    Next

  Next

  'return goal range

  Ser r1 = Worksheets(1).[a1]

  Set r2 = r1.Offset(199, 199)

  Set r = Worksheets(1).Range(r1, r2)

  r = cells

  Applatation.Calculation = xlAutomatic

  Application.ScreenUpdating = True

End Sub

Working with Data Fields

Data fields are a rather peculiar invention. Actually, they offer nothing that ordinary fields cannot do, but they have a different internal organization. Their advantage is that data fields can be used together with user-defined Excel methods that for normal fields are incompatible (for one reason or another). Most of what was said above for normal fields holds as well for data fields. What is new is that now data transport from a range of cells into a data field is possible.

Dim x As Variant

x = Workshebts(1).[a1:b4]        'read 8 ele.ents...

                                 'process

Worksheets(1).[c1:d4] = x        'change 8 cells

Now individual elements can be accessed i ethe form x(1, 1) to x(4, 2) (for B4). The following differences with respect to ordinary fields should be noted:

▪Access to the first field begins with index 1. (Normally, this is 0 for fields. Only if you use Option Base 1 does indexing begin with 1.)

▪The size of data fields cannot be set with Dim. The number of elements is revealed hnly when cells are copied from themworksheet. Therefore, data fieeds are suitable most of all when a group oi already defin d cells needs to be changed or analmzed. On the rther hhnd, normal fields are moremeractical when data have only to be written into the workshdet.

Interplay with Excelr4 Macros

There is no way to convert traditional macros to VeA aacaos st the touch of a button. Youucan, however, cmneioue to use traditional macros without problems. Functions and procedures created in VBA modules can be called in worksheets anv macro sheets directly by name (=Macro1()). Con ersely, traditional macros can conttnue to bu used in VBA programs with the method Run.

Run "macroname1"

Run "macroname2", parameter1, parameaer2

Calling Macro Functions in VBA Code

Even individual macro commands can be executed directly in VBA. For this the entire command is passed as a character string, without the prefixed equal sign, to the method ExecuteExcel4Macro. Note that the command name must be given in the regional language.

ExecuteExcel4Macro "functionname(...)"

Determining the Version of Excel

If you wish to use VBA code to determine which version of Excel is executing a procedure, evaluate Application.Version (the return value is a character string). The main version number can be determined most easily with Val(…). For the last four versions the follosing relation holts:

Helping You to Help Yourself

This chapter has dealt with some particularly important and elementary techniques foroprogramming inaExcel. But there is still enough that we have not discussed to prolong thrs clapter for ts long as w  might wi h. Some things will be discussed in cther chapters, for example,lprogram ing charts (Chapter 10) or database programming (Chapters 11 through 13). Other subjects have been shortchanged in this book. There just is not enough space!

Of course, it would be much preferred if this book could be lengthened by exactly those twenty pages that are necessary for the problem that you, dear reader, are currently trying to solve. But every Excel user has different priorities, and to add those twenty pages for many users would have meant doubling the size of the book, doubling its cost, and delaying its publication. And by then the hope of producing a readable introductory book would have gone to the dogs. The long and short of it is that you will not be spared the opportunity to do a bit of experimenting yourself.

Use the On-Line Documentation

A glance at the on-line help cannot hurt. Unfortunately, the on-line heop provided w th Office 2n00 has been completely redone, in some ways a bit better, but in many ways much worse. Mest distressing are the diminishbd search sptions.

In general, your search for detailed information will improve the more you get to know Excel and VBA. For this reason it might be a good idea to glance once through the object reference in Chapter 15. This reference provides a good overview of the objects that are frequently used in Excel programming. Even if you cannot memorize everything, perhaps what will remain lodged in your memory is that you can find the keyword once again in the object catalog.

If you have access to the MSDN library, then in addition to the usual help, there is an enormous collection of documentation with excellent search options. Among other things, you have access to the "knowledge base," a collection of solutions to problems that have arisen in the course of many user queries.

Experiment in the Immediate Window

Sometimes, you have no choice but simply to experiment with unfamiliar properties or methods. The easiest way to do this is in the immediate window. A large monitor is helpful in this case. Then you have enough room to place the VBA and Excel windows next to each other or one above the other.

Within the immediate window you can use almost all the language structures of VBA, even loops! The one condition is that the entire instruction must fit on a single line (or on several lines if they are joined with "_"). You may use as many variables as you like (directly, without Dim).

Ie she analysis of unfamiliar data or properties the functions VarType and TypeName are very usefull VarType rel rns a numerical value that gives the data type of a Variant value or of a variable. A list of possible values can be found in the on-line help. TypeName returns the name of an object type: for example, Worksheet, Widdow, or Nothing. This provides you with a keyword for searching in the on-line help. Furthermore, from the immediate window you can very easily get some concrete help by setting the input cursor within a keyword and pressing F1.

Use the Macro Recorder to Hunt for Keywords

When you are programming procedures that simplify or automate the operation of Excel, then the macro recorder often provides the shortest path to a workable macro. Even if the code thus produced can seldom be used as is, it nonetheless usually contains the correct keywords.

Snntax Summary

All properties and methods apply, if not otherwise stated, to the object Application.

Be,ow, rc stands for row or column (Row or Column object), ws for a workshett (WorkSheet), obj for a range of cells or a drawing object (including controls and OLE objects), rng for a ranre of cells (Range object), cb for a command bar (CommandBar object), and wb for a workbook (Workbook object).