BoatProvisionVBA
Using MS Excel to Develop Recipes, Menus and Provisioning Lists
I have used MS Excel for a number of years to develop lists of items required to provision SV Sarah for off-shore passages.  All of these lists, were just that, a record of the items I needed to provision for the voyage, manually entered into Excel. I only used Excel to manage and sort these provisioning lists.  What I really wanted was for Excel to generate the provision lists based on a planned passage and menus for that passage.  BoatProvisionVBA is my first attempt to implement such an application.
BoatProvisionVBA is an integrated Excel VBA application that uses a voyage outline,  a set of recipes, and a menu plan for the voyage to develop a provision list.  My goal is to eventually integrate BoatProvision with BoatRecords to allow that provision list to be compared with current provision inventory (not an insignificant programming task) and provide a shopping list.
This program is still under development but I have decided to provide an initial, but incomplete release.  Although it works today (12/1/2010), I am continuing to develop and test this application.  That said, anyone interested in downloading and testing the program for their own use will get my support. 
The latest version (2.01b) of the program for Excel 2010 can be downloaded by clicking here.
The latest version (2.01b) of the program for Excel 2003 can be downloaded by clicking here.
 
Version 1.99 adds the feature to print all recipes for a voyage or portion there of.
Version 1.98 includes the feature to generate a provisioning list from the meal plans for a voyage.
Version 1.97 included changes to the Menu workbooks used by the program.  If you have created Menu workbooks using an earlier version of the program, please read the ReadMe1.pdf file (included in the zip file) before using the program.
Version 2.00 includes two feature enhancements.  One to edit the provision tables workbook and the other to add a JPEG picture to the recipe instructions.
Version 2.01 moves my development platform from Office 2007 to Offic 2010.  As far as I can tell there is no difference between VBA environments of Office 2007 and 2010.  So will issue updates only for Excel 2010 and Excel 2003.  If someone encounters a problem running the Excel 2010 version under Excel 2007 please let me know.
The contents of the download zip file is shown below.
The first four files in the zip make up the application.  BoatProvisioner2007VBA.xlsm is the Excel 2007 application workbook and contains the userforms and VBA code.  MenuBook.xlt is the Excel 2003 workbook template for the Menu files.  ProvisionTables.xls contains the standard names and values used by the program.  For example, this workbook contains the standard names for ingredients that are used in the recipes.  This workbook has been populated with the names I have used in testing the application.  In the course of using the application you will likely add many additional names to these tables.  RecipeBook.xlt is the Excel 2003 workbook template for the recipe files.  Note that I use a mix of Excel 2007 and Excel 2003 workbooks in this applicatioin.  I have had no problem processing the Excel 2003 workbooks in the Office 2007 environment.  For now, I have kept the non-application workbooks in Excel 2003 to minimize the conversion processes in the Office 2003 environment.
The additional files, RecipeBookMine.xls and RecipeBookNYT.xls (no longer included in the zip files), contain some recipes I have used in the past and a few recipes from an old version the New York Times Cookbook.  These workbooks were provided as recipe examples that could be used to get familiar with the application without having to first create your own recipe database.
The screen captures below provide some information on how the application works.  Recent (version 1.96b) documentation in pdf format can also be downloaded by clicking here.  I have also created a video tutorial page for this program. 
The functions of the program are described below.  This provides a quicker review than downloading the program or the documentation.  However the form pages and program functions described are from an early stage in the program development.
The Recipe Page
Before the application can be used to develop a provision list for a voyage, there must be a collection of recipes from which the menu and then the provision list can be developed.  So some time will have to be expended in transferring recipes from your personal files and cookbooks to a database maintained by this program.  This is accomplished through the Recipe Create/Edit Page shown on the left.
The recipes can be entered with as little or much detail as desired.  In the screen capture I have selected a recipe I entered for Arroz con Pollo.  It is not necessary to enter the preparation steps, as I have.  If you don't intend  to generate the provision list, then it is also not necessary to enter an ingredients list for each recipe (see below). 
The value of the recipe steps recorded in the database is it might allow you to share the meal preparation duties with more of the crew.  Then again, that might have negative value.
The Ingredients Form is used to enter an ingredient list for a specific recipe.  The screen capture on the right shows the ingredients list I have created for the Arroz con Pollo recipe.  An ingredients list is only necessary if you want the program to generate a provision and shopping list for the voyage or if you want to use this program as your recipe repository.
If the object is just meal planning and provisioning, the recipes can be as simple that shown on the left.  I have called this recipe "Chicken Dinner".  It has a single ingredient, 1 whole chicken, and no recipe steps.  This recipe will allow me to build meal plans with chicken as the main dish and also create a provision list based on the number of Chicken Dinners I plan for the voyage.  Similar simple recipes for other dishes (e.g., Pork , Beef , Fish, etc.) could be entered into the database and used to generate a varied meal plan with a provisioning list, but without all of the preparation steps nor detailed ingredients lists.
The Voyage Page
Once the recipe database has been populated with a sufficient number of recipes for a voyage menu, then you can begin the process of developing that menu using the other pages in the application.
The first page in that development is the Voyage Page.  This form creates a worksheet in the Menu workbook for the voyage data.
On this page you identify the Departure and Destination for the voyage, how many passage legs are planned, the estimated duration for each leg and the number of crew on each leg.
From this information the program can derive the number of meals that need  to planned and the number of crew that need to be served at each meal.
The Menu Page
After the voyage parameters have been establish you can begin the menu planning on the Menu Page.
Here you step through each meal on each day of each leg of the voyage and create a unique menu for each of the meals.
In the screen capture on the left I have created a menu for the dinner meal on the first day of the first leg of the planned voyage.  The items in the menu list come from either one or more of the recipe workbooks created in the Recipe Page, or from a food item list, also created from the Recipe Page.
As you add a menu for each of the meals the ingredient items from the Recipe workbook and Food Items workbook are added to the provision list for this leg of the voyage.  The quantity of each item in the provision list is adjusted for the number of crew members that must be served at each meal.
The creation of a provision list has been moved to the Print page and the "Create Ingredients List" button has be removed from this form.
The Print Page
Once the menus have been established for at least one of the legs then the Print Page can be used to print the menu for that or all legs.
The program can also provide a print of all recipes used in the menu.  And finally a Provision List can be printed for each leg, derived from the contents of the menus and the ingredients list for each recipe.
When this application is integrated with the BoatRecords application, this Print Page will also be used to create a shopping list by comparing the Provision List to the Boat Inventory and identifying the provision items that are currently in the inventory.
Further development will include a page to record the menu plan usage during a voyage.  I expect that the meal plans will not be used in exactly the order of that created in the voyage workbook.  For example, the meal plan may have called for mostly pre-cooked or packaged meals for the first few days, but fair weather and the cook's comfort may have allowed the use of the other meals for those days, saving the pre-cooked and packaged meals for times of rough weather to come.  I want to provide a quick and intuitive means to record usage against the meal plan and allow the meal plan to be updated underway.  I also want the recorded usage to update the BoatRecords inventory to record the food and other items that have been consumed.  This will allow for the generation of intermediate shopping lists for layover destinations on the voyage.