...
The following procedure outlines a typical technician task of adding values found in the value list Excel workbooks.
...
- Obtain the lists of values from each description group.
- Open the VLB workbook from Value List folder.
...
Figure 2. Command icons. You click the magnifying glass when ready to begin searching.
2. Input your expedition number and select search parameter options.
After clicking the search icon (magnifying glass) in the VLB you will prompted to input your expedition number. The macros use this number to set the folder directory it will search. The folder directory/file structure should be consistent with other expeditions; otherwise, the macro will give you an error. Below is an example of the proper file structure. Note the underscore before the expedition folder.
V:\IODP_Share\ALL things DESClogik_DESClogik_EXP371\Value Lists
Next, the macro will open a user form shown in Figure 3 below. Now you will select your search options.
Figure 3. Value Lists Selection User Form.
Now you will select your search options. The user may choose from several options when performing a value search. They are outlined below, and in Figure 4.
Figure 4. Value Lists Search Options.
- Search All Value Lists Option (Callout 1). The user selects this option by clicking the Search All Value Lists button. The macro loops through all value list workbooks and returns all possible matches found for each of the user's values. The macro also returns the workbook in which it found the value, and sorts these returns alphabetically. This method ignores any categories or value lists selected from the drop down menus. The user may select or deselect the Exact Match Only option as well.
- Standard Search Option (Callout 2). The user selects this option by choosing an individual value list workbook in which to search, then clicking the Search button. They must first select a category. This method searches only the value list selected by the user. The user may select or deselect the Exact Match Only option as well.
- Fossil Category Search Option (Callout 3). The user selects this option by choosing the Value List Category, "Fossils" and leaving the Value Lists field empty, then clicking the Search button. The macro will only search through the fossil groups, e.g. benthic forams. The macro also formats the results to make copying and pasting into DESClogik configuration workbooks easy.
- Exact Match Only and All Possible Matches Option (Callout 4). The Exact Match Only option will restrict all search returns to exact matches only. Unselected, the search returns the nearest matches allowing for extraneous characters before and after the user value. For example, with the Exact Match Only option deselected the user value radiolarian might return radiolarian and with radiolarian marl. This search only returns the first match found. The All Possible Matches option will continue to search through a list and return all matches found within. The exact match feature can be turned on or off.
Result
The results of the searches are placed into the various columns to the right of the user's value lislist; the macro found values are always placed in cloumn column B. A detailed search result is shown in Figure 5. , below. Not all search returns have the same format.
Figure 5. Results of a Fossil category search. Values not found are in red header column.
The search return columns are formatted differently for different search options and values. For example, a search for fossils will place genus species in column B and the DESClogik formatted species with its reference in column C. For Search All Value List searches, the macro places all the information associated with the found value in the columns next to B. Since the number of columns and type of information associated with found values vary with different work books, so do the results columns. That is why the column headers are color-coded.
Though the exact placement of found value's associated information columns (i.e. definition or reference) changes, the headers are colored in such a way as to indicate which column is which. Yellow is only used for the user-inputted values (column A). The found values are colored green (Column B). The light blue columns are for found values and their associated information columns. Note that for all the value types, except fossils, the light blue columns appear in the exact order as they are in their respective value list workbooks. Green is for the workbook names of the found values, and lastly, the red column always appears at the far right and is reserved for values the macro was unable to find.
...
The macro specifically references the names of the "Value List Builder.xlsm" and "Value List Builder Scientist.xlsm" workbooks and the sheet "Paste Your Values Here". DO NOT CHANGE the names of the workbook or the first sheet. If you change these names, you can simply rename them the originals, or replace the workbook with a copy from the other two locations.
The file path is specific and the file structure of your expedition needs to follow the example given below. Note, that there is an underscore before the expedition number.