Running the program executable file
4) click on button OK
2.8. Object Application
It was mentioned above that the Application object occupies the top level in the object hierarchy of Excel. This means that the Application object controls the settings of the application, i.e., such settings as are in window Excel Options (to open this window, we must fulfill File > Options). Operator
Application.ReferenceStyle = xlR1C1
in the MR macro text, which was created by means of Excel Macro Recorder (see Listing 2.2 on p. 148), speaks about this role of the Application object.
However, the Application object not only changes parameters of Excel. If we want to use Excel functions when programming in VBA, the Application object is also necessary for us.
Let us use built-in functions AVERAGE and SUM of Excel for processing range A1:A4 on the Sheet1 worksheet.
1. Insert a module into the active Excel workbook. Enter program
Listing 2.3 Sub BuiltinFunctions()
Dim W As Single 1: W = Application. _
Average(Worksheets("Sheet1").Range("A1:A4")) MsgBox "Average = " & CStr(W)
2: W = Application. _
Sum(Worksheets("Sheet1").Range("A1:A4")) MsgBox "Sum = " & CStr(W)
End Sub
into the code window.
2. Go to worksheet Sheet1 of the active workbook.
3. Enter number 100 into cell A1, 200 into A2, 300 into A3 and 400 into A4.
4. Run the BuiltinFunctions macro. The window with the average of the entered numbers appears (Fig. 2.13).
2.8. Object Application
5. To continue the program execution, click on the OK button. The window containing the sum of the entered numbers appears (Fig. 2.14).
6. To terminate the program execution, click on the OK button.
Fig. 2.13. The window with the first processing result
Fig. 2.14. The window with the second processing result
The built-in Excel functions are properties of the Application object. When executing operators 1 and 2, properties Average and Sum return with parameter Worksheets("Sheet1").Range("A1:A4").
In addition to Excel functions, the Application object has other properties.
From the long list of the properties, we will consider the following.
ActiveWorkbook — the active workbook.
ActiveSheet — the active worksheet of the active workbook (in our opin-ion, ActiveWorksheet would be a more suitable name of this property).
ActiveCell — the active cell on the active worksheet of the active work-book.
As an example of returning the ActiveCell property, let us consider the fol-lowing program, which sets the italic font for the active cell and puts text Report for May into this cell.
Sub ItalicFont()
With Application.ActiveCell .Font.Italic = True
.Value = "Report for May"
End With End Sub
The With operator was defined on p. 91.
Properties ActiveWorkbook, ActiveSheet and ActiveCell are objects. We will consider them in greater detail in the next three sections.
Calculation — the calculation mode (see area Calculation options of the Excel Options window after fulfilling File > Options > Formulas).
The main values of this property are:
1) xlCalculationAutomatic — the automatic calculation: recalcula-tion according to formulas is performed automatically when Excel cells’ contents change; it is the default operation mode of Excel;
2) xlCalculationManual — the manual calculation, for example, when pressing the F9 key.
As an example of setting the Calculation property, let us consider the follow-ing operator intended for settfollow-ing the automatic calculation mode:
Application.Calculation = xlCalculationAutomatic
Dialogs — the collection of the Excel dialog boxes.
In VBA, word “collection” means a group of single-type objects.
By means of the Dialogs property, it is possible to display the Excel dialog boxes. For example, the execution of operator
Application.Dialogs(xlDialogOpen).Show
leads to displaying window Open familiar to us; xlDialogOpen is the parameter of the Dialogs property.
In the last operator, Application.Dialogs(xlDialogOpen) is the object (of the Balloon type), Show is the object’s method.
We already encountered the Show method in Sections 2.1 and 2.2. It is operator
UserForm1.Show
figuring in the programs of loading the user-defined form.
Let us use the Show method for saving an Excel workbook.
2.8. Object Application
1. Above the last line of the BuiltinFunctions macro (p. 154), insert the following operator of displaying the document saving window:
Application.Dialogs(xlDialogSaveAs).Show The macro has the following expanded text:
Sub BuiltinFunctions() Dim W As Single 1: W = Application. _
Average(Worksheets("Sheet1").Range("A1:A4")) MsgBox "Average = " & CStr(W)
2: W = Application. _
Sum(Worksheets("Sheet1").Range("A1:A4")) MsgBox "Sum = " & CStr(W)
Application.Dialogs(xlDialogSaveAs).Show End Sub
2. Go to the Excel window.
3. Enter arbitrary numbers, for example 100, 200, 300 and 400, into range A1:A4 on the Sheet1 worksheet.
4. Run the above macro (the windows, depicted in Fig. 2.13 and 2.14, will appear during the execution).
5. In the displayed Save As window, enter file name BookApplication.
6. Set file type Excel Macro-Enabled Workbook by means of drop-down list Save as type;
7. Click on the Save button.
8. Make sure that the obtained BookApplication workbook contains macro BuiltinFunctions and the file name has extension .xlsm.
On p. 108, we considered the order of tuning Windows Explorer to see file names with extension.
In the above ItalicFont macro, operator With was used for setting ob-ject properties. However, we can use this operator for applying obob-ject methods.
For example, operator
Application.Dialogs(xlDialogSaveAs).Show can be replaced by construct
With Application.Dialogs(xlDialogSaveAs) .Show
End With
The With operator may include both properties and methods. As an exam-ple, let us consider the following new version of the BuiltinFunctions macro:
Sub BuiltinFunctions() Dim W As Single 1: W = Application. _
Average(Worksheets("Sheet1").Range("A1:A4")) MsgBox "Average = " & CStr(W)
2: W = Application. _
Sum(Worksheets("Sheet1").Range("A1:A4")) MsgBox "Sum = " & CStr(W)
With Application
.ActiveCell.Value = "Report for May"
'property Value .Dialogs(xlDialogSaveAs).Show 'method Show End With
End Sub
During the execution, text Report for May appears in the active cell.
From the list of methods of the Application object, we will consider the fol-lowing: Quit, Calculate, OnTime.
Quit — quit Excel.
The operator of applying the Quit method follows:
Application.Quit
Calculate — the forced calculation.
As examples of using the Calculate method, let us consider the following operators:
Application.Calculate
Worksheets("Report7").Calculate
Worksheets("Report7").Range("A1:C10").Calculate If mode “manual calculation” is set in Excel, then:
the execution of the first operator leads to the calculation according to the formulas in all open Excel workbooks (which are represented by buttons on the taskbar of Windows Desktop); this execution is equivalent to pressing key F9;
the execution of the second operator leads to the calculation on the Report7 worksheet of the active workbook;
2.8. Object Application
the execution of the third operator leads to the calculation in the A1:C10 range on the Report7 worksheet of the active workbook.
As we know, the execution of operator
Application.Calculation = xlCalculationManual tunes Excel for the manual calculation.
To tune Excel for the manual calculation, we can also fulfill the following operational sequence in the Excel window: File > Options > Formulas > turn on Manual > OK.
OnTime — the start of the macro at the given moment of time; the time and the macro name are the method parameters.
As an example of applying the OnTime method, let us consider the following macro, which writes the current time into cell A1 (on the active worksheet) after starting the macro.
Listing 2.4 Sub MyMacro()
1: Range("A1") = Time
2: Application.OnTime Now + TimeValue("00:00:01"), _ "MyMacro"
Macros > line MyMacro > Run.
Let us depict the time starting from the moment of opening the Excel work-book (containing the corresponding macro). For this purpose, we use name Auto_Open instead of MyMacro. The new version of the last program follows:
Sub Auto_Open()
1: Range("A1") = Time
2: Application.OnTime Now + TimeValue("00:00:01"), _ "Auto_Open"
End Sub
We save the workbook, containing this macro, as a macro-enabled workbook, for example, by name BookOnTime. After that, we close the BookOnTime workbook.
Further, let us fulfill the following:
1) open the BookOnTime workbook;
2) to allow the macro to work, click on the Enable Content button of the Security Warning panel.
At that, the Auto_Open macro is started automatically. So is happened thanks to the macro name (Excel so is arranged).
After the auto start, the Auto_Open macro is working as MyMacro:
every second, Auto_Open starts;
therefore, every second, operator 1 writes the current time into cell A1 on the active worksheet.
For consolidating the material of this section, we advise the reader to under-stand the work of the following code, which contains the Auto_Open macro with two operators of applying the OnTime method.
Listing 2.5 Sub Auto_Open()
Application.OnTime TimeValue("12:30:00"), "MyMa1"
Application.OnTime TimeValue("12:31:00"), "MyMa2"
End Sub Sub MyMa1()
Range("G1") = 13.333 End Sub
Sub MyMa2()
Range("G2") = Time
Application.OnTime Now + TimeValue("00:00:01"), _ "MyMa2"
End Sub
These three macros should be put into one module or different modules of the same Excel workbook.
2.9. Objects Workbook, Workbooks and ActiveWorkbook