AppActivate title [, wait] title
Data Type: Variant
The name of the application as currently shown in the application window titlebar. Can also be the task ID returned from the Shell function.
Data Type: Boolean
If set to True, the calling application must itself wait to obtain the focus before activating the called application. If set to False (its default value), the application specified in title is activated immediately.
Sets the focus to the application with a titlebar caption matching title. The application title passed to AppActivate isn't necessarily the name of the program file of the application; it's the name currently displayed in the application's titlebar.
AppActivate Statement 123
Rules at a Glance
• title isn't case sensitive.
• The title of each running application is compared against title.
• If the application designated by title isn't running, AppActivate doesn't launch it.
• If no title matching title is found, an application whose title starts with title is matched. For example, the title "Microsoft Word" matches "Microsoft Word—MyDocument.doc".
• If more than one instance of an application is found, AppActivate passes the focus to one of the instances purely at random.
• The window state (Maximized, Minimized, or Normal) of the activated application isn't affected by AppActivate.
• If a matching application can't be found, runtime error 5, "Invalid procedure call or argument," is generated.
• AppActivate can be used with both standard windows and console mode or DOS applications. In the latter case, title must correspond to the window caption Windows assigns the console window.
Private Sub CommandButton2_Click() Dim bVoid As Boolean bVoid = ActivateAnApp("Microsoft Excel") End Sub
Function ActivateAnApp(vAppTitle As Variant) As Boolean
On Error GoTo Activate_Err
ActivateAnApp = False AppActivate vAppTitle ActivateAnApp = True
MsgBox "Application " & vAppTitle & _
" could not be activated" Resume Activate_Exit
Programming Tips & Gotchas
• You can also use the task ID returned by the Shell function with the AppAc-tivate statement, as this simple example demonstrates:
124 Chapter 7- The Language Reference
Option Explicit Private vAppID
Private Sub Command1_Click()
vAppID = Shell("C:\Program Files\Internet\IEXPLORE.EXE") End Sub
Private Sub Command2_Click()
• AppActivate is difficult to use with applications whose application titles change to reflect the state or context of the application. Microsoft Outlook furnishes an excellent illustration of this problem. If the user has Outlook in the Calendar section, the titlebar reads "Calendar—Microsoft Outlook," whereas in the Inbox section, the titlebar reads "Inbox—Microsoft Outlook."
• Due to the uncertain nature of attempting to activate an application over which you have little or no programmatic control, you are strongly advised to wrap the appActivate statement within stout error handling.
• Wherever possible, it's preferable to manipulate the other application using its COM interface—i.e., to create an instance of the application object.
• AppActivate is often used to give a particular window the focus before sending keystrokes to it using the SendKeys statement, which acts only upon the active window.
• All high-level languages by their very nature have limitations. After all, if we wanted a language that could do everything possible, we'd all be using assembler. To my mind the AppActivate and the Shell functions highlight the limitations of VB in 32-bit Windows, and especially Windows NT. To make a long story short, if you want to manipulate other applications with your application, you should either be using a technology, such as OLE automation, that focuses on controlling an application remotely, or you should be programming those parts of your application in C++—and that doesn't mean MFC! (Even MFC is too high-level for some of the low-level windows functionality you need to do the job right.)
Data Type: Any
The data to be assigned to the first array element elenientN
VB & VBA in a Nutshell: The Language, eMatter Edition Copyright © 2000 O'Reilly & Associates, Inc. All rights reserved.
Any number of data items you wish to add to the array. Return Value
A variant array consisting of the arguments passed into the function. Description
Returns a variant array containing the elements whose values are passed to the function as arguments.
The code fragment:
Dim vaMyArray vaMyArray = Array("Mr", "Mrs", "Miss", "Ms")
is the equivalent to writing:
Dim vaMyArray(3) vaMyArray(O) = "Mr" vaMyArray(l) = "Mrs" vaMyArray(2) = "Miss" vaMyArray(3) = "Ms"
Because Array creates a variant array, you can pass any data type, including user-defined types and objects, to the Array function. You can also pass the values returned by calls to other Array functions to create multidimensional arrays (but see the comment on multidimensional arrays in the "Programming Tips & Gotchas" section).
Rules at a Glance
• You can assign the array returned by the Array function only to a Variant.
• Although the array you create with the Array function is a variant data type, the individual elements of the array can be a mixture of different data types.
• The initial size of the array you create is the number of arguments you place in the argument list and pass to the Array function.
• The lower bound of the array created by the Array function is determined by the Option Base directive; if there is no Option Base statement, the lower bound of the array is 0.
• The array returned by the Array function is a dynamic rather than static array. Once created, you can redimension the array using Redim, Redim Preserve, or another call to the Array function.
• If you don't pass any arguments to the Array function, an empty array is created. Although this may appear to be the same as declaring an array in the conventional manner with the statement:
the difference is that you can then use the empty array with the Array function again later in your code.
126 Chapter 7- The Language Reference
Programming Tips & Gotchas
• You can effectively use the Array function only in situations where you know in advance the number of elements you will need. It's not possible to write the function statement with a variable number of elements.
• You can't assign the return value of Array to a variable previously declared as an array variable. Therefore, don't declare the variant variable as an array using the normal syntax:
Instead, simply declare a variant variable, such as: Dim MyArray as Variant
• Array can also be invoked as a method within the VBA object model* by using the syntax:
In this case, Option Base has no effect on the base element number of the array; the first element is always 0. Try this example:
Option Base 1
Private Sub CommandButton1_Click() Dim vaListOne As Variant vaListOne = VBA.Array("One", 2, "Three", 4) MsgBox vaListOne(1)
Dim vaListTwo As Variant vaListTwo = Array("One", 2, "Three", 4)
• The Array function is ideal for saving space and time and writing more efficient code when creating a fixed array of known elements, for example:
Dim Titles as Variant
Title = Array("Mr", "Mrs", "Miss", "Ms")
• You can use the Array function to create multidimensional arrays. However, accessing the elements of the array needs a little more thought. The following code fragment creates a simple two-dimensional array with three elements in the first dimension and four elements in the second:
Dim vaListOne As Variant vaListOne = Array(Array(1, 2, 3, 4), _ Array(5, 6, 7, 8), _ Array(9, 10, 11, 12))
* If you use the Object Browser to locate the Array method within the VBA object, though, you won't be able to find it, since, as a member of the _HiddenModule module, it's hidden from view, although it remains accessible.
Array Function 127
Surprisingly, the code you'd expect to use to access the array returns a "Subscript out of range" error:
'This line generates a Subscript out of range error MsgBox vaListOne(1, 2)
You can overcome this limitation by declaring a second variant and assigning to it the element from the first dimension, then accessing the second dimension element in the normal way, like this:
Dim vaListTwo As Variant vaListTwo = vaListOne(l)
• You can also use the Array function to populate the ActiveX (Microsoft Forms 2.0) ListBox or ComboBox controls, as the following example shows:
Private Sub CommandButton2_Click() ComboBoxl.Clear
ComboBoxl.List = Array("Mr", "Mrs", "Miss", "Ms") ComboBoxl.ListIndex = 0
Note that this doesn't work with the standard Visual Basic ListBox or ComboBox controls; it produces an "Argument not optional" compiler error. Our performance comparisons, however, indicate that the conventional technique of calling the control's AddItem method to add an item is between 5 and 25% faster than calling the Array function.
• Here's another neat trick you can use with the Array function: you can even create your own "on-demand" control array (of existing controls) by simply listing a group of existing controls in the argument list to pass to the Array function. You can then use the array element the same way you'd use an object variable, as the following code demonstrates:
Dim vaTest as Variant vaTest = Array(CommandButton1, CommandButton2, _ CommandButton3)
• Because you declare the variant variable to hold the array as a simple variant, rather than an array, and can then make repeated calls to Array, the function can create dynamic arrays. For example, the following code fragment dimensions a variant to hold the array, calls Array to create a variant array, then calls Array again to replace the original variant array with a larger variant array:
Dim varArray As Variant varArray = Array(10,20,30,40,50)
varArray = Array(10,20,30,40,50,60)
128 Chapter 7- The Language Reference
The major disadvantage of using this method is that, while it makes it easy to replace an array with a different array, it doesn't allow you to easily expand or contract an existing array.
Dim Statement, LBound Function, Option Base Statement, ReDim Statement, Ubound Function
Was this article helpful?