Exctracted from Excel Macro Help.
Option Explicit Statement
Used at module level to force explicit declaration of all variables
in that module.
Syntax
Option Explicit
Remarks
If used, the Option Explicit statement must appear in a module before
any procedures.
When Option Explicit appears in a module, you must explicitly declare
all variables using the Dim, Private, Public, ReDim, or Static statements.
If you attempt to use an undeclared variable name, an error occurs
at compile time.
If you don't use the Option Explicit statement, all undeclared variables
are of Variant type unless the default type is otherwise specified
with a Deftype statement.
Note Use Option Explicit to avoid incorrectly typing the name of an
existing variable or to avoid confusion in code where the scope of
the variable is not clear.
Option
Explicit Statement
Dengan menggunakan option ini, maka
semua variable (akan kita diskusikan lebih lanjut) harus
di deklarasikan.
Option Explicit harus dibuat dan diletakkan paling atas, sebelum prosedur
yang lain. Untuk menghindari kesalahan meng-edit pada waktu menggunakan
variable, sangat dianjurkan untuk memakai Option Explicit.
Dim Statement
Declares variables and allocates storage space.
Syntax
Dim [WithEvents] varname[([subscripts])] [As [New] type] [, [WithEvents]
varname[([subscripts])] [As [New] type]] . . .The Dim statement
syntax has these parts:
Part Description
WithEvents Optional. Keyword that specifies that varname is an object
variable used to respond to events triggered by an ActiveX object.
WithEvents is valid only in class modules. You can declare as many
individual variables as you like using WithEvents, but you can't
create arrays with WithEvents. You can't use New with WithEvents.
varname Required. Name of the variable; follows standard variable naming
conventions.
subscripts Optional. Dimensions of an array variable; up to 60 multiple
dimensions may be declared. The subscripts argument uses the following
syntax:
[lower To] upper [, [lower To] upper] . . .
When not explicitly stated in lower, the lower bound
of an array is controlled by the Option Base statement. The lower bound
is zero if no Option Base statement is present.
New Optional. Keyword that enables implicit creation of an object.
If you use New when declaring the object variable, a new instance of
the object is created on first reference to it, so you don't have to
use the Set statement to assign the object reference. The New keyword
can't be used to declare variables of any intrinsic data type, can't
be used to declare instances of dependent objects, and can’t
be used with WithEvents.
type Optional. Data type of the variable; may be Byte, Boolean, Integer,
Long, Currency, Single, Double, Decimal (not currently supported),
Date, String (for variable-length strings), String * length (for fixed-length
strings), Object, Variant, a user-defined type, or an object type.
Use a separate As type clause for each variable you declare.
Remarks
Variables declared with Dim at the module level are
available to all procedures within the module. At the procedure level,
variables are available only within the procedure.
Use the Dim statement at module or procedure level
to declare the data type of a variable. For example, the following
statement declares a variable as an Integer.
Dim NumberOfEmployees As Integer
Also use a Dim statement to declare the object type
of a variable. The following declares a variable for a new instance
of a worksheet.
Dim X As New Worksheet
If the New keyword is not used when declaring an object
variable, the variable that refers to the object must be assigned an
existing object using the Set statement before it can be used. Until
it is assigned an object, the declared object variable has the special
value Nothing, which indicates that it doesn't refer to any particular
instance of an object.
You can also use the Dim statement with empty parentheses
to declare a dynamic array. After declaring a dynamic array, use the
ReDim statement within a procedure to define the number of dimensions
and elements in the array. If you try to redeclare a dimension for
an array variable whose size was explicitly specified in a Private,
Public, or Dim statement, an error occurs.
If you don't specify a data type or object type, and
there is no Deftype statement in the module, the variable is Variant
by default.
When variables are initialized, a numeric variable
is initialized to 0, a variable-length string is initialized to a zero-length
string (""), and a fixed-length string is filled with zeros.
Variant variables are initialized to Empty. Each element of a user-defined
type variable is initialized as if it were a separate variable.
Note When you use the Dim statement in a procedure,
you generally put the Dim statement at the beginning of the procedure.
Sub TEST() Statement
Declares the name, arguments, and code that form
the body of a Sub procedure.
Syntax
[Private | Public | Friend] [Static] Sub name [(arglist)]
[statements]
[Exit Sub]
[statements]
End Sub
Sub TEST() Statement
Digunakan untuk mendeklarasikan nama prosedur,
argumen dan perintah yang akan membentuk sebuah Sub prosedur. Semua
prosedur didalam Excel Macro dimulai dengan Sub statement dan diakhiri
dengan End Sub. Dalam contoh ini nama yang digunakan adalah TEST.
LTrim, RTrim, and Trim Functions
Returns a Variant (String) containing a copy of a specified string
without leading spaces (LTrim), trailing spaces (RTrim), or both
leading and trailing spaces (Trim).
Syntax
LTrim(string)
RTrim(string)
Trim(string)
The required string argument is any valid string expression. If string
contains Null, Null is returned.
Replace Function
Description
Returns a string in which a specified substring has been replaced with
another substring a specified number of times.
Syntax
Replace(expression, find, replace[, start[, count[, compare]]])
The Replace function syntax has these named arguments:
Part Description
expression Required. String expression containing substring to replace.
find Required. Substring being searched for.
replace Required. Replacement substring.
start Optional. Position within expression where substring search is
to begin. If omitted, 1 is assumed.
count Optional. Number of substring substitutions to perform. If omitted,
the default value is –1, which means make all possible substitutions.
compare Optional. Numeric value indicating the kind of comparison to
use when evaluating substrings. See Settings section for values.
Settings
The compare argument can have the following values:
Constant Value Description
vbUseCompareOption –1 Performs a comparison using the setting
of the Option Compare statement.
vbBinaryCompare 0 Performs a binary comparison.
vbTextCompare 1 Performs a textual comparison.
vbDatabaseCompare 2 Microsoft Access only. Performs a comparison based
on information in your database.
Return Values
Replace returns the following values:
If Replace returns
expression is zero-length Zero-length string ("")
expression is Null An error.
find is zero-length Copy of expression.
replace is zero-length Copy of expression with all occurences of find
removed.
start > Len(expression) Zero-length string.
count is 0 Copy of expression.
for
inquiry and help
click
to enlarge
click
to enlarge
click
to enlarge
