Visual Basic Functions in Microsoft Access

Welcome to our Visual Basic Functions for Microsoft Access home page.  This section of our website contains all of the predefined functions available to you in a Microsoft Access database. 

Web site navigation for our Visual Basic function discussion is listed to the right.  Of course, Access gives you the ability to create any VB function you can think of.  But it is good to know what VB functions already exist so you don't re-invent the wheel.

We will give the syntax, discussion and examples of each of the functions listed to the left as we go down the page. We didn't create these functions so most of the syntax and usage information is taken from the Microsoft Access Visual Basic help system. We have added our own notes and discussion to help clarify the functions where we can. Let's get started!

Visual Basic Abs Function

The Visual Basic Abs function simply removes the sign from a number returning a positive value.  This is useful for calculating the absolute difference between values and adding them differences up.  You would first use the Abs function before adding the values.

This example uses the Abs function to compute the absolute value of a number.

Dim TheNumber
TheNumber = Abs(19.2)         ' Returns the absolute value '19.2'.
TheNumber = Abs(-19.2)        ' Returns the absolute value '19.2'.

Visual Basic Array Function for Microsoft Access

The VB Array function establishes an array (list) of values.  To create a constant array you define the array name as a variant.  See below;

Dim myArray as Variant

myArray = Array(5,10,15,21)

You can then refer to an array element by its integer position starting at 1:

Msgbox myArray(2)

The Msgbox above will display the number 10

Visual Basic Asc Function

The VBA Asc function returns the ASCII character code for the 1st letter in the Asc argument:

TheNumber = Asc("A")         ' Returns 65.
TheNumber = Asc("a")         ' Returns 97.
TheNumber = Asc("Avalanche")     ' Returns 65.

Here is a complete ASCII character chart. Visual Basic Atn Function

The arctangent is used to calculate the angles of a right triangle. It works opposite of the tangent function. While tangent will find the ratio of the two sides of a right triangle when given an angle, arctangent can find the angle given the ratio.  Go here to see more information on arctangent.

This example uses the Atn function to calculate the value of pi.

Dim pi
pi = 4 * Atn(1)         ' Calculates the numeric result for pi.

Visual Basic CallByName Function

The CallByName() function is used to manipulate objects by either executing a method or setting the value of a property.

Syntax

CallByName(object, procname, calltype,[args()])

The CallByName function syntax has these named arguments:

Argument List Definition

object - Required; Variant (Object). The name of the object on which the function will be executed.

procname - Required; Variant (String). A string expression containing the name of a property or method of the object.

calltype - Required; Constant. A constant of type vbCallType representing the type of procedure being called.
args() Optional: Variant (Array).

Remarks

The CallByName function is used to set or get a property, or initialize a method at runtime using a string name.

In the example below CallByName to set the MousePointer property of a text box. Then we get the value of the MousePointer property.  The last line initiates the Move method to move the text box:

CallByName Text1, "MousePointer", vbLet, vbCrosshair
Result = CallByName (Text1, "MousePointer", vbGet)
CallByName Text1, "Move", vbMethod, 100, 100

Visual Basic Choose Function

Choose returns a value from the list of choices based on the value of index. If index is 1, Choose returns the first choice in the list; if index is 2, it returns the second choice, and so on.

We have extensive examples and downloads for the Access VBA Choose function.

Visual Basic Chr Function

The Visual Basic Chr function returns a string containing the character associated with the specified character code.

Example:

Dim MyChar
MyChar = Chr(65)         ' Returns A.
MyChar = Chr(97)         ' Returns a.

More examples of the Chr function: CreateQueryDef in VBA

Visual Basic Command Function

The Visual Basic Command function returns the argument part of the command line used to invoke MS Visual Basic or an program developed with Visual Basic. The Visual Basic Command function is not available in Microsoft Office applications.

When Visual Basic is launched from the command line, any portion of the command line that follows ' /cmd ' is forwarded to the program as an argument. In the example below, cmdlineargs is the argument information returned by the Command function.

VB /cmd cmdlineargs

For applications created with Visual Basic and compiled, Command returns any arguments that appear after the name of the application on the command line. For example:

MyApp cmdlineargs

Visual Basic Conversion Functions
 

Function	Return Type	Range for expression argument
CBool	Boolean	Any valid string or numeric expression.
CByte	Byte	0 to 255.
CCur	Currency	-922,337,203,685,477.5808 to 922,337,203,685,477.5807.
CDate	Date	Any valid date expression.
CDbl	Double	-1.79769313486231E308 to -4.94065645841247E-324 for negative values; 4.94065645841247E-324 to 1.79769313486232E308 for positive values.
CDec	Decimal	+/-79,228,162,514,264,337,593,543,950,335 for zero-scaled numbers, that is, numbers with no decimal places. For numbers with 28 decimal places, the range is +/-7.9228162514264337593543950335. The smallest possible non-zero number is 0.0000000000000000000000000001.
CInt	Integer	-32,768 to 32,767; fractions are rounded.
CLng	Long	-2,147,483,648 to 2,147,483,647; fractions are rounded.
CSng	Single	-3.402823E38 to -1.401298E-45 for negative values; 1.401298E-45 to 3.402823E38 for positive values.
CStr	String	Returns for CStr depend on the expression argument.
CVar	Variant	Same range as Double for numerics. Same range as String for non-numerics.