VBA Functions for Access

VBA Cos Function

The Visual Basic Cos function takes an angle as an argument and returns the ratio of 2 sides of a right triangle (the Cosine). The ratio is the length of the side adjacent to the angle divided by the length of the hypotenuse.

The result returned are in the range -1.0 to 1.0 Example:

Dim TheAngle, TheSecant
TheAngle = 1.3                               ' Define angle in radians.
TheSecant = 1 / Cos(TheAngle)        ' Calculate secant.

The result of the Cos calculation is 0.26749....

VBA CreateObject Function

The Access Visual Basic CreateObject function creates and returns a reference value to an ActiveX object.  There are two arguments to the function:  Class & ServerName - class is a required argument that specifies the application name and class of the object to create.  The servername argument is optional and specifies the name of the network server where the object will be created. If servername is an empty string (""), the local machine is used.

Example:

        ' Declare an object variable to hold the object
        ' reference. Dim as Object causes late binding ( see more info on late binding ).
Dim ExcelSheet As Object
Set ExcelSheet = CreateObject("Excel.Sheet")

VBA CurDir Function

The Visual Basic CurDir function returns the current default path.

Example:

Dim ThePath
ThePath = CurDir               ' Returns "C:\WINDOWS\SYSTEM".
ThePath = CurDir("C")        ' Returns "C:\WINDOWS\SYSTEM".
ThePath = CurDir("D")        ' Returns "D:\EXCEL".

VBA CVErr Function
The Access Visual Basic CVErr function gives you the ability to create your own custom error codes.  If you create a visual basic procedure or function you can use the CVErr feature to return an error code value to the calling procedure.

Example:

Function Figure_Age(BDate)
if IsDate(Bdate) then
      ' do the age calculations

Else
      Figure_Age=CVErr(-999)
End if
End Function

VBA Date Function

The VB Date function returns a variant variable containing the current computer system date value.

Example:

Msgbox "Today's Date is " & Date

Result:  Today's Date is 12/12/2006

VBA DateAdd Function

The Visual Basic DateAdd function allows you to add a time interval to a specified date.

Syntax:  DateAdd(interval, number, date)

The VB DateAdd function syntax has these named arguments:
 
interval  - Required argument. String expression that is the interval of time you want to add.

number - Required. Numeric expression that is the number of intervals you want to add. It can be positive (to get dates in the future) or negative (to get dates in the past).

date - Required. Variant (Date) or literal representing date to which the interval is added.

Here are the VB interval definitions:

Interval                                   Interval Description

Example usage:

MsgBox DateAdd("d", 13, "12/12/2006")

Above VB dateadd examples displays ' 12/25/2006 '

VBA DateDiff Function

The VB DateDiff function returns a variant variable result of subtracting two dates.  The unit of measure is defined in the interval argument. 

Syntax

DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]])

Arguments:

interval - Required. String expression that is the interval of time you use to calculate the difference between date1 and date2. See a list of valid intervals in the DateAdd function above.

date1, date2 - Required; Variant (Date). Two dates you want to use in the calculation.

firstdayofweek - Optional. A constant that specifies the first day of the week. If not specified, Sunday is assumed.

firstweekofyear - Optional. A constant that specifies the first week of the year. If not specified, the first week is assumed to be the week in which January 1 occurs.

Example:

MsgBox DateDiff("yyyy", "01/01/2005", "11/11/2006")

Above displays '1'.

VBA DatePart Function

The VBA DatePart function give you the ability to extract part of a date constant of variable from a regular calendar date.  The part you select is called the interval and the intervals are the same as DateAdd function above.

See detailed information on the DatePart function in our Access SQL query examples - date/time query page.

VBA DateSerial Function

The Visual Basic DateSerial function returns the date (variant) for the specified year, month, and day.

Syntax:

DateSerial(year, month, day)

Where:

(All fields are required)

Year - Integer. Number between 100 and 9999, inclusive, or a numeric expression.

Month - Integer.  Two digit month between 1 and 12.

Day - Integer.  Two digit day between 1 and 31.

VBA DateValue Function

The VB DateValue Function converts a string date expression into a date value.

The syntax is DateValue(Date) where date is any standard representation of a date.

Below we use the DateValue function to convert a string to a date value. You can also use date constants or string literals to assign a date to a Variant or Date variable, for example, MyDate = #12/13/06#.  Not sure why this function is ever used.

Dim TheDate
TheDate = DateValue("December 13, 2006")         ' Converts to a date.