Object Models The Power of Programming with VBA

VBA is a single language, although when comparing code taken from a VBA program written for Word with one written for Access or Visual Basic, you could be forgiven for thinking you are reading code from two very different languages. This is because VBA interfaces with an application's object model, and much of the time the code you write references objects that are unique to the host application. To demonstrate this, in the VBA code fragments shown in Examples 1-1 through 1-4, generic VBA code...

Visual Basic Deletefile

Caching object references, 89 calendar (see date and time) Calendar property, 140-141 Call statement, 23, 141-142 callback functions, 121 CallByName function, 142-146 case conversion (strings), 399-400, 565 case conversion, 399-400, 565 converting character codes to, 159-160 getting ANSI Unicode codes, 129-132 ignoring when reading, 553 repeating into strings, 537-538 (see also strings) ChDir statement, 153-154 ChDrive statement, 155-156 Choose function, 156-158 Chr, Chr , ChrB, ChrB , ChrW...

Building a Robust Application

There are two approaches to error handling. The first is to let the error occur, then do something about it the second is to prevent the error from occurring. Each has its own merits. Supporters of the first approach argue that most errors occur very infrequently. For example, users of a computer system are by and large well trained and make few data entry errors. Therefore, the extra processing to prevali-date such things as numeric values is a waste of processing time and power it's more...

File SystemObject GetSpecial Folder Method VB

OFileSysObj .GetSpecialFolder( SpecialFolder) oFileSysObj Any object variable returning a FileSystemObject object. Data Type Special folder constant A value specifying one of three special system folders. Returns a reference to a Folder object of one of the three special system folders System, Temporary, and Windows. SpecialFolder can be one of the following special folder constants The Windows system folder ( windows system or windows system32) The folder that stores temporary files ( windows...

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

Intelli Sense and Statement Completion

Both the retail version of VB and the VBA development environment make use of Microsoft's IntelliSense technology. Statement Completion not only helps to speed the development of VB applications, but it acts as a guide, leading you through the object's hierarchy as you are coding by displaying only those objects, methods, properties, and events that are available for the code you are currently writing, as Figure 5-3 shows. Private Sub Coimiandl Click () dim oXLApp as excel. By adding an object...

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

Ending Your VB Program

At some stage, most users want to exit from a program. OK, yours might be a really great program, but unfortunately the user may want to go off and do some- thing else like go home You have to allow your application to both exit and tidy up before it ends. One advantage you have when building an application in a VBA-hosted environment is that you don't have to worry too much about finishing the program the majority of the work is taken care of by the host application. You just have to ensure...

The Structure of a VB Program

Any VB program whether a hosted VBA application or a VB executable is a collection of modules containing code, graphical user interface objects, and classes. This book concentrates on the language elements of VBA as they relate to both hosted VBA and the retail version of VB. The VBA and VB user interfaces whether Word, Excel, Project, or a VB form all fire events that are handled by the code you create using the VBA language. Therefore the code modules within your program are of greatest...

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

Put Statement

Put filenumber, recnumber , varname filenumber Record or byte number to begin the write operation. The name of the variable containing the data to be written to the file. Description Writes data from a program variable to a disk file. Rules at a Glance If filenumber is opened in random access mode, recnumber refers to the record number if the file is opened in binary access mode, recnumber refers to a byte number. Both bytes and records in a file are numbered from 1 upward. If recnumber is...

For Each Next Statement

For Each element In group statements Exit For statements Next element 322 Chapter 7 - The Language Reference A variant or object variable to which the current element from the group is assigned. A collection, object collection, or an array. A line or lines of program code to execute within the loop. Description Loops through the items of a collection or the elements of an array. Rules at a Glance The For Each code block is executed only if group contains at least one element. If group is a...

Declaring Variables and Constants

As was mentioned earlier, VBA supports a default data type, which means that, unlike many other programming languages, VBA allows the implicit declaration of variables. As soon as you use a variable or constant name within your code, VBA does all the necessary work of allocating memory space, etc., and the variable is considered to be declared. However, it's good programming practice and one that will save you endless hours of grief to explicitly declare any variables and constants you want to...

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

What Is VBA

Extensible, and it's ActiveX or OLE automation that provides the interface between VBA and its host application. It's this support for OLE automation that makes VBA an outstanding tool for rapidly developing robust Windows applications. Until the launch of VBA 5.0 in early 1997, the language had no development environment very much like VBScript today, VBA was simply a language interpreter. VBA 5.0 marked the start of an exciting new chapter for VBA it now has its own integrated development and...

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

Implementing a User Defined Type Property

Visual Basic 6 adds the user-defined type UDT to the list of data types a property can represent. However, its use isn't intuitive. Here are the steps needed to create a UDT property in VB6 1. Declare a Public user-defined type definition. 2. Declare a Private member variable whose data type is that of the user-defined type. 3. Declare a Public Property Get procedure whose data type is that of the user-defined type. 4. The assignation within the Property Get procedure should be the Private...

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

Option Base Statement

Used at the beginning of a module to specify the default lower bound for arrays dimensioned within the module. The default lower bound for arrays created in Visual Basic is 0. Therefore, you should only use Option Base 1 to change the default base for arrays to 1. 444 Chapter 7 - The Language Reference The Option Base statement must appear at the start of a module, before any array declarations. The Option Base statement affects only those arrays declared in the module in which the Option Base...

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

Rnd Function

Data Type Single Any valid numeric expression. Return Value A Single data type random number. Description Returns a random number. Rules at a Glance The behavior of the Rnd function is determined by seed, as described in this table The same number each time, using seed as the seed The next random number in the current sequence The next random number in the current sequence The Rnd function always returns a value between 0 and 1. If number isn't supplied, the Rnd function uses the last number...

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

Private Statement

Private WithEvents varname subscripts As New _ type , WithEvents varname subscripts _ As New type . . . WithEvents A keyword that denotes the object variable varname can respond to events triggered from within the object to which it refers. The name of the variable, following Visual Basic naming conventions. Denotes varname as an array and specifies the number and extent of array dimensions. 460 Chapter 7- The Language Reference Automatically creates an instance of the object referred to by the...

Dictionary Object VB

Microsoft Scripting Runtime SYSTEM32 SCRRUN.DLL Description The Dictionary object is another new feature of VB6 that has found its way into wider use from its humble beginnings in Version 2 of the VBScript scripting runtime. The Dictionary object is similar to a Collection object, except that it's loosely based on the Perl associative array. Like an array or a Collection object, the Dictionary object holds elements, which are called items or members, containing data. A Dictionary object can...

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

Sng Function

The range of expression is -3.402823E38 to -1.401298E-45 for negative values, 1.401298E-45 to 3.402823E38 for positive values. expression cast as a Single data type. Description Returns a single-precision number. Rules at a Glance expression must evaluate to a numeric value otherwise, a type mismatch error is generated. If the value of expression is outside the range of the double data type, an overflow error is generated. Dim sngMyNumber as Single If IsNumeric sMyNumber then 180 Chapter 7- The...

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

Len LenB Functions

Data Type Any except object A valid variable name. Return Value A Long integer Description Counts the number of characters within a string or the size of a given variable. Use LenB to determine the actual number of bytes required to hold a given variable in memory. string and varname are mutually exclusive that is, you must specify either string or varname, but not both. If either string or varname contains Null, Len returns Null. Len returns the size number of characters that a user-defined...

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

Str Str Functions Named Arguments

Data Type Numeric Variant Any numeric expression. Return Value A Variant of subtype String returned by Str or a String returned by Str representing number. Converts number from a numeric to a string. Rules at a Glance If the return value is positive, the Str function always includes a leading space in the returned string for the sign of number. Use the LTrim function to remove the leading space that the Str function adds to the start of the returned string. Both the CStr and Format functions...

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

DoEvents Function

In VBA, DoEvents returns 0 in the retail version of VB, it returns the number of open forms. Allows the operating system to process events and messages waiting in the message queue. For example, you can allow a user to click a Cancel button while a processor-intensive operation is executing. In this scenario, without DoEvents, the click event wouldn't be processed until after the operation had completed with DoEvents, the Cancel button's Click event can be fired and its event handler executed...

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

Mid Mid MidB MidB Functions

The expression from which to return a substring. The starting position of the substring. Use Optional Data Type Variant Long The length of the substring. Return Value A Variant of subtype String or a String data type. Description Returns a substring of a specified length from within a given string. 422 Chapter 7- The Language Reference If string contains a Null, Mid returns Null. If start is more than the length of string, a zero-length string is returned. If start is less than zero, runtime...

Get AutoServer Settings Function

Clsid object An object variable representing the RacReg library. The programmatic identifier ProgID for the component. The class identifier CLSID for the component. Return Value A Variant containing an array of values, described in the following table 1 if the ActiveX component is registered to execute remotely, 0 if regis- Returns registration information for an ActiveX object. Rules at a Glance Although both ProgID and Clsid are optional, one must be specified. They are also mutually...

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

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

Debug Print Method

Always evaluates to the Debug object. Expression or list of expressions to print. If omitted, a blank line is printed for details, see the following table . Spc n Tab n expression charpos Spc n Inserts n space characters in the output string. Inserts a Tab character at position n. Numeric or string expression to print in the Immediate window. Determines the position of the next character. Description Prints text in the Immediate pane of the Debug window in the design-time environment. 212...

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

Rem Statement

A textual comment to place within the code. Description Use the Rem statement or an apostrophe ' to place remarks within the code. Rules at a Glance Text or code commented out using either the Rem statement or an apostrophe isn't compiled into the final program and therefore doesn't add to the size of the executable. 488 Chapter 7- The Language Reference If you use the Rem statement on the same line as program code, a colon is required after the program code and before the Rem statement. For...

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

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

Dec Function

The range is for numbers with no decimal places. For numbers with up to 28 decimal places, the range is -7.9228162514264337593543950335. The smallest possible nonzero number is 0.0000000000000000000000000001. expression cast as a Variant Decimal subtype. 152 Chapter 7- The Language Reference As there is no intrinsic Decimal data type in Visual Basic, CDec actually casts expression as a variant of subtype Decimal. If the value of expression is outside the range of the double data type, an...

LOF Function

Returns the size of an open file in bytes. Rules at a Glance filenumber must be the number of a file opened using the Open statement. The following example shows how to use the LOF function to determine the length of a data file and to determine the number of records it contains Open sFileName For Random As iFile Len Len udtCustomer iMaxRecs LOF iFile Len udtCustomer For iRec 1 To iMaxRecs Get 1, iRec, udtCustomers iRec Next iRec Close iFile LOF works only on an open file if you need to know...

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

Get Attr Function

Data Type String File and optional path name. Return Value An integer representing the attributes set for the file or folder, being the sum of the following constant values Determines which attributes have been set for a file, directory, or folder. Rules at a Glance pathname may optionally include a directory or folder name and a drive letter, including a network drive. pathname can also follow the UNC format of machine_name drive. You can check if a particular attribute has been set by...

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

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