O

Object Browser, 79 Object data type, 28, 48 object models, 8-10, 77-83, 113 binding (see data binding) Collections (see Collection object) externally createable objects, 81 File System object model, 275-276 formatting (see data formatting) navigating through, 82-83 new for VB6, 616 polymorphism, 373-375 reading, 79-81 references to, 77-79 object references (see references) object variables, 28, 77-79 binding and, 47-50 declaring for events, 67 Nothing keyword, 39 Terminate event and, 65 setting...

Drive Object VB Description

Interrogates the system properties of any drive connected to the current machine, including network drives. The Drive object supports no methods. The RootFolder property returns a Folder object representing a drive's root folder or directory from this you can obtain a Folders collection object containing the subfolders of the root, and thus gain access to all parts of the drive. See the File System object model entry for an overview, including the library reference needed to access it. 242...

Output from VB to Excel

To finish with, here's an easy little application that places values from a VB application into an Excel spreadsheet. There are project-level (early bound) references created to both Excel and the ADODB 2.0 Reference Library. An ADO recordset has already been created and is passed as a parameter to the OutputToExcel function. The function creates an instance of a new Excel workbook and worksheet, then copies the values from the ADO recordset into the worksheet. Excel's functionality is used to...

Getting a VB Program to

Regardless of the type of application you're writing and the development tool (hosted VBA or the retail version of VB) you're using, there has to be a starting point or an entry point for your program. Here there is a major difference between VB and VBA a VB application is launched as an application in its own right, whereas the VBA program has to be launched by the host application. But in either case, the starting point you choose is decided by the type of application you are writing, as well...

Logmodeconstants

Key property (Dictionary), 229-230 keyboard key presses assigning VB macros to, 15, 17 simulating with SendKeys, 508-511 Keys method (Dictionary), 231 Kill statement, 397-398 language constants, 601 LastDLLErr property (Err), 100 LastDLLError property (Err), 259-261 last-modification time (files), 279-280 late binding, 48, 76, 78 email within VB (example), 86-88 (see also data binding) launching VB programs, 12-19 LBound function, 42, 45, 93, 398-399 LCase, LCase functions, 399-400 leading...

Mid MidB Statements

Mid( stringvar, start , length ) string stringvar The name of the string variable to be modified. The position within stringvar at which the replacement commences. The number of characters in stringvar to replace. The string that replaces characters within stringvar. Description Replaces a section of a string with characters from another string. Rules at a Glance If you omit length, as many characters of string as can be fitted into stringvar are used. 424 Chapter 7- The Language Reference If...

File OpenAsText Stream Method VB

OFileObj.OpenAsTextStream ( IOMode , Format ) oFileObj Any object variable returning a File object. A constant specifying the purpose for opening the file. Data Type Tristate constant A constant specifying ASCII or Unicode format. Return Value A TextStream object. Description Opens the referenced text file for reading or writing. 274 Chapter 7- The Language Reference IOMode can be one of the following IOMode constants Opens the file in append mode that is, the current contents of the file are...

Collection Count Property

Objectvariable.Count objectvariable Use Required Object variable referring to a Collection object. Returns the number of members in the collection. Rules at a Glance Collections are 1-based that is, the index of the first element of a collection is 1. In contrast, arrays are 0-based by default, the index of the first element of an array is 0. Set colSubCollection colMyCollection.Item(CStr(i)) MsgBox 168 Chapter 7- The Language Reference Set colSubCollection Nothing Next i Because collections...

Environ Environ Functions Named Arguments

Environ envstring number envstring The name of the required environment variable. The ordinal number of the environment variable within the environment string table. Environ returns a string containing the text assigned to envstring. Description Returns the value assigned to an operating-system environment variable. Rules at a Glance A zero-length string is returned if envstring doesn't exist in the operating system's environment-string table, or if there is no environment string in the...

Get Object Function

GetObject pathname , class pathname The full path and name of the file containing the ActiveX object. The class of the object see next list . 358 Chapter 7- The Language Reference The class of object to create, delimited from Appname by using a point . . For example, Appname.Objecttype. Returns a reference to an ActiveX object. Description Accesses an ActiveX server held within a specified file. Rules at a Glance Although both pathname and class are optional, at least one parameter must be...

Creating a Dynamic Control Array

To create a dynamic control array that is, an array of controls you can add to at runtime you must first place a control of the required type on the form and set its index property to 0. You can then use the Load statement to create new controls based on the control whose Index is 0. The new controls inherit all the properties of the original control, including its size and position. This means you must set the Left and Top properties for the new controls otherwise, all your controls will sit...

Option Private Module Statement

Restricts the scope and visibility of the contents of a module i.e., its variables, classes, functions, and procedures in VBA-enabled applications that allow references across multiple projects e.g., Microsoft Office applications to the module's project. Option Private Module has no effect in the standalone version of Visual Basic. The Option Private Module statement must appear in the declarations section of a module before any procedures. 446 Chapter 7- The Language Reference Publicly...

Set Statement

Set objectvar New objectexpression Nothing objectvar The name of the object variable or property. Creates a new instance of the object. An expression evaluating to an object. Assigns the special data type Nothing to objectvar, thereby releasing the reference to the object. Assigns an object reference to a variable or property. When using Dim, Private, Public, ReDim, or Static to declare an object variable, the variable is assigned a value of Nothing unless the New keyword is used in the...

Delete Setting Statement Named Arguments

DeleteSetting appname , section , key appname The name of the application. This must be a subkey of the HKEY_ CURRENT_USER Software VB and VBA Program Settings registry key. The name of the application key's subkey that is to be deleted. section can be a single key or a registry path separated with backslashes. The name of the value entry to delete. Description Deletes a complete application key, one of its subkeys, or a single value entry from the Windows registry. section can contain a...

Printer Object and Printers Collection

Since it's part of the VB library, the Printer object isn't available to VBA applications. When you write a VBA as opposed to VB program, you simply make use of the host application's own built-in printing functionality. For example Set oWordActiveDoc oWord.Documents.Add Set oWordSel oWord.Selection oWordSel.TypeText This is text coming in from the VB app. oWordSel.Font.Name Arial Visual Basic contains a global printer object, which refers to the default printer for the current system. Because...

Function Statement

Public Private Friend Static Function name _ arglist As type statements name expression Exit Function statements name expression End Function Gives the function scope through all procedures in all modules in the project. If used within a createable class module, the function is also accessible from outside the project. Public, Private, and Friend are mutually exclusive. Restricts the scope of the function to those procedures within the same module. Public, Private, and Friend are mutually...

File SystemObject CopyFile Method VB

OFileSysObj.CopyFile Source, Destination , OverwriteFiles oFileSysObj Any object variable returning a FileSystemObject object. FileSystemObject.CopyFile Method VB6 283 The path and name of the file to be copied. The path and optionally the filename of the copy to make. Flag indicating whether an existing file is to be overwritten True or not False . Copies a file or files from one folder to another. Rules at a Glance The default value for OverwriteFiles is True. The source path can be relative...

Err LastDLLError Property

A read-only property containing a long data type representing a system error produced within a DLL called from within a VB program. Only direct calls to a Windows system DLL from VB code assign a value to LastDLLError. The value of the LastDLLError property depends upon the particular DLL being called. Your code must be able to handle the various codes that can be returned by the DLL you are calling. Don't forget that a failed DLL call doesn't itself raise an error within your VB program. As a...

Filter Function VB

Filter SourceArray, FilterString , Switch , Compare SourceArray An array containing values to be filtered. The string of characters to find in the source array. A Boolean True or False value. If True, the default value, Filter includes all matching values in result if False, Filter excludes all matching values or, to put it another way, includes all nonmatching values . Type Constant of vbCompareMethod Enumeration 308 Chapter 7- The Language Reference An optional constant possible values are...

Choose Function

Choose index, item1 , item2, , itemn index An expression that evaluates to the number of the item to choose from the list. A comma-delimited list of values from which to choose. 156 Chapter 7 - The Language Reference A variant data type item chosen from the list the data subtype is that of the chosen item. Programmatically selects an item from a predefined list of values which are passed as parameters to the function based on its ordinal position in the list. Using Choose is a simpler...

Asc AscB AscW Functions

Asc string AscB string AscW string string Any expression that evaluates to a string. Return Value An integer that represents the character code of the first character of the string. The range for the returned value is 0-255 on non-DBCS systems, but -32768-32767 on DBCS systems. Returns the ANSI or Unicode character code that represents the first character of the string passed to it. All other characters in the string are ignored. Use AscB with Byte data and AscW on Unicode DBCS systems. The...

Declare Statement

Public Private Declare Sub name Lib libname _ Alias aliasname arglist Public Private Declare Function name Lib libname Alias aliasname arglist As type Keyword used to declare a procedure that has scope in all procedures in all modules in the application. Keyword used to declare a procedure that has scope only within the module in which it's declared. Keyword indicating that the procedure doesn't return a value. Mutually exclusive with Function. Indicates that the procedure returns a value....

Msg Box Function

MsgBox prompt , buttons , title , helpfile, context prompt The text of the message to display in the message box dialog. The sum of the Button, Icon, Default Button, and Modality constant values. The title displayed in the titlebar of the message box dialog. An expression specifying the help file to provide help functionality for the dialog. An expression specifying a context ID within helpfile. Return Value An Integer value indicating the button clicked by the user. Description Displays a...

Format Format Functions Named Arguments

Format expression , format , firstdayofweek , _ firstweekofyear Any valid string or numeric expression. A valid named or user-defined format expression. A constant that specifies the first day of the week. A constant that specifies the first week of the year. Return Value A variant of subtype string containing the formatted expression. Description Allows you to use either predefined or user-defined formats to create an infinite variety of ways to output string, numeric, and date time data. It's...

IsMissing Function

The name of an optional procedure argument of type Variant. Determines whether an argument has been passed to a procedure. If the argument name passed to IsMissing has not been passed to the procedure, True is returned otherwise, IsMissing returns False. False is returned if IsMissing is used on data types other than variants, which may lead to incorrect handling of optional arguments. Always use IsMissing to detect a missing optional variant parameter. Version 5 of both VB and VBA for the...

Input Input InputB InputB Functions

Input number, filenumber number Specifies the number of characters to return. 376 Chapter 7- The Language Reference Data Type Integer Any valid file number. Return Value A string in the case of Input and InputB or a variant string if Input or InputB Accesses data from within a file opened in input or binary mode. Rules at a Glance Input should be used only once with files opened in input or binary mode. The function begins reading characters from the current position of the file pointer. Input...

Property Set Statement

Public Private Friend Static Property Set name arglist, reference statements Exit Property statements End Property Gives the property scope through all procedures in all modules in the project. If used within a createable class module, the function is also accessible from outside the project. Public, Private, and Friend are mutually exclusive. Restricts the scope of the property to those procedures in the same module. Public, Private, and Friend are mutually exclusive. Only valid within a...

The Variant

VBA contains a special data type, the Variant. Internally, the Variant is highly complex, but it's also extremely easy to use. The Variant is the default data type of VBA, so the following code casts myVar as a variant The Variant data type allows you to use a variable with any of the intrinsic VBA data types, automatically working out what is the closest data type to the value you are assigning. When you consider the amount of processing required to determine what data type should be used for...

GoSub Return Statement

Passes execution to and returns from a subroutine within a procedure. Rules at a Glance GoSub and its counterpart, Return, must reside within the same procedure. This means you can't use GoSub to call a subroutine from within another procedure. A subroutine can contain any number of Return statements. Return causes execution to continue with the code immediately following the last executed GoSub. The only reason I've included this relic of the past here is to help if you have the misfortune of...

Set Attr Statement

SetAttr pathname, attributes pathname The name of the file whose attributes are to be set. Numeric expression or constant specifying the attributes. Description Changes the attribute properties of a file. Rules at a Glance Visual Basic now includes the following intrinsic constants for setting file attributes File attributes' constants can be added together or logically ORed to set more than one attribute at the same time. For example SetAttr SysFile.Dat, vbSystem Or vbHidden SetAttr...

Logical and Bitwise Operators

Logical operators allow you to evaluate one or more expressions and return a logical value. VBA supports six logical operators And, Or, Not, Eqv, Imp, and Xor. These operators also double as bitwise operators. A bitwise comparison examines each bit position in both expressions and sets or clears the corresponding bit in the result depending upon the operator used. The result of a bitwise operation is a numeric value. Performs logical conjunction that is, it only returns True if both expressionl...

Send Keys Statement

Expression evaluating to True or False denoting the wait mode. Description Programmatically simulates specified keys being typed at the keyboard. Rules at a Glance SendKeys sends its keystrokes to the application and application window that has the focus. One or more characters represent each key. The default setting for wait is False. Setting wait to True informs the application to wait until the keystrokes have been processed before passing control back to the current procedure. A False...

Folder Object VB

The Folder object allows you to interrogate the system properties of the folder and provides methods that allow you to copy, move, and delete the folder. You can also create a new text file within the folder. The Folder object is unusual because with it, you can gain access to a Folders collection object. The more usual method is to extract a member of a collection to gain access to the individual object. However, because the Drive object exposes only a Folder object for the root folder, you...

Debug Assert Method

Object.Assert booleanexpression object Always evaluates to the Debug object. Expression that evaluates to a Boolean value. Return Value None. Conditionally suspends program execution and transfers control to the Immediate window if the value of booleanexpression is False. booleanexpression must evaluate to a Boolean value. If booleanexpression is False, program execution is suspended and control transfers to the Immediate window. Program execution can be resumed by pressing the Continue button...

Class Module Properties

Depending on the type of project in which the class module .cls file is included, class modules support the following properties that control their precise behavior Only available when a class is part of an ActiveX project, the instancing property defines how instances of the class are created. Its values are The class becomes global to the project in which it's defined references are not necessary. For example, most VB language objects are global as soon as you load the environment, they are...

Printer Object

Circle Method ColorMode Property Copies Property Passes the address of a Visual Basic callback function to an API function Returns a reference to an ActiveX component Defines a prototype for a call to an external DLL library function Defines a prototype for a custom event Iterates through a collection or array of objects or values, returning a reference to each of the members Returns a reference to an ActiveX object Denotes that the current module implements all procedures of the specified...

Load ResData Function

A numeric or string value specifying the resource ID of the data to load. A numeric or string value denoting the format of the data to load. See the table below for valid values 408 Chapter 7 - The Language Reference Returns a binary Unicode string containing the specified resource from a resource .RES file included with the project. Resource files store graphics, strings, and other data inside the application, the contents of the .RES file being compiled into the final EXE. The advantage of...

Err HelpContext Property

A read write property that either sets or returns a long integer value containing the context ID of the appropriate topic within a Help file. 256 Chapter 7 - The Language Reference The Err object sets the HelpContext property automatically when an error is raised. If the error is user-defined, and you don't explicitly set the HelpContext property yourself, the Err object sets the value to 1000095, which corresponds to the Application-defined or object-defined error help topic in the VBA Help...

Cur Function

A string or numeric expression that evaluates to a number between -922,337,203,685,477.5808 and 922,337,203,685,477.5807. expression converted to a currency data type. Converts an expression into a currency data type. The currency data type is stored in eight bytes, with a precision to four decimal places. If the expression passed to the function is outside the range of the Currency data type, an overflow error occurs. Expressions containing more than four decimal places are rounded to four...

Array Variables

Before we look at the types of arrays at our disposal, let's quickly cover some of the terminology used when talking about arrays. Creating an array is called dimensioning the array i.e., defining its size . The individual data items within the array are known as elements, and the number used to access an element is known as an index. The lowest and highest index numbers are known as bounds or boundaries. In VBA, there are four types of arrays arrays can be either fixed or dynamic, and arrays...

Save Setting Statement

SaveSetting appname, section, key, setting appname The name of the value entry whose value is to be saved. Data Type String or numeric The value to save. Creates or saves an entry for a VB application in the Windows registry. Rules at a Glance If either the appname or section subkeys isn't found in the registry, it's automatically created. The function writes a value to a subkey of the KEY_CURRENT_USER Soft-ware VB and VBA Program Settings key of the registry. section need not be an immediate...

Raise Event Statement

RaiseEvent eventName arglist eventName Data Type Any defined by the Event statement A comma-delimited list of variables. Generates a predefined custom event within any procedure of an object module. Rules at a Glance eventName must already be defined in the Declarations section of the module using the Event statement. arglist must match the number and data type of parameters defined in the Event statement. The RaiseEvent and Event statements can be used only in object modules i.e., in form and...

Data Binding Objects VB Library to Reference

Microsoft Data Binding Collection SYSTEM32 MSBIND.DLL Description Apparently, when Microsoft was planning the new release of Visual Basic, they researched how professional developers were using the language. One result which seems to have taken the VB development team by surprise was that very few professional developers use the Data control and data bound controls. The reason for this is quite easy to understand rightly or wrongly, professional VB developers see the Data control and data bound...

Get AllSettings Function

GetAllSettings appname, section appname 352 Chapter 7- The Language Reference Relative path from appname to the key containing the settings to retrieve. Return Value A Variant containing a two-dimensional array of strings. Description Returns the registry value entries and their corresponding values for the application. Rules at a Glance GetAllSettings works exclusively with the subkeys of HKEY_CURRENT_ USER Software VB and VBA Program Settings. The elements in the first dimension of the array...

Open Statement

Open pathname For mode Access access lock As filenumber Len reclength The name of the file to open, along with an optional path. The file access mode append, binary, input, output, or random. Specifies the allowable operations by the current process. Specifies the allowable operations by other processes. 442 Chapter 7- The Language Reference A valid file number between 1 and 511. The length of the record or I O buffer. Before reading from and or writing to a disk file, you must first open the...

RmDir Statement Named Arguments

The path of the folder to be removed. Description Removes a folder. Rules at a Glance You may include a drive letter in path if you don't specify a drive letter, the folder is assumed to be on the current drive. If the folder contains files or other folders, RmDir will generate runtime error 75, Path File access error. The following subroutine deletes all the files in a folder and removes its subfolders. If those contain files or folders, it deletes those too by calling itself recursively until...

Implements Statement

The name of an object variable referencing a standard interface class. Description The Implements statement allows you to provide polymorphism within your object models. Polymorphism has the advantages of speed and flexibility over inheritance though, unfortunately, we have neither the time nor space to explore these concepts in depth here. Polymorphism boils down to a set of guidelines or a framework the user works within to create a different end result or implementation . Polymorphism is...

Chr Chr ChrB ChrB ChrW Functions

Chr charactercode Chr charactercode ChrB charactercode ChrB charactercode ChrW charactercode charactercode An expression that evaluates to either an ASCII or DBCS character code. Chr, ChrB, and ChrW return a variant of the string subtype that contains the character represented by charactercode. Chr and ChrB return a string containing the character represented by char- Returns the character represented by charactercode. Chr and Chr return the character associated with an ASCII or ANSI character...

MTSTransaction Mode Property VB only Description

Only available when a class is part of an ActiveX DLL project, you should set this property whenever the class is to be registered as a Microsoft Transaction Server MTS component. MTS uses this property to determine the level of support a particular component has for transactions. This property is available only at design time. When MTS components created using VB5 are added to an MTS package, the administrator of the MTS package must manually set the Transaction Support property for the...

Like Operator Syntax

If string matches pattern, result is True otherwise, result is False. The string to be tested against pattern. A series of characters used by the Like operator to determine if string and pattern match. Determines if a string matches a given pattern. Rules at a Glance If either string or pattern is Null, then result is Null. The default comparison method for the Like operator is binary. This can be overridden using the Option Compare statement. Binary comparison is based on comparing the...

EOF Function

An integer containing -1 True , or 0 False . Description Returns an integer evaluating to True -1 when the end of a file has been reached until the end of the file is reached, EOF returns False 0 . filenumber must be a valid number used in the Open statement to open either a random or sequential file. If you have opened the file using either random or binary access, a Get statement that can't read a complete record i.e., an attempt to access a record past the last record in the file causes EOF...

StdData Format Object VB

The stdDataFormat object sits silently between the Binding object and the data consumer control. Data coming into the Binding object is reformatted by the stdDataFormat object and made ready for display in the consumer control. Data changed by the user that the Binding object is returning to the database is automatically unformatted as it leaves the data consumer control. The formatting applied to the data is based on the properties you have set for the particular format object. In the case of...

Get Setting Function

GetSetting appname, section, key , default appname The path from the application key to the key containing the value entries. The name of the value entry whose value is to be returned. The value to return if no value can be found. Return Value A string containing the value of the specified key default if key, section, or appname aren't found. Returns a single value from a specified section of your application's entry in the HKEY_CURRENT_USER Software VB and VBA Program Settings branch of the...

Property Get Statement

Public Private Friend Static Property Get name _ arglist As type statements name expression Exit Property statements name expression End Property Public Gives the property scope through all procedures in all modules in the project. If used within a createable class module, the property is also accessible from outside the project. Public, Private, and Friend are mutually exclusive. Restricts the scope of the property to those procedures within the same module. Public, Private, and Friend are...

ChDir Statement

The path of the directory to set as the new default directory. Description Changes the current working default directory. VB amp VBA in a Nutshell The Language, eMatter Edition Copyright 2000 O'Reilly amp Associates, Inc. All rights reserved. Path can be an absolute or relative reference. On Windows systems, changing the default directory doesn't change the default drive it changes only a particular drive's default directory. sNewDir c program files my folder ChDir sNewDir ChDir 'c program...

Brief History of VBA

The incredible popularity of Visual Basic shortly after its launch prompted Microsoft to wonder if a cut down version of the product could replace the many different macro languages lurking behind its range of business applications. Bill Gates talked for many years since the days of DOS of a universal batch language. This goal is now coming to fruition in the shape of VBA. However, as the following chronology shows, this goal wasn't achieved overnight 1993 VBA launched with Microsoft Excel VBA...

Date Function

Data Type String or Numeric Any valid date expression. expression converted into a Date data type. Description Converts expression to a Date data type. The format of expression the order of day, month, and year is determined by the locale setting of your computer. To be certain of a date being recognized correctly by CDate, the month, day, and year elements of expression must be in the same sequence as your computer's regional settings otherwise the CDate function has no idea that 4 is supposed...

File Copy Statement

The name of the source file to be copied. The name and location of the file when copied. Description Copies a file. Rules at a Glance The source and destination arguments may contain a drive name and a folder name, but they must always contain the filename. You can't copy a file that is currently in an open state. Programming Tips amp Gotchas If you don't specify a drive or folder in either the source or destination, the file is assumed to be in the current drive or folder. Unlike copying a...

Load ResPicture Function

LoadResPicture resID, resType resID A numeric or string value specifying the resource ID of the picture to load. A numeric constant denoting the format of the picture to load. See the following table for valid constants and values Assigns a graphic from a resource file .RES to the Picture property of a form or The images to be loaded by LoadResPicture must be included in a resource .RES file. Each must be assigned a unique identifier, which is typically represented by a numeric constant. The...

ChDrive Statement

The letter of the drive A Z to set as the new default drive. Description Changes the current working default disk drive. Rules at a Glance If a zero-length string is supplied, the drive isn't changed. If driveletter consists of more than one character, only the first character determines the drive. The following example demonstrates a utility function that uses ChDrive to determine if a given drive is available. By centralizing the test, the function reduces the amount of coding required each...

InStr InStrB Functions

InStr start, stringtosearch, stringtofind , _ comparemode The starting position for the search. 382 Chapter 7- The Language Reference Finds the starting position of one string within another. Rules at a Glance The return value of InStr is influenced by the values of stringtosearch and stringtofind, as the following table details If the start argument is omitted, InStr commences the search with the first character of stringtosearch. If the start argument is Null, an error occurs. You must...

CallByName Function VB

CallByName object, procedurename, calltype, _ argument1, , argumentn A reference to the object containing the procedure being called. A constant that indicates the type of procedure being called. vbCallType constants are listed in the next table. 142 Chapter 7- The Language Reference Any number of variant arguments, depending on the argument list of the procedure to call. The called procedure is a Property Get The called procedure is a Property Let The called procedure is a method this can be a...

Param Array

The ParamArray keyword short for Parameter Array allows you to accept a variable number of arguments into a procedure. The ParamArray must be the last argument in the list, and it can't be used in the same argument list as an Optional argument. The ParamArray is an optional variant array. That is, the array can be empty, or it can contain any number of variant elements. To see how this operates, here's a quick example blnOK DoStuff Wednesday, 1234, _ CDate 04 12 1999 , 123.444 Private Sub...