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 2003/xp version (4.0e) of the application can be downloaded here.
The latest MS Office 2007 version (4.0e) of the application  can be downloaded here.
The user manual for the program in pdf format can be downloaded by clicking here. 
Note: I recently replaced my Winxp development system with a Win7 64 bit system.  I installed Office 2003 under Win7 (running in 32 bit mode).  Excel 2003 runs fine in that environment, but the BoatRecords application has some problems.  Whenever I start BoatRecords (click on the Records button) Excel aborts.  If I opent the VBA Editor before clicking the Records button everything works fine.  I could not determine why this happens.  I then installed Office 2003 in the xp emulation under Win7.  In that environment BoatRecords works fine with or without the VBA Editor open.  So, if you have a similar environment (Win7, Office 2003) you may need to run this application in xp emulation mode.  The Office 2007 version of the application runs fine in the Win7 environment.
Should you encounter this Excel abort situation, the only workaround I have been able to come up with is to open the VBA Editor and set a program breakpoint before starting BoatRecords.  This normally how I debug the program, but in this case it has the result of making the Excel abort problem go away.  I've published a page on how to use the VBA Editor to clear the problem.  You can view this page by clickinge here.  Hopefully I will find a permanent fix for the problem in the near future.
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. 

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 have a Maintenance Log workbook from a version earlier than 3.15 it will have to be manually upgraded for the current version of BoatRecords.  As with earlier versions of the Inventory workbook, send me your Maintenance workbook and I will attempt to convert it to the current format.
If you would like to test the program with fairly large inventory and maintenance databases you can download recent versions of the databases for my yacht, Sarah, here

The download file is a Zip file created with Winzip as shown in the screen capture above.
The contents of the Zip file are described below.
  • BoatRecordsVBA.xls - This is the BoatRecords application as a MS Excel 2003 Workbook. 
  • SarahInventory.xls - This is the Inventory workbook for SV Sarah.  It is a large inventory that can be used to exercise and learn the application before starting to inventory or log maintenance on your own boat. 
  • BlankInventory.xls - This is a blank Inventory workbook in MS Excel 2003 format.  Make a copy of this workbook and use that copy for the inventory of your boat.
  • BlankMaintenance.xls - This is a blank Maintenance log workbook in MS Excel 2003 format.  Make a copy of this workbook and use that copy for the Maintenance Log for your boat.
  • Several JPEG files.  These are cabin layout graphics for a few boats.  The P424LayoutA is Sarah's cabin layout. 
Although the development and maintenance of this application is accomplished in Office 2003, I have tested it in the Office 2007 environment on my Dell Netbook, and I now provide an Office 2007 version for download (see links above). 
 
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
    • 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 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 a generic inventory item to which specific items are linked.  For example I might have several quarts of motor oil on board from different brands and in different size containers.  For the purpose of tracking stocking levels I don't care about brands and would track the stocking level of the generic brand.  However for locating specific containers of oil I would still like to have records for the individual brands or containers.  I might keep the Rotella-T in a different locker than the Pennzoil.
  2. Provide an archive utility for the maintenance log to reduce the size, but keep the records accessible.
  3. Provide an undo facility
  4. Provide a comprehensive logging of all actions and and report generator for the logs (a preliminary step for the undo faciltiy).
  5. Integrate with the BoatProvisionVBA application I am developing to provide shopping lists for provisioning an off-shore passage.
Development, Testing and Using Environments
BoatRecordsVBA was developed and primarily tested in Excel 2003 under Winxp.  I have recently moved this development environment to the xp emulation environment under Win7.  In the short term I plan to keep the development in Excel 2003 to avoid backward compatibility problems.  In order to provide the Excel 2007 version of the application I save the program in Excel 2003 using the conversion utility provided by Microsoft.  Although Excel 2007 will open an Excel 2003 workbook it does not convert the links to the VBA libraries.  The Excel 2003 program version, when loaded by Excel 2007 is still looking for the Office 2003 VBA libraries, which do not exist in the Office 2007 environment unless both Office 2003 and Office 2007 are installed on the system.  Hence, the reason I provide two different versions of the application.
Below are the environments in which I currently run and test the application.
  • Winxp and Office 2003 - My navigation computers
  • Winxp and Office 2007 - My netbook
  • Win7 and Office 2007 - My primary computer
  • Win7 with xp emulation and Office 2003 - My development environment on my primary computer.
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.