#Else clause must be preceded by a matching #If
#Else If, #Else, or #End If must be preceded by a matching #If
#ElseIf must be preceded by a matching #If or #ElseIf and followed by an #ElseIf, #Else, or #End If
<Item> is not a valid control type (Error 720)
A compatible ActiveX component must be a Visual Basic executable or a DLL
A form can't be moved or sized while minimized or maximized
A module is not a valid type
A procedure of that name already exists
A procedure with a ParamArray argument cannot be called with named arguments
A property or method call cannot include a reference to a private object, either as an argument or as a return value (Error 98)
ActiveX component can't create object or return reference to this object (Error 429)
ActiveX Component did not run correctly (Error 338)
ActiveX component not correctly registered (Error 336)
ActiveX control 'item' not found (Error 363)
Add-in can't reference project
Ambiguous name detected
Application-defined or object-defined error
Argument not optional (Error 449)
Argument required for Property Let or Property Set
Array already dimensioned
Array argument must be ByRef
Assignment to constant not permitted
Automation error (Error 440)
Automation object doesn't have a default value (Error 443)
Bad DLL calling convention (Error 49)
Bad file mode (Error 54)
Bad file name or number (Error 52)
Bad interface for Implements: interface contains data fields
Bad interface for Implements: interface is derived from another pure interface with non-restricted methods
Bad interface for Implements: interface is not derived from Iunknown
Bad interface for Implements: method does not return HRESULT
Bad interface for Implements: method has out-only or LCID parameter
Bad interface for Implements: method has parameter with too many levels of indirection
Bad interface for Implements: method has underscore in name
Bad interface for Implements: methods uses type that is not supported by Visual Basic
Bad record length (Error 59)
Bad record number (Error 63)
Beginning of search scope has been reached; do you want to continue from the end?
Block If without End If
Breakpoint not allowed on this line
ByRef argument type mismatch
Calling convention not supported by Visual Basic
Cannot define a KWID_tkPUBLIC user-defined type within a private object module
Cannot display specified name because it is hidden
Cannot handle events for the object specified
Cannot jump to specified type because it is in the specified library, which is not currently referenced
Can't add a reference to the specified file
Can't assign or coerce array of fixed-length string or user-defined type to Variant
Can't assign to an array
Can't call Friend procedure on an object that isn't an instance of the defining class (Error 97)
Can't change data types of array elements
Can't create AutoRedraw image (Error 480)
Can't create necessary temporary file (Error 322)
Can't display hidden procedure
Can't display system information
Can't edit module
Can't empty Clipboard (Error 520)
Can't enter break mode at this time
Can't execute code in break mode
Can't execute immediate statements in design mode
Can't exit design mode because control can't be created
Can't find DLL entry point in specified DLL
Can't find project or library
Can't find Windows Help .exe file
Can't Get or Put user-defined type containing object reference
Can't have paramarrays with optional arguments
Can't load module; invalid format
Can't load or unload this object (Error 361)
Can't make an assignment to a read-only property
Can't open Clipboard (Error 521)
Can't perform operation because the project is protected
Can't perform requested operation (Error 17)
Can't perform requested operation since the module is hidden
Can't place conditional breakpoint on an array
Can't print form image to this type of printer (Error 486)
Can't print minimized form image
Can't quit at this time
Can't record into running module
Can't ReDim, Erase, or assign to Variant that contains array whose element is With object
Can't remove control or reference; in use
Can't remove default reference
Can't rename with different drive (Error 74)
Can't save file to TEMP directory (Error 735)
Can't set the project name at this time
Can't show non-modal form when modal form is displayed
Can't sink this object's events because it's already firing events to the maximum number of supported event recipients (Error 96)
Can't start new recording until current session is ended
Can't use character device names in file names: 'item' (Error 320)
Case Else outside Select Case
Case without Select Case
Circular dependencies between modules
Class doesn't support Automation (Error 430)
Class is not set (Error 31018)
Class not registered on local machine (Error 463)
Class not registered. 'item 1'
Code execution has been interrupted
Code resource lock error (Error 455)
Code resource not found (Error 454)
Compile error in hidden module: <module name>
Component could not successfully create requested object
Component 'item' or one of its dependencies not correctly registered: a file is missing or invalid
Component not correctly registered
Component not found in registered location
Conflicting attributes were found in 'item'. The defaults will be used
Conflicting names were found in 'item1'. The name 'item2' will be used
Connection to type library or object library for remote process has been lost (Error 442)
Constant expression required
Constants, fixed-length strings, arrays, user-defined types, and Declare statements not allowed as Public members of an object module
Could not access system registry (Error 335)
Could not create reference: 'item'
Could not execute specified program
Could not start Internet Explorer
Current module does not support Print method
Cyclic reference of projects not allowed
Data value named not found (Error 327)
Definitions of property procedures for the same property are inconsistent
Deftype statements must precede declarations
Destination label too far away; loop, Select Case, or block If too large
Device I/O error (Error 57)
Device unavailable (Error 68)
Disk full (Error 61)
Disk not ready (Error 71)
Display more load errors?
Division by zero (Error 11)
Do without Loop
Do you want to export specified object before removing it?
Duplicate declaration in current scope
Duplicate Deftype statement
Duplicate Option statement
Duplicate procedure name
Duplicate resources with same type and name
Edit can't be undone--proceed anyway?
Else without If
Empty Enum type not allowed
Empty watch expression
End If without block If
End of search scope has been reached; do you want to continue from the beginning?
End Select without Select Case
End With without With
Enum types defined in standard modules or private classes cannot be used in public object modules as parameters or return types for public procedures, as public data members, or as fields of public user defined types
Error accessing the system registry
Error in loading DLL (Error 48)
Error loading from file (Error 31037)
Error loading 'item'. A control could not be loaded due to load error. Continue?
Error loading 'item'. An error was encountered loading a property. Continue?
Error saving to file (Error 31036)
Errors during load. Refer to 'item' for details
Errors occurred during load
Event handler is invalid
Event not found
Exit Do not within Do...Loop
Exit For not within For...Next
Exit Function not allowed in Sub or Property
Exit Property not allowed in Function or Sub
Exit Sub not allowed in Function or Property
Expected End Function
Expected End Property
Expected End Sub
Expected End With
Expected Function or variable
Expected procedure, not module
Expected procedure, not project or library
Expected procedure, not user-defined type
Expected procedure, not variable
Expected Sub, Function, or Property
Expected user-defined type, not project
Expected variable or procedure, not Enum type
Expected variable or procedure, not module
Expected variable or procedure, not project
Expression too complex (Error 16)
External name not defined
Failed to activate control 'item 1'. This control may be incompatible with your application. Make sure you are using the version of the control that was provided with your application.
Failed to load control 'item1' from 'item2'. Your version of 'item2' may be outdated. Make sure you are using the version of the control that was provided with your application.
File already exists (Error 58)
File already open (Error 55)
File format no longer supported
File is read-only
File not found (Error 53)
File specified was not found
Filename or class name not found during Automation operation (Error 432)
Fixed or static data can't be larger than 64K
Fixed-length strings and use of the 'new' qualifier are not allowed for fields in a public user defined type defined in an object module
Fixed-length strings not allowed as the type of a public member of an object module; private object modules not allowed as the type of a public member of a public object module
For control variable already in use
For Each can only iterate over a collection object or an array
For Each control variable must be Variant or Object
For Each control variable on arrays must be Variant
For Each may not be used on array of user-defined type or fixed-length strings
For loop not initialized (Error 92)
For without Next
Form already displayed; can't show modally (Error 400)
Form not found (Error 424)
Forward reference to user-defined type
Function call on left-hand side of assignment must return Variant or Object
Function marked as restricted or uses a type not supported in Visual Basic
Identifier too long
Identifier under cursor isn't a procedure name
Illegal parameter. Can't write arrays (Error 328)
Illegal parameter. Can't write object because it does not support persistence.
Illegal parameter. Can't write user-defined type.
Incorrect DLL version
Incorrect OLE version
Input past end of file (Error 62)
Insufficient Immediate window memory to create variable
Insufficient memory to save Undo information
Insufficient project information to load project on platform or with version now being used
Interface not valid for Implements
Internal error (Error 51)
Invalid Access mode
Invalid attribute in Sub, Function, or Property
Invalid Base Address
Invalid Clipboard format (Error 460) [1 of 2]
Invalid Clipboard format (Error 460) [2 of 2]
Invalid data format
Invalid data type for constant
Invalid event name
Invalid file format (Error 321) [1 of 2]
Invalid file format (Error 321) [2 of 2]
Invalid format in resource file (Error 325)
Invalid in Immediate window
Invalid inside Enum
Invalid inside procedure
Invalid length for fixed-length string
Invalid Next control variable reference
Invalid object use (Error 425)
Invalid optional parameter type
Invalid or unqualified reference
Invalid ordinal (Error 452)
Invalid outside Enum
Invalid outside procedure
Invalid ParamArray use
Invalid pattern string (Error 93)
Invalid picture (Error 481) [1 of 2]
Invalid picture (Error 481) [2 of 2]
Invalid picture type (Error 485)
Invalid procedure call or argument (Error 5)
Invalid procedure name
Invalid property name
Invalid property value (Error 380) [1 of 2]
Invalid property value (Error 380) [2 of 2]
Invalid property-array index (Error 381)
Invalid syntax for conditional compiler constant declarations
Invalid type-declaration character
Invalid use of AddressOf operator
Invalid use of base class name
Invalid use of Me keyword
Invalid use of New keyword
Invalid use of Null (Error 94)
Invalid use of object
Invalid watch expression
Item' already exists in project
'item' cannot be added because it is referenced but not in use by any items in the project. To correct this, uncheck 'Remove information about unused ActiveX Controls' in Project Options.
Item' could not be loaded
'Item' could not be loaded. Remove it from the list of available add-ins?
Item' could not be registered
'item' designers can only be used in DLL projects
'item' designers cannot be private
'item' designers cannot be public in ActiveX EXE projects
'item' designers must be public and cannot be used in Standard EXE projects
'Item' has an old file format. When saved, it will be saved in a newer format.
'item' has caused an access violation. Remove it from the list of available Add-Ins?
'Item' is a binary form and can't be loaded into Visual Basic
'Item' is a read-only file
'item' is a single-threaded component and cannot be used in multi-threaded projects. Change the threading model for 'item' or contact the component vendor for an updated version.
'Item' property can't be read at run time (Error 393)
Access's relational spreadsheets rely on variables and syntax to manipulate the data, but understanding how these things actually function can be confusing. This chapter will teach you how to manipulate these functions using VBA.This chapter is from the book
In this chapter
Declaring Variables and Constants
Declaring Variables and Constants
The key to learning VBA is the same as if you were learning a foreign language. You must learn the basics first. VBA, like any language, has its own syntax and components that you must combine in just the right way for Access to understand your message and respond. Until you learn these rules, you'll find it difficult, if not impossible, to speak to Access.
VBA uses variables and constants to represent a value. Technically, a variable is a small portion of memory that stores a piece of information, but people tend to think of them as names that represent data. Constants are similar to variables in that they store a value. What the two have in common is that both represent values or objects. The main difference between the two is that a constant represents a value that doesn't change, whereas variables can be updated at any time.
Variables are a representation of a value or an object. You assign a descriptive name using your naming convention, declare a data type for the variable and then use it, reuse it, and even change it.
Before you use a variable, declare it using the statement in the formDim variablename As [New] datatype
where variablename identifies the variable by name, and datatype is one of many VBA data types. If you omit datatype, VBA defaults to a Variant. It's best to always explicitly declare a variable's data type. Variant variables require slightly more memory and are slightly slower than other types of variables. On the other hand, they don't limit the type of data you store. But you are unlikely to have the requirement to store many types of data in a single variable, which is the only good use for a variant.
The keyword is optional and can only be used when declaring an object variable. To learn more about this issue, read Chapter 8, "Understanding Objects.."
Generally, statements appear at the beginning of a procedure. This arrangement isn't required, but you'll find most developers adhere to this guideline. By grouping them at the beginning, you can find them much quicker.
You can declare a number of variables in a single line by separating them with commas, as follows:Dim variable1 As datatype, variable2 As datatype
Declared variables are supported by IntelliSense. That means you can choose the variable from the completion drop-down list rather than enter it from the keyboardthus avoiding typos.
Using Option Explicit
By default, VBA lets you enter undeclared variables in your code. To illustrate, open a standard blank module and enter the following code:Private Function DeclarationTest() varValue = "Undeclared variable" Debug.Print varValu End Function
Be sure to open the Immediate window if necessary by pressing Ctrl+G. Next, position the cursor inside the procedure and press F5 to execute it. Did you expect to see the string, in the Immediate window? The reason you don't see the string is because is misspelled in the statement. The variable does equal the string , but the variable equals at this point. Finding the problem can be difficult, especially in a long and complex procedure.
VBA can force you to declare variables to avoid such problemswhich is a good practice. To do so, enter the Option Explicit statement in the module's General Declarations area. After doing so, execute the procedure again. This time, VBA returns the error shown in Figure 3.1.
The Option Explicit statement can mean the difference between errors and no errorsand all you have to do is turn it on. We strongly recommend that you enable this feature and leave it on to avoid the almost unavoidable typos that occur when writing code.
Figure 3.1 Undeclared variables return an error when you're using Option Explicit.
Click OK to close the error message, and then click Reset to clear the error. Declare the variable by adding the following statement to the beginning of the procedure:Dim varValue As Variant
Next, run the procedure a third time. This time, VBA catches the misspelled variable , as shown in Figure 3.2. Click OK and then click Reset to clear the error and fix the misspelled variable. At this point, if you run the procedure, you'll see the expected string in the Immediate window, as shown in Figure 3.3.
Figure 3.2 VBA catches a misspelled variable.
Figure 3.3 After fixing the misspelled variable, VBA runs the procedure as expected.
The previous method enables the automatic variable declaration feature for only the current module. To enable this feature for all new modules, do the following:
In the VBE, choose Tools, Options.
Click the Editor tab.
Check the Require Variable Declaration option shown in Figure 3.4.
Click OK to close the Options dialog box.
Figure 3.4 Enable the automatic variable declaration feature.
Enabling the Require Variable Declarations feature for all modules affects only new modules inserted after enabling the feature. You must update any existing modules by adding the Option Explicit statement manually.
Your naming convention should also cover variables. Aside from your naming convention, there are a few inherent rules you need to consider:
A variable name must begin with an alphabetic character.
Don't use the following special characters in a variable name: , , , , , , and .
Variable names must be unique. It really isn't as simple as all that, but for now, knowing that you can't give two variables in the same procedure the same name is sufficient.
A variable name can consist of up to 255 characters.
Naming conventions are covered in Chapter 2, in the section "Establishing Good Habits in Coding."
You'll find that the term constant has many meanings in VBA. A constant represents a literal value, much in the same way a variable represents a value or an object. The difference is, the value of a constant can't be changed while the code is executingnot even by mistake.
Use the statement to declare a constant in the form[Public | Private] Const constantname As datatype = expression
Items in square brackets are optional, and the vertical bar indicates a choice. So a constant declaration can start with , or , or just . In any case, constantname identifies the constant by name, datatype specifies the constant's data type, and expression is the literal value that the constant equals. The expression argument can't refer to a variable, a result of a function procedure, or include one of VBA's many built-in functions.
The following example illustrates the use of a constant:
In a standard module, enter the following statement in the General Declarations area:Const conMessage As String = "Undeclared variable"
Enter the following procedure using the Insert Procedure dialog box or from the keyboard:Private Function ConstantTest() Dim varValue As Variant varValue = conMessage Debug.Print varValue End Function
With the insertion point somewhere inside , press F5 to execute the procedure. As you can see in Figure 3.5, the statement prints the contents of the variable , which refers to (the constant).
Change the string expression in the statement to and run the procedure again. This time, equals , as shown in Figure 3.6. Now imagine several references to throughout your module. Instead of updating each and every reference, you change just the value of in the declaration statement.
Figure 3.5 The variable equals the constant named .
Figure 3.6 Change the constant value in the declaration statement.
In addition to letting you define your own constants, VBA offers a number of predefined constants, known as intrinsic constants, that you can use to specify specific data types (and other values).
There are two advantages to using these constants. First, you don't have to define them yourself. Second, they improve the readability of your code. The intrinsic constant's name describes its value or purpose, thus making it instantly recognizable. To assign one of these subtypes to a variable, use that subtype's corresponding intrinsic constant as follows:varValue = vbEmpty
Intrinsic constants are predefined by VBA; you don't define them in your code, you simply refer to them in expressions and statements. You revisit intrinsic constants in the next section.