At manufacturing sites, routine inspections are carried out several times a year on pest monitors. The number of pests on the monitor are counted and recorded. These numbers then need communicating back to the site so they can understand any issues that need addressing. Sites can be very large and this data can be quite difficult to interpret.
I created a visualisation to show the number of pests in different monitors on the plans of a site for communication with stake holders.
I chose VBA to achieve this as i had familiarity with the technology and other reporting was done with Excel and Word.
The visualisation is created by generating oval objects with VBA and manually positioning the ovals on the plans.
The locations are then saved.
The size and colour of the ovals can then be changed with VBA based on the pest data in the workbook.
The range is automatically generated.
The inspections can be cycled through to see changes throughout the year, with summaries generatable for different time periods.
Summaries are also generatable for counts for different pest types:
- All Insect
- A limit check (A limit set for the number of pests in a monitor)
- House and Blow flies
- Stored Product Insects and others.
The examples below show mock Electronic Fly Killer Data.
Change between total pest numbers and counts for different types of pests
I created buttons to interact with the worksheet
Data is pulled from a data sheet and the size and colour of the oval shapes are changed based on that data.
The locations of the oval shapes are then recalculated based on the size change.
The data sheet is generated for each inspection with VBA from a single Table.
Which allows the oval shapes key to come from this data (the "unit name" A1 ) and then the oval shapes can be accessed based on this unit name. In the example below the object is called "EFK 10" and is accessed by the Worksheets("Bubble Plot").Shapes("EFK 10") property where the colour and the shape is changed.
bubble_plot_ws = Worksheets("Bubble Plot")
'colour ovals
bubble_plot_ws.Shapes(s_BubbleProperties.label(i)).Fill.ForeColor.RGB = RGB(r, g, b)
'resize ovals
bubble_plot_ws.Shapes(bubblelabel).Width = bubblesize
bubble_plot_ws.Shapes(bubblelabel).Height = bubblesize
The visualisation can be interacted with directly on the worksheet or a Power Point can be automatically generated.
VBA is used in the workbook to copy a range to the clipboard and then paste it into Power Point.
plot_rng.CopyPicture
myslide.Shapes.Paste
Eventually the plan was to create word reports and excel reports from this data as well
I created a BubblePlot Class Module
This Class Module has functionality seperated out into other Class Modules
