|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.
|BoatRecords version 3.8 (later versions are now available for download), I
believe, satisfied the all of those goals.
|The latest MS Office 2007-2013
version (5.02) of the application can be downloaded
The latest MS Office 97-2003 version (5.02) of the application can be downloaded here.
|The Program Download File Contents|
|The Excel 2007-2011 download file is a zip file created by Winzip as shown above. The contents of this zip file are described below.|
The Excel 2003 download file is identical to the Excel 2007-2011 file except the contents are Excel 97-2003 workbooks.
|The user manual for the program in pdf format can be downloaded by clicking
|I have also started to publish a series of videos on using this application here.|
|Note: In 2010 I 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.
I then installed Office 2003 in the xp emulation
under Win7. In that environment BoatRecords works fine. 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 my Win7 environment.
|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 keep my Inventory and Maintenance databases in the Excel 97-2003 format. I use the BoatRecordsVBA program in both the Office 2007 and Office 2003 environments. Keeping the databases in Excel 97-2003 format avoids a file conversion in Excel 2003 when loading and saving the files. Excel 2007 can load and save these files with no conversion delay.|
|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,
email me a copy and I will add it to this zip file.
|What Does BoatRecordsVBA Do?|
|BoatRecordsVBA performs two major functions:|
|How Do I Use BoatRecordsVBA|
|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
|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
Win8.1). In order to provide the Excel
2003 version of the application I save the program in Excel 97-2003
format using the conversion utility built into Excel 2007.
Below are the environments in which I currently run and test the application.
Almost all of the testing is done in the Win8.1, Office 2007 environment. I only use the Office 2003 environment for testing when a problem has been encountered under Office 2003 and cannot be reproduced under Office 2007.
|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 is one peristent problem I have been unable to resolve.
Occaisionally BoatRecordsVBA will cause Excel to crash and abort.
This seems to be caused by an internal bug in 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've found to get around the problem once it occurs is described on this page.
|Excel 2007 and 97-2003 Workbook Formats|
The individual Inventory and Maintenance workbooks can be maintained
in either Excel 97-2003 or Excel 2007 formats and accessed by the
program in either Excel format. That is, the Excel 2007 can
load, process and save the Inventory and Maintenance workbooks in
the Excel 97-2003 format. The Excel 97-2003 program version
can also work with the workbooks in Excel 2007 format; however, that
requires a free conversion add-in available from the MS Office
I use this program in both Excel 2007 and Excel 97-2003 formats as I use it on several differenct computers. I keep the Inventory and Maintenance workbooksin Excel 97-2003 format and do not use the conversion add-in. This reduces the load and save times for the workbook when working in Excel 97-2003. Excel 2007 can load either format without a conversion add-in and I cannot detect a difference in the time required to load and save workbooks in the two formats.