Excel DiveLog Guide

Using the Excel DiveLog workbook DiveLog.xlsm

This workbook, developed in Excel 2007 and compatible with Excel 2010, incorporates a dive log, recreational dive planner tables and nitrox calculation tables. It requires a windows platform with a minimum screen resolution of 1366x768 (when using the DiveLogdata entry form).

Whilst the workbook can be used with MAC versions of Excel, there are some differences; it has not been fully tested against a MAC and there are no guarantees that it is fully compatible. Initial testing on a MAC has not found any issues, other than the data entry form may not show exactly as it does for Windows.

The dive log includes a data entry form that includes automatic data validation and pressure group calculations for consecutive dives.

Whilst the calculations in this workbook have been prepared using PADI dive planners, it has not been endorsed by PADI. Divers using this workbook are advised make their dive plans using the training provided by PADI withtheir charts and methods. This workbook is purely a tool provided to supplement those provided by PADI.

Introduction

This Workbook is based on the PADI dive planners for AIR and Enriched Air (nitrox), both metric and imperial versions. The main purpose is to act as an electronic dive log, though it may also be used as a dive planner. This utility does not have PADI approval, and users are advised to use this only as a guide. For making proper planned dives, divers are advised to undertakethe necessary training and should refer to the approved PADI tools (PADI dive tables, Electronic Recreational Dive Planners, etc.)

Workbook security

The workbook makes use of VB Macros, so when first invoked, it is likely a security alert popup will be displayed. Users will need to select the radio button to enable the content for the macros to function correctly; other options may include making this a trusted document so that continual alerts are not triggered every time the document is opened.


Figure 2, Windows security alert /
Figure 3, MAC security alert

Worksheet protection

All the worksheets in the workbook are protected without passwords. Users are advised against making undue changes because many of the macros and user-defined functions use the worksheets; in particular much use is made of the RDP (Recreational Dive Planner) worksheets (which include the colour scheme).

Macro protection

The VBA project modules have been password protected. Users should contact the author via email regarding any issues and enhancements.

Workbook contents

The workbook is made up of many worksheets and also includes VB modules and forms. The worksheets, in order, are: -

  1. DiveLog; this can be completed manually by filling in the cells appropriately, though the preference is to use the Show Page button
  2. Notes; this is a reprint of the notes from the PADI dive planners
  3. AirMetric; a dive planner worksheet in metric units for use with air
  4. AirImperial; a dive planner worksheet in imperial units for use with air
  5. EANx32Metric; a dive planner worksheet in metric units when using Nitrox with 32% O2
  6. EANx32Imperial; a dive planner worksheet in imperial units when using Nitrox with 32% O2
  7. EANx36Metric; a dive planner worksheet in metric units when using Nitrox with 36% O2
  8. EANX36Imperial; a dive planner worksheet in imperial units when using Nitrox with 36% O2
  9. MaxDepthCalculator; a maximum depth calculator for use when using Nitrox
  10. EADforNitrox; the Equivalent Air Depth for Enriched Air (Nitrox) table
  11. OxygenExposureTable; the Oxygen Exposure Table for Enriched Air (Nitrox) table
  12. DiveRecord; this is automatically populated when a valid dive number is selected in the DiveLog worksheet and the Show Page button is clicked. This is the record used for printing purposes.
  13. Lists; these are read-only listed used to validate the DiveLog data

When the workbook is first opened, a menu is added to the toolbar; this will appear differently on a MAC than on a Windows platform. On a MAC, the toolbar appears as a floating popup menu bar. On a Windows platform, the menu is added to the Add-ins ribbon. The DiveLog worksheet will also be presented first. The DiveLogmenu is only visible when the DiveLog workbook is active, and will be removed from the Excel application when the workbook is closed.

The Dive Planner worksheets are used in automatically calculating pressure groups; this requires the diver to correctly select the gas mixture for his/her dive. It is recommended to set these first, though the values reliant on the gas mix should be corrected later.

The diver need only concern him/herself with the DiveLog worksheet; other worksheets are protected (without passwords) and should require not modification.

Using the DiveLog Worksheet Menu

Whilst the worksheet can be populated manually, it is recommended that the Show Page button is clicked to pop up the DiveLogdata entry form, which includes some features to make populating the worksheet easier; including dive times and pressure group calculations. None of the values offered need to be accepted; the user is free to override any of the values presented.

Some of the columns in the worksheet are shaded; this is because they will be populated with formulae when a dive number is entered. For this reason it is recommended that the diver completes the DiveNumber cell first. Once a Dive Number is entered, the shaded cells on that row will be automatically populated with formulae. Many of the cells in that row will also have validation criteria added to them, so the diver needs only to select from the drop-down box the appropriate values. Completing the dive record may be easier by using the DiveLogform (click the Show Page button on the DiveLog menu found in the Add-ins ribbon bar).

Correct operation of this worksheet and its formulae depends on the rows being completed sequentially by row; there is no restriction on whether the rows are in date order, though this is expected. The Pressure Group calculations and the surface interval calculations are made assuming the rows are in date order; the cumulative dive time calculations are made assuming contiguous rows of data.

The DiveLog Toolbar

The DiveLog Toolbar has four buttons and appears in different locations depending on whether the platform is Windows or MAC: -

  1. Show Page; this pops up a form not unlike a page from a PADI dive log, from which a dive record can be selected or completed, then printed.
  1. Show Planner; this form is used to plan dives and works in conjunction with the PADI dive tables

  1. Imperial Units(or Metric Units); this button is only enabled when using the MaxDepthCalculator worksheet; it is used to change the calculations from Imperial units to Metric units and vice versa. The caption on this button will change depending on the units in use on the MaxDepthCalculator worksheet
  1. Print Record, which will print the selected dive record (if the selected row on the DiveLog worksheet has a valid dive number) or if the active worksheet is the DiveRecord.

Using the DiveLogData Entry Form

This form is the preferred method for updating the DiveLog record. Records can be edited or modified and optionally printed from this form.

To use the correct RDP planner, select the Gas and Units before entering the dive data; see Figure 6.

Entering a New Dive Number

Start by selecting a dive number (top left box of the form). If this is a new record, select the blank item at the end of the list. This will force the next empty row in the DiveLog worksheet to be used and populated with the a dive numberbased on the dive numbers already used (the next highest number will be used); see Figure 7.The dive number cannot be changed on the data entry form, but there is no restriction on the worksheet though duplicate dive numbers will be highlighted.

The boxes on the form that are shaded are populated by formulae (with values calculated from the DiveLog worksheet)as the form is filled; the diver is free to change these values if he/she so desires. Once changed, the formula in the cell on the DiveLog will be overwritten; to re-establish the formula (say in the event of a mistake) it will be necessary to switch to the DiveLog worksheet and delete the appropriate cell contents; the macro will automatically repopulate the empty cell with the appropriate formula.

Filling in a dive number on the DiveLog worksheet will add new formulae to the given rowin the DiveLog (but only if the appropriate cells are empty); validation formulae will also be added in the form of drop-down boxes to those cells where a limited range of values can be entered.

Printing a Dive Record

The Print button found on the DiveLog form will print the displayed record in a format that is very similar to a page found in the PADI dive log.

Page Setup

The page is configured to print as an A5 page on A4 paper, in landscape orientation. The following figures show the Page Setup forms. To make adjustments, first select the DiveRecordworksheet, then use print preview to show the page; on this form there this is a print setup button which can be used to set the page for printing.

Page

The page is defined to print in landscape orientation.
The form has been designed to fit on an A5 page, so when printing in landscape, the image will occupy half of the page. This size image will fit in the PADI dive log folder once folded and punched.
  • Landscape button is selected.
  • Fit to 1 page tall is selected.
  • Paper size is A4.
/
Figure 8, Page Setup, Page tab

Margins

The Left margin is 1cm wider than the right margin to allow for punching.
The page is centered vertically for aesthetics. /
Figure 9, Page Setup, Margins tab

Header/Footer

The custom footer contains only my recognition /
Figure 10, Page Setup Header/Footer tab

Page Setup - Sheet

No Changes to the default are made to the Sheet tab. /
Figure 11, Page Setup Sheet tab

The RDP planner worksheets

Figure 12shows part of the AirMetric Recreation Dive Planner worksheet.

There are no user configurable cells in any of the RDP planner worksheets; they have been protected (no passwords) against accidental changes. The RDP worksheet used in the macros is dependent on correct information being supplied with regards to gas used and units if using nitrox.

The RDP planner calculator

This form uses the appropriate Planner worksheet based on the selected radio button.

Figure 13uses the AirMetric worksheet planner. It shows that after a dive to 20 metres for 40 minutes the Pressure Group is R; a safety stop is mandatory. After a surface interval of 3 hours and 1 minute, the pressure group is A. After a second dive to 20 metres for 39 minutes the pressure group is U.

If dives are planned beyond the limits for depth or time, a suitable message will be displayed on the form in red text. Figure 14shows the result of only having a surface interval of 1 hour and planning a second dive of 20 metres for 30 minutes; it reports the issue and advises on the maximum dive time.

Minimum surface Interval

To calculate the minimum surface interval between two dives, enter the two dive profiles in the form, then click the Minimum SI button; A popup form will show the minimum value in (hh:mm format) and ask if you wish to use this. Clicking Yes the RDP planner calculator form will be populated with this figure and the appropriate pressure groups will be calculated. Ignoring the suggestion, the diver is free to enter his/her own value for the surface interval.

The surface Interval calculated is that required to reach pressure group A.

Figure 16and Figure 17show two consecutive dives, both to 20 metres and both for 20 minutes; Figure 16shows the values using the suggested surface interval and Figure 17show the values from entering a different value.


Figure 16, Calculation with suggested Surface Interval /
Figure 17, Calculation without suggested Surface Interval
Author: Peter Roberts
Email: / Page 1 of 9