The Business Analytics Programming

Business Analytics Programming

Table of Contents
1. Introduction............................................................................................................................1
1.1 Background related to Business Issues.........................................................................1
1.2 Literature Review............................................................................................................1
1.3 Objectives of the VBA Algorithm..................................................................................2
2. VBA Impacts...........................................................................................................................2
3. Users Operational Manual....................................................................................................4
4. Technical Documentation....................................................................................................12
5. Conclusion.............................................................................................................................13
References.....................................................................................................................................14
Appendix.......................................................................................................................................15

1. Introduction
The main objective of this project is to create VBA application which is used to help
automate an organizational process. The purpose of this application is to automate and secure the
employees’ personal information in an organization.
A VBA application is developed with the help of the VBA algorithm and all the necessary
functionalities will be completed by creation and updating of an employee management database
for the ABC Company.
1.1 Background related to Business Issues
In this project, the business scenario revolves around the ABC Company, which needs an
effective VB application for employee management system, because at present this company
lacks an employee management application, due to which the manual way of managing the
employee like employee records and information is followed. Thus, the existing management on
keeping the employee information and records takes place manually by hand-written records on
paper/books. Employee information is not always trustworthy because it is handwritten and
might have the possibility of human error, for instance, the data duplication problems can take
place, and the managers cannot determine the required information. During manual filling, there
is also a possibility of data misplacement. Therefore, it is important to create an employment
management application for this company to ease its management operations.
1.2 Literature Review
As per (Bagade, Shende and Sambre, 2016), the research paper aims to manage the
database of the Municipal corporations. This researchers presents the usage of how the employee
Information Management System supports to maintain the information of employees. It can
facilitate by keeping the employee information up-to-date. It ensure accuracy of the information
to be stored. Additionally, the employee information system comprises of all types of details like,
personal information of employees, service associated records, leave records, salary statements,
maintenance of attendance records etc. The researches says that it benefits by helping to easily
track all the required employees’ details from the joining date till the end of their service.
1

1.3 Objectives of the VBA Algorithm
The objective of the VBA algorithm is used to automate the ABC company process. As,
instead of employee management applications, the company uses manual record
keeping methods to store employee records. Employment management represents the power of a
company, as employees are considered as the company’s strength, the manual record keeping can
impact the effectiveness of the company’s business processes. It can even impact business
growth. Thus, the employee management system is essential for ABC. The following aspects are
included for the VBA application:
 Graphical user interface on Microsoft Excel
 Demonstrate the programming skills which includes the Array, VBA objects like
User forms, variables, data types, conditional structure, loops, procedures,
function etc.
 Also demonstrate clear understanding of programming usability, readability and
correctness.
2. VBA Impacts
Basically, the employees are an organization’s strength and help to improve it. The
application/system of employee management consists of crucial work associated and significant
personal information of the employees. The purposed employment application is used to ensure
that the following are met (Bhargav, 2019):
 Improve the efficiency of workforce management - The purposed application is used
to monitor and manage the employee information, and it can quickly spiral into the
administrative quagmire. If managers track the employee data manually, it only raises the
likelihood of human errors and it makes the business vulnerable to regulatory and legal
risks. So, the application is used to spiral view the employee data, and it does not create
business vulnerability. It makes the employee information work for the business and not
against it.
 Employee engagement - This system increases employee engagement, and it is a
valuable asset for business. It can realize its full potential, then it creates a perfect
environment in which management continuously engages with the employee. It is a
2

powerful organizer of employee and employee conversation. To create a platform for
tracking and evaluating employee development, the manager partners with workers in the
development, motivation and goal setting of a viable career path. Therefore, it improves
worker maintenance.
 HR data metrics and Analytics - Basically, the system of employee management is
used to capture, store and organize the information of employment in a conventional
electronic form, but it is not fully exploited if the data collected cannot be analyzed for
the invaluable insights which can help resolve the deep and hidden business challenges.
The trends and metrics have created the root cause of company improvement. Therefore,
the application of employee management is useful for HR staff for time consuming
administrative tasks, and it can concentrate on business innovation to the next level.
 Mitigate the risk compliance - This application also ensures risk compliance. Generally,
the employers have regulatory and legal obligations to employee and employee
information. Compliance can be a problem with companies constantly trying to keep
tracking the new rules that apply to their company. Thus, the employee management
system created it easier for business to capture, repot, analyze, and document the
employee information.
 Reduce the paperwork–The purposed solution reduces paper work, as the existing
system of book keeping to record the employee information requires manual efforts. In
such case, the employee information recorded is not always trustworthy because it is
hand written and might have human error. Therefore, a new application is created which
does not require any paper work.
 Securing the employee Information–In existing system, the employee information and
records are stored manually and documented. Therefore, the information is not always
trusted due to possibility of human mistakes. The paper documents can be misplaced and
have data duplication, therefore, the purposed application secures the employee
information. The company contains sensitive information about their employee like their
personal details, and unauthorized person handles the remuneration of the employee
records, driving license numbers, phone numbers, social security numbers, dependent and
spouse details, and also handles the bank accounts, which is dangerous. Hence, the access
3

control is centralized, where the users are assigned privileges based on their
responsibilities.
The purposed employee management application is even used to ensure the greater
understanding of the ABC Company by designing and analyzing the database suitable for the
company. The purposed database is used to store the employee personal information.
Additionally, it improves the skills and managerial work for the managers to record employee
information.
3. Users Operational Manual
The user manual for employee management application is presented below (Larsen,
2017).
First, open Excel sheet, and create homepage, database page and print page for the proposed
application. The below screenshots demonstrates the creation of these pages.
The home page contains two buttons such as employee details, and close. The employee
details button is used to open the employee management application, and close button is used to
close the program.
4

The database page is used to store the employee data which contains the employee
number, employee name, address 1, address 2, address 3, phone number and designation. If a
user needs to enter the employee information using an application, the data is automatically
stored in the data page.
The print page is used to print the employee information and takes the data from the
applications (Löffler and Posch, 2013).
Before opening the employee management application on homepage, a user form must be
created for the employee management applications as demonstrated below.
The user forms include three frames,
The first frame consists of the following (Morgado, 2016),
 Two options buttons - Select Employee No and select Employee name, it is used to select
the particular employee number, and employee.
 Two combo box – Combo Box 1 for Select Employee No option button and Combo box 2
for selecting the Employee Name, it is used to display the stored employee number, and
employee name.
 Search buttons – It is used to search the particular employee name and number from the
stored database.
The second frame consists of personal information such as employee number, employee name,
5

address 1, address 2, address 3, phone number and designation.
The third frame consists of five buttons,
 Add – This button is used to enter the employee information such as it is used to select
the particular employee number, and employee (Nevogt, 2019).
 Save – This button is used to save the entered employee information to the database.
 Delete – This button is used to delete the employee information from database.
 Close – This button is used to close the employee management application.
 Print – This button is used to print the saved employee information from the database.
Once the user forms are created successfully for the employee management system, go to
excel homepage, click employee details button to open the created application, as demonstrated
below (Shepherd, 2011).
Afterwards, click on Add button to enter the employee details as presented below.
6

Once the employee details are added, click on save button to save the employee details. It
automatically stores the data to the excel data page, which is represented below (Simpson and
Stockman, 2013).
Also another employee detail is added as demonstrated below.
7

If a user needs to view a particular employee information, use search method to select the two
option buttons like select employee number and select employee name. Then, search select
employee number based search to select the select employee number and click on the combo box
which displays the stored employee numbers as demonstrated below (Stivala, 2018).
8

If user selects 112 of employee number and clicks on search then it provides the full
information of the selected employee number, as shown below (Vine, 2012).
The user can take the print out of employee details in PDF format by clicking on print button,
and save the file as demonstrated below.
9

The printed employee details are represented below (Walkenbach, 2013).
The user can delete the employee details by clicking on the delete button, and it shows the
delete message as depicted in the below screenshot (Walkenbach, 2013).
10

Next, click on yes to delete the employee information from the database, and it
successfully deletes 112 employee information.
At last, click on close, to close the application. This step is illustrated in the following screenshot
(Walkenbach, 2013).
11

4. Technical Documentation
Technical Document for the Employee Management Application
Dim
Dim is used for declaration on the created application
Private Sub cmdadd
The add command is used to enter the employee information Add button I used to add the
employee information (Walkenbach, 2018).
Private Sub cmdDelete_
The delete button is used to delete the employee information from the database.
Private Sub cmdSave
The save button is used to save the entered employee information on database.
Private Sub cmdPrint
The print button is used to print the saved employee information to PDF format.
Private Sub cmdClose
The close button is used to close the application and ends the program.
Private Sub cmdSearch
12

The search button is used to search the selected employee numbers or employee names
from the stored database.
Private Sub OptionButton1
This option button is used to select a particular employee number, if option button 1 is
selected then it means, combo box 1 will be enabled as true at the same time combo box 2 will
automatically be enabled as false.
Private Sub OptionButton2
This option button is used to select the particular employee name, if the option button 2 is
selected then it means, combo box 2 will be enabled as true at the same time combo box 1 will
automatically be enabled as false.
Private Sub prComboBoxFill
Combo box 1 is used to display only stored employee numbers to search the selected the
employee information based on employee numbers.
Private Sub prComboBoxFill2
The combo box 2 is used to display only stored employee name to search the selected
employee information based on the employee names.
User Forms Initialize
User forms initialize is used to initialize and call the combo box, prComboBoxFill2,
CmdSave button cmdDelete button, and the Frame2.
5. Conclusion
This project is successfully created using the VBA application for the ABC Company
because employee management systems are being adopted by various sectors in the real world
irrespective of their size, to benefit the company’s day-to-day processes, like record
maintenance, record updating and tracking.
13

References
Bagade, T., Shende, A. and Sambre, S. (2016). Employee Management System at Municipal
Corporation, Hinganghat. International Journal of Research, 3(3).
Bhargav, S. (2019). 5 Advantages of an Employee Management System. [online]
Technotification. Available at: https://www.technotification.com/2019/02/employee-
management-system-advantages.html [Accessed 27 Dec. 2019].
Larsen, R. (2017). Engineering with Excel. Hoboken: Pearson Education.
Löffler, G. and Posch, P. (2013). Credit risk modeling using Excel and VBA. Chichester: John
Wiley & Sons.
Morgado, F. (2016). Programming Excel with VBA. Berkeley, CA: Apress.
Nevogt, D. (2019). Employee Management System: How to Start Using One. [online] Hubstaff
Time Tracking Software. Available at: https://blog.hubstaff.com/employee-management-system/
[Accessed 27 Dec. 2019].
Shepherd, R. (2011). Microsoft Access 2010 VBA macro programming. New York: McGraw-
Hill.
Simpson, A. and Stockman, J. (2013). Access 2007 vba programming for dummies. Hoboken,
N.J.: John Wiley & Sons.
Stivala, A. (2018). Vba Programming. US: Tritech Digital Media.
Vine, M. (2012). Microsoft Access VBA programming for the absolute beginner. Boston, MA:
Course Technology PTR.
Walkenbach, J. (2013). Excel 2007 power programming with vba. Hoboken, N.J.: Wiley.
Walkenbach, J. (2013). Excel 2010 power programming with vba. Hoboken, N.J.: Wiley.
Walkenbach, J. (2013). Excel 2013 power programming with VBA. Hoboken, NJ: J. Wiley &
Sons.
Walkenbach, J. (2018). Excel VBA Programming for Dummies. For Dummies.
14

Appendix
Dim blnNew As Boolean
Dim TRows, i As Long
Private Sub cmdClose_Click()
If cmdClose.Caption = "Close" Then
Unload Me
Else
cmdClose.Caption = "Close"
cmdNew.Enabled = True
cmdDelete.Enabled = True
End If
End Sub
Private Sub cmdDelete_Click()
TRows = Worksheets("Data").Range("A1").CurrentRegion.Rows.Count
Dim strDel
strDel = MsgBox("Delete ?", vbYesNo, "Delete")
If strDel = vbYes Then
For i = 2 To TRows
If Trim(Worksheets("Data").Cells(i, 1).Value) = Trim(ComboBox1.Text) Then
15

' Sheet1.Range(i & ":" & i).Delete
Worksheets("Data").Range(i & ":" & i).Delete
txtEmpNo.Text = ""
txtEmpName.Text = ""
txtAdd1.Text = ""
txtAdd2.Text = ""
txtAdd3.Text = ""
txtTel.Text = ""
txtDesignation.Text = ""
Call prComboBoxFill
Exit For
End If
Next i
If Trim(ComboBox1.Text) = "" Then
cmdSave.Enabled = False
cmdDelete.Enabled = False
Else
cmdSave.Enabled = True
cmdDelete.Enabled = True
End If
cmdNew.Enabled = True
16

cmdClose.Caption = "Close"
End If
If Trim(txtEmpNo.Text) = "" Then
cmdSave.Enabled = False
cmdDelete.Enabled = False
Frame2.Enabled = False
Else
cmdSave.Enabled = True
cmdDelete.Enabled = True
Frame2.Enabled = True
End If
End Sub
Private Sub cmdNew_Click()
blnNew = True
txtEmpNo.Text = ""
txtEmpName.Text = ""
txtAdd1.Text = ""
txtAdd2.Text = ""
17

txtAdd3.Text = ""
txtTel.Text = ""
txtDesignation.Text = ""
cmdClose.Caption = "Cancel"
cmdNew.Enabled = False
cmdDelete.Enabled = False
cmdSave.Enabled = True
Frame2.Enabled = True
End Sub
Private Sub cmdPrint_Click()
Dim strFile As Variant
Dim InitialName As String
InitialName = "EmpNo_" & Trim(txtEmpNo.Text)
Worksheets("Print").Cells(3, 6).Value = txtEmpNo.Text
Worksheets("Print").Cells(6, 3).Value = txtEmpName.Text
Worksheets("Print").Cells(7, 3).Value = txtAdd1.Text
Worksheets("Print").Cells(8, 3).Value = txtAdd2.Text
Worksheets("Print").Cells(9, 3).Value = txtAdd3.Text
Worksheets("Print").Cells(11, 3).Value = txtTel.Text
Worksheets("Print").Cells(13, 3).Value = txtDesignation.Text
18

strFile = Application.GetSaveAsFilename(InitialFileName:=InitialName, _
fileFilter:="Excel Files (*.pdf), *.pdf")
If strFile <> False Then
Sheet1.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:=strFile, _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=True
End If
End Sub
Private Sub cmdSave_Click()
If Trim(txtEmpNo.Text) = "" Then
MsgBox "Enter Emp. No.", vbCritical, "Save"
txtEmpNo.SetFocus
Exit Sub
End If
Call prSave
cmdClose.Caption = "Close"
cmdNew.Enabled = True
19

ThisWorkbook.Save
End Sub
Private Sub prSave()
''''' Save the Data
If blnNew = True Then
TRows = Worksheets("Data").Range("A1").CurrentRegion.Rows.Count
With Worksheets("Data").Range("A1")
.Offset(TRows, 0).Value = txtEmpNo.Text
.Offset(TRows, 1).Value = txtEmpName.Text
.Offset(TRows, 2).Value = txtAdd1.Text
.Offset(TRows, 3).Value = txtAdd2.Text
.Offset(TRows, 4).Value = txtAdd3.Text
.Offset(TRows, 5).Value = txtTel.Text
.Offset(TRows, 6).Value = txtDesignation.Text
End With
txtEmpNo.Text = ""
txtEmpName.Text = ""
txtAdd1.Text = ""
txtAdd2.Text = ""
txtAdd3.Text = ""
txtTel.Text = ""
txtDesignation.Text = ""
Call prComboBoxFill
20

Else
For i = 2 To TRows
If Trim(Worksheets("Data").Cells(i, 1).Value) = Trim(ComboBox1.Text) Then
Worksheets("Data").Cells(i, 1).Value = txtEmpNo.Text
Worksheets("Data").Cells(i, 2).Value = txtEmpName.Text
Worksheets("Data").Cells(i, 3).Value = txtAdd1.Text
Worksheets("Data").Cells(i, 4).Value = txtAdd2.Text
Worksheets("Data").Cells(i, 5).Value = txtAdd3.Text
Worksheets("Data").Cells(i, 6).Value = txtTel.Text
Worksheets("Data").Cells(i, 7).Value = txtDesignation.Text
txtEmpNo.Text = ""
txtEmpName.Text = ""
txtAdd1.Text = ""
txtAdd2.Text = ""
txtAdd3.Text = ""
txtTel.Text = ""
txtDesignation.Text = ""
Exit For
End If
Next i
End If
blnNew = False
If Trim(txtEmpNo.Text) = "" Then
21

cmdSave.Enabled = False
cmdDelete.Enabled = False
Frame2.Enabled = False
Else
cmdSave.Enabled = True
cmdDelete.Enabled = True
Frame2.Enabled = True
End If
End Sub
Private Sub cmdSearch_Click()
blnNew = False
txtEmpNo.Text = ""
txtEmpName.Text = ""
txtAdd1.Text = ""
txtAdd2.Text = ""
txtAdd3.Text = ""
txtTel.Text = ""
txtDesignation.Text = ""
TRows = Worksheets("Data").Range("A1").CurrentRegion.Rows.Count
If OptionButton1.Value = True Then
For i = 2 To TRows
If Trim(Worksheets("Data").Cells(i, 1).Value) = Trim(ComboBox1.Text) Then
22

txtEmpNo.Text = Worksheets("Data").Cells(i, 1).Value
txtEmpName.Text = Worksheets("Data").Cells(i, 2).Value
txtAdd1.Text = Worksheets("Data").Cells(i, 3).Value
txtAdd2.Text = Worksheets("Data").Cells(i, 4).Value
txtAdd3.Text = Worksheets("Data").Cells(i, 5).Value
txtTel.Text = Worksheets("Data").Cells(i, 6).Value
txtDesignation.Text = Worksheets("Data").Cells(i, 7).Value
Exit For
End If
Next i
Else
For i = 2 To TRows
If Trim(Worksheets("Data").Cells(i, 2).Value) = Trim(ComboBox2.Text) Then
txtEmpNo.Text = Worksheets("Data").Cells(i, 1).Value
txtEmpName.Text = Worksheets("Data").Cells(i, 2).Value
txtAdd1.Text = Worksheets("Data").Cells(i, 3).Value
txtAdd2.Text = Worksheets("Data").Cells(i, 4).Value
txtAdd3.Text = Worksheets("Data").Cells(i, 5).Value
txtTel.Text = Worksheets("Data").Cells(i, 6).Value
txtDesignation.Text = Worksheets("Data").Cells(i, 7).Value
23

Exit For
End If
Next i
End If
If Trim(txtEmpNo.Text) = "" Then
cmdSave.Enabled = False
cmdDelete.Enabled = False
Frame2.Enabled = False
Else
cmdSave.Enabled = True
cmdDelete.Enabled = True
Frame2.Enabled = True
End If
End Sub
Private Sub OptionButton1_Click()
ComboBox1.Enabled = True
ComboBox2.Enabled = False
End Sub
Private Sub OptionButton2_Click()
ComboBox1.Enabled = False
24

ComboBox2.Enabled = True
End Sub
Private Sub prComboBoxFill()
TRows = Worksheets("Data").Range("A1").CurrentRegion.Rows.Count
ComboBox1.Clear
For i = 2 To TRows
ComboBox1.AddItem Worksheets("Data").Cells(i, 1).Value
Next i
End Sub
Private Sub prComboBoxFill2()
TRows = Worksheets("Data").Range("A1").CurrentRegion.Rows.Count
ComboBox2.Clear
For i = 2 To TRows
ComboBox2.AddItem Worksheets("Data").Cells(i, 2).Value
Next i
End Sub
Private Sub UserForm_Initialize()
Call prComboBoxFill
Call prComboBoxFill2
cmdSave.Enabled = False
25

cmdDelete.Enabled = False
Frame2.Enabled = False
OptionButton1.Value = True
End Sub
26