Home

Introduction to Visual Basic Built-In Functions

 

Overview of Built-In Procedure

 

Introduction 

A procedure is referred to as "built-in" if it shipped with its programming language. To make your job a little easier, the Visual Basic language is equipped with many procedures that you can use right away in your program. Based on this, before creating your own procedure, first check whether the functionality you are looking for is already implementing in one of the available procedures because those that ship with the Visual Basic language are highly reliable and should be preferred.

Before using a built-in procedure, you must of course be familiar with it. This comes either by consulting the documentation or by experience. This means that you must know its name, its argument(s), its return value, and its role. The Visual Basic programming language provides one of the richest libraries you will ever see. In fact, it is the richest of the .NET-based languages, giving you access to functions that are not directly available to other languages such as C# or C++/CLI. Because there so many of those functions, we will review only the most usually used. Eventually, when necessary, in other lessons, we may introduce new ones.

 

Conversion Functions

You may recall that when studying data types, we saw that each had a corresponding function used to convert a string value or an expression to that type. As a reminder, the general syntax of the conversion functions is:

ReturnType = FunctionName(Expression)

The Expression could be of any kind. For example, it could be a string or expression that would produce a value such as the result of a calculation. The conversion function would take such a value, string, or expression and attempt to convert it. If the conversion is successful, the function would return a new value that is of the type specified by the ReturnType in our syntax.

The conversion functions are as follows:

Function  
Name Return Type Description
CBool Boolean Converts an expression into a Boolean value
CByte Byte Converts an expression into Byte number
CDbl Double Converts an expression into a floating-point number with double precision
CDec Decimal Converts an expression into a decimal number
CInt Integer Converts an expression into an integer (natural) number
CLng Long Converts an expression into a long integer (a large natural) number
CObj Object Converts an expression into an Object type
CSByte SByte Converts an expression into a signed byte
CShort Short Converts an expression into a short integer
CSng Single Converts an expression into a floating-point number with single precision
CUInt UInt Converts an expression into an unsigned integer
CULng ULong Converts an expression into an unsigned long integer
CUShort UShort Converts an expression into an unsigned short integer

These functions allow you to convert a known value to a another type. Besides these functions, the Visual Basic language provides a function named CType. Its syntax is:

CType(expression, typename)

As you can see, the CType() function takes two arguments. The first argument is the expression or the value that you want to convert. An example could be name of a variable or a calculation:

CType(250.48 * 14.05, ...)

The second argument is the type of value you want to convert the first argument to. From what have learned so far, this second argument can be one of the data types we reviewed in Lesson 3. Here is an example:

CType(250.48 * 14.05, Single)

If you choose one of the Visual Basic language's data types, the expression produced by the first argument must be able to produce a value that is conform to the type of the second argument:

  • The conversion from the first argument to the type of the second argument must be possible: the value produced by the first must be convertible to the second arguments. For example, if the first argument is a calculation, the second argument must be a number-based data type. In the same way, you cannot convert a date to a number-based type
  • If the first argument is a number or the result of a calculation, its resulting value must be lower than or up to the range of values of the second argument. Here is an example:
     
    Public Module Exercise
    
        Public Function Main() As Integer
    
            MsgBox(CType(250.48 * 14.05, Single))
    
            Return 0
        End Function
    
    End Module

    This would produce:

    Conversion

  • If the first argument is a number or the result of a calculation that produces an integer or a floating-point number, its resulting value must be convertible to an integer or a floating point number up to the range of values of the second argument. Here is an example:
     
    Public Module Exercise
    
        Public Function Main() As Integer
    
            MsgBox(CType(7942.225 * 202.46, UInteger))
    
            Return 0
        End Function
    
    End Module

    This would produce:

    Conversion

  • If the first argument is a number or the result of a calculation that produces an integer or a floating-point number, the second argument is a number-based data type but whose range cannot hold the resulting value of the first argument, the conversion would not be allowed (the conversion will fail):
     

    Conversion

After the CType() function has performed its conversion, it returns a value that is the same category as the second argument. For example, you can call a CType() function that converts an expression to a long integer. Here is an example:

Public Module Exercise

    Public Function Main() As Integer
        Dim Number As Long

        Number = CType(7942.225 * 202.46, Long)

        Return 0
    End Function

End Module

The function can also return a different type, as long as its type can hold the value produced by the expression. Here are two examples:

Public Module Exercise

    Public Function Main() As Integer
        Dim Number As UInteger

        Number = CType(7942.225 * 202.46, Long)

        Return 0
    End Function

End Module

Or

Public Module Exercise

    Public Function Main() As Integer
        Dim Number As Single

        Number = CType(7942.225 * 202.46, Long)

        Return 0
    End Function

End Module

If you try storing the returned value into a variable that cannot hold it, you would receive an error:

Conversion

 

The Memory Used by a Data Type

In Lesson 3, we saw that different data types are used to store different values. To do that, each data type requires a different amount of space in the computer memory. To know the amount of space that a data type or a variable needs, you can call the Len() function. Its syntax is:

Public Shared Function Len( _
   ByVal Expression As { Boolean | Byte | SByte | Char | Double |
   Integer | UInteger | Long | ULong | Object | Short | UShort |
   Single | String | DateTime | Decimal } _
) As Integer

To call this function, you can declare a variable with a data type of your choice and optionally initialize with the appropriate value, then pass that variable to the function. Here is an example:

Public Module Exercise

    Public Function Main() As Integer
        Dim Value As Integer

        Value = 774554

        MsgBox(Value & " needs " & Len(Value) & " bytes to be stored in memory.")
        Return 0
    End Function

End Module

This would produce:

Length

Arithmetic Functions

 

Getting the Integral Part of a Number

If you have a decimal number but are interested only in the integral part, to assist you with retrieving that part, the Visual Basic language provides the Int() and the Fix() functions. Their syntaxes are:

Public Shared Function Int( _
    ByVal Number As { Double | Integer | Long | 
		      Object | Short | Single | Decimal }) _
    As { Double | Integer | Long | Object | Short | Single | Decimal }
Public Shared Function Fix( _
    ByVal Number As { Double | Integer | Long | 
		      Object | Short | Single | Decimal }) _
    As { Double | Integer | Long | Object | Short | Single | Decimal }

Each function must take one argument. The value of the argument must be number-based. This means it can be an integer or a floating-point number. If the value of the argument is integer-based, the function returns the (whole) number. Here is an example

Public Module Exercise

    Public Function Main() As Integer
        Dim Number As Integer

        Number = 286345
        MsgBox(Int(Number))

        Return 0
    End Function

End Module

This would produce:

Int

If the value of the argument is a decimal number, the function returns only the integral part. Here is an example

Public Module Exercise

    Public Function Main() As Integer
        Dim Number As UInteger

        Number = 7942.225 * 202.46
        MsgBox(Int(Number))

        Return 0
    End Function

End Module

This would produce:

Int

 

This function always returns the integral part only, even if you ask it to return a floating-point-based value. Here is an example:

Public Module Exercise

    Public Function Main() As Integer
        Dim Number As Single

        Number = 286345.9924
        MsgBox(Int(Number))

        Return 0
    End Function

End Module

This would produce:

Int

 

Random Functions

A random number is a value that is not known in advanced until it is generated by the compiler. To assist you with getting a random number, the Visual Basic language provides a function named Rnd. Its syntax is:

Public Shared Function Rnd[(Number)] As Single

This function takes an optional argument. If the argument is not passed, the compiler would simply generate a positive decimal number between 0 and 1. Here is an example:

Public Module Exercise

    Public Function Main() As Integer
        
        MsgBox("Random Number: " & Rnd())

        Return 0
    End Function

End Module

This would produce:

Random Number

You may wonder how the compiler generates a random number. Without going into all the details, in most cases, a compiler refers to the system clock (the clock of the computer on which the application is). It uses a certain algorithm to get that number.

If you call the function like we did above, every time you execute the application, you are likely to get the same result. Depending on how you want to use the number, in some cases, you may want to get a different number every time. To support this, random arithmetic supports what is referred to as a seed. If you do not use a seed, the compiler would keep the same number it generated from the system clock the first time it was asked to produce a random number. Seeding allows the compiler to reset this mechanism, which would result in a new random number.

To assist you with seeding, the Visual Basic language provides a function named Randomize. Its syntax is:

Public Shared Sub Randomize ([ Number ])

This function takes one optional argument. If you can this function without the argument, the compiler would refer to the system clock to generate the new random number. Of course, to get the number, you must call this function before calling Rnd(). Here is an example:

Public Module Exercise

    Public Function Main() As Integer
        Randomize()

        MsgBox("Random Number: " & Rnd())

        Return 0
    End Function

End Module

This time, every time the Rnd() function is called, the compiler generates a new number. Instead of letting the compiler refer to the system clock, you can provide your own seed value. To do this, pass a number to the Randomize() function.

We mentioned that the Rnd() function generates a number between 0 and 1. Of course, in some cases you will want the number to be in a higher range, such as between 0 and 100 or between 0 and 100000. All you have to do is to multiply the result to a number of your choice. Here is an example:

Public Module Exercise

    Public Function Main() As Integer
        Randomize()

        MsgBox("Random Number: " & CStr(100 * Rnd()))

        Return 0
    End Function

End Module

This would produce:

Random Number

Also notice that the result is a decimal number. If you interested in only the integral part of the number, you can call the Int() function.

Besides Visual Basic's own combination of the Rnd() and the Randomize() functions, the .NET Framework supports random numbers in another way (using Random).
 

Fundamentals of Built-In Date Functions

 

Introduction

The Visual Basic language has a strong support for date values. It is equipped with its own data type named Date. To create and manipulate dates, you have various options. To declare a date variable, you can use either the Date or the DateTime data types.

While Date is a true data type, DateTime is a class but, even though we have not studied classes yet, we can start using DateTime. We will restrict ourselves to using it only as a data type.

In Lesson 3, we saw how to declare a date variable using Date. We also saw that, if you already know the components of the date value you want to use, you can include them between two # signs but following the rules of a date format from the Regional Settings of Control Panel. Here is an example:

Public Module Exercise

    Public Function Main() As Integer

        Dim DateHired As Date

        DateHired = # 02/08/2003 #

        MsgBox("Date Hired: " & DateHired)
        Return 0
    End Function

End Module

This would produce:

Date

An alternative to initializing a date variable is to use a function named DateSerial. Its syntax is:

Public Function DateSerial(ByVal [Year] As Integer, _
   			   ByVal [Month] As Integer, _
   			   ByVal [Day] As Integer) As DateTime

As you can see, this function allows you to specify the year, the month, and the day of a date value, of course without the # signs. When it has been called, this function returns a Date value. Here is an example:

Public Module Exercise

    Public Function Main() As Integer

        Dim DateHired As Date

        DateHired = DateSerial(2003, 02, 08)

        MsgBox("Date Hired: " & DateHired)
        Return 0
    End Function

End Module

When passing the values to this function, you must restrict each component to the allowable range of values. You can pass the year with two digits from 0 to 99. Here is an example:

Public Module Exercise

    Public Function Main() As Integer
        Dim DateHired As Date

        DateHired = DateSerial(03, 2, 8)

        MsgBox("Date Hired: " & DateHired)
        Return 0
    End Function

End Module

If you pass the year as a value between 0 and 99, the compiler would refer to the clock on the computer to get the century. At the time of this writing (in 2008), the century would be 20 and the specified year would be added, which would produce 2003. To be more precise and reduce any confusion, you should always pass the year with 4 digits.

The month should (must) be a value between 1 and 12. If you pass a value higher than 12, the compiler would calculate the remainder of that number by 12 (that number MOD 12 = ?). The result of the integer division would be used as the number of years and added to the first argument. The remainder would be used as the month of the date value. For example, if you pass the month as 18, the integer division would produce 1, so 1 year would be added to the first argument. The remainder is 6 (18 MOD 12 = 6); so the month would be used as 6 (June). Here is an example:

Public Module Exercise

    Public Function Main() As Integer
        Dim DateHired As Date

        DateHired = DateSerial(2003, 18, 8)
        MsgBox("Date Hired: " & DateHired)

        Return 0
    End Function

End Module

This would produce:

Date Serial

As another example, if you pass the month as 226, the integer division (226 \ 12) produces 18 and that number would be added to the first argument (2003 + 18 = 2021). The remainder of 226 to 12 (226 MOD 12 = 10) is 10 and that would be used as the month. Here is an example:

Public Module Exercise

    Public Function Main() As Integer
        Dim DateHired As Date

        DateHired = DateSerial(2003, 226, 8)
        MsgBox("Date Hired: " & DateHired)

        Return 0
    End Function

End Module

This would produce:

Date Serial

 

If the month is passed as 0, it is considered 12 (December) of the previous year. If the month is passed as -1, it is considered 11 (November) of the previous year and so on. If the month is passed as a number lower than -11, the compiler would calculate its integer division to 12, add 1 to that result, use that number as the year, calculate the remainder to 12, and use that result as the month.

Depending on the month, the value of the day argument can be passed as a number between 1 and 28, between 1 and 29, between 1 and 30, or between 1 and 31. If the day argument is passed as a number lower than 1 or higher than 31, the compiler uses the first day of the month passed as the second argument. This is 1. If the day is passed as -1, the day is considered the last day of the previous month of the Month argument. For example, if the Month argument is passed as 4 (April) and the Day argument is passed as -1, the compiler would use 31 as the day because the last day of March is 31. If the Month argument is passed as 3 (March) and the Day argument is passed as -1, the compiler would refer to the Year argument to determine whether the year is leap or not. This would allow the compiler to use either 28 or 29 for the day value. The compiler uses this algorithm for any day value passed as the third argument when the number is lower than 1.

If the Day argument is passed with a value higher than 28, 29, 30, or 31, the compiler uses this same algorithm in reverse order to determine the month and the day.

Converting a Value to Date

If you have a value such as one provided as a string and you want to convert it to a date, you can call the CDate() function. Its syntax is:

Function CDate(Value As Object) As Date

This function can take any type of value but the value must be convertible to a valid date. If the function succeeds in the conversion, it produces a Date value. If the conversion fails, it produces an error.

The Components of a Date

 

Introduction

As seen so far, a date is a value made of at least three parts: the year, the month, and the day. The order of these components and how they are put together to constitute a recognizable date depend on the language and they are defined in the Language and Regional Settings in Control Panel.

The Year of a Date

The Visual Basic language supports the year of a date ranging from 1 to 9999. This means that this the range you can consider when dealing with dates in your applications. In most operations, when creating a date, if you specify a value between 1 and 99, the compiler would use the current century for the left two digits. This means that, at the time of this writing (2008), a year such as 4, 04, or 44 would result in the year 2004. In most cases, to be more precise, you should usually or always specify the year with 4 digits.

If you have a date value whose year you want to find out, you can call the Year() function. Its syntax is:

Public Function Year(ByVal DateValue As DateTime) As Integer

As you can see, this function takes a date value as argument. The argument should hold a valid date. If it does, the function returns the numerical year of a date. Here is an example:

Public Module Exercise
    Public Function Main() As Integer

        Dim DateHired As Date = #2/8/2004#

        MsgBox("In the job since " & Year(DateHired))

        Return 0
    End Function

End Module

This would produce:

Year

The Month of a Year

The month part of a date is a numeric value that goes from 1 to 12. When creating date, you can specify it with 1 or 2 digits. If the month is between 1 and 9 included, you can precede it with a leading 0.

If you have a date value and want to get its month, you can call the Month() function. Its syntax is:

Public Function Month(ByVal DateValue As DateTime) As Integer

This function takes a Date object as argument. If the date is valid, the function returns a number between 1 and 12 for the month. Here is an example:

Public Module Exercise

    Public Function Main() As Integer

        Dim DateHired As Date = #2/8/2004#

        MsgBox("Month hired " & Month(DateHired))

        Return 0
    End Function

End Module

This would produce:

Month

As mentioned already, the Month function produces a numeric value that represents the month of a date. In a year, a month is recognized by an index in a range from 1 to 12. A month has also a name. The name of a function is given in two formats: complete or short. These are:

Month Index Complete Name Short Name
1 January Jan
2 February Feb
3 March Mar
4 April Apr
5 May May
6 June Jun
7 July Jul
8 August Aug
9 September Sep
10 October Oct
11 November Nov
12 December Dec

Instead of getting the numeric index of the month of a date, if you want to get the name of the month, you can call a function named MonthName. Its syntax is:

Public Function MonthName(ByVal Month As Integer, _
   			  Optional ByVal Abbreviate As Boolean = False) As String

This function takes one required and one optional argument. The required argument must represent the value of a month. If it is valid, this function returns the corresponding name. Here is an example:

Public Module Exercise

    Public Function Main() As Integer

        Dim DateHired As Date = #2/8/2004#

        MsgBox("Day hired " & MonthName(Month(DateHired)))

        Return 0
    End Function

End Module

This would produce:

Month

The second argument allows you to specify whether you want to get the complete or the short name. The default is the complete name, in which case the default value of the argument is False. If you want to get the short name, pass the second argument as True. Here is an example:

Public Module Exercise

    Public Function Main() As Integer

        Dim DateHired As Date = #2/8/2004#

        MsgBox("Month hired " & MonthName(Month(DateHired), True))

        Return 0
    End Function

End Module

The Day of a Month

The day is a numeric value in a month. Depending on the month (and the year), its value can range from 1 to 29 (February in a leap year), from 1 to 28 (February in a non-leap year), from 1 to 31 (January, March, May, July, August, October, and December), or from 1 to 30 (April, June, September, and November).

If you have a date value and you want to know its day in a year, you can call the Day() function. Its syntax is:

Public Function Day(ByVal DateValue As DateTime) As Integer

This function takes a date as argument. If the date is valid, the function returns the numeric day in the month of the date argument. Here is an example:

Public Module Exercise

    Public Function Main() As Integer

        Dim DateHired As Date = #2/8/2004#

        MsgBox("Day hired " & Day(DateHired))

        Return 0
    End Function

End Module

This would produce:

Day

The Day of a Week

A week is a combination of 7 consecutive days of a month. Each day can be recognized by an index from 1 to 7 (1, 2, 3, 4, 5, 6, 7). The day of each index is recognized by a name. In US English, the first day has an index of 1 is named Sunday while the last day with an index of 7 is named Monday. Like the months of a year, the days of a week have long and short names. These are:

US English Day Index Complete Name Short Name
1 Sunday Sun
2 Monday Mon
3 Tuesday Tue
4 Wednesday Wed
5 Thursday Thu
6 Friday Fri
7 Saturday Sat

These are the default in US English. In most calculations, the Visual Basic language allows you to specify what day should be the first in a week.

To get the name of the day of a week, you can a function named WeekdayName. Its syntax is:

Public Function WeekdayName( _
   ByVal Weekday As Integer, _
   Optional ByVal Abbreviate As Boolean = False, _
   Optional ByVal FirstDayOfWeekValue As FirstDayOfWeek = FirstDayOfWeek.System _
) As String

This function takes one required and two optional arguments. The required argument must be, or represent, a value between 0 and 7. If you pass it as 0, the compiler will refer to the operating system's language to determine the first day of the week, which in US English is Sunday. Otherwise, if you pass one of the above indexes, the function would return the corresponding name of the day. Here is an example:

Public Module Exercise

    Public Function Main() As Integer

        MsgBox("Day hired: " & WeekdayName(4))

        Return 0
    End Function

End Module

This would produce:

Week Day Name

If you pass a negative value or a value higher than 7, you would receive an error.

The second argument allows you to specify whether you want to get the complete or the short name. The default value of this argument is False, which produces a complete name. If you want a short name, pass the second argument as True. Here is an example:

Public Module Exercise

    Public Function Main() As Integer

        MsgBox("Day hired: " & WeekdayName(4, True))

        Return 0
    End Function

End Module

As mentioned already, the Visual Basic language allows you to specify what days should be the first day of the week. This is the role of the third argument.

Built-In Time Functions

 

Introduction

The Visual Basic language supports time values. To create a time value, you can declare a variable of type Date. To initialize the variable, create a valid value using the rules specified in the Regional and language Settings of Control Panel, and include that value between two # signs. Here is an example;

Public Module Exercise

    Public Function Main() As Integer

        Dim DepositTime As Date = #7:14#

        MsgBox("Deposit Time: " & DepositTime)

        Return 0
    End Function

End Module

This would produce:

Time

Creating a Time Value

Instead of including the time in # signs, you can also provide it as a string. To support this, the Visual Basic language provides a function named TimeValue. Its syntax is:

Public Function TimeValue(ByVal StringTime As String) As DateTime

This function expects a valid time as argument. If that argument is valid, the function returns a time value. Here is an example:

Public Module Exercise

    Public Function Main() As Integer

        Dim DepositTime As Date = TimeValue("7:14")

        MsgBox("Deposit Time: " & DepositTime)

        Return 0
    End Function

End Module

As an alternative to initializing a time variable, you can call a function named TimeSerial. Its syntax is:

Public Function TimeSerial(ByVal Hour As Integer, _
   			   ByVal Minute As Integer, _
   			   ByVal Second As Integer) As DateTime

This function allows you to specify the hour, the minute, and the second values of a time. If you pass valid values, the function returns a time. Here is an example:

Public Module Exercise

    Public Function Main() As Integer

        Dim DepositTime As Date

        DepositTime = TimeSerial(7, 14, 0)

        MsgBox("Deposit Time: " & DepositTime)

        Return 0
    End Function

End Module

The Components of a Time Value

 

The Hours of a Day

In US English, a time is made of various parts. The first of them is the hour. The time is a 24th spatial division of a day. It is represented by a numeric value between 0 and 23. When creating a time value, you specify the hour on the left side. To get the hour of a valid time, you can call a function named Hour. Its syntax is:

Public Function Hour(ByVal TimeValue As DateTime) As Integer

This function takes a time value as argument. If a valid time is passed, the function returns the hour part.

The Minutes of an Hour

An hour is divided in 60 parts. Each part is called a minute and is represented by a numeric value between 0 and 59. If you have a time value and want to get its minute part, you can call a function named Minute. Its syntax is:

Public Function Minute(ByVal TimeValue As DateTime) As Integer

When calling this function, pass it a time value. If the argument holds a valid value, the function returns a number between 0 and 59 and that represents the minutes.

The Seconds of a Minute

A minute is divided in 60 parts and each part is called a second. It is represented by a numeric value between 0 and 59. If you have a time value and want to extract a second part from it, you can call the Second() function named . Its syntax is:

Public Function Second(ByVal TimeValue As DateTime) As Integer

If you call this function, pass a valid time. If so, the function would return a number represents the seconds part.

Operations on Date and Time Values

 

Introduction

Because dates and times are primarily considered as normal values, there are various operations you can perform on them. You can add or subtract a number of years or add or subtract a number of months, etc. The Visual Basic language provides its own mechanisms for performing such operations thanks to its vast library of functions.

Adding a Value to a Date or a Time

To support the addition of a value to a date or a time, the Visual Basic language provides a function named DateAdd. In the Visual Basic language, the DateAdd() function comes in two versions whose syntaxes are:

Public Overloads Function DateAdd( _
   ByVal Interval As DateInterval, _
   ByVal Number As Double, _
   ByVal DateValue As DateTime _
) As DateTime
' -or-
Public Overloads Function DateAdd( _
   ByVal Interval As String, _
   ByVal Number As Double, _
   ByVal DateValue As Object _
) As DateTime

This function takes three arguments that all are required.

Because we have not studied enumerations and classes yet, we will ignore the first version.

The DateValue argument is the date or time value on which you want to perform this operation. It must be a valid Date or DateTime value.

The Interval argument is passed as a string. It specifies the kind of value you want to add. This argument will be enclosed between double quotes and can have one of the following values:

Interval Used To Add
s Second
n Minute
h Hour
w Numeric Weekday
ww Week of the Year
d Day
y Numeric Day of the Year
m Month
q Quarter
yyyy Year

The Number argument specifies the number of Interval units you want to add to the DateValue value. If you set it as positive, its value will be added. Here are examples:

Public Module Exercise

    Public Function Main() As Integer
        Dim LoanStartDate As Date = #6/10/1998#
        Dim DepositTime As Date = TimeValue("7:14:00")

        MsgBox("Loan Length: " & DateAdd("yyyy", 5, LoanStartDate))
        MsgBox("Time Ready: " & DateAdd("h", 8, DepositTime))

        Return 0
    End Function

End Module

This would produce:

Date Add

Date Add

Subtracting a Value From a Date or a Time

Instead of adding a value to a date or a time value, you may want to subtract. To perform this operation, pass the Number argument as a negative value. Here are examples:

Public Module Exercise

    Public Function Main() As Integer
        Dim LoanPayDate As Date = #8/12/2008#
        Dim TimeReady As Date = TimeValue("17:05")

        MsgBox("Loan Length: " & DateAdd("m", -48, LoanPayDate))
        MsgBox("Time Deposited: " & DateAdd("n", -360, TimeReady))

        Return 0
    End Function

End Module

This would produce:

Date Add

Date Add

The Difference Between Two Date or Time Values

Another valuable operation performed consists of finding the difference between two date or time values. To help you perform this operation, the Visual Basic language provides a function named DateDiff. This function allows you to find the number of seconds, minutes, hours, days, weeks, months, or years from two valid date or time values. The DateDiff function takes 5 arguments, 3 are required and 2 are optional.

The formula of the function is

Public Overloads Function DateDiff( _
    ByVal Interval As [ DateInterval | String ], _
    ByVal Date1 As DateTime, _
    ByVal Date2 As DateTime, _
    Optional ByVal DayOfWeek As FirstDayOfWeek = FirstDayOfWeek.Sunday, _
    Optional ByVal  WeekOfYear As FirstWeekOfYear = FirstWeekOfYear.Jan1 _
) As Long

This function takes five arguments, three of which are required and two are optional.

The Date1 argument can be the start date or start time. The Date2 argument can be the end date or end time. These two arguments can also be reversed, in which case the Date2 argument can be the start date  or start time and the Date1 argument would be the end date or end time. These two values must be valid date or time values

The Interval argument specifies the type of value you want as a result. This argument will be enclosed between double quotes and can have one of the following values:

Interval Used To Get
s Second
n Minute
h Hour
w Numeric Weekday
ww Week of the Year
d Day
y Numeric Day of the Year
m Month
q Quarter
yyyy Year

Here is an example:

Public Module Exercise

    Public Function Main() As Integer
        Dim LoanStartDate As Date = #8/12/2003#
        Dim LoanEndDate As Date = #10/5/2008#
        Dim Months As Long = DateDiff("m", LoanStartDate, LoanEndDate)

        MsgBox("Loan Start Date: " & vbTab & LoanStartDate & vbCrLf & _
               "Loan End Date: " & vbTab & LoanEndDate & vbCrLf & _
               "Loan Length: " & vbTab & Months & " months")

        Return 0
    End Function

End Module

This would produce:

Date Difference

By default, the days of a week are counted starting on Sunday. If you want to start counting those days on another day, supply the Option1 argument using one of the following values: vbSunday, vbMonday, vbTuesday, vbWednesday, vbThursday, vbFriday, vbSaturday. There are other variances to that argument.

If your calculation involves weeks or finding the number of weeks, by default, the weeks are counted starting January 1st. If you want to count your weeks starting at a different date, use the Option2 argument to specify where the program should start.

 

 

 

Finance and Accounting

 

Introduction 

An asset is an object of value. It could be a person, a car, a piece of jewelry, a refrigerator. Anything that has a value is an asset. In the accounting world, an asset is a piece of/or property whose life's span can be projected, estimated, or evaluated. As days, months or years go by, the value of such an asset degrades.

When an item is acquired for the first time as "brand new", the value of the asset is referred to as its cost. The declining value of an asset is referred to as its depreciation. At one time, the item will completely lose its worth or productive value. Nevertheless, the value that an asset has after it has lost all of its value is referred to its salvage value. At any time, between the purchase value and the salvage value, accountants estimate the value of an item based on various factors including its original value, its lifetime, its usefulness (how the item is being used), etc.

 

The Double Declining Balance

The Double Declining Balance is a method used to calculate the depreciating value of an asset. To support it, the Visual Basic language provides a function named DDB. Its syntax is:

Function DDB( _
   ByVal Cost As Double, _
   ByVal Salvage As Double, _
   ByVal Life As Double, _
   ByVal Period As Double, _
   Optional ByVal Factor As Double = 2.0 _
) As Double

The first argument, Cost, represents the initial value of the item.

The Salvage argument is the estimated value of the asset when it will have lost all its productive value. The Cost and the Salvage values must be given in their monetary values.

The value of Life is the length of the lifetime of the item. This could be the number of months for a car or the number of years for a house, for example.

The period is a factor for which the depreciation is calculated. It must be in the same unit as the life argument. For the Double Declining Balance, this period argument is usually 2.

Here is an example:

Public Module Exercise

    Public Function Main() As Integer
        Dim BoatPrice As Double
        Dim EndValue As Double
        Dim LifeTime As Double
        Dim Depreciation As Double

        BoatPrice = 22455
        EndValue = 5000
        LifeTime = 60 ' Months
        Depreciation = DDB(BoatPrice, EndValue, LifeTime, 2)

        MsgBox("=-= Double Declining Balance =-=" & vbCrLf & _
               "Boat Price: " & vbTab & BoatPrice & vbCrLf & _
               "End Value: " & vbTab & EndValue & vbCrLf & _
               "Life Time: " & vbTab & LifeTime & " months" & vbCrLf & _
               "End Balance: " & vbTab & Depreciation)
        Return 0
    End Function

End Module

This would produce:

Double Declining Balance

The Straight Line Method

Another method used to calculate the depreciation of an item is through a concept referred to as the Straight Line Method. This time, the depreciation is considered on one period of the life of the item. To support this technique, the Visual Basic language provides a function named SLN. Its syntax is:

Function SLN( _
   ByVal Cost As Double, _
   ByVal Salvage As Double, _
   ByVal Life As Double _
) As Double

The Cost argument is the original amount paid for an item (refrigerator, mechanics toolbox, high-volume printer, etc).

The Salvage, also called the scrap value, is the value that the item will have (or is having) at the end of Life.

The Life argument represents the period during which the asset is (or was) useful. It is usually measured in years.

Here is an example

Public Module Exercise

    Public Function Main() As Integer
        Dim GuitarPrice As Double
        Dim EndValue As Double
        Dim LifeTime As Double
        Dim Depreciation As Double

        GuitarPrice = 1250
        EndValue = 250
        LifeTime = 44 ' Months
        Depreciation = SLN(GuitarPrice, EndValue, LifeTime)

        MsgBox("=-= Straight Line Method =-=" & vbCrLf & _
               "Guitar Price: " & vbTab & GuitarPrice & vbCrLf & _
               "End Value: " & vbTab & EndValue & vbCrLf & _
               "Life Time: " & vbTab & LifeTime & " months" & vbCrLf & _
               "Depreciation: " & vbTab & Depreciation)
        Return 0
    End Function

End Module

This would produce:

Straight Line Method

The Sum of the Years' Digits

The Sum-Of-The-Years’-Digits provides another method for calculating the depreciation of an item. Imagine that a restaurant bought a commercial refrigerator ("cold chamber") for $18,000 and wants to estimate its depreciation after 5 years using the Sum-Of-Years’-Digits method. Each year is assigned a number, also called a tag, using a consecutive count; this means that the first year is appended 1, the second is 2, etc. This way, the depreciation is not uniformly applied to all years.

Year => 1, 2, 3, 4, and 5

The total count is made for these tags. For our refrigerator example, this would be

Sum = 1 + 2 + 3 + 4 + 5 = 15

Each year is divided by this sum, also called the sum of years, used as the common denominator:

Sum of the Years' Digits

This is equivalent to 1. As you can see, the first year would have the lowest divident (1/15 ≈ 0.0067) and the last year would have the highest (5/15 ≈ 0.33).

To calculate the depreciation for each year, the fractions (1/15 + 2/15 + 3/15 + 4/15 + 5/15) are reversed so that the depreciation of the first year is calculated based on the last fraction (the last year divided by the common denominator). Then the new fraction for each year is multiplied by the original price of the asset. This would produce (this table assumes that the refrigerator will have a value of $0.00 after 5 years):

Year Fraction * Amount = Depreciation
1 5/15 * $18,000.00 = $6,000.00
2 4/15 * $18,000.00 = $4,800.00
3 3/15 * $18,000.00 = $3,600.00
4 2/15 * $18,000.00 = $2,400.00
5 1/15 * $18,000.00 = $1,200.00
Total Depreciation = $18,000.00

The function used to calculate the depreciation of an asset using the sum of the years' digits is called SYD and its syntax is:

SYD(cost, salvage, life, period)

The cost argument is the original value of the item; in our example, this would be $18,000.

The salvage parameter is the value the asset would have (or has) at the end of its useful life.

The life is the number of years the asset would have a useful life (because assets are usually evaluated in terms of years instead of months).

The period parameter is the particular period or rank of a Life portion. For example, if the life of the depreciation is set to 5 (years), the period could be any number between 1 and 5. If set to 1, the depreciation would be calculated for the first year. If the Period is set to 4, the depreciation would calculated for the 4th year. You can also set the period to a value higher than life. For example, if life is set to 5 but you pass 8 for the period, the depreciation would be calculated for the 8th year. If the asset is worthless in the 8th year, the depreciation would be 0.

Finance Functions

 

Introduction

Microsoft Access provides a series of functions destined to perform various types of financially related operations. These functions use common factors depending on the value that is being calculated. Many of these functions deal with investments or loan financing.

The Present Value is the current value of an investment or a loan. For a savings account, a customer could pledge to make a set amount of deposit on a bank account every month. The initial value that the customer deposits or has in the account is the Present Value. The sign of the variable, when passed to a function, depends on the position of the customer. If the customer is making deposits (car loan, boat financing, etc), this value must be negative. If the customer is receiving money (lottery installment, family inheritance, etc), this value should be positive.

The Future Value is the value the loan or investment will have when the loan is paid off or when the investment is over. For a car loan, a musical instrument loan, a financed refrigerator, a boat, etc, this is usually 0 because the company that is lending the money will not take that item back (they didn't give it to the customer in the first place, they only lend him or her some money to buy the item). This means that at the end of the loan, the item (such as a car, boat, guitar, etc) belongs to the customer and it is most likely still worth something.

As described above and in reality, the Future Value is the amount the item would be worth at the end. In most, if not all, loans, it would be 0. On the other hand, if a customer is borrowing money to buy something like a car, a boat, a piano, etc, the salesperson would ask if the customer wants to put a "down payment", which is an advance of money. Then, the salesperson or loan officer can either use that down payment as the Future Value parameter or simply subtract it from the Present Value and then apply the calculation to the difference. Therefore, you can apply some type of down payment to your functions as the Future Value.

The Number Of Periods is the number of payments that make up a full cycle of a loan or an investment.

The Interest Rate is a fixed percent value applied during the life of the loan or the investment. The rate does not change during the length of the Periods.

For deposits made in a savings account, because their payments are made monthly, the rate is divided by the number of periods (the Periods) of a year, which is 12. If an investment has an interest rate set at 14.50%, the Rate would be 14.50/12 = 1.208. Because the Rate is a percentage value, its actual value must be divided by 100 before passing it to the function. For a loan of 14.50% interest rate, this would be 14.50/12 = 1.208/100 = 0.012.

The Payment is the amount the customer will be paying. For a savings account where a customer has pledged to pay a certain amount in order to save a set (goal) amount, this would be the amount the customer would pay every month. If the customer is making payments (car loan, mortgage, deposits to a savings account, etc), this value must be negative. If the customer is receiving money (lottery installment or annuity, family inheritance, etc), this value must be positive.

The Payment Time specifies whether the payment is made at the beginning or the end of the period. For a monthly payment, this could be the beginning or end of every month.

 The Future Value of an Investment

To calculate the future value of an investment, you can use the FV() function. The syntax of this function is:

FV(Rate, Periods, Payment, PresentValue, PaymentType)

The Number of Periods of an Investment

To calculate the number of periods of an investment or a loan, you can use the NPer() function. Its syntax is:

NPer(Rate, Payment, PresentValue, FutureValue, PaymentType);

Investment or Loan Payment

The Pmt() function is used to calculate the regular payment of loan or an investment. Its syntax is:

Pmt(Rate, NPeriods, PresentValue, FutureValue, PaymentType)

In the following example, a customer is applying for a car loan. The cost of the car will be entered in cell C4. It will be financed at a rate entered in cell C6 for a period set in cell C7. The dealer estimates that the car will have a value of $0.00 when it is paid off.

The Amount Paid As Interest During a Period

When a customer is applying for a loan, an investment company must be very interested to know how much money it would collect as interest. This allows the company to know whether the loan is worth giving. Because the interest earned is related to the interest rate, a company can play with the rate (and also the length) of the loan to get a fair (?) amount.

The IPmt() function is used to calculate the amount paid as interest on a loan during a period of the lifetime of a loan or an investment. It is important to understand what this function calculates. Suppose a customer is applying for a car loan and the salesperson decides (or agrees with the customer) that the loan will be spread over 5 years (5 years * 12 months each = 60 months). The salesperson then applies a certain interest rate. The IPMT() function can help you calculate the amount of interest that the lending institution would earn during a certain period. For example, you can use it to know how much money the company would earn in the 3rd year, or in the 4th year, or in the 1st year. Based on this, this function has an argument called Period, which specifies the year you want to find out the interest earned in.

The syntax of the IPmt() function is:

IPmt(Rate, 
    Period, NPeriods, PresentValue, FutureValue, PaymentType)

The Rate argument is a fixed percent value applied during the life of the loan.

The PresentValue is the current value of the loan or investment. It could be the marked value of the car, the current mortgage value of a house, or the cash amount that a bank is lending.

The FutureValue is the value the loan or investment will have when the loan is paid off.

The NPeriods is the number of periods that occur during the lifetime of the loan. For example, if a car is financed in 5 years, this value would be (5 years * 12 months each =) 60 months. When passing this argument, you must remember to pass the right amount.

The Period argument represents the payment period. For example, it could be 3 to represent the 3rd year of a 5 year loan. In this case, the IPmt() function would calculate the interest earned in the 3rd year only.

The PaymentType specifies whether the periodic (such as monthly) payment of the loan is made at the beginning (1) or at the end (1) of the period.

The FutureValue and the PaymentType arguments are not required.

The Amount Paid as Principal

While the IPmt() function calculates the amount paid as interest for a period of a loan or an investment, the PPmt() function calculates the actual amount that applies to the balance of the loan. This is referred to as the principal. Its syntax is:

PPMT(Rate, Period, NPeriods, 
      PresentValue, FutureValue, PaymentType)

The argument are the same as described in the previous sections

The Present Value of a Loan or an Investment

The PV() function calculates the total amount that future investments are worth currently. Its syntax is:

PV(Rate, NPeriods, Payment, FutureValue, PaymentType)

The arguments are the same as described earlier.

The Interest Rate

Suppose a customer comes to your car dealer and wants to buy a car. The salesperson would first present the available cars to the customer so the customer can decide what car he likes. After this process and during the evaluation, the sales person may tell the customer that the monthly payments would be $384.48. The customer may then say, "Wooooh, I can't afford that, man". Then the salesperson would ask, "What type of monthly payment suits you". From now on, both would continue the discussion. Since the salesperson still wants to make some money but without losing the customer because of a high monthly payment, the salesperson would need to find a reasonable rate that can accommodate an affordable monthly payment for the customer. 

The Rate() function is used to calculate the interest applied on a loan or an investment. Its syntax is:

RateE(NPeriods, Payment, PresentValue, FutureValue, PaymentType, Guess)

All of the arguments are the same as described for the other functions, except for the Guess. This argument allows you to give some type of guess for a rate. This argument is not required. If you omit it, its value is assumed to be 10.

The Internal Rate of Return

The IRR() function is used to calculate an internal rate of return based on a series of investments. Its syntax is:

IRR(Values, Guess)

The Values argument is a series (also called an array or a collection) of cash amounts that a customer has made on an investment. For example, a customer could make monthly deposits in a savings or credit union account. Another customer could be running a business and receiving different amounts of money as the business is flowing (or losing money). The cash flows don't have to be the same at different intervals but they should (or must) occur at regular intervals such as weekly (amount cut from a paycheck), bi-weekly (401k directly cut from paycheck, monthly (regular investment), or yearly (income). The Values argument must be passed as a collection of values, such as a range of selected cells, and not an amount. Otherwise you would receive an error.

The Guess parameter is an estimate interest rate of return of the investment.

The Net Present Value

The NPV() function uses a series of cash flows to calculate the present value of an investment. Its syntax is:

NPV(Rate, Value1, Value2, ...)

The Rate parameter is the rate of discount in during one period of the investment.

As the NPV() function doesn't take a fixed number of arguments, you can add a series of values as Value1, Value2, etc. These are regularly made payments for each period involved. Because this function uses a series of payments, any payment made in the past should have a positive value (because it was made already). Any future payment should have a negative value (because it has not been made yet).

  • DDB
  • PV
  • FV
  • IPmt
  • Payment
  • PPmt
  • IRR
  • MIRR
  • NPer
  • NPV
  • Rate
  • SLN
  • SYD

 

Custom Libraries

 

Introduction

If the .NET Framework doesn't have a function (or a class) you are looking for, you can create one and be able to use it over and over again in different programs. You can even create one or a series of commercial functions (or classes) and be able to distribute or sell it. To make this happen, you can "package" one or more procedures (or classes) in a library.

A library is a program that contains procedures (and/or classes) and/or other resources that other programs can use. Such a program is created with the same approach as the programs we have created so far. Because a library is not an executable, it doesn't need the Main() procedure. A library usually has the extension .dll.

Creating a Library

A library can be made of a single file or as many files as necessary. A file that is part of a library can contain one or more procedures (or classes). Each procedure (or class) should implement a behavior that can eventually be useful and accessible to other procedures (or classes). The contents of a library are created exactly like those we have used so far. Everything depends on how you compile it.

To create a library, start by typing its code in a text file. Once the library is ready, to compile it, at the Command Prompt, you would execute the following command:

vbc /target:library NameOfFile.vb

After doing this, a library with the name of the file and the extension .dll would be created. If you use the above technique, the new library would be created using the name of the file. Otherwise, if you want a custom name, use the following syntax:

vbc /target:library /out:DesiredNameOfLibrary.dll NameOfFile.vb

Practical LearningPractical Learning: Creating a Library

  1. Start a new file in Notepad and type the following in it:
     
    Module Operations
    
        Function Addition(ByVal x As Double, ByVal y As Double) As Double
    	Return x + y
        End Function
    
        Function Subtraction(ByVal x As Double, ByVal y As Double) As Double
    	return x - y
        End Function
    
        Function Multiplication(ByVal x As Double, ByVal y As Double) As Double
    	return x * y
        End Function
    
        Function Division(ByVal x As Double, ByVal y As Double) As Double
    	if y = 0 Then return 0
    	return x / y
        End Function
    
    End Module
  2. Save the file in a new folder named Operations1
  3. Save the file itself as exo.vb
  4. Switch to the Command Prompt and change to the Operations1 folder
  5. To create the library, type vbc /target:library /out:Arithmetic.dll exo.vb and press Enter
     
    C:\VBasic\Operations1>vbc /target:library /out:Arithmetic.dll exo.vb
    Microsoft (R) Visual Basic .NET Compiler version 7.10.3052.4
    for Microsoft (R) .NET Framework version 1.1.4322.573
    Copyright (C) Microsoft Corporation 1987-2002. All rights reserved.
    
    
    C:\VBasic\Operations1>
  6. Start another file in Notepad and type the following:
     
    Imports System
    Imports Arithmetic
    
    Module Exercise
    
        Sub Main()
    	Dim Number1 As Double, Number2 As Double
    	Dim Result As Double
    
    	Number1 = 244.58
    	Number2 = 5082.88
    	Result  = Operations.Addition(Number1, Number2)
    
    	Console.WriteLine("{0} + {1} = {2}" & vbCrLf, Number1, Number2, Result)
        End Sub
    End Module
  7. Save the file in a new folder named Algebra1
  8. Save the file itself as Exercise.vb
  9. Using Windows Explorer or My Computer, copy the Arithmetic.dll file from the Operations1 folder to the Algebra1 folder 
  10. Switch to the Command Prompt and change to the Algebra1 folder
  11. To compile the program, type vbc /reference:Arithmetic.dll Exercise.vb and press Enter
  12. To execute the application, type Exercise and press Enter

 

 

Previous Copyright © 2008 Yevol Next