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...
Print Statement
Print filenumber, outputlist filenumber A list of expressions to output to a file. Spc n Tab n expression charpos Spc n Insert n space characters before expression. 450 Chapter 7 - The Language Reference Position the insertion point either at the next print zone by omitting n or at column number n . Position of the insertion point for the first character of the next expression. Description Outputs formatted data to a disk file opened for append or output. Rules at a Glance You can delimit...
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...
Calendar Property
Calendar calendarconstant calendarconstant can be either of the following constants Application uses the Gregorian calendar default Returns or sets a value specifying the type of calendar to use with your project. The Gregorian calendar year of 1998 which is based on the birth of Christ is roughly equivalent to the Hijri year of 1418 which is based on the birth of Mohammed . Note that the new year of each calendar is different, and the Hijri calendar, being based more accurately on the cycles...
Enum Statement
Public Private Enum name membername constantexpression membername constantexpression The name of the enumerated data type. The name of a member of the enumerated data type. The value to be assigned to membername. Description Defines an enumerated data type. All the values of the data type are defined by the instances of membername. 246 Chapter 7- The Language Reference The Enum statement can appear only at module level, in the declarations section of a form, code module, or class module. The...
InputBox Function
InputBox prompt , title , default , xpos _ , ypos , helpfile, context String to be displayed in the text box on loading. The distance from the left side of the screen to the left side of the dialog box. The distance from the top of the screen to the top of the dialog box. Specifies the Help file to use if the user clicks the Help button on the dialog box. Specifies the context number to use within the Help file specified in helpfile. InputBox returns a variant string containing the contents of...
Implementing Custom Events
In the early versions of VB, programmers were limited to working with the built-in events. In VB5, however, three simple keywords Event, RaiseEvent, and WithEvents were added to the language to allow the programmer to define custom events or to trap events in external objects that would otherwise be inaccessible. Custom events can be used for any of the following To report the progress of an asynchronous task back to the client application from an out-of-process ActiveX EXE component. To pass...
GetObject 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...
DateAdd Function
DateAdd interval, number, date interval An expression denoting the interval of time you need to add or subtract see the table Interval Settings . An expression denoting the number of time intervals you want to add or subtract. A Variant of subtype Date or a literal denoting the date on which to base the DateAdd calculation. 196 Chapter 7- The Language Reference A Variant of subtype Date. Description Returns a variant of subtype Date representing the result of adding or subtracting a given...
CollectionItem Method
objectvariable .Item index objectvariable Use Required An object variable of type Collection. If a string, index is the key if numeric, index is the ordinal position. Description Returns the member of the collection whose key or ordinal position corresponds to index. If index is a string, it's taken to be the key, and the member of the collection with the key of index is returned. If index is a number, it's taken to be the index number and the member in the ordinal position index is returned....
Folders Collection Object VB Description
The Folders collection object is a container for Folder objects. Normally, you expect to access a single object from the collection of that object for example, you'd expect to access a Folder object from the Folders collection object. However, things are the other way round here you access the Folders collection object from an instance of a Folder object. This is because the first Folder object you instantiate from the Drive object is a Root Folder object, and from it you instantiate a...
LBound Function
398 Chapter 7- The Language Reference A number specifying the dimension of the array. Return Value A Long integer. Description Determines the lower limit of a specified dimension of an array. The lower boundary is the smallest subscript you can access within the specified array. If dimension isn't specified, 1 is assumed. To determine the lower limit of the first dimension of an array, set dimension to 1, 2 for the second, and so on. The lower bound of an array dimension can be set using To,...
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...
Load Statement
Data Type A Form or Control object An expression that evaluates to a form or control. Description Loads a form or control into memory. Rules at a Glance When a control or form is first loaded using the Load statement, it's resident in memory, but it isn't visible on the screen. To make a form visible, use the form's Show method. To make a control visible, set its Visible property to True. You can explicitly create a control only with the Load statement as part of a control array. See following...
Constants Defined Through the VBVBA Interface
The rules for defining constants in the Conditional Compilation Arguments text box are somewhat different than for constants defined in code using the Const statement. The value assigned through the VB VBA interface must be an integer literal it can't be an expression formed by using multiple literals or conditional constants, along with one or more operators, nor can it be a Boolean value i.e., True or False . If multiple conditional constants are assigned through the user interface, they are...
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...
IfThen and Platform
In Visual Basic 4.0, which included both 16- and 32-bit versions, the major application of the If Then compiler directive was to generate separate executables for the 16- and 32-bit Windows platforms. For this purpose, VBA included two conditional compiler constants, Win16 and Win32 if one was True, the other was automatically set to False. Visual Basic 5.0 and VBA 5.0 onwards, however, support only the 32-bit Windows platforms. Consequently, the Win16 and Win32 constants are no longer...
DeleteSetting 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...
Folder Object
Attributes Property Copy Method CreateTextFile Function DateCreated Property DateLastAccessed Property DateLastModified Property Delete Method Drive Property IsRootFolder Property Move Method Name Property ParentFolder Property Path Property ShortName Property ShortPath Property Returns a FileAttributes constant value Copies this folder and its contents to another location Returns a TextStream object for the newly created text Returns the date the folder was created Returns the date the folder...
Using Word as a Report Writer from VB
This first application demonstrates how you can seamlessly use Microsoft Word to print output from your VB program without the user knowing that you have actually used Microsoft Word 'create an error handler On Error GoTo cmdWordDoc_Err 'create the local Early Bound object variables Dim oWord As Word.Application Dim oWordActiveDoc As Word.Document Dim oWordSel As Word.Selection 'Create a new instance of Word Set oWord New Word.Application 'Create a new document object Set oWordActiveDoc...
Syntax Mag
An expression evaluating to True or False. Program statements to execute while condition remains True. Description Repeatedly executes program code while a given condition remains True. Rules at a Glance A Null condition is evaluated as False. If condition evaluates to True, the program code between the While and Wend statements is executed. After the Wend statement is executed, control is passed back up to the While statement, where condition is evaluated again. When condition evaluates to...
MkDir Statement
The name of the folder to be created. Description Creates a new folder. Rules at a Glance If you omit the drive from path, a new folder is created on the current drive. You can specify the drive using either its local drive letter or its UNC name. Programming Tips amp Gotchas If your program is running on Windows NT, ensure that the logged-in user has the rights to create a folder on the specified drive prior to calling the MkDir statement. VB doesn't make the new folder the current folder...
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...
Program Structure and Flow
Calls the procedure specified as a string variable Defines a prototype for a call to an external DLL library function Repeats a section of code while or until a condition is met Branches to the next line of code outside of the currently executing structure For Each .Next Statement Iterates through a collection or array of objects or values, returning a reference to each to the members Iterates through a section of code a given number of times Declares the procedure or variable to have scope...
IfThenElse Directive
statements ElseIf furtherexpression Then elsestatements End If expression An expression made up of operators and conditional compiler constants that evaluate to True or False. One or more lines of code that are executed if expression evaluates to True. An expression made up of operators and conditional compiler constants that evaluates to True or False. furtherexpression is evaluated only if the preceding expression evaluates to False. One or more lines of code that are executed if...
FileSystemObjectCopyFile 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...
Get Statement
Get filenumber, recnumber , varname filenumber 350 Chapter 7 - The Language Reference The variable into which the data is read. Copies data from a file on disk into a program variable. Rules at a Glance For files opened in random mode, recnumber refers to the record number in the file. For files opened in binary mode, recnumber refers to the byte number within the file. The number of bytes read by the Get statement is governed by the data type of varname. The position of the first record or...
SLN Function
The value of the asset at the end of its useful life. The length of the useful life of the asset. A Double representing depreciation per period. Computes the straight-line depreciation of an asset for a single period. VB amp VBA in a Nutshell The Language, eMatter Edition Copyright 2000 O'Reilly amp Associates, Inc. All rights reserved. The function uses a very simple formula to calculate depreciation cost - salvage life The depreciation period is determined by the time period of life. All...
Sin Function
A Double containing the sine of an angle. 520 Chapter 7- The Language Reference Returns the ratio of two sides of a right triangle in the range -1 to 1. Rules at a Glance The ratio is determined by dividing the length of the side opposite the angle by the length of the hypotenuse. You can convert degrees to radians using the formula radians degrees pi 180 You can convert degrees to radians using the formula degrees radians 180 pi The math teacher I wish I'd listened to in school.
Input Statement
Input filenumber, varlist filenumber Comma-delimited list of variables or user-defined types. Description Reads delimited data from a sequential file opened in input or binary mode. Rules at a Glance Use Input only with files opened in input or binary modes. Variables in varlist can't be array or object variables. Both the type and position of data items in the file must match the variables in varlist. If Input expects to find a numeric value based on the type of a variable in varlist, and the...
CInt Function
The range of expression is -32,768 to 32,767 fractions are rounded. 160 Chapter 7- The Language Reference Converts expression to an integer any fractional portion of expression is 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 Integer data type, an overflow error is generated. When the fractional part of expression is exactly 0.5, CInt always rounds it to the nearest even number. For example, 0.5...
PV Function
The number of payment periods in the annuity. The future value of the loan or annuity. Flag specifying whether payments are due at the start or the end of the period. A Double specifying the present value of an annuity. Description Calculates the present value of an annuity either an investment or loan based on a regular number of future payments of a fixed value and a fixed interest rate. The present value is the current value of a future stream of equal cash flows discounted at some fixed...
Implementing a UserDefined 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...
CreateObject Function
Set objectvariable CreateObject library. object , servername objectvariable Use Required A variable to hold the reference to the instantiated object. The name of the application or library containing the object. The type or class of object to create. Use Optional Available in VB6 only The name of the server on which the object resides. A reference to an ActiveX object. Description Creates an instance of an OLE Automation ActiveX object. Prior to calling the methods, functions, or properties of...
ErrLastDLLError 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...
Fix Function
The same data type as passed to the function containing only the integer portion of number. Removes the fractional part of a number. Operates in a similar way to the Int function. 310 Chapter 7- The Language Reference If number is Null, Fix returns Null. The operations of Int and Fix are identical when dealing with positive numbers numbers are rounded down to the next lowest whole number. For example, both Int 3.14 and Fix 3.14 return 3. If number is negative, Fix removes its fractional part,...
DatePart Function
DatePart interval, date , firstdayofweek , _ The unit of time to extract from within date see the table Interval Settings . The Date value that you want to evaluate. A numeric constant that defines the first day of the week. If not specified, Sunday is assumed see the table First Day of Week Constants . A numeric constant that defines the first week of the year. If not specified, the first week is assumed to be the week in which January 1 occurs see the table First Week of Year Constants ....
Public Statement
Public 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, which must follow Visual Basic naming conventions. Denotes varname as an array and specifies the dimensions and number of elements of the array. Used to automatically create an instance of the object referred to by the object...
RSet Statement
The name of a string variable to receive string. A string expression to be copied into stringvar. 500 Chapter 7 - The Language Reference Copies string into stringvar, right-aligning string within stringvar. Rules at a Glance RSet has meaning only when dealing within fixed-length strings. If the length of string is less than that of stringvar, the extra characters within stringvar are padded with spaces. If the length of string is greater than that of stringvar, string is truncated to the length...
Initialize Event Syntax
Private Sub object_Initialize Description Use the Initialize event of an object or class to prepare the object or class for use, setting any references to subobjects or assigning values to module-level variables. The Initialize event is triggered automatically when an object or class module is first used. The precise point at which the Initialize event is fired depends on how the object is created. The Initialize event isn't triggered by the declaration of a new object. It's not until the...
Sub Statement
Public Private Friend Static Sub name arglist statements Exit Sub statements End Sub Public Gives the sub procedure scope through all procedures in all modules in the project. If used within a createable class module, the sub procedure is also accessible from outside the project. Public, Private, and Friend are mutually exclusive. Restricts the scope of the sub procedure to those procedures within the same module. Public, Private, and Friend are mutually exclusive. Only valid within a class...
BindingCollection RemoveMethod VB
oBindingColl. Remove Binding oBindingColl Use Required An object reference that returns a BindingCollection object. A reference to a binding object in the BindingCollection. Description Removes a previously added Binding object from the BindingCollection object. 138 Chapter 7- The Language Reference Unlike most other Remove methods attached to collections in VBA, the Binding-Collection's Remove method's parameter is a reference to the object being removed from the collection. In this short...
File System Object Model VB Library to Reference
Microsoft Scripting Runtime SYSTEM32 SCRRUN.DLL Description For years, VB developers have been using the VBA language to perform tasks such as opening, writing, and closing files, but with VB6, that's about to change. Of course, for backward compatibility, all the original file and directory manipulation statements and functions are still there, but now VB includes the File System object model, a rich object model for local and network file access. The File System object model is itself not an...
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...
Sgn Function
Data Type Numeric A numeric expression. Return Value A Variant of subtype Integer. Description Determines the sign of a number. Rules at a Glance The return value of the Sgn function is determined by the sign of number I suppose that someone, somewhere, has found a really good use for the Sgn function. However, its usefulness escapes me, because you need to carry out a test on the return value of the function identical to that which you could use on the number to find its sign. If you're...
IsNull Function
An expression containing string or numeric data. Return Value Boolean True or False . Description Determines whether expression contains any Null data. Rules at a Glance If the expression passed to IsNull contains null data, True is returned otherwise, IsNull returns False. All variables in expression are checked for null data. If null data is found in any one part of the expression, True is returned for the entire expression. Although any data type can be passed to the function, only a variant...
Erase Statement
Resets the elements of an array to their initial unassigned values. In short, Erase clears out or empties an array. Specify more than one array to be erased by using commas to delimit array-list. Fixed array variables remain dimensioned on the other hand, all memory allocated to dynamic arrays is released. The following table describes how Erase reinitializes the elements of a fixed array. Each member of the user-defined type is treated as Once you use Erase to clear dynamic arrays, they must...