Sub ConvertValue()
    Dim strInput As String
    Dim intNumber As Integer
    strInput = InputBox("Enter a Number:")
    intNumber = CInt(strInput)
    .....................
End Sub

The above example takes a string variable, which the InputBox function returns as a string and converts it to an integer value and stores to the integer, variable.

VBA is intelligent enough to convert values without the need to apply conversion functions or explicitly declare variables. However, there are occasions when this rule doesn’t work and to handle unforeseen errors, users need to handle data conversion (as above).

In future releases of VBA, data type declarations will become more stringent in how users can work with variables and will therefore need to use such functions (as above) to handle cast and conversion issues correctly.

DATETIME Class

This class is a collection of date/time conversions and interrogations.

Some functions in this class:

Date – return/sets the system’s date

Now – returns/sets the system’s date and time

Day(Date) – returns the day element of the date

Month(Date) – returns the month element of the date

Year(Date) – returns the year element of the date

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

– returns the difference between two dates driven by the interval

DateSerial(Year, Month, Day) – returns a valid date from 3 separate values

DateValue(Date) – converts a string date into a date data type date

Weekday(Date, [firstdayofweek]) – Returns a string day of the week

'Converts a string date to date date type date
Sub DateExample1()
    Dim strDate As String
    strDate = "10 May 2010"
    MsgBox DateValue(strDate)
End Sub

'Works out the difference between two dates
'returns the number of months (interval)
Sub DateExample2()
    Dim dtmStartDate As Date
    dtmStartDate = #5/2/2010#
    MsgBox DateDiff("m", dtmStartDate, Date) & " Months"
End Sub

INFORMATION Class

This class is a collection of status functions to help evaluate conditions of variables and objects alike.

Some functions in the class:

IsArray(variant) – returns true or false

IsDate(expression) – returns true or false

IsError(expression) – returns true or false

IsEmpty(expression) – returns true or false

IsMissing(variant) – returns true or false

IsNull(expression) – returns true or false

IsNumeric(expression) – returns true or false

IsObject(expression) – returns true or false

An expression or variant is the variable being tested to see if it is True or False.

MATH Class

This class is a collection of mathematical functions that can be used to change variables with ease and without having to create your own functions.

Some functions in the class:

Abs(Number) – returns the absolute number (always a positive value)

Rnd([Number]) – returns a random value

Round(Number, [NumDigitsAfterDecimal]) – returns a rounded value

Sqr(Number) – returns a square value (x²)

'Generates a random value between 1 and 100.
Sub RandomNumber()
    Dim intNumber As Integer
    intNumber = Int((100 * Rnd) + 1)
    MsgBox intNumber
End Sub

STRING Class

This class is a collection of text (string) based functions that include conversion, extractions and concatenation.

Some functions in the class:

Asc(String) – returns the numeric ASCII value of the character string

Chr(CharCode) – returns the character string from the code supplied

Format(Expression, [Format], [FirstDayOfWeek], [FirstWeekOfYear])

- returns the format presentation of the expression

InStr([Start], [String1], [String2], [Compare]) – returns the numeric position of the first character found from left to right

InStrRev(StringCheck, StringMatch, [Start], [Compare]) – returns the numeric position of the first character found from right to left

LCase(String) – returns the string in lowercase

UCase(String) – returns the string in uppercase

Left(String, Length) – returns the remaining characters from the left of the length supplied

Right(String, Length) – returns the remaining characters from the right of the length supplied

Len(Expression) – returns a value of the length of characters supplied

Mid(String, Start, [Length]) – returns the portion of characters as determined by the start and end parameters supplied

Trim(String) – removes unwanted spaces from left and right of the string supplied and extra spaces in between multiple strings

Sub StringExample1()
    Dim strString As String
    strString = "Microsoft Excel VBA"
    'Returns 17 (17th character starting from first character)
    MsgBox InStr(1, strString, "V", vbTextCompare)
    'Returns 7 (7th character from left starting ‘at the sixth position)
    MsgBox InStr(6, strString, "o", vbTextCompare)
End Sub

Sub StrngExample2()
MsgBox Format(12.5 * 1.175, "£0.00")
End Sub

Which Library?

Some functions in VBA may appear to be duplicates to functions known in Excel.

Sub WhichLibrary()
    'Excel's Round function
    MsgBox Application.WorksheetFunction.Round(10.2356, 2)
    'VBA's Round function
    MsgBox Round(10.2356, 2)
End Sub

The above example uses two functions that appear in both VBA and Excel.

By default, VBA is the library used when calling such a function.

To use the Round() function from Excel’s library, users need to call the qualification (object hierarchy) first.

The MsgBox statement and function and InputBox function are members of the Interaction class in the VBA library.

Next Topic: Creating Add-Ins

Want to teach yourself Access? Free online guide at About Access Databases

Note: The VBA library is the top most reference followed by the Excel library and both can not be moved or disabled. Therefore, calling a function which exists in both libraries will always use the VBA reference (as implicit).

VBA Keywords: MsgBox, InpuBox, Application, WorksheetFunction and VBA Class (all functions).