BoatRecords
Using MS Excel to Manage Boat Inventory and Maintenance Records
BoatRecords is the latest (and I hope the last) rewrite of the boat inventory and boat maintenance applications I've developed using MS Visual Basic for Applications (VBA) and MS Excel.  The Inventory and Maintenance applications were originally developed independently and then integrated by the InventoryMaint application.  That integration I viewed as just as a transitory application to a better solution.

There were six primary design goals for the re-write of the InventoryMaint application.
  1. Allow users to implement a new version of the program without any changes to their database (except when the new program version requires changes to those databases - see item 6)
  2. Eliminate the need to save the workbook with the code (InventoryMaint) except when the code has been changed (never for users)
  3. Eliminate the multiple UserForms, one for each function in the applications
  4. Implement the application as an Excel Add-In, not just as a workbook application
  5. Provide a configuration tool that will allow the application to be used for cabin layouts other than the Pearson 424.
  6. Allow for the ability to convert Inventory databases and Maintenance Log files when a new version of the application required a new database format.
BoatRecords version 3.8 (later versions are now available for download), I believe, satisfied the all of those goals. 
The latest MS Office 2007-2016 version (5.03f) of the application can be downloaded here.  This should be compatible with any of the currently supported versions of MS Office.  Important:  This program does not run in the 64-bit version of Excel.  With Excel 2010 a 64-bit version can be installed, but the default install is 32-bit.  I only became aware of this recently (October, 2019).  I cannot have both 32-bit and 64-bit versions on the same computer, so this program is only available in the 32-bit version.  My current development environment is Excel 2019, 32-bit.
The latest MS Office 97-2003 version (5.02) of the application  can be downloaded here.  That will be the last update to the program in 97-2003 format.
The Program Download File Contents
The Excel 2007-2016 download file is a zip file created by Winzip as shown above.  The contents of this zip file are described below.
  • BlankInventory2007.xlsx - This is an Excel 2007 workbook created with the worksheets used that will contain the Inventory data.  This workbook is blank and ready for Inventory data.
  • BlankMaintenance2007.xlsx - This is an Excel 2007 workbook ready for Maintenance data.
  • BoatRecordsVBA2007.xlsm - This is the macro-enabled Excel 2007 workbook that contains the BoatRecordsVBA program.

The Excel 2003 download file is identical to the Excel 2007-2016 file except the contents are in Excel 97-2003 file format.
The user manual for the program in pdf format can be downloaded by clicking here. 
I have also started to publish a series of videos on using this application here.
Currently my development system is Win10, Office2016.
The major change in version 3.5 was to modify the forms to fit on a netbook screen.  The previous screens were too tall and I did not want to implement scrollbars.  I have not updated all of the documentation to reflect the new form layouts.  That will take some time as all of the screen captures will have to be replaced.  The data fields and controls on the form have not been changed, they have just been relocated on the form.  No new features were implemented in 3.5.

Version 3.6 provides the Configure tool on the Tools Page that can be used to create a layout for other than Pearson 424s.  In previous version this required manually editing the Layout Worksheet in the Inventory Workbook.

Version 3.7 implements the Inventory Import function from the Tools Page.  This allows Inventory workbooks created by previous versions of the program to be upgraded to the current version.  This will likely be required whenever I make a significant change to the Inventory workbook format.  With the release of BoatRecords version 3.7 the base version number for Inventory workbooks has been established at 3.6.  That is, BoatRecords version 3.7 can open and process any Inventory workbook of version 3.6 or later.  An Inventory workbook from a version earlier than 3.6 must be imported before the workbook can be access with BoatRecords version 3.7 or later.  When a user attempts to open an Inventory Workbook from an earlier version, the program will notify the user that the workbook must first be imported.

The minimum version level for an imported Inventory workbook is 3.15.  If you have an Inventory database from an earlier version than 3.15, the import tool will not be able to upgrade this file to the current level. In this case please send a copy of your Inventory workbook to the webmaster email address at the bottom of this page.  I will attempt to manually convert your inventory workbook to the current format.

Version 3.8 implemented a Temporary Status for Inventory Items and Containers.  An example of this Temporary Status would be the loan of an item to another boat owner.  This implementation required several changes to the format and content of the Inventory Workbook.  In order to use version 3.8 and later of BoatRecordsVBA with any inventory generated by an earlier version (3.15 - 3.7) will require that inventory to be imported into this release.  This version also includes a Minimize button on the main form.  This reduces the application form to a single small window, which can be moved out of the way to work with other applications, but still keep the program active.  This feature was implemented for my Netbook whose small screen is completely filled by the main form.

Version 3.9 fixed a few bugs in the temporary status feature and added that feature to the Locker Page (an oversight in the previous verion).

Version 4.0 added the Find page to provide more efficient searching for Inventory records.  This first also included an initial implementation indices for the database.  This allows nearly all of the fields in the Inventory record to be entered via drop-down lists rather than typing. 

Version 4.2 is the first program version to be developed under Excel 2007 and Win7 rather than Excel 2003 and Winxp.  I am now doing the development and most of the testing in the Excel 2007/Win7 environment.  The Excel 2003 version is created from the Excel 2007.   With the help of one of the users I found a problem with the backup of Inventory workbooks that was introduced into the program after version 4.0.  Version 4.2c should fix this problem.  If you have been using the backup feature with version 4.2, I recommend you update to 4.2c and create new backups with that version.  Inventory backups created by version 4.2 are not reliable.  This user also helped me identify a problem with the Inventory Report generation that can render the Inventory workbook inaccessible to the program running under Excel 2007.  This problem is fixed in version 4.2e.  The same user also made me aware that the program has a problem with date strings when the host computer does not use the USA standard (mmddyyyy).  Version 4.2f should fix this problem.

With version 4.5 a new feature, Generic Inventory Item, has been implemented.  This feature is intended to facilitate the locating and tracking of common inventory items that are not co-located in a single container or locker.  An example of the use of a Generic Inventory Item is engine motor oil that may be in several containers in different lockers on the boat.  The Generic feature allows these items to be tracked and located using a single inventory item.  An overview and demonstration of this feature has been provided on the Inventory Video Page.  Version 4.54 made some minor changes to the Generic processing and included a number of fixes to outstanding issues/problems.

Version 4.54b adds a significant increase in the logging of inventory action.  Hopefully this will help me to debug some of the outstanding problems with the program, in particular the generation of blank rows within the inventory worksheet.  To prevent this additional logging from affecting the program performance I have also added to code to segment the log files to a maximum of 31 days.  The Log file display form has been modified to display any of the file segments available.  The update also corrects a program bug in the inventory reports.  Version 4.54c through 4.54h fix a number of program bugs.  Version 4.55 also fixes a number of bugs, in particular a bug in the Configure Tool.

Version 4.57 added the capability to enter pictures of Inventory items.  The pictures must be JPEG files.  This version also includes a number of bug fixes.

Version 4.58 added the capability to modify maintenance task names or move/copy a task to another maintenance system, and update previously recorded tasks with the modification.

Version 5.00 implemented a tool to export the Inventory and Maintenance workbooks as xml files.  This currently provides no functionality.  It is planned as a means to migrate the application for Excel VBA to Visual Studio and no longer require Excel.  This may not happen, but I have at least started to re-write the application.

Currently BoatRecords does not provide an Import utility for the Maintenance Log workbook.  There have been no functional changes to this workbook since version 3.15. 
If you would like to test the program with fairly large inventory and maintenance databases you can download a recent version of the databases for my yacht, Sarah, here.  Please note that I used to keep my Inventory  and Maintenance databases in the Excel 97-2003 format.  I used the BoatRecordsVBA program in both the Office 2007 and Office 2003 environments.  Keeping the databases in Excel 97-2003 format avoided a file conversion in Excel 2003 when loading and saving the files.  Excel 2007 and later can load and save these files with no conversion delay.  Currently my workbooks are in Office 2016 format.
Boat Layout Diagrams
The  BoatRecordsVBA program will display a boat layout diagram, such as the one above for Sarah, in JPEG format.  As you see I have numbered each of the lockers on Sarah.  Those numbers are used by the program to identify the lockers in the Inventory.  It can be helpful to have a diagram of your boat available to label each of the lockers or other storage locations.  This diagram was developed by another Pearson 424 owner in MS PowerPoint and modified by myself for Sarah's locker configuration.  I then saved the layout in JPEG format, which is the only format the program can handle at this time.

I no longer include the boat layout jpegs in the BoatRecordsVBA zip file.  I have created another zip file with the layout diagrams I have, which can be downloaded here.  Anyone who has developed a layout diagram in any graphic format, and wishes to share that layout with others, please email me a copy and I will add it to this zip file.
Currently the BoatRecordsLayout.zip file contains diagrams of the following boats:

  • Pearson 424 Layout A (Sarah)
  • Pearson 424 Layout B
  • Columbia 8.7 (Vela Llena)
  • Columbia 9.6 (Pelagic)
  • Crealock 37
The purpose of the boat layout diagram is to provide a graphical depiction of the location of the various lockers.  That was useful in a very early version of the program that used only a locker number to specify the location of inventory items.  Once the locker descriptions were added to the program, the layout diagram was no longer necessary.  I haven't referred to Sarah's layout diagram for years.  The program will display any JPEG diagram you provide, but no prograam function depends on the layout.  The program runs just fine without one.  It may be useful when building the locker configuration, helping with the locker descriptions, but after that ...
What Does BoatRecordsVBA Do?
BoatRecordsVBA performs two major functions:
  1. Creates and maintains an inventory database for a boat.
    • Organizes the inventory into the cabins and lockers of the boat
    • Further organizes the inventory into containers (e.g., toolboxes)
    • Organizes the inventory items into logical groups (e.g., engine service parts) for tracking on board stocking levels
    • Provides categories and sub-categories for the inventory items to make them more easily retrieved and updated
    • Organizes similar items under a single Generic description.
    • Provides listings and reports on the inventory
    • Provides tools to maintain and update the database.
  2. Creates and maintains a database of maintenance tasks for a boat
    • Organizes the tasks under maintenance systems (e.g., main engine)
    • Supports both scheduled and unscheduled tasks
    • Supports the scheduling of tasks by operational hours in addition to date
    • Generates reports on the status and history of the maintenance tasks
    • Provides tools to maintain and update the database.
How Do I Use BoatRecordsVBA
  • I have entered a nearly complete inventory of everything on-board Sarah.  This is currently over 2,000 items.  This is a much more detailed inventory than most would want.  I've done it so that I can exercise all of the program features with a fairly large database.  Having done so, I have found the detailed database very useful.  The downside of an extremely detailed database is that it takes more time to maintain.  A detailed inventory database that is out of date can be worse than no inventory database at all.
  • I record the expiration dates on critical items such as flares and medication.  The Expiration Report identifies all inventory items whose expiration date has passed.
  • I use the Container feature to inventory all of my toolboxes and the various parts bin I have on-board.  When I need a 12mm box wrench, I immediately know if I have one, in which toolbox I put it and where I put that toolbox.
  • I use the Generic Item feature to track similar consumable items that are not co-located or have different detailed data as a single inventory item. 
  • I use the Kit feature to track my important consumables and spare parts.  Right now I know I need to purchase an Oil filter for my Yanmar engine.  I know that without searching through lockers and containers.
  • On a passage, I use the Locker Inventory Report to track the use of provisions and develop re-provision shopping list when in port.  I keep most non-refrigerated food items in 3 or 4 lockers.  I generate the Locker report after the initial provisioning, storing and inventorying of the provisions.  The locker report listing is placed in each of the provisions lockers and updated by pen or pencil when an item is used or added.  At the end of the passage I use the annotated locker report to update the inventory.
  • I use the maintenance log to record not just maintenance tasks, but also events.  For example I log everytime I switch fresh water tanks to provide a measure of my water usage.  Similarly I record when I switch propane tanks.
  • I use the Look Ahead maintenance report to identify the scheduled tasks I need to perform in the next week, month, or further out.
  • I use the integration of the maintenance tasks with inventory kits to record my use of consumables and parts.

 
What Will BoatRecordsVBA Do in Future?
That's a big question.  It is doing nearly everything I need right now, so further development is not a high priority.  I will add new features as I find them necessary or useful.  If someone using the program were to request some new features I will give that request serious consideration.
There are a few items I believe would improve the application
  1. Provide an archive utility for the maintenance log to reduce the size, but keep the records accessible.
  2. Convert the maintenance log worksheet into a semi-relational database, with different record formats for different tasks.
  3. Provide a comprehensive logging of all actions and and report generator for the logs.  A limited logging capability was implemented in version 4.2 and expanded in 4.5.  This is primarily a tool for debugging the program.
  4. Integrate with the BoatProvisionVBA application I am developing to provide shopping lists for provisioning an off-shore passage.
Development, Testing and User Environments
BoatRecordsVBA was initially developed and tested in Excel 2003 under Winxp.  Since 2010 this development environment has been implemented under Excel 2007 and Win7 (now Win10 and Excel 2016). 
I try to test this application thoroughly before posting a new version on this web page; however, I am developing this application primarily for my own use and generally test it only on my Inventory and Maintenance databases.  This provides me with confidence that the program will handle large inventory and maintenance log databases, but I don't do a lot of testing of the program starting from scratch with empty databases (unless I make a program change in that area).  Recently a user, trying to initially build an inventory from a blank workbook, ran into a lot of problems.  I think I was able to resolve all of his problems with version 4.0e.  However, I expect different users with different needs will uncover other bugs in the program that I do not find in my testing.  I will attempt to fix any of these problems that are reported to me.
Excel Abort Problem
There was one peristent problem I was unable to resolve in earlier versions of Excel (<2017).  Occaisionally BoatRecordsVBA would cause Excel to crash and abort.  This seems to be caused by an internal bug in earlier versons of Excel that is triggered by executing the BoatRecordsVBA program.  This also happens with my OffShoreNavigationVBA application.  These are both very large VBA programs.  I have written several other VBA applications, which have not encountered this problem, but they are much smaller than the applications that do encounter the problem.
When the problem occurs it can persist.  That is, repeated attempts to run the application will cause the Excel abort.  The only reliable way I found to get around the problem once it occurs is described on this page.
This problem has not ocurred on any of my PCs since moving to Win 10 and Office versions after 2013.  That provides support to my guess this is a bug in earlier versions of Windows and/or Office.  If anyone encounters the problem with Excel 2017 or later, please let me know.  If you run the program under Excel 2013 or earlier you may encounter this problem.