Introducing the Application Object
|
| < Day Day Up > |
|
Introducing the Application Object
The Application object is the highest object within the Microsoft Excel Object Model. The Application object contains all of the properties and methods to fully manipulate the Excel application, along with the objects that represent individual workbooks and the data they contain.
Because it is the topmost object within the object model, logically you would need to begin all references with the Application object. To refer to the first cell in a worksheet (A1), you would need to start at the Application object, go to the Worksheet object, and then select the Cell object. To set the first cell equal to 100, the VBA code would be as follows:
Application.Workbooks(1).Worksheets(1).Cells(1,1) = 100
Lengthy sequences in dot notation are cumbersome to use, so the Excel programmers did expose some of the more common objects directly, such as workbooks, worksheets, and cells, without the code having to go through the Application object. Care must be taken, especially when working with cells directly, that you have selected the proper workbook and worksheet. If you’re sure you have selected the proper workbook and worksheet, such as by using the Worksheet object’s Activate method described in the next section of this chapter, you could abbreviate the previous command to Cells(1,1) = 100.
Properties
Working with the Application object will usually involve reading or setting one of its many properties. The Application object contains more than 170 properties that let you control every aspect of the Excel application. From workbooks and worksheets to columns and rows, the Application object provides access to practically every element of Excel and Excel workbooks.
With so many properties, it is impossible to know every available property, and it’s not necessary to do so. There is a short list of about 10 properties that are the most common properties and should be learned to fully work with Excel using VBA. The other properties can be learned as you need them. The important thing is to know that they are there for future exploration.
The following Application object properties are the most used:
-
ActiveCell
-
ActiveChart
-
ActiveSheet
-
ActiveWindow
-
ActiveWorkbook
-
RangeSelection
-
Selection
-
ScreenUpdating
-
StatusBar
-
ThisWorkbook
You can get information on every element of the Excel object model in the Visual Basic Editor help system by typing object model in the Ask A Question box and viewing the Microsoft Excel Object Model help topic.
The short list of Application properties contains the most common objects that are used within Excel. Most have been exposed directly by the Excel programming team so that an explicit reference to the application object isn’t needed. These properties are described in the following sections in alphabetical order.
ActiveCell Property
The ActiveCell property is exactly what its name implies: a reference to the currently active cell on the active work sheet on the active workbook. When called, the ActiveCell property returns a Range object that can be used to set the value or formula of the cell along with any formatting changes you might want to make (font style, borders, format of numbers, and so on).
The following example uses the CellBorder procedure to examine the value of a cell and change the border around the cell if it has a value between 500 and 1000. The ApplyBorders procedure loops through all of the specified data cells within the Y2001ByMonth.xls workbook, shown in Figure 6-2, and then calls the CellBorder procedure for each cell.
Figure 6-2: The ActiveCell property lets you make changes to the active cell without writing a specific cell or range reference.
Sub ApplyBorders()
Dim MyCell As Range
For Each MyCell In _
ActiveSheet.Range(D6:O36").Cells
MyCell.Select
If ActiveCell > 500 And ActiveCell < 1000 Then
With ActiveCell.Borders
.Weight = xlThick
.Color = vbBlue
End With
End If
Next MyCell
End Sub
It is important to remember that the value of the ActiveCell property is different from the value of the Selection property. A Selection can return a range of multiple cells, but only one cell within the selection can be the active cell. When you look at a worksheet where more than one cell is selected, the active cell is the cell with the border around it and no highlight; the other cells in the selection are highlighted. (The default highlight color is a grayish blue.)
ActiveChart Property
The ActiveChart property lets you manipulate the currently selected chart, whether it is an embedded chart or a chart sheet. All of the properties of the chart are available, which means you can change the data points on the chart, the format of the chart, and other attributes.
The following example automatically builds a three-dimensional column chart in the SalesByCategory.xls workbook using the existing data and positions it beneath the data source, as shown in Figure 6-3.
Sub BuildChart()
Charts.Add 'Add a new chart object
ActiveChart.ChartType = xl3DColumn 'Set the Chart type to 3D Column
ActiveChart.SetSourceData _
Source:=Sheets("ByCategory").Range("C1:G13"), _
PlotBy:=xlColumns 'Set the data source
ActiveChart.Location Where:=xlLocationAsObject, Name:="ByCategory"
With ActiveChart 'Format the chart
.HasTitle = True
.ChartTitle.Characters.Text = "Monthly Sales by Category"
.Axes(xlCategory).HasTitle = True
.Axes(xlCategory).AxisTitle.Characters.Text = "Month"
.Axes(xlSeries).HasTitle = True
.Axes(xlSeries).AxisTitle.Characters.Text = "Category"
.Axes(xlValue).HasTitle = True
.Axes(xlValue).AxisTitle.Characters.Text = "Sales"
End With
ActiveSheet.Shapes(1).IncrementLeft -133.5 'Position the chart
ActiveSheet.Shapes(1).IncrementTop 214.5
ActiveSheet.Shapes(1).ScaleWidth 1.77, msoFalse, msoScaleFromTopLeft
ActiveSheet.Shapes(1).ScaleHeight 1.35, msoFalse, msoScaleFromTopLeft
End Sub
Figure 6-3: The ActiveChart property lets you streamline your chart creation and manipulation.
For more information on charts and how to change them programmatically, see Chapter 15, “Charts.”
ActiveSheet Property
The ActiveSheet property returns a worksheet object that represents the currently selected worksheet. You can perform all worksheet manipulations using this object. For example, if you created a workbook where each worksheet recorded hourly sales figures for every day in a month, you could write a macro to create a new sheet with the name of the next month and the correct weekday next to the date. Figure 6-4 shows the results of this macro, which uses the January worksheet of the Y2004ByMonth.xls workbook as its model.
Sub CopySheet()
Sheets("January").Select 'Select sheet to copy
ActiveSheet.Copy After:=Sheets(Sheets.Count) 'Copy sheet to last position
Sheets(Sheets.Count).Select 'Select new sheet
ActiveSheet.Name = Format(Str$(Sheets.Count) & "/01/03", "MMMM")
'Rename to latest month
Range("D6:O36").Select 'Select data cells and erase
Selection.ClearContents
Range("C6").Select 'Start at first day, set day of week for first 3 days
ActiveCell.FormulaR1C1 = Format(Str$(Sheets.Count) & "/01/" & _
Str$(Year(Now())), "ddd")
Range("C7").Select
ActiveCell.FormulaR1C1 = Format(Str$(Sheets.Count) & "/02/" & _
Str$(Year(Now())), "ddd")
Range("C8").Select
ActiveCell.FormulaR1C1 = Format(Str$(Sheets.Count) & "/03/" & _ Str$(Year(Now())), "ddd")
Range("C6:C8").Select
Selection.AutoFill Destination:=Range("C6:C36"), Type:=xlFillDefault
'Autofill remaining days
Range("C6:C36").Select 'Correct borders
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
End With
Range("C36").Select
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
End With
End Sub
Figure 6-4: This macro creates a new worksheet based on an existing template but adds the correct weekday and date for the upcoming month as well as the unchanging hour column headings.
ActiveWindow Property
The ActiveWindow property returns a Windows object that represents the currently selected window within the Excel application. The ActiveWindow is always the topmost window within an application.
The Caption property of a Window object also doubles as a Name property. When you assign a value to the Caption property, you make it possible to refer to a particular window in the Windows collection by using the Caption value instead of an index number. The following code fragment assigns the string "My Window" to the Caption property of the active window, and then invokes the ActiveSheet object’s Calculate method to recalculate all of the formulas on the active sheet in MyWindow.
ActiveWindow.Caption = "My Window"
Application.Windows("My Window").ActiveSheet.Calculate
The best use of the ActiveWindow object is to control the zoom level of the window or move the window to display a particular cell. The ScrollRow and ScrollColumn properties provide the cell that’s in the upper-left corner.
The following example illustrates how to use the ScrollColumn, ScrollRow, and Zoom properties. The example enters text into cell H18 using a 4-point font size, too small to read at Zoom levels below 200 percent. The macro then increases the Zoom level to the maximum of 400% and positions the window so that cell H18 is in the upper-left corner.
Sub ZoomScrollSample()
Range("H18").Select
ActiveCell.Font.Size = 4
ActiveCell.Value = "This text is very small and hard to read."
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollRow = 16
ActiveWindow.Zoom = 100
MsgBox "Click OK to zoom in to read", vbOKOnly + vbInformation, _
"Programming Microsoft Excel Inside Out"
ActiveWindow.Zoom = 400
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollRow = 18
End Sub
The next two macros can be assigned to buttons to allow one-click access to zoom in or out.
Sub ZoomIn()
Dim intZoom As Integer
intZoom = ActiveWindow.Zoom + 50
If intZoom > 400 Then intZoom = 400
ActiveWindow.Zoom = intZoom
End Sub
Sub ZoomOut()
Dim intZoom As Integer
intZoom = ActiveWindow.Zoom - 50
If intZoom < 50 Then intZoom = 50
ActiveWindow.Zoom = intZoom
End Sub
ActiveWorkbook Property
The ActiveWorkbook property returns a Workbook object that references the currently selected workbook. If the clipboard window is active, the ActiveWorkbook property returns Nothing.
| Note | The ActiveWorkbook property also returns Nothing if the only open workbook is hidden. |
The following example builds a new quarterly workbook by extracting the desired sheets out of the annual workbook:
Sub ExtractQuarterlyFigures()
Dim szMyName As String, szQuarter As String, intCount As Integer
Dim szSheetName As String, szName As String
szMyName = ActiveWorkbook.Name
szQuarter = InputBox("Which quarter to extract (1,2,3, or 4)?", _
" Microsoft Office Excel 2003 Programming Inside Out", "1")
Application.Workbooks.Add
Select Case szQuarter
Case 1: szName = "1st Quarter.xls"
Case 2: szName = "2nd Quarter.xls"
Case 3: szName = "3rd Quarter.xls"
Case 4: szName = "4th Quarter.xls"
Case Else
MsgBox "Invalid entry ('" & szQuarter & "').", vbOKOnly + _
vbInformation, " Microsoft Office Excel 2003 Programming Inside Out"
Exit Sub
End Select
Workbooks(Workbooks.Count).SaveAs szName
For intCount = 1 To 3
Workbooks(szMyName).Activate
ActiveWorkbook.Sheets(intCount * Val(szQuarter)).Activate
Range("A1", ActiveCell.SpecialCells(xlLastCell)).Select
szSheetName = ActiveSheet.Name
Selection.Copy
Workbooks(szName).Activate
Sheets(intCount).Select
ActiveSheet.Paste
ActiveSheet.Name = szSheetName
Next intCount
End Sub
DisplayAlerts Property
The DisplayAlerts property determines if Microsoft Excel will display alerts while a macro is running. By changing the value to False, Excel will choose the default response for all alerts that would have been displayed; the one exception is when using the SaveAs method for workbooks. When DisplayAlerts is True, the OverWrite alert has a default response of Yes, but Excel will use No as a response when DisplayAlerts is False.
Excel will reset the DisplayAlerts property to the default value of True when the macro completes.
The following macro, which removes all unused worksheets from a workbook, sets the DisplayAlerts property to False so that the user isn’t prompted every time the macro attempts to delete a worksheet:
| Warning | You must run this workbook on a workbook that contains some data. If you run this on a new workbook with nothing but empty sheets, you get VB runtime error 1004: "A workbook must contain at least one visible sheet."
Sub RemoveEmptySheets() |
RangeSelection Property
The RangeSelection property returns a Range object that represents the selected cells on the selected workbook. Even when a graphic object is selected, the RangeSelection property will return a range of cells, returning the last cells that were selected.
When cells are selected, the RangeSelection property and the Selection object both represent the same cells. When a graphic object is selected, the RangeSelection property will still return the last cells that were selected.
The following example displays the address of the cells currently selected:
MsgBox ActiveWindow.RangeSelection.Address
Selection Property
The Selection property returns an object type of whatever the current selection happens to be. For cells, it will return a Range object; for charts, it will return a Chart object.
Assuming the current selection is a range of cells, this example will clear all values and formulas.
Selection.Clear
StatusBar Property
The StatusBar property allows you to change the current message displayed on the Status Bar at the bottom of the Excel window. This message can be helpful when you have a procedure that will take some time to complete because it allows you to keep the user informed that something is happening. To disable any message you might have placed on the status bar, set it to False.
This example is a modified version of the BuildChart procedure that was used previously to demonstrate the ActiveChart property. Several lines of code were added to display messages to the user on the Status Bar about what the macro was currently doing.
 |
Pausing a Macro
One of the features of Excel is the ability to pull in data from outside sources. Suppose the flower shop has decided to expand and now has multiple stores. As the owner, you want to build a report that examines the sales data from each of the different stores every day. However, the macro to build the report takes some time to compile all the information and present in the manner you desire, so you’d like it to run overnight so that the report is waiting for you in the morning.
Rather than having to wait until all stores have submitted their data to the main server and then running your macro, you can use the Wait method to pause the macro until a specific time. Using the code Application.Wait "20:00:00" instructs the macro to wait until 8:00 P.M. before continuing to execute. To pause for a specific time interval and not a specific time, you can add the time interval to the current time.
Careful testing should be done before setting too great of an interval for pausing. The Wait method will halt all activity within Microsoft Excel and also has the potential to prevent you from using any other application on your computer.
|
ScreenUpdating Property
VBA macros execute very quickly, especially when compared with the speed at which a human could perform the same steps. As Excel performs the different actions programmed in the macro, the screen changes to reflect those actions. Enough actions could happen fast enough that the screen updates aren’t processed as quickly as the actions in the macro and it appears as if the screen is flashing as it attempts to reflect the different steps performed. These screen updates also require some processing time, which will slow down the execution of the macro.
To minimize the screen flashing and also maybe to gain a few seconds in processing time, especially on macros that require a lot of processing time, you can disable screen updates using the ScreenUpdating property.
Setting the ScreenUpdating property to False at the start of a macro instructs Excel not to update the screen during execution until the property has been reset to True. It is very important to fully test a macro for errors and add some type of error trapping when turning off screen updates so that the user does not think the macro or Excel has crashed when there is actually an error condition that’s not being displayed to the user. It’s also important to set ScreenUpdating to True, the default value, when the procedure has executed so that any other procedure that might have called the one that turns the screen updating off doesn’t have to be concerned with whether or not the screen updates were turned on or off outside its own scope.
The following procedure creates a new chart based on the data in the SalesByCategory.xls workbook and then waits three seconds before terminating the procedure.
Sub BuildChart()
Application.StatusBar = "Adding new chart..."
Application.Wait (Now + TimeValue("0:00:03"))
Application.ScreenUpdating = False
Charts.Add 'Add a new chart object
ActiveChart.ChartType = xl3DColumn 'Set the Chart type to #D Column
ActiveChart.SetSourceData Source:=Sheets("ByCategory").Range("C1:G13"), _
PlotBy:=xlColumns 'Set the data source
ActiveChart.Location Where:=xlLocationAsObject, Name:="ByCategory"
Application.StatusBar = "Configuring new chart..."
With ActiveChart 'Format the chart
.HasTitle = True
.ChartTitle.Characters.Text = "Monthly Sales by Category"
.Axes(xlCategory).HasTitle = True
.Axes(xlCategory).AxisTitle.Characters.Text = "Month"
.Axes(xlSeries).HasTitle = True
.Axes(xlSeries).AxisTitle.Characters.Text = "Category"
.Axes(xlValue).HasTitle = True
.Axes(xlValue).AxisTitle.Characters.Text = "Sales"
End With
ActiveSheet.Shapes(1).IncrementLeft -133.5 'Position the chart
ActiveSheet.Shapes(1).IncrementTop 214.5
ActiveSheet.Shapes(1).ScaleWidth 1.77, msoFalse, msoScaleFromTopLeft
ActiveSheet.Shapes(1).ScaleHeight 1.35, msoFalse, msoScaleFromTopLeft
Application.Wait (Now + TimeValue("0:00:03"))
Application.StatusBar = False
Application.ScreenUpdating = True
End Sub
ThisWorkbook Property
ThisWorkbook returns a Workbook object that refers to the workbook that contains the macro that’s currently running. This property lets Add-Ins refer to the workbook that contains the code. ActiveWorkbook will not work because it refers to the currently active workbook and not the workbook that actually contains the code being executed.
The following example demonstrates the ThisWorkbook property by displaying a message box with the name of the active workbook and the name of the workbook the macro is being executed from:
Sub TestThisWorkbook()
Dim strMessage As String
strMessage = "Active Workbook = " & ActiveWorkbook.Name & vbCrLf
strMessage = strMessage & "This Workbook = " & ThisWorkbook.Name
MsgBox strMessage, vbOKOnly + vbInformation, _
"Microsoft Office Excel 2003 Programming Inside Out"
End Sub
Methods
Methods are the actions that objects can perform. They allow the contents of the object container to be processed. You can think of it as a kitchen blender. The blender is the object container. The food placed inside the blender is the contents. Each of the individual buttons, stir, chop, liquefy, and so on, is a different method.
Calculate Method
Calculate forces all open workbooks to recalculate all cells that contain new, changed, or volatile cells and their dependents. Using this method is similar to pressing the F9 key, and it is used to force Excel to recalculate a workbook when the Calculation option in the Tools, Options dialog box has been set to manual. This example recalculates all open workbooks if the Calculation option is not set to automatic.
Sub RecalcAll()
If Application.Calculation <> xlCalculationAutomatic Then
Calculate
End If
End Sub
|
Limiting the Calculations
The Calculate method can also be used on Worksheet and Range objects, and it allows you to narrow down the number of calculations that are performed. Using a Worksheet object will perform all needed calculations on the specified worksheet. The following example calculates the active sheet:
Sub RecalcSheet()
If Application.Calculation <> xlCalculationAutomatic Then
ActiveSheet.Calculate
End If
End Sub
To calculate a limited number of cells, use a Range object to specify the cells you want to recalculate. This example recalculates the formulas for all cells in the range C1:G13:
Sub Recalc()
ActiveSheet.Range("C1:G13").Cells.Calculate
End Sub
|
CalculateFull Method
CalculateFull forces all open workbooks to recalculate all cells regardless of the contents. The following example recalculates all open workbooks:
Sub ReCalcFull()
Application.CalculateFull
End Sub
FindFile Method
The FindFile method displays the Open dialog box and opens the file selected by the user. It returns a True value if a file was successfully opened or a False if the user clicked the Cancel button. The following example prompts the user to locate the ProductList.xls file:
Sub FindProductList()
Dim bReturn As Boolean, strPrompt As String
strPrompt = "Please locate the ProductList.xls file."
MsgBox strPrompt, vbOKOnly + vbInformation, " Microsoft Office Excel 2003 _
Programming Inside Out"
bReturn = Application.FindFile
If Not bReturn Then
strPrompt = "File not opened."
MsgBox strPrompt, vbOKOnly + vbInformation, " Microsoft Office Excel 2003 _
Programming Inside Out"
End If
End Sub
InputBox Method
The InputBox method is very similar to the InputBox function in that both the method and function display a simple dialog box that allows the user to enter information to be manipulated within your macro. The difference between the two is the InputBox method can perform selective validation of the user’s input and can be used with other Excel objects, error values, and formulas. The InputBox function, by contrast, just returns a value you can work with in a macro.
For more information on creating input boxes, see Chapter 4, “VBA Programming Starter Kit.”
As with the InputBox function, you can specify a prompt to tell the user what type of value you are looking for, a title to appear within the title bar of the dialog box, a default value, where on the screen the dialog box should appear, and a link to a Help file. Additionally, you can specify the type of value the InputBox should accept. The choices are shown in Table 6-1, along with usage notes. You can add the values together to specify more than one type. For example, you could add the value for number (1) and the value for text (2) together and use the sum of 3 to allow the InputBox to accept both numbers and text. When no type is specified, the default value for text is used.
| Value | Meaning | Notes |
|---|---|---|
| 0 | A formula | Any valid Excel formula is returned as a text string. You must use the FormulaLocal property to assign the formula to a Range object. Cell references are returned in A1-style and use the ConvertFormula function to switch to R1C1-style if needed. |
| 1 | A number | Will also calculate a formula, if entered, to return a number. For example, if =8*8 is entered, the InputBox will return 64. |
| 2 | Text | Accepts a text (string) value. |
| 4 | A logical value | Accepts any variant that can be converted to a logical value. |
| 8 | A cell reference, as a Range object | The Set statement must be used to assign the result to a Range object. |
| 16 | An error value, such as #NULL! | Returns a string value consisting of the word “Error” and the error number associated with the error value. (See Table 6-2 for a list of error values.) Any non-error value entered returns “Error 2015”. |
| 64 | An array of values | Accepts a list of values. |
| Error Value | Return |
|---|---|
| ##### | Error 2015 |
| #DIV/0! | Error 2007 |
| #N/A | Error 2042 |
| #NAME? | Error 2029 |
| #NULL! | Error 2000 |
| #NUM! | Error 2036 |
| #REF! | Error 2023 |
| #VALUE! | Error 2015 |
The full syntax for using the InputBox method is
expression.InputBox(Prompt, Title, Default, Left, Top, HelpFile,
HelpContextID, Type)
Expression, an Application object, and Prompt, a string value, are both required elements for calling the InputBox method. The other parameters are optional, but placeholders must be used when calling the method in standard form. To avoid placeholders, you can name the parameters you are supplying, such as the following:
Application.InputBox(Prompt:="My Prompt", Type:=8)
The InputBox allows the user to use the mouse to select a cell or range of cells from any open workbook. The Set statement must be used to assign the selected cells to a Range object; otherwise, the value of the selected cells is used.
You need to use a Set statement whenever you want to assign a specific instance of an object to a variable. For example, you can create a variable to point to a Worksheet object using the following statement:
Dim wksNewSheet As Worksheet
There is one more step to go, though; merely creating a variable to point to an object neither creates a new object nor points to an existing instance of the object. To have the wksNewSheet variable point to a worksheet named January, you would use the following statement:
Set wksNewSheet = Worksheets("January") Now that the variable wksNewSheet is assigned to the worksheet named January, you can use the variable name as a pointer to the January worksheet. These two lines of code are equivalent.
wksNewSheet.Name = "January2"
Worksheets("January").Name = "January2"
The following example uses the InputBox method to ask the user to specify a range of cells to use for the monthly totals and then prompts the user for the values needed:
Sub InputMonthlyTotals()
Dim strPrompt As String, rgeMonth As Range, sglTotal As Single
Dim intColumn As Integer
strPrompt = "Please select cells to place monthly totals in."
Set rgeMonth = Application.InputBox(Prompt:=strPrompt, Title:=" Microsoft _
Office Excel 2003 Programming Inside Out", Type:=8)
strPrompt = "Please enter the total sales."
For intColumn = 1 To rgeMonth.Columns.Count
sglTotal = Application.InputBox(Prompt:=strPrompt, Title:=" Microsoft _
Office Excel 2003 Programming Inside Out", Type:=1)
rgeMonth(1, intColumn) = sglTotal
Next intColumn
End Sub
|
The FormulaLocal Property and Local Settings
The FormulaLocal property returns or sets a formula in the language specified in the active Windows user’s regional settings. For example, if a user types the formula =SOMME(B3:B15) into an input box (SOMME is the French version of SUM), you can assign the formula to cell B16 on the first worksheet in your workbook using the following statement:
Range(B16).FormulaLocal = InputBox("Enter a formula.") |
Intersect Method
The Intersect method compares two or more ranges to determine if they overlap or share any common cells. Figure 6-5 shows one range bounded at B3:E6 and a second range bounded at D5:G8. The intersection of the two would be the range D5:E6 as illustrated in the following example:
Sub DisplayIntersection()
Range("B3:E6").BorderAround Color:=vbBlack, Weight:=xlThick
Range("B3:E6").Select
Set rge1 = Selection
Range("D5:E8").BorderAround Color:=vbYellow, Weight:=xlThick
Range("D5:E8").Select
Set rge2 = Selection
Set myRange = Application.Intersect(rge1, rge2)
Range(myRange.Address).Select
Selection.Interior.Color = vbBlue
End Sub
Figure 6-5: The Intersect method finds the cells representing the intersection of two ranges.
A popular use of the Intersect method is to determine if a cell selected by the user is within a specific range. The TestPlants procedure prompts the user to select the largest monthly total for Plants in the SalesByCategory.xls file and displays an error message if a cell is selected that is not one of the monthly plant totals.
Sub TestPlants()
Dim strPrompt As String, rgePlants As Range, rgeIntersect As Range
strPrompt = "Please select the highest monthly total for Plants."
Set rgePlants = Application.InputBox(Prompt:=strPrompt, Title:=" Microsoft _
Office Excel 2003 Programming Inside Out", Type:=8)
Set rgeIntersect = Application.Intersect(Range("G2:G13"), rgePlants)
If rgeIntersect Is Nothing Then
strPrompt = "You did not select a Plant value".
MsgBox strPrompt, vbOKOnly + vbInformation
End If
End Sub
OnKey Method
The OnKey method allows you to create hotkeys that will run a particular routine when pressed or block Excel from behaving normally when the key combination is pressed. The syntax for the OnKey method is
Application.OnKey(Key, Prodecure)
Key is a string value representing the key combination to be assigned; see Table 6-3 for a list of special key codes.
| Key | Key Code |
|---|---|
| Backspace | {Backspace} or {BS} |
| Break | {Break} |
| Caps Lock | {CapsLock} |
| Clear | {Clear} |
| Delete or Del | {Delete} or {Del} |
| Down Arrow | {Down} |
| End | {End} |
| Enter | ~ (Tilde) |
| Enter (Numeric Keypad) | {Enter} |
| F1 through F15 | {F1} through {F15} |
| Help | {Help} |
| Home | {Home} |
| Insert or Ins | {Insert} |
| Left Arrow | {Left} |
| Num Lock | {NumLock} |
| Page Down | {PGDN} |
| Page Up | {PGUP} |
| Return | {Return} |
| Right Arrow | {Right} |
| Scroll Lock | {ScrollLock} |
| Tab | {Tab} |
| Up Arrow | {Up} |
| Key Modifier | Key Code |
| Shift | + (Plus Sign) |
| Ctrl | ^ (Caret) |
| Alt | % (Percent Sign) |
Procedure is a string value representing the name of the procedure to be invoked. If Procedure is blank (that is, the parameter is set to the empty string by assigning it the value " "), the key combination performs no action, even if Excel normally reacts to the key combination. If the Procedure argument is omitted entirely (that is, there is nothing after the comma), the key combination is reset to normal, default actions.
You can combine a key with one or more of the key modifiers (Shift, Ctrl, and/or Alt) to create key combinations as needed. The following example runs a procedure when Alt+H is pressed:
Application.OnKey "%h", "MyProcedure"
The following example takes the key combination that normally opens the Excel Find and Replace (Ctrl+H) dialog box and uses it to invoke a custom procedure:
Application.OnKey "^h", "MyProcedure"
This example stops any code from running when Ctrl+H is pressed.
Application.OnKey "^h", ""
This last example resets Ctrl+H to the default action in Excel (Find and Replace).
Application.OnKey "^h"
The most common usage of the OnKey method would be to set hot keys when a workbook is opened or closed. This allows the workbook to provide hot keys that are specific to the workbook and resets the keys to their default values when closed. The two following procedures, when placed inside the Y2001ByMonth.xls file, assign the hot key of Alt+C to the CopySheet procedure to add a new worksheet to the workbook:
Sub Auto_Open()
Application.OnKey "%c", "CopySheet"
End Sub
Sub Auto_Exit()
Application.OnKey "%c"
End Sub
SendKeys Method
The SendKeys method allows you to send keystrokes to the keyboard buffer for when you need to send keystrokes to the current application. This can be especially useful if you know your procedure will cause a dialog box to open and there is no method to prevent the dialog box from appearing and waiting on user input.
The syntax for the SendKeys method is
expression.SendKeys (Keys, Wait)
-
expression is an optional expression that returns an Application object.
-
Keys is a required variant expression, usually expressed as a string, that contains the key codes you want to send. (See Table 6-3 for a list of key codes for special keys.)
-
Wait is an optional variant expression, usually expressed as a True or False Boolean value that specifies if the procedure should halt further execution until the keys have been processed. When True, the procedure will pause until all keys sent have been processed. When False or omitted, the procedure continues execution without regard to whether or not the keys have been processed.
The keys are placed into a key buffer first until an application calls for keys from the buffer. This means the keys need to placed into the buffer before you call a method that will require the keystrokes.
This example goes through a list of customers and generates a personalized e-mail message for each customer. The SendKeys method is used to pass an Alt+S keystroke to the default e-mail program to actually send the e-mail message. The Wait method is used to allow the e-mail program time to generate the e-mail message before trying to send it.
Sub SendEmail()
Dim strLink As String, rgeEmail As Range
Dim strMsg As String
strMsg = "%0A%0AThis month save $10 off all orders over $100.%0A "
strMsg = strMsg & "Visit The Garden Company for all your gardening needs."
For Each rgeEmail In Range(Cells(2, 1), Cells(7, 1))
strLink = "Mailto:" & rgeEmail.Offset(0, 1).Value & "?subject="
strLink = strLink & "Monthly Special%0A&body="
strLink = strLink & "Dear " & rgeEmail.Value & ",%0A"
strLink = strLink & strMsg
ActiveWorkbook.FollowHyperlink (strLink)
Application.Wait (Now + TimeValue("0:00:02"))
SendKeys "%s", True
Next rgeEmail
End Sub
This chapter introduced the Application object, which contains a number of useful properties and methods that let you affect how Excel functions at its highest levels. You can assign new procedures to control key combinations, prevent the screen from flashing as a procedure makes multiple changes to a workbook, and work with the active window, workbook, chart, sheet, or cell with the ActiveWindow, ActiveWorkbook, ActiveChart, ActiveSheet, and ActiveCell properties. Chapter 7 moves to the next level of detail, introducing you to the Workbook and Worksheet objects.
|
| < Day Day Up > |
|
EAN: 2147483647
Pages: 161