These are projects posted by the students of Dr. Gove Allen at Brigham Young University. These students have taken one semester-long course on VBA and generally have had no prior programming experience

Tuesday, April 14, 2015

Searching books and assign emlpoyees to work

In March 2015, one of the Orem public library's staff members had requested that I initiate a VBA project with the intent of streamlining and automating a process via VBA that will ultimately help visitors locate and check-out their desired book or collection from the library's vast book shelves. The project also would assist library administrators to more efficiently scheduling work shifts for library staff, a task currently taking 20 minutes per schedule. Below is a list of the project's objectives:

a.      A drop-box list with the names of all of books. When people click the title of books, all information related to the said books, including its complete title, author’s name (s), call#, annotation, and page will pop up immediately in different cells with the proper formatting.

b.      A shift schedule. A sheet will be created and named “Worker_schedule”. On this sheet, there are 3 tables. The table is located in the top left side which details the five workdays for the library. The second row of the table provide the number of workers and staff person’s name are displayed. The second table is on the bottom left side. Names of available workers are marked in red. The third table is on right side, it will show the most efficient shift schedule directly when cells in the first table are changed. Based on the city’s needs, I applied the VBA knowledge that I have learned from school to solve their problems. I will introduce the data I got from the Orem city as following.

The city of Orem has a datasheet like the first figure below (until now, they have about 200 rows and 10 columns, but this database is constantly getting bigger). Based on the client's requirements, I will first use VBA macro to reformat the information to another sheet. They require that once the book name or other items are inputted in the datasheet, the macro will automatically update the information accordingly and into another sheet with specific formatting for each row. When users select any book from the database, they will immediately see a table like the second figure below.

No comments:

Post a Comment

Blog Archive