Use VBA programming in Excel to improve personnel data management efficiency

I. Overview

The Human Resources Department carries a large amount of data information about enterprise personnel labor, personnel, training, social security, archives and documents, and the amount of information is accumulated over time, constantly perform the "points" work in stages-summarize statistics and archive, and sometimes perform "differential" work-find, sort out and generate data reports for specific information, and manage human resources of large and medium-sized enterprises, daily information and data processing workload is quite large.

Nowadays, computers are widely used in personnel management and office applications. Almost every office computer is equipped with Microsoft Office software. Most people use Word and Excel, in particular, Excel is widely used in daily data processing. Excel replaces the previous manual tables, allowing you to quickly, intuitively, and efficiently manage simple databases. However, in the face of a large number of repetitive, tedious, and sequential jobs, to improve work efficiency, programming is required to associate data and automate transaction data processing.

Through the programming practices of personnel wages, social security, and education application software in recent years, I feel that the programming management of personnel trivial and variable data, from the ease of use of programs to the efficiency of programming, excel VBA programming is the best choice.

Ii. Features of VBA program in Excel

1. program code (macro) is encapsulated in electronic documents. The Excel application platform under office is used. Data and programs can be stored in the same Excel document, you can also save the program in an Excel document, call worksheet data in another or more Excel documents, or call data in other databases.

2. Programs Written in VBA do not need to be compiled, and there is no library file. The programs are "green. When the electronic document is running, the system prompts whether to run the "macro" to determine whether to run the program. The "Digital Signature" is used to confirm the program developer and ensure the program security, the "engineering protection" password is used to prevent the designer's program from being modified and viewed.

3. VBA (Visual Basic for Application) is developed from Visual Basic. Many people have learned the basic language. Its Syntax conforms to people's conventional thinking habits and is easy to learn. With its object-oriented programming method, VBA provides powerful functions and is easy to learn and master. For a general user, it allows the user to record various operations of the user through the macro recorder, convert it into VBA code, so that the user can easily automate the daily trivial work, greatly improve work efficiency. For programmers, VBA can directly apply the powerful functions of office software and support application development and application. Therefore, it makes application design and development easier and faster.

Iii. Implementation of VBA database programming in Excel

1. Database Requirements and database pointer implementationMethod

Generally, we store a specific data set in a worksheet to form a data table. columns represent fields and rows represent records. In the program, we need to control the data in each cell in this rectangle, the "Record Pointer" must be introduced to determine the database insertion, deletion, modification, record jump, and first and last records. VBA does not provide controls or components in this aspect. Therefore, to manage worksheets in programming, you must make corresponding restrictions and write code.

First, when creating a sheet data table, you must fully consider the required fields. The starting "row" is artificially fixed, and the fields (columns) after the program are compiled cannot be randomly increased or decreased, the starting "row" position cannot be changed; otherwise, you need to modify the program again.

The data in the programmed control worksheet. The record pointer is the row number (rows) of the record. The first record is fixed, and the row number of the last record is automatically determined by the program to obtain the maximum value of the row number, the record pointer value can only be between the first record (minimum value) and the last record (maximum value. Example:

Dim record as integer defines the record pointer as an integer

Dim recordmin as integer defines the first record as an integer

Dim recordmax as integer defines the ending record as an integer

Recordmin = 5' the first record is in row 5th

Recordmax = recordmin 'assigns an initial value

'Assume that column 3rd is a key field, that is, the cell record in this column cannot be empty.

Sheets ("sheet1"). Select 'activate or select sheet1 Worksheet

Do until cells (recordmax, 3). value = "'exit the loop when it is determined to be null

Recordmax = recordmax + 1

Loop 'loop ends to get the row number of the End record

Record = recordmin jump to the first record

Record = recordmax 'jump to the end record

Record = record + 1' the record points to the next row

If record> recordmax then record = recordmax 'avoid exceeding the end record

Record = record-1 'record refers to the top row

If record <recordmin then record = recordmin'

2. database maintenance interface implementationMethod

Database maintenance can be directly processed in workbooks. However, to improve the appearance of the program interface, program integrity, reduce accidental data errors, and improve data entry efficiency, a form (userform) is usually used) and controls to add, insert, delete, and modify data, and transfer and export data. The process of programming implementation is briefly summarized as follows:

(1) Insert a user form (userform );

(2) add controls and command buttons based on the database processing fields and implementation processing functions;

(3) form initialization programming: Associate the form with the workbook data in the corresponding workbook, set "Data Pointer", initialize the control, and associate it with the corresponding field (column) data, program the command button. Example 1:

 

To implement the name list box, use the following statement:

With listbox1

Record = recordmin record pointer to first record

Do until sheets ("basic information library"). cells (record, 3). value =''

. Additem sheets ("basic information library"). cells (record, 3). Value

Record = record + 1

Loop

End

To implement a photo image frame and use the image call function (loadpicture), you only need to use the following statement:

On Error resume next

Photoname = "./Photo/"+gh1_0000.jpg" 'under the photo directory

If Dir (photoname) <> ''then' if the photo file exists, call

Image1.picture = loadpicture (photoname)

End if

3. Database report output implementationMethod

Excel has powerful report implementation and printing functions. Many application programming now transfers data to a workbook for printing. It is extremely simple and easy to program and implement database printing in Excel, you can use the recording macro to edit and control the printing of a specific worksheet in the program. You can also transfer the data in multiple tables to a newly generated worksheet as required to print the data, to achieve complex report output.

In programming the report output program design, it basically calls the VBA function in Excel to set the table, especially the report browsing function, you can adjust the print width of rows and columns according to your needs and data length, and set the print page by yourself, which greatly reduces programming difficulty and improves the flexibility of report output. The process of programming implementation is briefly summarized as follows:

(1) set the table line of the worksheet. The program example is as follows:

Sheets ("sheets1"). Select 'select Worksheet

Range (cells (), cells (2 + I, 13). Select select the area where the table is located.

With selection. Borders (xledgeleft) 'available recording macro

. Linestyle = xlcontinous

. Weight = xlthin

. Colorindex = xlautomatic

End ......

(2) design the Print dialog window of the Report. Example 2:

In preview, adjust the settings. 3. The statement for previewing is as follows:

Windows (BM + Nian + ".xls"). Activate select the workbook

Sheets (March). Select select Worksheet

Activewindow. selectedsheets. printpreview browse and select a worksheet

Printed statement:

Activewindow. selectedsheets. printout copies: = textbox1.text 'output to the printer based on the number of copies

4. Program encapsulation interface implementationMethod

Use menubars to create a menu bar. The original menu bar of Excel is automatically deleted. Use menus to add a menu item and use menuitems to add a menu item. The following is an example of the statement:

Sub setmenu ()

On Error resume next 'ignore Error

Application. Caption = ""

Menubars ("mymenu"). Delete

''Delete custom menu

Menubars. Add ("mymenu ")

'Custom menu items

Menubars ("mymenu"). Menus. Add caption: = "wage data maintenance"

Menubars ("mymenu"). Menus ("wage data maintenance"). menuitems. Add caption: = "wage activity item input", onaction: = "gzlr"

Menubars ("mymenu"). Menus ("salary data maintenance ")_

. Menuitems. begingroup = true

Menubars ("mymenu"). Menus ("wage data maintenance"). menuitems. Add _ caption: = "personal income tax processing", onaction: = "SDS"

(Omitted)

End sub

It can implement a menu interface of 4. When the program is running, you do not need to enter the worksheet to ensure the integrity of the program.

 Iv. Notes for VBA Application

1. Data attributes of column data

The Worksheet in VBA programming has a database-related nature. columns represent fields and rows represent records, the character attributes of the column field cells are consistent, especially for columns that store "values". The column cells must be numbers rather than characters. When you input data, do not enter "space" into the value grid. Otherwise, an error occurs during calculation. Maintain data in the program as much as possible to reduce errors.

2. Key reference name

In a program application, the workbook, worksheet, column location, and first row location are referenced as "absolute address" in the program implementation and cannot be changed at will. Otherwise, the program runs incorrectly. Of course, if necessary, you can also encrypt the workbook so that you cannot open the workbook when you leave the program. The workbook is stored in multiple subdirectories, so that others cannot easily see it.

In the past three years, I have used VBA to program and design multiple database applications to solve some small problems. I feel that the programming efficiency is much higher than that of FoxPro, which is especially suitable for programming and management of personnel data, low programming difficulty and easy database creation. If you are tempted, the bookstore has a large number of VBA programming books. Through learning, it will certainly become your programming tool and a good helper to improve work efficiency.