5.11 Tips and Tricks

5.11 Tips and Tricks

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 miscellaneous.xls .

Speed Optimization

Procedures that make extensive changes to a worksheet can run very slowly. Two possible reasons for this are the time expended on constant updating of the screen and recalculation of the table after every change. You can make a significant improvement in the speed of your macro if during its execution you deactivate screen updating and worksheet recalculation. For this you need to set the Application properties ScreenUpdating and Calculation at the beginning and end of the procedure.

 Sub  HighSpeed  ()   Dim calcMode As XlCalculation, updateMode As Boolean   '   ' begin speed optimization   calcMode = Application.Calculation   updateMode = Application.ScreenUpdating   Application.Calculation = xlManual   Application.ScreenUpdating = False   '   ' here place the actual code of the macro   '   ' end speed optimization   Application.Calculation = calcMode   Application.ScreenUpdating = updateMode   Application.Calculate    ' recalculate everything (if necessary) End Sub 

The procedure HighSpeed saves the current values of both properties at the outset and then sets them to False and xlManual, 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 Calculate .

Note  

If you use the command Exit Sub in your procedure, you must not forget first to reset Calculation to its previous setting. It is less critical that the property ScreenUpdating be reset, since it is automatically set to True by VBA after the macro finishes its execution. The instruction with True is necessary only if you wish to update the screen during execution of the macro.

Time-Intensive Calculations

Infotext in the Status Bar

While lengthy calculations are underway you should notify the user of the progress of the calculation by means of a message in the status bar. This gives the user some feedback and indicates that the computer has not (yet) crashed. The status bar should at least give information as to what the computer is up to at the moment. Even better would be an indication of progress, in the form of a percentage of the calculation completed, though this is not always possible, of course.

The text of the status bar is set with Application. StatusBar . 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 property 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.

Note  

Unfortunately, in Excel, as always, there is no possibility of displaying the state of a lengthy calculation with a progress bar (with small blue squares). Excel itself uses this layout object frequently, such as during opening and closing of files. However, there are no VBA methods for control of the progress bar.

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 bar is activated if it was invisible.

In the For loop for the calculation there are two If tests built in. The first tests whether the current value of the loop variable 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. The second test determines whether more than one second has passed since the previous updating of the status bar. If that is the case, then the display in the status bar 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.

 ' miscellaneous.xls, Module1 Sub  slowcode  ()   Const loopEnd = 1000000   Dim statusMode&, nextUpdateTime As Date   Dim i&, x#, result&   Application.EnableCancelKey = xlErrorHandler   On Error 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     If i Mod 50 = 0 Then                    ' test only once every 50 loops       If Now > nextUpdateTime Then   ' update status bar         nextUpdateTime = Now + TimeSerial(0, 0, 1)         Application.StatusBar = "calculation " & _             CInt(i / loopEnd * 100) & " percent complete"       End 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 ' restore old setting   Exit Sub slow_error:   If Err = 18 Then     result = MsgBox("Continue the program?", vbYesNo)     If result = vbYes Then Resume Next   End If   ' otherwise, stop procedure   Application.StatusBar = False             ' return control to Excel   Application.DisplayStatusBar = statusMode ' restore old setting   If Err = 18 Then Exit Sub   Error Err                                 ' error message End Sub 

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 EnableCancelKey plays an important role, one that controls the behavior of Excel when Ctrl+Break is pressed. If EnableCancelKey is set to xlErrorHandler , then in reaction to Ctrl+Break an error with the number 18 occurs, which can be caught in an error-handling routine. Details on the subject of error handling and program interrupts can be found in Chapter 6.

Warnings

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 Application property DisplayAlerts to False .

Blocking Input

By setting the property Application. Interactive to False , you can block Excel from receiving any input (keyboard and mouse). As a rule, that is not necessary, since Excel does not accept input during the execution of a macro.

Caution  

The property Interactive must be set to True at the end of the procedure, even if the procedure is prematurely exited by means of Exit Sub . The procedure must be protected against possible errors (see Chapter 6), so that even in the case of an error that property is reset. There is no way of resetting the property outside of VBA code. Not only will Excel be blocked by this property, the program will not even be able to be ended! In the end, unsaved data will be lost.

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

With Application. CalculationState you can determine whether Excel is able to recalculate a worksheet. The property assumes one of three states: xlDone (done), xlCalculating (the recalculation is currently taking place), or xlPending (a recalculation is necessary, but has not yet begun).

Application. Ready 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 execution of several programs is now possible without DoEvents .

In some (very rare) cases, however, it is possible to react to certain events in parallel to the execution of VBA code. For example, you start a procedure with one button and you want to allow the user to interrupt the procedure by clicking a second button. In this case you must use DoEvents in the procedure of button 1 to allow event processing for button 2. This works only beginning with Excel 2000, and the possible range of applications making use of this feature is small.

The example file DoEvents.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 Private Sub  CommandButton1 Click  ()    Do     [a1] = Rnd     DoEvents     If stopsignal Then Exit Do   Loop   stopsignal = False End Sub Private Sub CommandButton2_Click()   stopsignal = True End Sub 

Working 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 of efficiency. The following lines show how 10,000 cells can be filled with numbers .

 ' example file miscellaneous.xls, Module1 ' the simplest yet slowest variant: ca. 10 seconds Sub  SlowFill  ()   Dim i#, j#, k#, r As Range   Set r = Worksheets(1).[a1]   Sheets(1).Activate   r.CurrentRegion.Clear   Application.ScreenUpdating = False   Application.Calculation = xlManual   For i = 0 To 199   ' rows     For j = 0 To 199 ' columns       k = i * 200 + j       r.Offset(i, j) = k     Next   Next   Application.Calculation = xlAutomatic   Application.ScreenUpdating = True: Beep End Sub 

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

• Use predefined Excel methods, that is, work with methods such as AutoFill (fill cells automatically), PasteSpecial (insert contents and execute operations such as subtraction, and multiplication), and Copy (copy ranges of cells). Of course, these 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 clipboard an efficient transport of data in both directions is possible, that is, 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) As Variant                         '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 working with cells attention must be paid to certain details:

• The target range must be specified exactly. If it is smaller than the field, then correspondingly fewer elements will be transferred. If it is too large, then the excess cells will be filled with the 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 fields the first index gives the row, the second the column (that is, field(row, column) ). This corresponds to the format familiar from Offset , 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   Application.Calculation = 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   Set r1 = Worksheets(1).[a1]   Set r2 = r1.Offset(199, 199)   Set r = Worksheets(1).Range(r1, r2)   r = cells   Application.Calculation = xlAutomatic   Application.ScreenUpdating = True End Sub 

Tip  

In Excel 7 only ranges up to a maximum of 5200 cells can be changed. The above program would have to be changed in such a way that cells would be changed in blocks (say, of 10 rows).

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 = Worksheets(1).[a1:b4]        'read 8 elements...                                  'process Worksheets(1).[c1:d4] = x        'change 8 cells 

Now individual elements can be accessed in the 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 only when cells are copied from the worksheet. Therefore, data fields are suitable most of all when a group of already defined cells needs to be changed or analyzed . On the other hand, normal fields are more practical when data have only to be written into the worksheet.

Interplay with Excel-4 Macros

There is no way to convert traditional macros to VBA macros at the touch of a button. You can, however, continue to use traditional macros without problems. Functions and procedures created in VBA modules can be called in worksheets and macro sheets directly by name (= Macro1() ). Conversely, traditional macros can continue to be used in VBA programs with the method Run.

 Run "macroname1" Run "macroname2", parameter1, parameter2 

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

Tip  

We repeat once more how Excel worksheet functions are used in VBA code: with the English function name and prefixed by WorksheetFunction (a property of the Application object), WorksheetFunction.Sum(Range("A1:A3")).

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 following relation holds:

EXCEL VERSION	CONTENTS OF VERSION
Excel 5	"5.0"
Excel 7 alias 95	"7.0"
Excel 97	"8.0" ("8.0a", "8.0b", etc., according to service release)
Excel 2000	"9.0"
Excel 2002	"10.0"

Tip  

If you wish to program Excel applications that can be executed in older versions of Excel, then you must restrict yourself to the greatest common divisor of all the versions. Excel 2000 and 2002 differ only in a few new objects, properties, and methods. Even the differences between Excel 2000 and Excel 97 are relatively slight . Avoid ADO and FSO, and then things cannot go too far wrong. However, the differences between these and earlier versions are large. Among other things, the format of *.xls files has been changed.

Helping You to Help Yourself

This chapter has dealt with some particularly important and elementary techniques for programming in Excel. But there is still enough that we have not discussed to prolong this chapter for as long as we might wish. Some things will be discussed in other chapters, for example, programming 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 help provided with Office 2000 has been completely redone, in some ways a bit better, but in many ways much worse . Most distressing are the diminished search options.

Tip  

The quickest way to your goal is often the object catalog. There you can select a keyword and press F1 .

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.

Tip  

The MSDN library is also available over the Internet, though the search options there are less convenient . http://msdn.microsoft.com

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 ).

In the analysis of unfamiliar data or properties the functions VarType and TypeName are very useful. VarType returns 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 , Window , 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.

Syntax Summary

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

BACKGROUND CALCULATION, OPTIONS FOR PROGRAM EXECUTION
Interactive = True/False	allow input
EnableCancelKey = xlDisabled	no reaction to Ctrl+Break
= xlErrorHandler	error 18 on Ctrl+Break
DisplayAlerts = True/False	alert during macro execution
DisplayStatusBar = True/False	display status bar
StatusBar = "infotext"/False	place text in the status bar

SPEED OPTIMIZATION
ScreenUpdating = True/False	screen updating on/off
Calculation = xlAutomatic/xlManual	automatic/manual calculation
object.Calculate	recalculate range/sheet/entire application

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

EXCEL-4 MACROS AND WORKSHEET FUNCTIONS
Run "macroname" [, para1, para2 ]	execute Excel-4 macro
ExecuteExcel4Macro "KOMMANDO( )"	execute Excel-4 macro command (German)
WorksheetFunction.Function()	execute worksheet function (English)

EXCEL VERSION NUMBER
Application.Version	character string with Excel version number