MicroBeaut Visual Basic for Applications (VBA) Snippets
Install MicroBeaut VBA Snippets from the Marketplace
Provides VBA Snippets for:
VBA Constants
Statements
Functions
Conversion functions
Math functions
The following constants
can be used anywhere in your code in place of the actual values.
Calendar constants
Prefix
VbCalendar
The VbCalendar
argument has the following values.
Constant |
Value |
Description |
vbCalGreg |
0 |
Indicates that the Gregorian calendar is used. |
vbCalHijri |
1 |
Indicates that the Hijri calendar is used. |
Color constants
Prefix
ColorConstants
The ColorConstants
argument has the following values.
Constant |
Value |
Description |
vbBlack |
0x0 |
Black |
vbRed |
0xFF |
Red |
vbGreen |
0xFF00 |
Green |
vbYellow |
0xFFFF |
Yellow |
vbBlue |
0xFF0000 |
Blue |
vbMagenta |
0xFF00FF |
Magenta |
vbCyan |
0xFFFF00 |
Cyan |
vbWhite |
0xFFFFFF |
White |
Comparison constants
Prefix
VbCompareMethod
The VbCompareMethod
argument has the following values.
Constant |
Value |
Description |
vbUseCompareOption |
-1 |
Performs a comparison by using the setting of the Option Compare statement. |
vbBinaryCompare |
0 |
Performs a binary comparison. |
vbTextCompare |
1 |
Performs a textual comparison. |
vbDatabaseCompare |
2 |
For Microsoft Access (Windows only), performs a comparison based on information contained in your database. |
Date constants
The following constants
can be used anywhere in your code in place of the actual values.
Day of Week
Prefix
VbDayOfWeek
The VbDayOfWeek
argument has the following values.
Constant |
Value |
Description |
VbUseSystemDayOfWeek |
0 |
Use the day of the week specified in your system settings for the first day of the week. |
vbSunday |
1 |
Sunday (default) |
vbMonday |
2 |
Monday |
vbTuesday |
3 |
Tuesday |
vbWednesday |
4 |
Wednesday |
vbThursday |
5 |
Thursday |
vbFriday |
6 |
Friday |
vbSaturday |
7 |
Saturday |
First Week Of Year
Prefix
VbFirstWeekOfYear
The VbFirstWeekOfYear
argument has the following values.
Constant |
Value |
Description |
vbUseSystem |
0 |
Use NLS API setting. |
VbFirstJan1 |
1 |
Start with week in which January 1 occurs (default). |
vbFirstFourDays |
2 |
Start with the first week that has at least four days in the new year. |
vbFirstFullWeek |
3 |
Start with the first full week of the year. |
Dir, GetAttr, and SetAttr constants
Prefix
VbFileAttribute
The VbFileAttribute
argument has the following values.
Constant |
Value |
Description |
vbNormal |
0 |
Normal (default for Dir and SetAttr) |
vbReadOnly |
1 |
Read-only |
vbHidden |
2 |
Hidden |
vbSystem |
4 |
System file |
vbVolume |
8 |
Volume label |
vbDirectory |
16 |
Directory or folder |
vbArchive |
32 |
File has changed since last backup |
vbAlias |
64 |
On the Macintosh, identifier is an alias |
Only VbNormal, vbReadOnly, vbHidden, and vbAlias are available on the Macintosh.
AppActivate
Activates an application window.
Prefix
AppActivate
Syntax
AppActivate title, [ wait ]
Beep
Sounds a tone through the computer's speaker.
Prefix
Beep
Syntax
Beep
Call
Transfers control to a Sub procedure, Function procedure, or dynamic-link library (DLL) procedure.
Prefix
Call
Syntax
[ Call ] name [ argumentlist ]
ChDir
Changes the current directory or folder.
Prefix
ChDir
Syntax
ChDir path
ChDrive
Changes the current drive.
Prefix
ChDrive
Syntax
ChDrive drive
Close
Concludes input/output (I/O) to a file opened by using the Open statement.
Prefix
Close
Syntax
Close [ filenumberlist ]
Const
Declares constants
for use in place of literal values.
Prefix
Const
Syntax
[ Public | Private ] Const constname [ As type ] = expression
Date
Sets the current system date #mmmm d, yyyy#
.
Prefix
Date
Syntax
Date = date
DeleteSetting
Deletes a section or key setting from an application's entry in the Windows registry
or (on the Macintosh) information in the application's initialization file.
Prefix
DeleteSetting
Syntax
DeleteSetting appname, section, key
Dim
Declares variables
and allocates storage space.
Prefix
Dim
Dim WithEvents
Syntax
Dim [ WithEvents ] varname [ ( [ subscripts ] ) ] [ As [ New ] type ]
Do...Loop
Repeats a block of statements
while a condition is True or until a condition becomes True.
Prefix
Do Until Loop
Do While Loop
Syntax
Do [{ While | Until } condition ]
[ statements ]
[ Exit Do ]
[ statements ]
Loop
Or,
Prefix
Do Loop Until
Do Loop While
Syntax
Do
[ statements ]
[ Exit Do ]
[ statements ]
Loop [{ While | Until } condition ]
End
Ends a procedure or block.
Prefix
End
End Function
End If
End Property
End Select
End Sub
End Type
End With
Syntax
End
End Function
End If
End Property
End Select
End Sub
End Type
End With
Enum
Declares a type for an enumeration.
Prefix
Enum
Syntax
[ Public | Private ] Enum name
membername [= constantexpression ]
membername [= constantexpression ] . . .
End Enum
Erase
Reinitializes the elements of fixed-size arrays
and releases dynamic-array storage space.
Prefix
Erase
Syntax
Erase arraylist
Error
Simulates the occurrence of an error.
Prefix
Error
Syntax
Error errornumber
Event
Declares a user-defined event.
Prefix
Event
Syntax
[ Public ] Event procedurename [ (arglist) ]
Exit
Exits a block of Do…Loop, For…Next, Function, Sub, or Property code.
Prefix
Exit Do
Exit Fo
Exit Fu
Exit Pr
Exit Su
Syntax
Exit Do
Exit For
Exit Function
Exit Property
Exit Sub
FileCopy
Copies a file.
Prefix
FileCopy
Syntax
FileCopy source, destination
For Each...Next
Repeats a group of statements
for each element in an arrays
or collection.
Prefix
For Each
Syntax
For Each element In group
[ statements ]
[ Exit For ]
[ statements ]
Next [ element ]
For...Next
Repeats a group of statements
a specified number of times.
Prefix
For Next
Syntax
For counter = start To end [ Step step ]
[ statements ]
[ Exit For ]
[ statements ]
Next [ counter ]
Function
Declares the name, arguments, and code that form the body of a Function procedure
.
Prefix
Function
Function Static
Syntax
[Public | Private | Friend] [ Static ] Function name [ ( arglist ) ] [ As type ]
[ statements ]
[ name = expression ]
[ Exit Function ]
[ statements ]
[ name = expression ]
End Function
Get
Reads data from an open disk file into a variable.
Prefix
Get
Syntax
Get [ # ] filenumber, [ recnumber ], varname
GoSub...Return
Branches to and returns from a subroutine within a procedure.
Prefix
GoSub
Syntax
GoSub line
... line
line ...
Return
GoTo
Branches unconditionally to a specified line within a procedure.
Prefix
GoTo line
Syntax
GoTo line
If...Then...Else
Conditionally executes a group of statements
, depending on the value of an expression.
Prefix
If
Syntax
If condition Then [ statements ] [ Else elsestatements ]
Or,
If condition Then
[ statements ]
[ ElseIf condition-n Then
[ elseifstatements ]]
[ Else
[ elsestatements ]]
End If
Load
Loads an object but doesn't show it.
Prefix
Load
Syntax
Load object
Reads data from an open sequential file and assigns the data to variables.
Prefix
Input
Syntax
Input #filenumber, varlist
Kill
Deletes files from a disk.
Prefix
Kill
Syntax
Kill pathname
Let
Assigns the value of an expression to a variable or property.
Prefix
Let
Syntax
[ Let ] varname = expression
Reads a single line from an open sequential file and assigns it to a String variable.
Prefix
Line Input
Syntax
Line Input #
Lock, Unlock
Controls access by other processes to all or part of a file opened by using the Open statement.
Prefix
Lock
Unlock
Syntax
Lock [ # ] filenumber, [ recordrange ]
Unlock [ # ] filenumber, [ recordrange ]
LSet
Left aligns a string within a string variable, or copies a variable of one user-defined type
to another variable of a different user-defined type.
Prefix
LSet
Syntax
LSet stringvar = string
LSet varname1 = varname2
Mid
Replaces a specified number of characters in a Variant (String) variable with characters from another string.
Prefix
Mid
Syntax
Mid(stringvar, start, [ length ] ) = string
MkDir
Creates a new directory or folder.
Prefix
MkDir
Syntax
MkDir path
Name
Renames a disk file, directory, or folder.
Prefix
Name
Syntax
Name oldpathname As newpathname
On Error
Enables an error-handling routine and specifies the location of the routine within a procedure; can also be used to disable an error-handling routine.
Prefix
On Error Go
On Error Re
Syntax
On Error GoTo 0
On Error GoTo line
On Error Resume Next
On...GoSub, On...GoTo
Branch to one of several specified lines, depending on the value of an expression.
Prefix
On GoSub
On GoTo
Syntax
On expression GoSub destinationlist
On expression GoTo destinationlist
Open
Enables input/output (I/O) to a file.
Prefix
Open
Syntax
Open pathname For mode [ Access access ] [ lock ] As [ # ] filenumber [ Len = reclength ]
Option Base
Used at the module level to declare the default lower bound for array subscripts.
Prefix
Option Ba
Syntax
Option Base { 0 | 1 }
Option Compare
Used at the module level to declare the default comparison method to use when string data is compared.
Prefix
Option Co
Syntax
Option Compare { Binary | Text | Database }
Option Explicit
Used at the module level to force explicit declaration of all variables in that module.
Prefix
Option Ex
Syntax
Option Explicit
Option Private
When used in host applications that allow references across multiple projects, Option Private Module prevents a module's contents from being referenced outside its project. In host applications that don't permit such references, for example, standalone versions of Visual Basic, Option Private has no effect.
Prefix
Option Pr
Syntax
Option Private Module
Print
Writes display-formatted data to a sequential file.
Prefix
Print #
Syntax
Print #filenumber, [ outputlist ]
Private
Used at the module level to declare private variables and allocate storage space.
Prefix
Private
Private With
Syntax
Private [ WithEvents ] varname [ ( [ subscripts ] ) ] [ As [ New ] type ]
Property Get
Declares the name, arguments, and code that form the body of a Property procedure, which gets the value of a property.
Prefix
Property Get
Property Get Static
Syntax
[ Public | Private | Friend ] [ Static ] Property Get name [ (arglist) ] [ As type ]
[ statements ]
[ name = expression ]
[ Exit Property ]
[ statements ]
[ name = expression ]
End Property
Property Let
Declares the name, arguments, and code that form the body of a Property procedure, which assigns a value to a property.
Prefix
Property Let
Property Let Static
Syntax
[ Public | Private | Friend ] [ Static ] Property Let name ( [ arglist ], value )
[ statements ]
[ Exit Property ]
[ statements ]
End Property
Property Set
Declares the name, arguments, and code that form the body of a Property procedure, which sets a reference to an object.
Prefix
Property Set
Property Set Static
Syntax
[ Public | Private | Friend ] [ Static ] Property Set name ( [ arglist ], reference )
[ statements ]
[ Exit Property ]
[ statements ]
End Property
Public
Used at the module level to declare public variables and allocate storage space.
Prefix
Public
Public With
Syntax
Public [ WithEvents ] varname [ ( [ subscripts ] ) ] [ As [ New ] type ]
Put
Writes data from a variable to a disk file.
Prefix
Put
Syntax
Put [ # ] filenumber, [ recnumber ], varname
RaiseEvent
Fires an event declared at the module level within a class, form, or document.
Prefix
RaiseEvent
Syntax
RaiseEvent eventname [ ( argumentlist ) ]
Randomize
Initializes the random-number generator.
Prefix
Randomize
Syntax
Randomize [ number ]
ReDim
Used at the procedure level to reallocate storage space for dynamic array variables.
Prefix
ReDim
Syntax
ReDim [ Preserve ] varname ( subscripts )
Rem
Used to include explanatory remarks in a program.
Prefix
Rem
Syntax
Rem comment
Reset
Closes all disk files opened by using the Open statement.
Prefix
Reset
Syntax
Reset
Resume
Removes an existing directory or folder.
Prefix
Resume
Resume Ne
Resume Li
Syntax
Resume [ 0 ]
Resume Next
Resume line
RmDir
Removes an existing directory or folder.
Prefix
RmDir
Syntax
RmDir path
RSet
Right aligns a string within a string variable, or copies a variable of one user-defined type to another variable of a different user-defined type.
Prefix
RSet
Syntax
RSet stringvar = string
RSet varname1 = varname2
SaveSetting
Saves or creates an application entry in the application's entry in the Windows registry or (on the Macintosh) information in the application's initialization file.
Prefix
SaveSetting
Syntax
SaveSetting appname, section, key, setting
The root of these registry settings is: Computer\HKEY_CURRENT_USER\Software\VB and VBA Program Settings
.
Seek
Sets the position for the next read/write operation within a file opened by using the Open statement.
Prefix
Seek
Syntax
Seek [ # ] filenumber, position
Select Case
Executes one of several groups of statements, depending on the value of an expression.
Prefix
Select Case
Syntax
Select Case testexpression
[ Case expressionlist-n
[ statements-n ]]
[ Case Else
[ elsestatements ]]
End Select
SendKeys
Sends one or more keystrokes to the active window as if typed at the keyboard.
Prefix
SendKeys
Syntax
SendKeys [ # ] filenumber, position
Set
Assigns an object reference to a variable or property.
Prefix
Set
Syntax
Set objectvar = {[ New ] objectexpression | Nothing }
SetAttr
Sets attribute information for a file.
Prefix
SetAttr
Syntax
SetAttr pathname, attributes
Static
Used at the procedure level to declare variables and allocate storage space. Variables declared with the Static statement retain their values as long as the code is running.
Prefix
Static
Syntax
Static varname [ ( [ subscripts ] ) ] [ As [ New ] type ]
Stop
Suspends execution.
Prefix
Stop
Syntax
Stop
Sub
Declares the name, arguments, and code that form the body of a Sub procedure.
Prefix
Sub
Sub Static
Syntax
[ Private | Public | Friend ] [ Static ] Sub name [ ( arglist ) ]
[ statements ]
[ Exit Sub ]
[ statements ]
End Sub
Time
Sets the system time. #hh:mm:ss AM/PM#
Prefix
Time
Syntax
Time = time
Type
Used at the module level to define a user-defined data type containing one or more elements.
Prefix
Type
Syntax
[ Private | Public ] Type varname
elementname [ ( [ subscripts ] ) ] As type
[ elementname [ ( [ subscripts ] ) ] As type ] . . .
End Type
Unload
Removes an object from memory.
Prefix
Unload
Syntax
Unload object
While...Wend
Removes an object from memory.
Prefix
While Wend
Syntax
While condition
[ statements ]
Wend
Width
Assigns an output line width to a file opened by using the Open statement.
Prefix
Width
Syntax
Width #filenumber, width
With
Executes a series of statements on a single object or a user-defined type.
Prefix
With
Syntax
With object
[ statements ]
End With
Write
Writes data to a sequential file.
Prefix
Write
Syntax
Write #filenumber, [ outputlist ]
Asc
Returns an Integer representing the character code corresponding to the first letter in a string.
Prefix
Asc
Syntax
Asc(string)
Chr
Returns a String containing the character associated with the specified character code.
Prefix
Chr
Syntax
Chr(charcode)
Returns a Variant (String) containing an expression formatted according to instructions contained in a format expression.
Prefix
Format
Syntax
Format(Expression, [ Format ], [ FirstDayOfWeek ], [ FirstWeekOfYear ])
Hex
Returns a Variant (String) containing an expression formatted according to instructions contained in a format expression.
Prefix
Hex
Syntax
Hex(number)
Oct
Returns a Variant (String) representing the octal value of a number.
Prefix
Oct
Syntax
Oct(number)
Str
Returns a Variant (String) representation of a number.
Prefix
Str
Syntax
Str(number)
Val
Returns the numbers contained in a string as a numeric value of appropriate type.
Prefix
Val
Syntax
Val(string)
Abs
Returns a value of the same type that is passed to it specifying the absolute value of a number.
Prefix
Abs
Syntax
Abs(number)
Atn
Returns a Double specifying the arctangent of a number.
Prefix
Atn
Syntax
Atn(number)
Cos
Returns a Double specifying the cosine of an angle.
Prefix
Cos
Syntax
Cos(number)
Exp
Returns a Double specifying e (the base of natural logarithms) raised to a power.
Prefix
Exp
Syntax
Exp(number)
Int, Fix
Returns a Double specifying e (the base of natural logarithms) raised to a power.
Prefix
Int
Fix
Syntax
Int(number)
Fix(number)
Log
Returns a Double specifying the natural logarithm of a number.
Prefix
Log
Syntax
Log(number)
Rnd
Returns a Single containing a pseudo-random number.
Prefix
Rnd
Syntax
Rnd [ (Number) ]
Sgn
Returns a Variant (Integer) indicating the sign of a number.
Prefix
Sgn
Syntax
Sgn(number)
Sin
Returns a Double specifying the cosine of an angle.
Prefix
Sin
Syntax
Sin(number)
Sqr
Returns a Double specifying the sine of an angle.
Prefix
Sqr
Syntax
Sqr(number)
Tan
Returns a Double specifying the tangent of an angle.
Prefix
Tan
Syntax
Tan (number)
Reference
Release Notes
[0.0.1]
- Initial release of MicroBeaut VBA Snippets
[0.0.2]
- Changed package description
- Revised statements
- Added new statements
[0.0.3]
- Changed the prefix for,
Dim WithEvent
On Error *
Private WithEvent
Public WithEvent
[0.0.4]
- Removed statements
If...Then..Exit
inside,
Do...Loop
For Each...Next
For...Next
Function
If...Then...Else
- Updated descriptions
- Added new statements
- Added Conversion functions
- Added Math functions
[0.0.5]
- Fixed package not updated
[0.0.6]
License
MIT License
Copyright © 2023 MicroBeaut