Excel VBA Lesson 11: Date and Time Functions

<<Lesson 10>> [Contents] <<Lesson 12>>

Excel VBA provides various built-in date and time functions that allow us to write VBA codes involving dates and times. We can use date and time functions to display system date and time, add and subtract data and time, converting a string to date and more.

11.1 Date Functions

The date functions are explained in Table 11.1.

Table 11.1 Date and Time Functions

Function Description
Now returns current system date and time
Date returns current system date
Day(Date) Returns the day of the month for the date specified in the argument
Weekday(Date) Returns weekday as an integer for the date specified in the argument
WeekdayName(Weekday(Date)) Returns the name of weekday for the date specified in the argument
WeekdayName(Weekday(Date), True) Returns the abbreviated name of weekday for the date specified in the argument
Month(Date) Returns the month of the year in integer for the date specified in the argument
MonthName(Month(Date)) Returns the name of month of the year for the date specified in the argument
MonthName(Month(Date)) Returns the abbreviated name of month of the year for the date specified in the argument
Year(Date) Returns the year in integer for the date specified in the argument

 

Example 11.1

Private Sub CommandButton1_Click()

Cells(1, 2) = Now
Cells(2, 2) = Date
Cells(3, 2) = Day(Date)
Cells(4, 2) = Weekday(Date)
Cells(5, 2) = WeekdayName(Weekday(Date))
Cells(6, 2) = WeekdayName(Weekday(Date), “true”)
Cells(7, 2) = Month(Date)
Cells(8, 2) = MonthName(Month(Date))
Cells(9, 2) = MonthName(Month(Date), “true”)
Cells(10, 2) = Year(Date)

End Sub




The output is as shown in Figure 11.1

Figure 11.1


11.2 Time Functions

The time functions are explained in Table 11.2.

Table 11.2 Time Functions

Function Description
Time Returns the current system time
Hour Returns the hour from its argument
Minute Returns the minute from its argument
Second Returns the second from its argument
Timer Returns the number of seconds since midnight



Example 11.2

Private Sub CommandButton1_Click()

Cells(1, 2) = Time
Cells(2, 2) = Hour(Time)
Cells(3, 2) = Minute(Time)
Cells(4, 2) = Second(Time)
Cells(5, 2) = Timer

End Sub

The output is shown in Figure 11.2

Figure 11.2

11.3 DatePart Function

The DatePart function returns the part of the date specified in the arguments. The arguments are:

YYYY- Year
q- Quarter
m- Month
Y- Day of Year
d- Day
w- Weekday
ww- Week
h- Hour
n- Minute
s- Second

 

Example 11.3

Private Sub CommandButton1_Click()

Cells(1, 2) = DatePart(“YYYY”, Now)
Cells(2, 2) = DatePart(“q”, Now)
Cells(3, 2) = DatePart(“m”, Now)
Cells(4, 2) = DatePart(“y”, Now)
Cells(5, 2) = DatePart(“d”, Now)
Cells(6, 2) = DatePart(“w”, Now)
Cells(7, 2) = DatePart(“ww”, Now)
Cells(8, 2) = DatePart(“h”, Now)
Cells(9, 2) = DatePart(“n”, Now)
Cells(10, 2) = DatePart(“s”, Now)

End Sub

The argument Now is to return the current date and time.The output is shown in Figure 11.3

Figure 11.3

11.4 DateAdd and DateDiff Functions

The function DateAdd is to add dates and the DateDiff is the function to subtract dates.

The syntax of DateAdd is

DateAdd(“t”,n,date)

Where t indicates the interval of the part of the date to add, either d(day), m(month) or year and n is the value to add.

The syntax of DateDiff is

DateDiff(“t”,date1,date2)

Where t indicates the interval of the part of the date to subtract. The interval can be YYYY, m, w, ww, d, h, n, s, same as parameters for DatePart. The function of calculating the difference between date1 and date2.

 

Example 11.4

Private Sub CommandButton1_Click()

Cells(1, 2) = Now
Cells(2, 2) = DateAdd(“yyyy”, 2, Now)
Cells(3, 2) = DateAdd(“m”, 10, Now)
Cells(4, 2) = DateAdd(“d”, 100, Now)
Cells(5, 2) = DateAdd(“h”, 10, Now)
Cells(6, 2) = DateAdd(“YYYY”, 3, “2015/3/28”)
Cells(7, 2) = DateDiff(“YYYY”, Now, “2020/4/16”)
Cells(8, 2) = DateDiff(“m”, Now, “2020/4/16”)
Cells(9, 2) = DateDiff(“ww”, Now, “2020/4/16”)
Cells(10, 2) = DateDiff(“d”, Now, “2020/4/16”)
Cells(11, 2) = DateDiff(“YYYY”, “2016/5/20”, “2020/4/16”)
Cells(12, 2) = DateDiff(“m”, “2016/5/20”, “2020/4/16”)

End Sub

The output is shown in Figure 11.4

Figure 11.4

<<Lesson 10>> [Contents] <<Lesson 12>>

Excel VBA Lesson 10: Financial Functions

<<Lesson 9>> [Contents] <<Lesson 11>>

Excel VBA offers a number of financial functions that can be used for accounting and financial calculations. In this lesson, we shall deal some of those functions
that perform basic financial calculations. They are PV, FV and Pmt.

10.1 PV

PV returns the present value of a certain amount of money a person needs to invest in order to earn a certain amount of money in the future(future value),
based on the interest rate and the number of years this amount of money is kept. Additionally, it can also return the present value of an annuity which means the present value of a series of payments in
the future

The syntax of PV in Excel VBA is

PV(Rate, Nper, Pmt, FV, Due)

The parameters in the parentheses are explained below:

Rate – Interest rate per period
Nper – Number of payment periods
Pmt – Amount of periodic payment for an annuity
FV – Future value
Due – Indicates when the payment is due. Its value is 1 for beginning of month and 0 for end of the month



Example 10.1

Do you know how much you need to invest today and how much you need to save monthly in order to obtain $1,000,000 thirty years from now?
Let’assume a fixed deposit interest rate is 4% per annum and you are willing to save $100 monthly in the bank, you can write the following Excel VBA code
to find out the initial investment you need to fork out.

Private Sub CommandButton1_Click()
Dim TheRate, FuVal, Payment As Single

Dim NPeriod As Integer

TheRate = InputBox(“Enter the rate per annum”)
FuVal = InputBox(“Enter future value”)
Payment = -InputBox(“Enter amount of monthly payment”)
NPeriod = InputBox(“Enter number of years”)

MsgBox (“The Initial Investment is ” & Round(PV(TheRate / 12 / 100, NPeriod * 12, Payment, FuVal, 1), 2))
End Sub

Running the program will produce a series of input boxes where the user can enter various values. The answer is shown in Figure 10.1.
The value is negative because this is the amount you need to pay.

Figure 10.1



10.2 FV

FV returns the amount of money you will earn in future by putting in an initital investment and continue to pay certain amount periodically.
The amount is depending on the interest rate and the duration. It reflects time value of money.

The syntax of FV in Excel VBA is

FV(Rate, Nper, Pmt, PV, Due)

Example 10.2

In this example, you want to find the future value if your initial investment is $100,000, your monthly payment is $100, interest rate 5% and the investment period is 30 years

Private Sub CommandButton1_Click()

Dim TheRate, PVal, Payment As Single

Dim NPeriod As Integer

TheRate = InputBox(“Enter the rate per annum”)
PVal = InputBox(“Enter initial investment amount)
Payment = -InputBox(“Enter amount of monthly payment”)
NPeriod = InputBox(“Enter number of years”)

MsgBox (“The Initial Investment is ” & Round(FV(TheRate / 12 / 100, NPeriod * 12, -Payment, -PVal, 0), 2))

End Sub

We place negative signs in front of Payment and Pval as you are paying out the money. Running the program will produce a series of input boxes where the user can enter various values. The answer is shown in Figure 10.2.
.

Figure 10.2

10.3 Pmt

Pmt is an Excel VBA function that returns a number of periodic payments you need to make for a certain PV and FV.

The syntax of Pmt in Excel VBA is

Pmt(Rate,Nper, PV, FV, Due)

Example 10.3

For example, you borrowed a mortgage loan of 500,000 from the bank to buy a property. You have agreed to pay back the loan in thirty years
by a monthly instalment method at an interest rate of 4% per annum. Now you need to calculate the amount of monthly payment.
In this case, Rate=4/100/12 (monthly rate), Nper=30×12=360 months,PV=500,000, FV=0 (loan settled) and due=0 as you normally paying at end of the month.

Private Sub CommandButton1_Click()

Dim TheRate, PVal As Single

Dim NPeriod As Integer

TheRate = InputBox(“Enter the rate per annum”)
PVal = InputBox(“Enter Loan Amount”)
NPeriod = InputBox(“Enter number of years”)

MsgBox (“The monthly payment is ” & Round(Pmt(TheRate / 12 / 100, NPeriod * 12, Pval, 0, 0), 2))

End Sub

Running the program will produce a series of input boxes where the user can enter various values. The answer is shown in Figure 10.3.
.The value is negative because this is the amount you need to pay

Figure 10.3

<<Lesson 9>> [Contents] <<Lesson 11>>

Excel VBA Lesson 7: Trigonometric Functions

<<Lesson 6>> [Contents] <<Lesson 8>>

In the previous lesson, we have learned how to write code using various mathematical functions in Excel VBA. In this lesson, we shall proceed to learn how to work with trigonometric functions.The three basic trigonometric functions are Sin, Cos and Tan which stand for sine, cosine and tangent. We also deal with the inverse of tangent, Atn.

7.1 The Sin function

The Sin function returns the sine value of an angle. We need to convert the angle to radian as Excel VBA cannot deal with the angle in degree.
The conversion is based on the following equation:

π radian= 180o
so 1o=π/180 radian

The issue is how to get the exact value of p? We can use p=3.14159 but it will not be accurate.
To get exact value of π, we use the arc tangent function, i.e. is Atn. Using the equation tan(π/4)=1, so Atn(1)=π/4, therefore, π=4Atn(1)

The syntax of the Sin function in Excel VBA is

Sin(Angle in radian)



Example 7.1

In this example, we use pi to represent π and assign the value of π using the formula pi = 4*.Atn(1). We use the function Round the value of sine to four decimal places.
Private Sub CommandButton1_Click()
Dim pi As Single

pi = 4*.Atn(1)

MsgBox(“Sin 90 is” & Round(Sin(pi/2), 4))

End Sub

Running the program produces the message as shown in Figure 7.1

Figure 7.1

7.1 The Cos function

The Cos function returns the cosine value of an angle

The syntax of the Cos function in Excel VBA is

Cos(Angle in radian)

Example 7.2

Private Sub CommandButton1_Click()
Dim pi As Single

pi = 4*.Atn(1)

MsgBox(“Cos 60 is” & Round(Cos(pi/3), 4))

End Sub
Running the program produces the message as shown in Figure 7.2

Figure 7.2

<<Lesson 6>> [Contents] <<Lesson 8>>

Excel VBA Lesson 6: Mathematical Functions

<<Lesson 5>> [Contents] <<Lesson 7>>

In Excel VBA macro programming, we can write codes that can perform arithmetic operations using standard arithmetic operators.
However, for more complex mathematical calculations, we need to use the built-in mathematical functions in Excel VBA.

There are numerous built-in mathematical functions in Excel VBA.
Among them are Abs, Exp, Int, Fix, Rnd, Round, sqr and more. We shall deal with trigonometric functions and Financial Functions in coming lessons.

6.1 The Abs Function

In Excel VBA, the Abs function returns the absolute value(positive value) of a given number.
The syntax is

Abs(Number)

Example 6.1

Private Sub CommandButton1_Click()

Cells(1,1)=Abs(-100)

End Sub

Running the program will display 100 in cell A1



6.2 The Exp Function

The Exp of a number x is the value of ex.The syntax is:

Exp(Number)

Example 6.2

Private Sub CommandButton1_Click()

Cells(1,1)=Exp(1)

End Sub

Running the program will displays 2.718282 in cell A1

6.3 The Int Function

Int is the function that converts a number into an integer by truncating its decimal part and the resulting integer is the largest integer that is smaller than the number.

The syntax is

Int(Number)

Example 6.3

Private Sub CommandButton1_Click()

Cells(1,1)=Int(2.4)

Cells(2,1)=Int(4.8)

Cells(3,1)=Int(-4.6)

Cells(4,1)=Int(0.32)

End Sub

Running the program will display the results as 2 in cell A1,4 in cell A2,-5 in cell A3 and 0 in cell A4.

6.4 The Fix Function

Fix and Int is the same if the number is a positive number as both truncate the decimal part of the number and return an integer.
However, when the number is negative, it will return the smallest integer that is larger than the number. The syntax is:

Fix(number)

Example 6.4

Private Sub CommandButton1_Click()

Cells(1,1)=Fix(2.4)

Cells(2,1)=Fix(4.8)

Cells(3,1)=Fix(-4.6)

Cells(4,1)=Fix(-6.32)

End Sub

Running the program will displays the results as 2 in cell A1,4 in cell A2,-4 in cell A3 and -6 in cell A4.


6.5 The Rnd Function

The Rnd function returns a random value between 0 and 1.Rnd is very useful when we deal with the concept of chance and probability.
The syntax is:

Rnd

Example 6.5

Private Sub CommandButton1_Click()

Dim x As Integer

For x = 1 To 10

nbsp;Cells(x, 1) = Rnd()

Next x

End Sub

Running the program will display ten random numbers between 0 and 1 from cell A1 to cell A10.

6.6 The Round Function

Round is the function that rounds up a number to a certain number of decimal places. The Format is Round (n, m) which means to round a number n to m decimal places. For example, Round (7.2567, 2) =7.26

6.6 The Sqr Function

Sqr is the function that computes the square root of a number. For example, Sqr(4)=2, Sqr(9)=2 and etc.

<<Lesson 5>> [Contents] <<Lesson 7>>

Excel 2010 VBA Lesson 9: String Manipulation Functions

[Lesson 8]<<[Table of Contents]>>[Lesson 10]

Excel 2010 VBA can handle strings just as well as the stand-alone Visual Basic program. All the string handling functions in Visual Basic such as Len, Right, Left, Mid, Trim, Ltrim, Rtrim, Ucase, Lcase, Instr, Val, Str, Chr, and Asc can be used in Excel 2010 VBA macro programming.

9.1 The InStr function

InStr is a function that looks for and returns the position of a substring in a phrase.

Example 9.1

Private Sub cmdInstr_Click()
Dim phrase As String
 phrase = Cells(1, 1).Value
 Cells(4, 1) = InStr(phrase, “ual”)
End Sub

http://excelvbatutor.com/index.php/page-sitemap.xml
The function InStr(phrase,”ual”) will find the substring “ual” from the phrase “Visual Basic” entered in cells(1,1) and then return its position, in this case, it is 4 from the left.

9.2 The Left function

Left is a function that extracts the characters from a phrase, starting from the left.

Left(phrase,4) means 4 characters are extracted from the phrase, starting from the leftmost position.

Example 9.2

Private Sub cmdLeft_Click()
Dim phrase As String
 phrase = Cells(1, 1).Value
 Cells(2, 1) = Left(phrase, 4)
End Sub

This code returns the substring “Visu” from the phrase “Visual Basic” entered in cells(1,1)

http://excelvbatutor.com/index.php/page-sitemap.xml

9.3 The Right function

Right is a function that extracts the characters from a phrase, starting from the Right.

Right(phrase,5) means 5 characters are extracted from the phrase, starting from the rightmost position.

Example 9.3

Private Sub cmdRight_Click()
Dim phrase As String
 phrase = Cells(1, 1).Value
 Cells(3, 1) = Right(phrase, 5)

This code returns the substring “Basic” from the phrase “Visual Basic” entered in cells(1,1)

9.4 The Mid function

Mid is a function that extracts a substring from a phrase, starting from the position specified by the second parameter in the bracket.

Mid(phrase,8,3) means a substring of three characters are extracted from the phrase, starting from the 8th position from the left, including empty space.

Example 9.4

Private Sub cmdMid_Click()
Dim phrase As String
 phrase = Cells(1, 1).Value
 Cells(5, 1) = Mid(phrase, 8, 3)
End Sub

This code returns the substring “Bas” from the phrase “Visual Basic” entered in cells(1,1)

9.5 The Len function

Len is a function that returns the length of a phrase(including empty space in between)

Example 9.5

Private Sub cmdLen_Click()
Dim phrase As String
 phrase = Cells(1, 1).Value
 Cells(6, 1) = Len(phrase)
End Sub

The code returns 12 for the phrase “Visual Basic” entered in cells(1,1)

9.6 The Ucase and the Lcase functions

The Ucase function converts all the characters of a string to capital letters. On the other hand, the Lcase function converts all the characters of a string to small letters. For example,

Ucase(“excel vba”) =EXCEL VBA

Lcase(“Excel VBA”) =excel vba

9.7 The Str and Val functions

The Str is the function that converts a number to a string while the Val function converts a string to a number. The two functions are important when we need to perform mathematical operations.

9.8 The Chr and the Asc functions

The Chr function returns the string that corresponds to an ASCII code while the Asc function converts an ASCII character or symbol to the corresponding ASCII code. ASCII stands for “American Standard Code for Information Interchange”. Altogether there are 255 ASCII codes and as many ASCII characters. Some of the characters may not be displayed as they may represent some actions such as the pressing of a key or produce a beep sound. The format of the Chr function is

Chr(charcode)

and the format of the Asc function is

Asc(Character)

The following are some examples:

Chr(65)=A, Chr(122)=z, Chr(37)=% , Asc(“B”)=66, Asc(“&”)=38
http://excelvbatutor.com/index.php/page-sitemap.xml

[Lesson 8]<<[Table of Contents]>>[Lesson 10]

Excel 2010 VBA Lesson 8: Formatting Functions

[Lesson 7]<<[Table of Contents]>>[Lesson 9]

Data in the previous lesson were presented fairly systematically through the use of functions like Int, Fix, and Round. However, the Format function improves the presentation of the numeric values. There are two types of Format function, one of them is the built-in format while another one is the user-defined function.



8.1 Predefined Format function

The syntax of the predefined Format function is

Format (n, “style argument”)

where n is a number and the list of style arguments are listed in Table 8.1

Table 8.1

The output is shown in Figure 8.1
vba2010_fig8.1



Figure 8.1

8.2 user-defined Format function

The syntax of the user-defined Format function is

Format (n, “user’s format”)

Although it is known as user-defined format, we still need to follow certain formatting styles. Examples of user-defined formatting style are listed in Table 8.2

Table 8.2

Example 8.2

Private Sub CommandButton1_Click()
 Cells(1, 1) = Format(781234.57, “0″)
 Cells(2, 1) = Format(781234.57, “0.0″)
 Cells(3, 1) = Format(781234.576, “0.00″)
 Cells(4, 1) = Format(781234.576, “#,##0.00″)
 Cells(5, 1) = Format(781234.576, “$#,##0.00″)
 Cells(6, 1) = Format(0.576, “0%”)
 Cells(7, 1) = Format(0.5768, “0.00%”)
End Sub

The output is shown in Figure 8.2
vba2010_fig8.2

Figure 8.2



[Lesson 7]<<[Table of Contents]>>[Lesson 9]

Excel 2010 VBA Lesson 7: Mathematical Functions

[Lesson 6]<<[Table of Contents]>>[Lesson 8]

Mathematical functions are very useful and important in Excel 2010 VBA programming because very often we need to deal with mathematical concepts in programming such as chance and probability, variables, mathematical logic, calculations, coordinates, time intervals and etc.

The common mathematical functions in Excel 2010 VBA are Int, Sqr, Abs, Exp, Log, Sin, Cos, Tan , Atn, Fix , Rnd and Round)
Rnd is very useful when we deal with the concept of chance and probability. The Rnd function returns a random value between 0 and 1. In Example 7.1. When you run the program, you will get an output of 10 random numbers between 0 and 1 each time you click on the OK button of the message box.

Example 7.1

Private Sub CommandButton1_Click()
 For x = 1 To 10
  MsgBox Rnd
 Next x
End Sub

Random numbers in its original form are not very useful in programming until we convert them to integers. For example, if we need to obtain a random output of 6 random integers ranging from 1 to 6, which make the program behave as a virtual die, we need to convert the random numbers using the format  Int(Rnd*6)+1. Let’s study the following example:

In this example, Int(Rnd*6) will generate a random integer between 0 and 5 because the function Int truncates the decimal part of the random number and returns an integer. After adding 1, you will get a random number between 1 and 6 every time you click the command button. For example, let say the random number generated is 0.98, after multiplying it by 6, it becomes 5.88, and using the integer function Int(5.88) will convert the number to 5, and after adding 1 you will get 6.

Example 7.2

Private Sub Commandbutton1_Click ( )
 Num=Int(Rnd*6)+1
 MsgBox Num
End Sub

Now, run the program and then click on the button, you will get an output of number 1 to 6 randomly display on the message box.

b) Int is the function that converts a number into an integer by truncating its decimal part and the resulting integer is the largest integer that is smaller than the number. For example, Int(2.4)=2, Int(4.8)=4, Int(-4.6)= -5, Int(0.032)=0 and so on.
c) Sqr is the function that computes the square root of a number. For example, Sqr(4)=2, Sqr(9)=2 and etc.
d) Abs is the function that returns the absolute value of a number. So Abs(-8) = 8 and Abs(8)= 8.
e) Exp of a number x is the value of ex. For example, Exp(1)=e^1 = 2.7182818284590
f) Fix and Int is the same if the number is a positive number as both truncate the decimal part of the number and return an integer. However, when the number is negative, it will return the smallest integer that is larger than the number. For example, Fix(-6.34)= -6 while Int(-6.34)=-7.
g) Round is the function that rounds up a number to a certain number of decimal places. The Format is Round (n, m) which means to round a number n to m decimal places. For example, Round (7.2567, 2) =7.26
g) Log is the function that returns the natural Logarithm of a number. For example,
Log (10)= 2.302585


<br /> (adsbygoogle = window.adsbygoogle || []).push({});<br />

Example 7.3

Private Sub CommandButton1_Click()
 For i = 1 To 10
  MsgBox Sqr(i )
 Next
End Sub

This program will find the square root of number 1 to 10 and displays them on the message box each time you click on the OK button.

Example 7.4

In this example, we created an Excel 2010 VBA to compute the values of Int(x), Fix(x), Round(x,4) and Log(x) and displays them in respective cells. Notice that you can combine two or more functions in your code, like Round(Log(x)). It uses the Do Loop statement and the Rnd function to generate random numbers. The statement x = Rnd * 7 generate  random numbers between 0 and 7 . Using commas in between items will create spaces between them and hence a table of values can be created. The program is shown below and the output is shown in Figure 7.1:

Private Sub CommandButton1_Click()
Dim n As Integer
Dim x As Single
 n = 2
Do While n < 12
 x = Rnd * 7

 Cells(n, 1) = x
 Cells(n, 2) = Int(x)
 Cells(n, 3) = Fix(x)
 Cells(n, 4) = Round(Cells(n, 1), 4)
 Cells(n, 5) = Round(Log(x), 4)
 Cells(n, 6) = Round(Sqr(x), 4)
 n = n + 1
Loop
End Sub
vba2010_fig7.1

Figure 7.1



<br /> (adsbygoogle = window.adsbygoogle || []).push({});<br />

[Lesson 6]<<[Table of Contents]>>[Lesson 8]

Excel 2010 VBA Lesson 6: Subroutines and Functions

[Lesson 5]<<[Table of Contents]>>[Lesson 7]

Another way to write code in Excel  2010 VBA is to launch the code window directly by clicking on View Code in the Developer menu bar. In the editor window, click on insert module to start code that is not related to an Active-X control.

The module is a code sheet that is specific to your Excel 2010 VBA. Code created in the module is not directly triggered by events on the spreadsheet, but need to be called directly. It can be called by running a macro, press F5, running a UserForm etc. If the code is a function, it can be inserted directly into the cells of the spreadsheet.

An Excel 2010 VBA  project usually uses one or more modules to store the necessary subroutines or functions known as procedures. You can make the subroutines or functions private or public. If the code is private, it can only be used by the current workbook whereas a code that is made public can be used by other procedure in another module in the workbook.

 6.1: Subroutines

A Subroutine in Excel  VBA  is a procedure that performs a specific task and to return values, but it does not return a value associated with its name. However, it can return a value through a variable name. Subroutines are usually used to accept input from the user, display information, print information, manipulate properties or perform some other tasks. It is a program code by itself and it is not an event procedure because it is not associated with a runtime procedure or an Excel VBA control such as a command button. It is called by the main program whenever it is required to perform a certain task. Sub procedures help to make programs smaller and easier to manage.

A Subroutine begins with a Sub statement and ends with an End Sub statement. The program structure of a sub procedure is as follows:

Sub subProg(arguments)
 Statements
End Sub

Subroutines are called by the main program using the Call keyword.

Sub MainProg( )
 Call  SubProg()
End Sub

A subroutine is different from a function that it does not return a value directly. You can include parameters in a subroutine.

Example 6.1

In this example, the main program calls the subroutine findHidden and execute it. The end result is a message box that displays the hidden text.

Private Sub CommandButton1_Click()
 Call findHidden
End Sub

Sub findHidden()
 hidden_txt = "@#%43&*"
 MsgBox hidden_txt
End Sub



Example 6.2

Private Sub CommandButton1_Click()
 Call salary(10, 300)
End Sub

Sub salary(wage As Single, hours As Single)
 MsgBox wage * hours
End Sub

In this example, the Call command calls the subroutine salary and passes the parameters 10 and 300 to it. It will calculate the salary based on wage per hour and the number of hours and display on the message box.

6.2 Functions

In Excel VBA 2010, a function is similar to a subroutine but the main purpose of the function is to accept a certain input from the user and return a value which is passed on to the main program to finish the execution. There are two types of functions, the built-in functions (or internal functions) and the functions created by the programmers, or simply called user-defined functions.

The first built-in function that we have already learned and familiar with its usage is the Message Box. We are not going to repeat here but we shall take a look at its syntax once more, i.e.

message=MsgBox(Prompt, Style Value,Title)

Now we shall examine the next commonly used function in Excel VBA,  the InputBox function.

6.2.1 InputBox function

An InputBox( ) function displays a message box where the user can enter a value or a message in the form of text. The format is

myMessage=InputBox(Prompt, Title, default_text, x-position, y-position)

myMessage is a variant data type but typically it is declared as a string, which accepts the message input by the users. The arguments are explained as follows:

  • Prompt       – The message displayed normally as a question asked.
  • Title            – The title of the Input Box.
  • default-text  – The default text that appears in the input field where users can use it as his intended input or he may change to the message he wishes to key in.
  • x-position and y-position – the position or the coordinate of the input box.




Example 6.1

In this example, we insert a label and a command button into the MS Excel spreadsheet. Double click on the command button and enter the Excel VBA code as follows:

Private Sub CommandButton1_Click()
Dim userMsg As String
 userMsg = InputBox(“What is your message?”, “Message Entry Form”, “Enter your messge here”, 500, 700)
If userMsg <> “” Then
 MsgBox( userMsg)
Else
 MsgBox(“No Message”)
End If
End Sub

* The InputBox  is show in Figure 6.1

vba2010_fig6.1

Figure 6.1: InputBox

6.2.1 Creating User Defined Functions

The syntax to create a function is as follows:

Public Function functionName (Arg As dataType,……….) As dataType

or

Private Function functionName (Arg As dataType,……….) As dataType

* Public indicates that the function is applicable to the whole project while Private indicates that the function is only applicable to a certain module or procedure.

In order to create a user-defined function in Excel  VBA 2010, you need to go into the Visual Basic Editor in MS Excel Spreadsheet. In the Visual Basic Editor, click on Insert on the menu bar to insert a module into the project. Enter the following code as shown in Figure 6.2. This function is to calculate the cube root of a number.

vba2010_fig6.2

Figure 6.2

Now enter the function CubeRoot just like you enter the formula of MS Excel, as shown in Figure 6.3. The value of cube root for the number in cell C4 will appear in cell D4.

vba2010_fig6.3




[Lesson 5]<<[Table of Contents]>>[Lesson 7]

Excel VBA Lesson 5: Sub Procedures and Functions

<<Lesson 4>> [Contents] <<Lesson 6>>

5.1 Sub Procedure

A sub procedure in Excel VBA is a procedure that performs a specific task and to return values, but it does not return a value associated with its name. However, it can return a value through a variable name. Sub procedures are usually used to accept input from the user, display information, print information, manipulate properties or perform some other tasks. It is a program code by itself and it is not an event procedure because it is not associated with a runtime procedure or an Excel VBA control such as a command button. It is called by the main program whenever it is required to perform a certain task. A Sub procedure helps to make programs smaller and easier to manage.



A sub procedure begins with a Sub statement and ends with an End Sub statement. The program structure of a sub procedure is as follows:

Sub ProcedureName (arguments)
Statements
End Sub

Example 5.1

In this example, a sub procedure ResizeFont is created to resize the font in the range if it fulfils a value greater than 40. There are two parameters or arguments associated with the sub procedure, namely x for font size and Rge for range. This sub procedure is called by the event procedure Sub CommandButton1_Click () and passed the values 15 to x (for font size) and Range (“A1:A10”) to Rge (for range) to perform the task of resizing the font to 15 for values>40 in range A1 to A10.

Private Sub CommandButton1_Click()
ResizeFont 15, Range(“A1:A10”)
End Sub

Sub ResizeFont(x As Variant, Rge As Range)
Dim cel As Range
For Each cel In Rge
If cel.Value > 40 Then
cel.Font.Size = x
End If
Next cel
End Sub

vba_Figure20.1

 Figure 5.1




To make the program more flexible and interactive, we can modify the above program to accept input from the user. The values input by the user through the input boxes will be passed on to the procedure to execute the job, as shown in Example 20.2.

Example 5.2

Private Sub CommandButton1_Click()
Dim rng As String
rng = InputBox(“Input range”)
x = InputBox(“Input Font Size”)
ResizeFont x, Range(rng)
End Sub

Sub ResizeFont(x As Variant, Rge As Range)
Dim cel As Range
For Each cel In Rge
If cel.Value > 40 Then
cel.Font.Size = x
End If
Next cel
End Sub

5.2 Functions

In Excel VBA, a function is similar to a sub procedure but the main purpose of the function is to accept a certain input from the user and return a value which is passed on to the main program to finish the execution. There are two types of functions, the built-in functions (or internal functions) and the functions created by the programmers, or simply called user-defined functions. The first built-in function is the Message Box. The message box acts as a dialog box where users can interact with the computer, it is able to perform certain actions in response to what the user clicks or selects.

The syntax for a message box in Excel VBA is as follows,

message=MsgBox(Prompt, Style Value,Title)

The first argument, Prompt, will display the message in the message box. The Style Value determines what type of command button will appear in the message box. The Title argument will display the title of the message board while message is a variable that holds values that are returned by the MsgBox ( ) function. The values are determined by the type of buttons being clicked by the users. It has to be declared as Integer data type in the procedure or in the general declaration section. Please refer to Lesson 10 of Visual Basic Tutorial for the detail listings of the Style Value as well as the returned value.

In this example, We created three command buttons which show different Options. We put in a few program codes in the last button which involve the use of If…Then…Elseif statements.

Figure 5.2

Double-click the top button and enter the following code:

Private Sub CommandButton1_Click()

MsgBox (“Welcome to VBA Programming”)

End Sub

By clicking the first message box, you will see the message as shown in Figure 5.2

Figure 5.3

Now double-click on the second button and enter the following code:

Private Sub CommandButton2_Click()

Dim message As String
message = MsgBox(“Click Yes to Proceed, No to stop”, vbYesNoCancel, “Login”)
If message = vbYes Then
MsgBox “You may proceed”
ActiveWorkbook.Activate
ElseIf message = vbNo Then
MsgBox “Your application is terminated”
ActiveWorkbook.Close
End If

End Sub

Click on the button and you shall see the following dialog. You will notice the Yes, No, Cancel buttons:

Figure 5.4

Now double-click on the second button and enter the following code:

Private Sub CommandButton3_Click()
Dim message As Integer
message = MsgBox(“Click Yes to Proceed, No to stop”, vbYesNo, “Login”)
If message = 6 Then
Range(“A1”).Value = “You may proceed”
ActiveWorkbook.Activate
ElseIf message = 7 Then
ActiveWorkbook.Close
End If
End Sub

Click on the button and you would notice that the displayed message box comprises only the Yes and No button, as shown in Figure 5.4

Figure 5.4

5.2.1 InputBox function

An InputBox( ) function displays a message box where the user can enter a value or a message in the form of text. The syntax is

myMessage=InputBox(Prompt, Title, default_text, x-position, y-position)

myMessage is a variant data type but typically it is declared as a string, which accepts the message input by the users. The arguments are explained as follows:

  • Prompt represents the message displayed normally as a question asked.
  • The title represents the title of the Input Box.
  • default-text represents the default text that appears in the input field where the user can use it as his or her intended input
  • x-position and y-position represents the position or the coordinate of the input box.

Example 5.3

In this example, we insert a label and a command button into the MS Excel spreadsheet. Double click on the command button and enter the Excel VBA code as follows:

Private Sub CommandButton1_Click()
Dim userMsg As String
userMsg = InputBox(“What is your message?”, “Message Entry Form”, “Enter your messge here”, 500, 700)
If userMsg <> “” Then
MsgBox( userMsg)
Else
MsgBox(“No Message”)
End If
End Sub
* The InputBox is shown in Figure 5.4

vba2010_fig6.1

Figure 5.5  InputBox

5.2.1 Creating User-Defined Functions

The syntax to create a User-Defined function is as follows:

Public Function functionName (Arg As dataType,…) As dataType

or

Private Function functionName (Arg As dataType,…) As dataType

Public indicates that the function is applicable to the whole project while Private indicates that the function is only applicable to a certain module or procedure.

In order to create a user-defined function in Excel VBA, you need to go into the Visual Basic Editor in MS Excel Spreadsheet.In the Visual Basic Editor, click on Insert on the menu bar to insert a module into the project. Enter the following code as shown in Figure 5.2. This function is to calculate the cube root of a number.

vba2010_fig6.2

Figure 5.6




Now enter the function CubeRoot just like you enter the formula of MS Excel, as shown in Figure 5.3. The value of cube root for the number in cell C4 will appear in cell D4.

vba2010_fig6.3

Figure 5.7

 

<<Lesson 4>> [Contents] <<Lesson 6>>

Excel VBA Lesson 8: String Manipulation Functions

<<Lesson 7>> [Contents] <<Lesson 9>>

Excel VBA can handle strings just as well as the stand-alone Visual Basic program. All the string handling functions in Visual Basic such as Len, Right, Left, Mid, Trim, Ltrim, Rtrim, Ucase, Lcase, Instr, Val, Str ,Chr and Asc can be used in Excel VBA.

8.1 The InStr function

InStr is a function that looks for and returns the position of a substring in a phrase

Example 8.1

Private Sub cmdInstr_Click()
Dim phrase As String
phrase = Cells(1, 1).Value
Cells(4, 1) = InStr(phrase, “ual”)
End Sub

The function InStr(phrase,”ual”) will find the substring “ual” from the phrase “Visual Basic” entered in cells(1,1) and then return its position, in this case, it is 4 from the left.



8.2 The Left function

Left is a function that extracts the characters from a phrase, starting from the left.

Left(phrase,4) means 4 characters are extracted from the phrase, starting from the leftmost position.

Example 8.2

Private Sub cmdLeft_Click()
Dim phrase As String
phrase = Cells(1, 1).Value
Cells(2, 1) = Left(phrase, 4)
End Sub

This code returns the substring “Visu” from the phrase “Visual Basic” entered in cells(1,1)

8.3 The Right function

he Right function that extracts the characters from a phrase, starting from the Right.Right(phrase,5) means 5 characters are extracted from the phrase, starting from the rightmost position.

Example 8.3

Private Sub cmdRight_Click()
Dim phrase As String
phrase = Cells(1, 1).Value
Cells(3, 1) = Right(phrase, 5)

This code returns the substring “Basic” from the phrase “Visual Basic” entered in cells(1,1)



8.4 The Mid function

Mid is a function that extracts a substring from a phrase, starting from the position specified by the second parameter in the bracket.

Mid(phrase,8,3) means a substring of three characters are extracted from the phrase, starting from the 8th position from the left, including empty space.

Example 8.4

Private Sub cmdMid_Click()
Dim phrase As String
phrase = Cells(1, 1).Value
Cells(5, 1) = Mid(phrase, 8, 3)
End Sub

This code returns the substring “Bas” from the phrase “Visual Basic” entered in cells(1,1)

8.5 The Len function

Len is a function that returns the length of a phrase(including empty space in between)

Example 8.5

Private Sub cmdLen_Click()
Dim phrase As String
phrase = Cells(1, 1).Value
Cells(6, 1) = Len(phrase)
End Sub

The code returns 12 for the phrase “Visual Basic” entered in cells(1,1)

Visual Basic Editor in MS Excel can handle strings just as good as a stand-alone VB program. All the string handling functions in Visual Basic such as Left, Right, Instr, Mid and Len can be used in Excel Visual Basic Editor.

The output of all the examples are shown in the Figure below:

Excel VBA

8.6 The Ucase and the Lcase functions

The Ucase function converts all the characters of a string to capital letters. On the other hand, the Lcase function converts all the characters of a string to small letters. For example,

Ucase(“excel vba”) =EXCEL VBA

Lcase(“Excel VBA”) =excel vba



8.7 The Str and Val functions

The Str is the function that converts a number to a string while the Val function converts a string to a number. The two functions are important when we need to perform mathematical operations.

8.8 The Chr and the Asc functions

The Chr function returns the string that corresponds to an ASCII code while the Asc function converts an ASCII character or symbol to the corresponding ASCII code. ASCII stands for “American Standard Code for Information Interchange”. Altogether there are 255 ASCII codes and as many ASCII characters. Some of the characters may not be displayed as they may represent some actions such as the pressing of a key or produce a beep sound. The format of the Chr function is

Chr(charcode)

and the format of the Asc function is

Asc(Character)

The following are some examples:

Chr(65)=A, Chr(122)=z, Chr(37)=% , Asc(“B”)=66, Asc(“&”)=38

<<Lesson 7>> [Contents] <<Lesson 9>>