The Excel Macro was created by Dr. D. Pierrot ([email protected]) using the code developed by Ernie Drs. Lewis and Doug Wallace in1998.

Purpose:

The EXCEL Macro spreadsheet calculates the concentrations of inorganic carbon system parameters if two of the parameters are provided along with temperature, pressure, total   phosphate concentration, and total silicate concentration.  The four inorganic carbon

parameters are:

TA = total alkalinity

TCO2 = total inorganic carbon

pH = acidity

pCO2 or fCO2 = partial pressure of CO2  or fugacity of CO2

What it does…

This macro is a direct adaptation of CO2Sys.BAS program from Dr. Ernie Lewis and Dr. Doug Wallace (See the “INFO” sheet in the macro for contact information).

Input:

-    Salinity, Temperature and Pressure (or depth) (input conditions). If left empty, the Pressure is assumed zero in the calculations.

-    Total Si (optional) and Total Phosphate (optional). If left empty the total Si and Total P concentrations are assumed zero in the calculations.

-    Two (2) known CO2   parameters (TA, TCO2  , pH,pCO2  or fCO2)

Temperature and Pressure (or depth) at which the parameters wish to be calculated (output conditions)

Output (for both “input” and “output” conditions):

-    The other three (3) CO2 parameters.

-    Contributions to the alkalinity.

-    Carbonate speciation.

-    Degree of saturation (Ω) for calcite and for aragonite.

-    Revelle Factor.

The last four (4) are referred to as “Auxiliary Data” in the macro.

What it doesn't do…

Unlike CO2Sys.BAS, this macro does not calculate the sensitivity of the output on the input (referred to as “Partials” in the original program).

Layout of the workbook:

The Macro is in an Excel workbook that is comprised of three worksheets (see tabs in lower lefthand corner of workbook):

INFO: This contains important information on the calculations that are performed.  This information is copied verbatim from the original program developed by Lewis and

Wallace.  This compiled QuickBASIC program is available at the CDIAC website: http://cdiac.esd.ornl.gov/oceans/co2rprt.html .

The topics are listed in the left-hand column and can be accessed by clicking on the appropriate cell.

INPUT: This is the location where the user can specify which apparent carbonate

dissociation constants to use, as well as the dissociation constant for KHSO4-  , the pH scale for the input/output pH values, and the Total Boron formulation.

The constants can be changed by clicking on the appropriate cell, which is then

highlighted. Note that, for reference, the constants used are listed on the data page (columns N and O) but only after the calculations are performed.

Data: This worksheet is where the data is input and where the output data appears after clicking on the red "START START START..." ribbon at the top of the worksheet (see below)

How to Run the Macro.

1.   Open the file in Excel …

a.   Remember to enable macros.

b.   If Excel doesn’t give you the choice, goto “ToolsàMacrosàSecurity…” and select “medium”. Close the file andre-open it.

2.   In Sheet “INFO”:

You can select which section of the program you want information on by selecting the appropriate option from the left column. The information will be listed in the text box in the middle of the page.

3.   In Sheet “INPUT”:

a.   Select the set of CO2  constants you want to use for the calculations b.   Select the KHSO4

c.   Select the pH scale of your data.

d.   Select the Total Boron formulation to use.

4.   In Sheet “Data”:

a.   If copying from another Excel file, it is suggested to only paste the VALUES in the cells.

b.   Input your data in the appropriate columns for Salinity, Temperature (oC)   and Pressure (dbars). Total Si and Total P (in μmol/kg SW) are optional. If left blank zero concentrations will be used as default

c.   Input the CO2 parameters in theirrespective columns. If more than two are entered, the FIRST TWO from the left will be used. You may use different sets of parameters in different rows.

d.   Set the output conditions at which you want your results.

e.   Click the red “Start” Button located on the top left part of the “Data” sheet.

f.   Calculations will stop when an entire row of data (columns “A” to “L”) is empty.

g.   You can either clear your data (columns “A” to “L”) or clear the results    (columns “L” to the end) by clicking on the appropriate button located on top of the column “M” in this sheet.

Note:

The input conditions are the conditions during analysis.  For

shipboard analyses the input pressure would be 0 and the input

temperature would be the temperature of analysis (usually 25oC for TA or TCO2).

TA and TCO2  are not dependent on input temperature.

For pH,pCO2 or fCO2 the analysis temperature is critical in the calculations.

The output conditions are the temperature and pressure at which

you wish to calculate the parameters. For instance, if you measure the pH  at 25 ˚C and wish to determine the pH at the in situ depth and temperature at which the sample was obtained, you would list 0 and 25 as input

P(dbars) and t(oC), and use the in situ P(dbars) and t(oC) as output.

5.   After the program starts:

a.   You will be asked if you entered your data properly…this gives you a chance to cancel your action.

b.   Result calculations are separated in two sections: Results for the “Input Conditions” are posted in columns “Q“ to “AK” and are labeled “in” .

Those for the “Output Conditions” are posted in columns “AM” to “BB” and are labeled “out” .

c.   You will be asked if you want to calculate the “Auxiliary Data”. This

corresponds to Ω, Revelle Factor…etc…any column right of the “pCO2 “ column in both results sections. Choosing “No” will save time.

d.   If Pressure, Total P or Total Si are missing, equal to -999, or -9, their

value is set to zero and the calculation performed anyway. In this case, the whole corresponding row is colored in red and column “BC” (labeled “Subflag”) will mention which input parameter was set to zero.

Known Issues…

The program cannot handle TEXT instead of NUMBERS in the data and will create a run-time error ‘ 13’ (Type Mismatch).

Once in a while, the program will encounter an empty cell which will also create a run-time error ‘13’ (Type Mismatch) instead of ignoring it.  This is because there are several different types of empty cells in excel, including text cells without text, blank cells and deleted cells that all look like the same empty cells but have a different underlying source code.

The easiest way to solve this problem is to do the following:

Select the entire columns A to L.

Go to the menu “Edit à Replace…” (Ctrl + H)

DO NOT ENTER ANYTHING in the “Find What:” and “Replace With:” fields.

Show more options by clicking on the “Options >>” button

SELECT “Match entire cell contents” option

Click “Replace All”

This should solve the problem