Project
February 2000
TOTAL FLUX & SCALANT PROGRAM
A Membrane System Design Assistant
By
Thomas D. Wolfe
PerLorica Inc.
Project 9C-R193-NTSX
Project Officer
Thomas F. Speth, Ph.D., P.E.
Environmental Engineer
Water Supply and Water Resources Division
26 W. Martin Luther King Drive
Cinncinati, OH 45268
Water Supply and Water Resources Division
U.S. Environmental Protection Agency
26 W. Martin Luther King Drive
Cinncinati, OH 45268
-------
Notice
This work includes the development of an Excel 97 program with Visual Basic for Applications
supporting code. The work is copyright 1999 and 2000 by PerLorica Inc., however permission
is hereby given to freely copy, modify, and distribute this work as derivative products or in whole
or part, provided PerLorica Inc. is referenced as the source work. Excel 97 and Visual Basic for
Applications are copyright by Microsoft, Redmond Washington. Use of Excel 97 and Visual
Basic for Applications requires a separate license from Microsoft.
Permission is given to the US EPA and the National Technical Information Service to reproduce
and sell the following copyrighted material - The Total Flux and Sealant Program - A program
written for Excel 97 or higher January 2000. The following copyright information will be included:
"Reproduced from The Total Flux and Scaling Program, 2000, by T.D. Wolfe with permission
from PerLorica Inc.
Abstract
This works covers the development of a program - the Total Flux and Sealant program (TFS) - for
designing a generic membrane system using Reverse Osmosis or Nanofiltration membranes. In
addition to design of a membrane system the program calculates solubility conditions for the feed
and concentrate streams using a variety of methods. The User is provided a facility to input and
balance a water analysis using a variety of input methods, calculate a carbonate equilibrium based
on pH and temperature, and specify the use of acid or a softening resin to "pretreat" the water before
the membrane system.
The membrane system design allows the user to utilize "generic" membrane elements or create new
types of elements and membranes specific to their own application from either data sheets or
empirical data. The design function calculates pressures, pressure drops, and the ion by ion salt
passage through the membranes. The program returns the ionic concentration of each stream and
calculates the scaling index for many potentially scaling species using either ASTM methods or
methods based on ion complex calculations closely resembling the traditional programs used in the
geological sciences.
The program requires a PC with Excel 97 or higher to run. It is designed so that the reasonably
experienced Excel user can understand the calculations taking place, particularly as regards scaling
and equilibrium conditions.
-------
Table of Contents
Total Flux and Sealant Program
Notice n
Abstract n
Table of Figures iv
EXECUTIVE SUMMARY & INTRODUCTION 1
Acknowledgments 2
CALCULATION METHODOLOGY 3
General Comments 3
Organization of the Spreadsheet - by Sheet/Tab 3
Solubility Methods 5
ASTM Methods 5
Other Minerals 6
RO & NF Calculation Methods 8
General Approach 8
Data Collection 11
Carbonate Equilibrium 11
USER PROGRAM INSTRUCTIONS 13
System and Software Requirements 13
Basic Program Installation and Operation 13
List of Needed Add-In files and typical locations 15
Special Functions 15
Add /Edit Membrane Element Types 15
Modify/Create New Membrane Type 15
Printing 16
REFERENCES 17
SOURCE CODE AND ANNOTATIONS 19
General Comments and Arrangement 19
UtilsAnalysis Module 19
Database Module 23
RO/NF Ions 32
RONFCalc 42
MODMEMBRANES 73
System Design Sheet Code 76
iii
-------
Table of Figures
FIGURE 1 - PROGRAM FLOW 2
FIGURE 2 - BANKING DESCRIPTION 3
FIGURE 3 - INSTALLING THE SOLVER ADD-IN 12
FIGURE 4 - MANUALLY INSTALL THE SOLVER ADD-IN 13
iv
-------
Total Flux and Sealant Program
Project 9C-R193-NTSX
Final Report
Executive Summary & Introduction
This project covers the development of computer program written in Microsoft Excel to assist the user
in the evaluation of reverse osmosis and nanofiltration process applications. This program is capable
of receiving a water analysis and evaluating the analysis as to its self consistency - i.e. charge
balance and carbonate/pH equilibrium. Scaling potential of many common compounds of interest in
membrane systems are calculated, using either ASTM standard methods or a similar to that used by
WaterEQ or Phreeqc based on ion complex formations.
With a validated water analysis, the user may proceed to design a membrane system using "generic"
membrane types - i.e. Thin File Composite (TFC), Cellulose Acetate (CA), or Nanofiltration (NF).
Physical element configurations are included for most standard sizes and a facility for customizing
these sizes and membrane performance parameters is included for skilled and knowledgeable users.
A typical membrane system comprising multiple banks with or without concentrate recycle can be
specified. Once flow rates and recovery values are specified, a detailed calculation on a bank by
bank basis is performed. This returns analytical, pressure, and flow information at each bank in the
system. The program can then optionally or automatically calculate the scaling conditions in the
feed, feed plus recycle, and concentrate streams. Thus the user is provided a generic facility for
designing membrane systems and calculating scale potential throughout the specified system. The
program also contains a simple database facility which allows the user to store analyses and project
information within the spreadsheet itself.
This program, written in Excel with use of the Visual Basic programming language provides a skilled
user an insight into the actual calculations used for the design of membrane systems and provides an
easy to transport framework for exporting scaling calculations to other venues. This program also
provides a spreadsheet ready subsystem which translate the mostly graphical methods provided by
ASTM into Excel based formulas.
-------
Acknowledgments
Dr. William Bourcier of Lawrence Livermore National Laboratory assisted greatly in the development
of the solubility methodology used in the program. His contributions were essential to the
development of the robust solubility calculations.
Special thanks to Dr. Thomas Speth of the US EPA for his continued support during this project and
many insightful comments.
2
-------
Calculation Methodology
General Comments
The Program Flow is designed as follows:
Enter Analytical Data - Ions, pH, Acid, etc.
Enter System Flows, Membranes, etc.
H
Calculate System & Analytical Data
4
Complete Scaling Calculations & Display
II
< Display and/or Print
Results
Figure 1 - General Program Flow
Since this is a spreadsheet, the data and calculations are organized by type and function onto
different "sheets" or tabs. Buttons on the sheets provide an alternate means of navigation between
sections.
Where it was reasonably possible the spreadsheet itself was used to perform the calculations. For
example, the carbonate equilibria are handled in the spreadsheet itself for the feedwater entry. In
addition, Langelier Saturation Index (LSI) and ASTM methods for CaS04, SrS04, BaS04, and Si02
are also handled in the spreadsheet.
Organization of the Spreadsheet—by Sheet/Tih
Only the first two sheets (Analysis & System Design) plus the DataBase sheet require any user
interaction. The other sheets are either display sheets for results or intermediate calculation sheets.
1. Analysis - Sheet for entering analytical and project data. Ions, pH, acid, chlorine
content. Units available are mg/l, meq/l, mg/l as CaCCh, deg F, deg C, deg K.
Already selected flow and recovery data also shows on this sheet if available.
This sheet also alerts the user to a badly balanced analysis and provides a provision
for the input of the free chlorine content. Free chlorine is reacted stoichiometrically
with any Ferrous iron (Fe2+) to oxidize it to Ferric iron (Fe3*)
3
-------
2. System Design - This sheet allows the user to select an element type and then
complete the design of the membrane system. As data is entered, flow and recovery
data, as well as total membrane area and number of elements and vessels are
continually updated. At the bottom of the tab, an approximate TDS for concentrate
and permeate is displayed. This becomes an exact value after calculation is
complete. The user may specify:
• The number of banks in the system
where a bank is defined as an array of
pressure vessels with common parallel
feed. Figure 2 at right illustrates a 2 bank
system arranged in a 4:2 array of
pressure vessels.
• Number of elements per vessel
• Feed, recycle, concentrate, and/or
permeate flow rates
• Flow Units of measure - gpm, gpd,
m3/hr, liter/min, liter/hr
• Percent Recovery, for both the total
system and the internal recovery (with
recycle included).
When data entry/system design is complete the user may "Calculate" the system to
launch the Visual Basic RO/NF calculation engine and the scaling calculations via the
Excel solver.
The user may also add or edit membrane data directly from this sheet.
3. Summary & Streams Sheets - These sheets are "Display Only" sheets and are not
meant to be modified by the user. They collect the data from the calculations,
organize it, and display it in a convenient, readily printable format. All system data is
available in these two sheets.
4. DataBase - This sheet stores project information for later recall. The user enters this
sheet to locate an old project and load it into the system
5. SoluCalcs - This sheet handles the calculations for LSI, CaSC>4, SrSC>4, BaSCU,
SiC>2, and CaF2. With the exception of CaF2 all of these calculation are per the
referenced ASTM method. CaF2 is calculated by using the modified Debye Huckel
calculation with the stream Ionic Strength. The Ksp for CaF2 is corrected for
temperature using the Van't Hoff method.
6. Membrane Data - This sheet keeps the membrane information added or edited by
the user. It also keeps the data for relative salt transport numbers of the different ions
relative to CI or Na ions. Experienced users may modify the ion transport data
directly on this sheet or create a new membrane type altogether. Cells AA15 to AK63
(light blue background) contain this data. The value for each ion is the salt transport
ratio relative to sodium for cations and relative to chloride for anions.
Banking
Bankl
Bank 2
Figure 2 - Banking Description
4
-------
7. InfoLic - This sheet stores the version numbers of the release and contains the
general license disclaimer.
8. Calc- This sheet sets up and solves the complexation equations for computing the
Saturation Index (SI) for the species of interest. Treated Feed (Feed after acid has
been added and any free CI2 reacted with Ferrous Iron), Net Feed (defined as
Treated Feed + Recycle) and Concentrate streams are addressed. The user may
interact directly on this sheet only by clicking the colored buttons.
9. LoqKs - This large sheet contains the base data for each of the complex species
evaluated. Three sets of tables are provided with spaces between them. The spaces
relate to the same spacing on the Calc and ActCof sheets so that cell name
references are consistent. Log Ksp's for the species are provided along with a
temperature dependence polynomial. If no temperature dependence polynomial was
available in the database used then the Van't Hoff method is applied. Since most data
is available for 25 deg C, this provides reasonable accuracy for membrane systems,
which typically operate in the 5 to 60 deg C range.
10. ActCof - This sheet determines the correct activity coefficient to apply to each specie
- both complex and mineral. The Davies equation is used with Ionic strength to
calculate the appropriate activity depending on the net charge of the components or
the overall complex.
Sheets SoluCalcs, Membrane Data, Calc, LogKs, and ActCof contain much documentation as plain
text on the sheet with cited references and detail explanations of the how the individual calculations
are effected.
Solubility Methods
ASTM Methods
CaSC>4, BaSC>4, and SrSC>4 are taken from ASTM D 4692, Figures 1-3 respectively. ASTM D4993 is
the source for SiC>2 solubility. Langelier Saturation Index (LSI) is taken from the definition of LSI in
the literature, but most particularly from Stumm and Morgan (see reference bibliography). ASTM data
was used for these common scale forming minerals since this the accepted industry method.
Calcium Fluoride (CaF2) is computed in a similar manner to the ASTM data except that the Ksp was
taken from the Phreeqc database. The Van't Hoff method is used to correct for different
temperatures.
The ASTM methods are essentially polynomials derived by curve fitting techniques. These provide a
Ksp value as a function of temperature and ionic strength (IS). The corrected Ksp is compared to the
actual ion product for the stream of interest and a percent of solubility or percent of saturation is
defined as follows. This method is also used for calcium fluoride.
Ion Product
Percent Solubility
Ksp corrected
Equation 1
The Langelier Saturation Index (LSI) is computed as follows:
LSI = pH — pHs where
pHs = (pK2 - pKSo + pCa + pAlk)
-------
Equation 2
pH is the pH of the solution, "pHs" is Langelier's calculated saturation pH. Thus a negative LSI
indicates that CaCCh should not precipitate from solution.
• The values of pK2 and pKSo are the carbonate solubility products and are
corrected for temperature. PK2 is corrected for ionic strength as well.
• pCa equals -log([Ca]) where [Ca] = moles/liter calcium, corrected for ionic
strength using the Debye Huckel method.
• pALK equals -logflTotal Alkalinity]), and Total Alkalinity = [HCO3] + [CO3] + [OH]
- [H], [HCO3] and [CO3] are also corrected for ionic strength. Langelier originally
did this with charts from empirical data.
• The value for carbonate (CO3) is computed from the HC03 and pH of the stream
in question.
It should be noted that LSI is not considered useful above an ionic strength of about 0.3. There are
many excellent texts on LSI calculation so it will not be covered in further detail here.
Other Minerals
Less common other minerals which are nevertheless of interest to membrane system designers are
handled by the more rigorous aqueous complexation methods typical of the WaterEQ or Phreeqc
programs (WATEQ4F, Plummer et al., 1976, PHREEQE Parkhurst et al., 1980). A given solution
analysis is first numerically "speciated" into the various aqueous complexes that are present using
thermodynamic stability constants. The saturation state of the solid is then calculated from the true
solubility product, which is not a function of ionic strength, and the ionic concentrations determined
from the speciation calculation.
After a careful consideration of many possible minerals, it was decided to limit the calculations and
display to the minerals of most interest in the operating temperature and operating pH range of most
membrane systems - i.e. 5 to 50 deg C, and pH 4 to 10.
The minerals of interest include:
Analcime
Gibbsite - aluminum hydroxide
Amorphous AI(OH)3 - aluminum hydroxide
Fe(OH)3 (amorphous) - Ferric hydroxide
Goethite - Ferric hydroxide
Siderite - Ferrous carbonate
Vivianite - Ferrous phosphate
Hydroxyapatite - Calcium hydroxy phosphate
Gypsum (also by ASTM methods) - Calcium sulfate
Sepiolite (High pH silica foulant) - Mag Silicate
NaAISi206.H20
AI(OH)3
AI(OH)3
Fe(OH)3
FeO(OH)
FeCCh
Fe3(P04)2:8H20
Ca5(P04)30H
CaS04.2H20
Mg4Si60i5(0H)2:6H20
6
-------
In a very simplistic sense, the approach in this case is to create a list of all possible complex species
which could exist in the solution, and then calculate a series of equations describing these complexes
based on known Ksp data for each complex. The "left over" ions, that is the concentrations left after
the complexes are accounted for, are the concentration of the ions available to form the minerals of
interest. These ion products are then compared to known Ksp values for the minerals of interest and
a Solubility Index (SI) is calculated:
f
SI = Log
IonPRODUCT
Ksp
\
= Log(IP)-Log(Ksp)
Equation 3
A SI negative value suggests under-saturation while a SI positive suggests over-saturation and
possible precipitation. An SI of zero of course suggests the solution is exactly at saturation.
For example, the program calculates complex concentrations for the following primary ferric ion
(Fe+++) complexes:
Fe^n-
Cl- = FeCI++
Fe^n-
SC>4~ = FeS04+
Fe^H-
20H" = Fe(OH)2+
Fe^n-
30H" = Fe(OH)3(aq)
Fe^n-
HCO3- = FeHC03+
Fe^n-
CO3- = FeC03+
Fe^n-
HPO4 - = FeHP04+
Fe* +
H2PO4 = FeH2P04++
For another simpler example, Na and S04 form a complex with one negative charge - i.e. NaSCX
The Log(Ksp) is calculated for this complex at the solution temperature on the sheet entitled LogKs.
The formula for this complex is created as follows, where a represents the activity of the species
under consideration. Activity values are calculated for each complex and each species on the sheet
ActCof, and the "Available" concentration is determined on the sheet entitled "Calc".
[NaS0 ] = \mavauable *aNa\S04\vaaMe *aSQ4* l(Tlog^
4 rv
Equation 4
aNoS04
Of course many other complexes containing both Na and SO4 are also produced, and the amount
available of each ion is affected by all of the other complexes. The solution to the problem must be
obtained iteratively. Using the Excel built in Solver provides a convenient method for solving this
problem and the program implements a series of VBA commands which setups the Solver and then
runs its automatically. The remaining "available" concentrations of each ionic species - that is the
real solution concentration less the amount "complexed" with the various complexes as determined
by the Solver - is the concentration available for mineral formation.
For example, assume we wish to calculate the actual ion product for CaSC>4 - 2H2O in the solution to
determine the scaling potential. We calculated the actual and available concentrations as follows.
7
-------
Specie
Actual Cone
Available Cone
Activity coefficient (a)
Ca(molal)
0.00374
0.00292
0.447
SCMmolal)
0.01041
0.00885
0.447
We then calculated the Log(Ksp) for CaSC>4-2H20 in this solution and temperature to be -4.59. The
logarithm of the ion product for CaSC>4 based on the concentration available is compared to the
Log(Ksp) to provide an SI.
SI =-5.29 - (-4.59)= -0.70
Thus at this condition CaSC>4 is significantly under saturation. Since the SI is log based, the percent
of saturation is lO^ = 0.1995 or about 20%. In this example, the ASTM method, which uses a Ksp
corrected for ionic strength with typical analyses yielded a value of about 21 % of saturation. Thus the
ASTM method compares very well and slightly conservatively in this case.
Nevertheless, it should be noted that the calculations performed using the ion complex method are
performed for minerals that may actually supersaturate to a very high degree, and thus not cause
problems in many membrane system applications. The aluminum and iron compounds investigated
are also almost always present to some degree in surface and ground waters. This program
provides the user a means to effectively determine the optimum pH conditions to minimize potential
fouling from these compounds. However, the user should note that iron and aluminum compounds
are usually present in the amorphous condition and the fouling or non-fouling tendencies of the
compounds may be greatly dependent on the presence or absence of organic or biological
components in the feedwaters.
One further note, if the user does enters "0.0" or no value for an ion, the program assigns a default
minimum value of 1 ppt (1 part per trillion). This prevents division by zero and the small values are
neglible in the calculations. For HC03 however, a minimum value of 0.1 ppm is assigned so that the
carbonate distribution calculations avoid division by zero. For ferric and ferrous ions the program
assumes the user input distribution is correct. Free chlorine (if specified) is reacted stoichiometrically
with any Fe2+ converting it to ferric ion, Fe3+, to the extent available. Excess free chlorine after
reaction with ferrous iron is next reacted with any NH4 present, removing it from consideration as an
assumed NH3CI species. Dissolved oxygen conditions are not addressed.
RO & NF Calculation Methods
General Approach
The general approach used is the same as used by most commercial membrane design program.
Basically the user needs to guess a design and then a detailed calculation is made. The TFS
program assists this process by continually displaying and updating an estimated feed pressure and
TDS values for the user at the bottom of the System Design sheet.
With a reasonable guess in hand, the convergence to a final design is usually quite rapid. The TFS
program makes one simplification over most commercial programs which greatly helps the
calculation rates in that each vessel is converted to the form of one element, which directly returns
SI = Log(0.00292 * 0.447 * 0.00885*0.447) - log (Ksp)
Equation 5
8
-------
the performance for each bank in the system. TFS calculates salt flux on and ion by ion basis during
each pass through the system and balances carbonates in the concentrate and permeate analyses
while accounting for the free passage of CO2 through the membranes.
Water flux, or more properly the flow rate, is calculated as follows, where Pi is osmotic pressure. The
effective pressure or PNet is the net trans-membrane differential pressure. All values are first
converted to meters, seconds, and atmospheres to make calculations simpler.
Equation 6
( differential \
Flow(m3 / sec) = A ValuemKsec,atm) * d reci2*| /' eeclPress(Pi Membrane Pi Permeate)atm
The concentration of each ion in the permeate is found as follows. A variable, "MegDiff" is defined
for each ion first which is the effective difference in concentration of the ion from the membrane
surface to the permeate.
MeqDiff = BulkLMAvg Cone * ConcPolarization - PermConc Equation 7
Pen,,Com = MeqDiff * BValue 'BCorrectionBylon E„ation8
PNet * A Value + BValue
PNet is as defined above, i.e. the Feed Pressure less differential pressure less the osmotic pressure
difference between the average feed stream and permeate. BcorrectionBylon is stored in the
spreadsheet as a function of the membrane type selected. This the ratio of salt flux for the ion in
question relative to either chloride or sodium.
Since the permeate concentration obviously affects the water flux and required pressure and vice
versa, an iterative approach is necessary to solve the system. The general steps are as follows:
a) Acquire a valid water analysis and target feed pH from the user supplied data. Read this
data into the internal variables.
b) Acquire flow and recovery information from the graphic screen (System Design sheet).
c) Convert all flow rates to the internal flow scheme - we use m3/sec as the base flow units.
d) Acquire from the user the suggested array, bank, and vessel layout and save in the internal
variables. Typically, a system with higher recovery needs more banks. For example a 75%
recovery system might specify 6 elements per vessel, 2 banks total in the array, and an
array layout of 16:8. That is 16 vessels feeding 8 vessels in bank 2.
e) Acquire from the user the selected membrane element information. When the user selects
the type of element, the program loads the specific data about the element such as A value,
B value, B values relative to Na or CI per each ion (dependent on membrane type), area in
m2, differential pressure coefficients and so on. Membrane data is stored on the "Membrane
Data" sheet.
9
-------
Setup up the selected membrane element in the vessel and calculate delta pressure and
concentration polarization coefficients (called the "BetaCoef' in the code) for the selected
elements. Delta pressure is calculated from a coefficient specific to the membrane
configuration. The TFS program by default utilizes coefficients based on commercial
membranes typical of those elements used for so called "pure water" applications which
assumes water is the medium processed and assumes normal thickness feed size spacer
materials. The user can adjust these coefficients for special elements as needed.
dp = dpCoef ' [FeedFlim+ConcFlow-r Et|uatl0„9
ConcPolarization = CP = Exp(BetaCoef * Recovery) Equation 10
At this point calculations are ready to begin. The first step is to "Guess" the applied
pressure needed to produce the water flow rate required. To do this the total membrane
area in the system is calculated and the feed osmotic pressure is calculated. Based on
nominal recovery and a nominal rejection for the element, a concentrate concentration is
estimated by mass balance. The concentrate osmotic pressure is then calculated. Next a
differential pressure is calculated for the first and last elements in the system and the
average is used to guess a differential pressure for the system. The "Guess" pressure
needed is then calculated:
„ , . Flow ,,, , , FeedPi + ConcPi Equation 11
( iuess(a/m) = b 1 otal dp H
" A" value * Area 2
N.B. - Flow is m3/sec, "A" value is m/(sec*atm) or m3/sec/m2/atm, dp and Pi in atm.
Guess is the net pressure, which includes osmotic pressure and dp losses so the applied
pressure is increased by average of the feed and concentrate osmotic pressures and the
differential pressure. Osmotic pressure is calculated using the typical equation of state,
summed for each ion in the analysis. Total organic carbon contributions (if any) are ignored
since the contribution is highly dependent on the type of organic carbon present.
OsmoticPressure(atm) = Pi = RT * ^a([c]( Equation'
Alllons
R = GasConst, T = degK,at = activity, \c\ = moles!I
The Calculations for RO and NF are arranged in a "Do While Loop" that starts with an
assumed applied pressure. From this pressure, flow rates and concentrations are calculated
on a vessel by vessel basis through the system. The total calculated flow is then compared
to the target flow the user provided. If low, then the pressure is raised proportionally and if
low then the pressure is lowered proportionally. Once the calculated flow is within the set
percentage of the target flow - typically 0.2% to 1% - then the calculation program
terminates and data is returned to the spreadsheet for display to the user.
10
-------
j) RO and NF calculations differ in approach to the transport of ions. For reverse osmosis
systems, the permeate anions and cations are balanced equally. That is, any difference in
overall charge in the first pass through the permeate concentration calculation (Equation 8)
is split equally and the permeate concentration adjusted proportionally for each ion. For an
NF system, the anions are assumed to control and the cations are adjusted to balance the
permeate analysis.
k) If a convergence cannot be obtained, the user is alerted. Failure to converge usually occurs
when too few or too large a number of vessels are specified for the flow rates that are
required. The applied pressure required to produce the needed flow cannot be reconciled
with the differential pressures necessary to pass the flow through the system.
In the source code annotation section of this report, the detail calculations can be seen. Many of
the above functions utilize sub routines or functions defined to make the program code more
readable and easily maintained.
Data Collection
Data collection in the TFS program from the User takes place on the Analysis and System Design
sheets. Integral to the Analysis sheet are the calculations to balance the analysis for charge neutrality
and to calculate the carbonate distribution. The user has the choice of entering either a measured
bicarbonate value or a measured Total Inorganic Carbon value. With the pH entered by the user, a
carbonate distribution is calculated.
The second sheet - "System Design" - allows the user to enter the flow rates, recovery, and system
layout. Calculation of the flows occurs as the data is entered. One important note is that recycle flow
affects the concentration of the feedwater actually going to the system, sometimes quite dramatically.
The TFS program allows the user to evaluate the option of designing a "tree" array approach with
many banks or opting for a single bank system with recycle. Use of the recycle option causes the
main calculation program to loop through the system to iteratively calculate the concentrations of the
"Net Feed" stream.
Carbonate Equilibrium
The calculation of the appropriate carbonate and pH distribution is an important part of this program.
Note that membrane systems readily pass carbon dioxide (CO2) while carbonate (CO3) is quite well
rejected. Thus the permeate side of the membrane is enriched with carbon dioxide while the
feed/concentrate side of the membrane is enriched with carbonate and bicarbonate. The net effect of
this separation is to lower the pH of the permeate while the concentrate pH rises. The TFS program
effectively models this behavior. For applications in which a membrane system precedes a Dl
system, the additional carbon dioxide can pose a load on the anion resins. The TFS program can
help the user to evaluate the use of acid versus scale inhibitor or softening resins as the pretreatment
step.
The steps necessary to calculate the carbonate equilibrium are as follows:
a) The solution ionic strength is calculated, defined as
IS = 0.5 * ^[Ion]* Abs(z) Equation 13
All Ions
b) pK1 and pK2 are corrected for the solution temperature.
11
-------
Ionic strength corrections are then applied to pK1 and pK2. The methods are described in
the Stumm and Morgan reference in the Literature Citations section.
pK1 and pK2 are converted back to K1 and K2 values.
The [H+] concentration is calculated from solution pH
The Mole Fraction (fraction of the total inorganic carbon) for HCO3 is calculated from K1, K2,
and pH. If the HCO3 concentration was specified then the Total Carbon (TC) is calculated
from the mole fraction of HCO3. If Total carbon was specified then the concentration of
HCO3 is calculated using the mole fraction of HCO3.
f
MoleFraction HCO, =
TotalCarbon = TC = ¦
Y
(H+) 1 K 2
- + 1 +
v Kl H+,
[HC03\
Equation 14
MoleFraction HCO,
Knowing the total carbon, we then calculate the C02 and C03 values from pH and K1 and
K2 as follows. The values are in moles/liter.
[C03] = rC*
f (H+f 1 H+x'
v ' +1+-
K\*K2
K 2
Equation 15
[C02] = TC*
(K\f , K\*K2
v ' +1 + ¦
W
(H+y
For the treated feed - i.e. the feed with the pH adjusted, we have already computed the
Total Carbon (TC) and thus the new carbonate distribution can be readily calculated from
the equations above. The amount of acid required is easily computed from the change in
bicarbonate concentration from the User supplied feed water to the Treated Feed water
composition.
12
-------
User Program Instructions
System and Software Requirements
The TFS program requires a computer running Microsoft Office 97 or higher. Excel 97 or higher must
be installed. The Solver add-in as well as Visual Basic for Applications must also be installed.
Normally these two components are installed with Excel as defaults. A Pentium class computer is
required for best performance and a screen resolution of at least 800 by 600 is needed.
Basic Program Installation and Operation
1. Make a copy of your program diskette or download file.
2. Verify that Excel 97 or later in installed on your computer.
3. Launch Excel.
4. Set the calculations so that iteration is on. Tools:Options:Calculation. Be sure the
iteration box is checked. If not, then check it now and click OK.
5. Check to see if the Solver Add-in is installed. Select Tools, Add-lns. This will bring up
the Add-lns box as shown in Figure 3:
6. If the Solver box is present and checked, you are ready to run TFS. If the Solver add-in
Figure 3 - Install the Solver Add-in Prior to First Loading TFS
jf.ile Ldit View insert Format Tools Data Window Utilities Help
-II » : i » B /¦ u ® % . % i? 3f • *•" - A .
C ^ E I J & !CL ? jf 9> IS
±1
rl,W
-
- :
V'sV., rv
r Internet Assistant VBA
F Lookup Wizard
F MS Query Add-in
F ODBC Add-in
F Power Utility Pak 97
r Report Manager
"Lliiul.i-,. J'.i ,
r* Template Wizard with Data Tracking H?•
r* Update Add-in Links 2^ '
? X
I
is present but not checked, check and click OK. The system may ask you to insert your
Excel or Office setup disk. In the very unlikely event the Solver add-in cannot be found,
then you must install it first. Your Office CD's will have the files but for convenience, the
"Solver" files have been included with the TFS release disk. They are in a directory
called "Solver" which contains two files - "Solver.xla" and "Solver32.dll" On Windows 95
and 98 systems the Solver directory is usually stored in the C:\Program FilesWIicrosoft
Office\Office\Library directory. Copy the entire Solver directory from either your Office
setup disk or the TFS release to the \Library directory. Then use the Browse function to
13
-------
find the program Solver.xla and install it - ...\Library \Solver\ Solver.xla. See the next
figure - figure 4.
Figure 4 - Manually finding (browsing) the Solver Add-in if necessary
; F_i 1 e Ldit VInu insert: format t-.i Jtlndou Utilities Help
'
Q
H
El
Library
Analysis
3
x!a
da
HfHtmi.xIa
'* a
la
HjfSumif.xIa
" i.xla
ia
.xia
.> -¦
jAdd~Ins C*.xla; •
"It
7. Copy the program file - "TFS Ver x.x.xx.xls" from the release disk to a new hard drive
directory of your choice.
8. Use the Excel File:Open command to find and open the file.
9. The program will load, go through some startup initialization and start at the Analysis
Tab. Wait for the loading to finish then click the InfoLicense tab to read the conditions of
use.
10. Return to the Analysis sheet. Enter analytical data in the cells with blue text and a white
background.
206.0
The cells which contain formulas or links or static text often contain a pop up help balloon
explaining their function. Most cells on this sheet which are not meant to accept user data
are protected and a message will display if you attempt to enter data, or enter non-numeric
data in a field expecting numeric data.
11. Complete the analytical data, specify a target pH or resin softening, and then move to
the System Design sheet by selecting the System Design tab at the bottom of the
screen.
12. In the System Design sheet you may enter flow and recovery information as well as the
membrane type plus system design or layout. The smaller field near the bottom of this
sheet - rows 37 through 42 - show an estimated pressure and permeate TDS conditions
as flow rates are changed. You will need to adjust the layout and number of membranes
(banks, number of vessels, and number of elements per vessel) so that the pressure is
in a reasonable range - say 100 to 1200 psig.
13. Once complete, press the Calculate button and the calculations will be launched. On
completion, the smaller field shows the calculated pressure and TDS. Buttons near this
field labeled "See Summary Data" and "See Stream Data" take you to the two summary
14
-------
data sheets which display all of the calculated data available. Both sheets can be
printed.
14. If it is desired to save the system data, press on the tab labeled "DataBase" and find
(and press) the button labeled "Save Current Data Set Now." This saves the data into
the first row of the database. Stored system may be sorted in numerous ways to find old
data sets. To restore an old dataset, position the cursor anywhere in the row holding the
data which is to be restored and then press the "Load Selected Data Set Now" button.
This restores the only the feed flow and analytical data so you can test your current
system design on a new water by simply recalculating.
List of Needed Add-In files and typical locations
You will need the following files.
• The main program - TFSP ver xx.xls, located in your specified directory
• Solver.xla - Typically in C:\Program FilesWIicrosoft Office\Office\Library\Solver
• Solver32.dll - Typically in C:\Program FilesWIicrosoft Office\Office\Library\Solver
• TFS Program Documentation.doc (This file), located in your specified directory.
• WateQF.txt - the WaterQF database file in text format, located in your directory.
• Phreeqc.txt - the Phreeqc database file in text format, located in your directory.
Special Functions
In addition to the basic operations above, the knowledgeable user can make several additions to the
program functionality.
Add/Edit Membrane Element Types
Before using this function, save the spreadsheet to prevent inadvertent loss to existing data. Go to
the System Design sheet and select the button on the sheet entitled Add/Edit Element. This calls up
a form which allows the user to create a new membrane from typical spec sheet data provided by the
manufacturer. Enter data in ALL of the boxes on the form.
Modify/Create New Membrane Type
This feature allows the user to change the characteristics of the membrane salt passage by ion. To
modify the membrane data, select the Modify Membrane button or move to cell AB16. The user may
then modify the temperature correction factor or any of the constants which relate individual ion
passage to CI or Na.
To create a new membrane, it is easiest to use the Add New Membrane button on the Membrane
Data sheet. Press the button. Select the first/next empty row to create the new membrane data. A
maximum of 10 membrane types are allowed. The button will take you to the first empty column.
First - enter the data. The "Type Name" (row 16) must be unique. Any of the other data may be
copied from the prior membrane type. If the new type is NF be sure to set the value in the Calculate
15
-------
as Nanofiltration row to TRUE. When done entering data in the column, press the NEXT button at the
bottom of the screen. It will ask you for the name of the membrane type you are entering. This
appears in the membrane type list on the System Design sheet so make it descriptive.
The program will show the current number of membrane types (the program came with 3) so you
should accept the program value unless something is wrong.
The program will return to the System Design sheet and the new membrane should appear in the left
hand drop down box. However, there are no elements yet with this type of membrane so now you
must create at least one element use the Add/Edit Element button as described above. Once
completed, you are ready to use the new element with the newly defined membrane type.
Printing
The TFS program was designed with the bulk of the returned data displayed on two sheets, each
suitable for printing on one page. The release version of the program is setup so that the Summary
and Streams sheets print on one page each. Of course the user may change this arrangement at
any time through the normal Excel printing mechanisms.
Source code may be printed as well, however, some intermediate export function is required. To
print or view the code in a module, first launch the Visual Basic editor-Tools|Macros|Visual Basic
Editor or use the Alt+F11 key. In the left hand pane, highlight the module you wish to export or print
and right click or use the File function in the VB menu. Select Export or Print as desired.
16
-------
References
ASTM. (1993) Section VI - Water-Treatment Materials: Section D 3739 - 88 "Standard practice for
calculation and Adjustment of the Langelier Saturation Index for Reverse Osmosis".
ASTM. (1993) Section VI - Water-Treatment Materials: Section D 4692 - 87 "Standard practice for
calculation of sulfate scaling salts (CaS04, SrS04, BaS04) for reverse osmosis". In Annual Book of
ASTM Standards, Vol. 11.02, pp. 868-871.
ASTM. (1993) Section VI - Water-Treatment Materials: Section D 4993 - 89 "Standard practice for
calculation and Adjustment of Silica (SiC>2) for Reverse Osmosis".
Bennett, A.C., and Adams, F., 1976, Solubility and solubility product of dicalcium phosphate dihydrite
in aqueous solutions and soil solutions: Soil. Sci. Soc. Amer. Jour., 40, 39-42.
Bethke C. M. (1992) The Geochemist's Workbench; A users guide to Rxn, Act2, Tact, React, and
Gtplot. U. of Illinois Press.
Byrne W., 1995, Reverse Osmosis A practical guide for industrial users., Tall Oaks Publishing
Cox, J.D., Wagman, D.D., and Medvedev, V.A., 1989, Codata key values for thermodynamics:
Hemisphere Pub., New York, 271 p.
Delany J. M., (1985) Reaction ofTopopah Spring Tuff with J-13 water: A geochemical modeling
approach., Lawrence Livermore National Laboratory, Report Number UCRL-53631;
Eriksson P., (1988) Water and Salt Transport Through Two Types of Polyamide Composite
Membranes, Journal of Membrane Science, 36, p 297-313
Hem J. D., (1992) Study and interpretation of the chemical characteristics of natural waters, United
States Geological Survey, Report Number Water Supply Paper 2254; 263 p. p.;
Hogfeldt E. (1982) Stability Constants of Metal-Ion Complexes. Part A Inorganic Ligands. Pergamon
Press. 310 pages p.
Holland H. D. (1978) The Chemistry of the Atmosphere and Oceans. John Wiley & Sons. 351 p. p.
Johnson J. W. and Lundeen S. R., (1994) GEMBOCHS thermodynamic datafiles for use with the
EQ3/6 software package, Lawrence Livermore National Laboratory, Report Number LLNL--YMP
milestone report MOL72, 99; 126 p.;
Langmuir, D., (1979), Techniques of estimating thermodynamic properties for some aqueous
complexes of geochemical interest, in Jenne, E.A., ed., Chemical Modeling in Aqueous
systems: Speciation. Sorption. Solubility, and Kinetics: ACS Symp. Ser. 93, Amer. Chem. Soc.,
Washington, 353-387.
Merten U (editor), (1966), Transport Properties of Osmotic Membranes, in Desalination by
Reverse Osmosis. MIT Press.
Montgomery JM, (1985), Water Treatment Principles and Design, Wiley and Sons.
17
-------
NaumovG. B., Ryzhenko B. N., and Khodakovsky I. L, (1974) Handbook of Thermodynamic Data,
United States Geological Survey, Report Number USGS-WRD-74-001; 328 p. p.;
Nriagu, J.O., 1972, Stability of vivianite and ion-pair formation in the system Fe3(P04)2-H3P04-
H20: Geochim. Cosmochim. Acta, 36, 459-470.
Parkhurst D. L, Thorstenson D. C., and Plummer L. N., (1980) PHREEQE- A computer program for
geochemical calculations, United States Geological Survey, Report Number
Plummer L. N., Jones B. F., and Truesdell A. H., (1976) WATEQF - A Fortran IV version ofWATEQ,
a computer program for calculating chemical equilibrium of natural waters., United States Geological
Survey, Report Number Wat. REs. Inv. 76-13.;
Robie, R.A., Hemingway, B.S., and Fisher, J.R., 1979, Thermodynamic properties of minerals and
related substances at 298.15K and 1 bar (10s Pascals) pressure and at higher temperatures-.
U.S.G.S. Bull. 1452 (with corrections), 1452, 1456p.
Shock, E.L., and Helgeson, H.C., 1988, Calculation of the thermodynamic and transport
properties of aqueous species at high pressures and temperatures: Correlation algorithms for
ionic species and equation of state predictions to 5kb and 1000°C: Geochim. Cosmochim. Acta,
52, 2009-2036.
Shock, E.L., and Helgeson, H.C., 1989, Corrections to Shock and Helgeson (1988): Geochim.
Cosmochim. Acta, 53, 215.
Shock, EL; Sassani, DC; Willis, M; Sverjensky, DA., 1997, Inorganic species in geologic fluids:
Correlations among standard molal thermodynamic properties of aqueous ions and hydroxide
complexes. Geochim. Cosmochim. Acta, 61:907-950.
Smith R. M. and Martell A. E. (1976) Critical Stability Constants. Volume 4: Inorganic complexes.
Plenum Press. 257 p.
Spycher N. F. and Reed M. H., (1989) SOLVEQ: A computer program for computing aqueous-
mineral-gas equilibria., University of Oregon, Report Number unpublished;
Stumm W., Morgan J, 1976, Aquatic Chemistry, Wiley and Sons
Sverjensky D. A., Shock E. L., and Helgeson H. C. (1997) Prediction of the thermodynamic
properties of aqueous metal complexes to 1000 C and 5 kb. Geochim. Cosmochim. Acta 61:1359-
1412.
Truesdell, A.H., and Jones, B.F., 1974, WATEQ, a computer program for calculating chemical
equilbria of natural waters: U.S.G.S. J. Res., 2, 233-248.
Wolery T. J., (1992) EQ3NR, A computer program for geochemical aqueous speciation-solubility
calculations: Theoretical manual, User's Guide, and Related Documentation, Lawrence Livermore
National Laboratory.
Woods, T.L., and Garrels, R.M., 1987, Thermodynamic values at low temperature for natural
inorganic materials: An uncritical summary: Oxford Univ. Press, Oxford.
18
-------
Source Code and Annotations
General Comments and Arrangement
The Visual Basic source code was written using the standard software provided in Microsoft Office
97. The source code is not protected in any way and can be viewed by the following procedure.
Load the program, select Tools from the standard Excel Menu Bar, select Macros, select Visual
Basic Editor. In the left hand pane, locate the program folder. Under this folder locate the folder icon
labeled "Modules" and double click it. Select one of the modules - for example RO/NF calc. This
opens the Visual Basic editor in the right hand side of the screen. The code should be visible.
The code has been broken down logically into several different code "modules" according to function.
A Visual Basic or any type of Basic programmer should readily be able to understand and modify the
code as they may desire. No additional compilers or software are needed to make changes to the
code, so the user should keep a copy of the original installed program should the need arise for a
complete code backup.
Most of the functions used are accessible in the Microsoft provided Visual Basic and Excel Help
Files. If the Visual Basic help file cannot be found, it may be necessary to install it using the original
MS Office installation disk or network installation pathname. The only poorly documented (from
Microsoft) functions used in the TFS program are the calls to launch the Excel Solver directly from
Visual Basic. These can be found by "recording" a macro which includes a manual call to the Solver.
The actual source code itself is displayed below. Comments are preceded with the single
exclamation point (') and do not execute. Comments in italics are added in the final report notation.
Note that some lines have wrapped themselves in the report format and do not indicate a new line of
code.
Where reasonably convenient, references to cells in the worksheets is via the Range Name rather
than a direct cell reference. All cell references are via the "A1" type format. The
Application.Screenupdating function turns on or off the screen update. This prevents flicker while
many cells are being changed.
UtilsAnalysis Module
This module contains code to switch units
Attribute VB_Name = "UtilsAnalysis"
Option Explicit
Public strFlowFormat As String
This sub responds to a change in the units for analysis - ie meq/l to ppm to ppm as CaC03 etc.
Sub AnalyisGroup_Click()
Attribute AnalyisGroup_Click.VB_Description = "Macro recorded 5/26/98 by Tom Wolfe"
Attribute AnalyisGroup_Click.VB_ProcData.VB_Invoke_Func = " \nl4"
!
' Modified to reflect changes made
' Since we have now separated the User Input from the values used for calculation to avoid
problems with bad input.
' Changed the cell references for following
' Input_Anion, Input_Cation, Input_Silica, Input_Balance, Input_C02, Input_C03
' Added update to Feed_C03 and Feed_C02, which were the Input_C02, Input_C03
' Added new range - User_Sum_Metals and formula for this cell
19
-------
' NB be sure to change the cell references in VB if rows are added on the PL_IonBal Sheet
' AnalyisGroup_Click Macro
Dim myRange As Range
If BoolLoading = False Then
Application.ScreenUpdating = False
End If
' also need to turn off iteration
Application.Iteration = False
Application.Calculation = xlManual
This section looks to see if we are using Total Inorganic Carbon or HC03
Dim boolTIC As Boolean ' added 11/1/99 for Total Inorganic carbon
If Left(Range("TotallnorganicCarbonFlag")•Value, 1) = "Y" Or
Left(Range("TotallnorganicCarbonFlag")•Value, 1) = "y" Then
boolTIC = True
Else
boolTIC = False
End If
The actual calculations for units are done are the spreadsheet - this routine moves the proper values to
the input section and resets the pointers. All internal calculations are handled in meq/l units.
Select Case Range("Units_Analytical")
Case "ppm"
Select Case Range("Units_01dAnalytical")
Case "ppm"
GoTo windup 'Exit Sub
Case "ppm CaC03" ' was ppm now wants to be ppm as CaC03
Range (,,Input_cation") .Value = Range ( "out_cations_caco3 ") .Value
Range("Input_anion").Value = Range("out_anions_caco3").Value
Range("Input_balance").Value = Range("out_balance_caco3").Value
Range("Input_silica").Value = Range("out_silica_caco3").Value
' now fix up the C02 and C03 so they re-iterate properly
Range("Input_C02").Formula = "=Calc_C02_CaC03"
Range("Input_C03").Formula = "=Calc_C03_CaC03"
' 8 July 1999 change
' The Feed_C02 range already existed
Range("Feed_C02").Formula = "=Calc_C02_CaC03"
Range("Feed_C03").Formula = "=Calc_C03_CaC03"
' 11/1/99 add version AV total inorganic carbon
' just need to fix up the HC03
If boolTIC = True Then
Range("User_HC03").Value = Range("HC03_FromTC_CaC03").Value
End If
Case "mEq/L" ' was ppm and now wants to be meq/l
Range (,,Input_cation") .Value = Range (,,out_cations_meq") .Value
Range ( "Input_anion"). Value = Range (,,out_anions_meq"). Value
Range("Input_balance").Value = Range("out_balance_meq").Value
Range ( "Input_silica"). Value = Range (,,out_silica_meq"). Value
' now fix up the C02 and C03 so they re-iterate properly
Range("Input_C02").Formula = M=Calc_C02_meqM
Range ( "Input_C03 "). Formula = "=Calc_C03_meq"
' 8 July 1999 change
' The Feed_C02 range already existed
Range("Feed_C02").Formula = "=Calc_C02_meqM
Range ( "Feed_C03 "). Formula = "=Calc_C03_meq"
' 11/1/99 add version AV total inorganic carbon
' just need to fix up the HC03
If boolTIC = True Then
Range("User_HC03").Value = Range(MHC03_FromTC_meqM).Value
End If
End Select
Case "ppm CaC03"
Select Case Range ( "Units_01dAnalytical")
Case "ppm"
20
-------
Range (,,Input_cation") .Value = Range (,,out_cations_ppmn) .Value
Range (,,Input_anion"). Value = Range (,,out_anions_ppmM). Value
Range ( "Input_balance") .Value = Range (,,out_balance_ppm") .Value
Range ( "Input_silica"). Value = Range (,,out_silica_ppmn). Value
' now fix up the C02 and C03 so they re-iterate properly
Range("Input_C02").Formula = "=Calc_C02"
Range("Input_C03").Formula = "=Calc_C03"
' 8 July 1999 change
' The Feed_C02 range already existed
Range("Feed_C02").Formula = "=Calc_C02"
Range("Feed_C03").Formula = "=Calc_C03"
' 11/1/99 add version AV total inorganic carbon
' just need to fix up the HC03
If boolTIC = True Then
Range("User_HC03").Value = Range(MHC03_FromTCM).Value
End If
Case "ppm CaC03" ' was ppm now wants to be ppm as CaC03
GoTo windup
Case "mEq/L" ' was ppm and now wants to be meq/1
Range ( "Input_cation") .Value = Range (,,out_cations_meq") .Value
Range ( "Input_anion"). Value = Range (,,out_anions_meq"). Value
Range("Input_balance").Value = Range("out_balance_meq").Value
Range ( "Input_silica"). Value = Range (,,out_silica_meq"). Value
' now fix up the C02 and C03 so they re-iterate properly
Range("Input_C02").Formula = M=Calc_C02_meqM
Range ( "Input_C03 "). Formula = "=Calc_C03_meq"
' 8 July 1999 change
' The Feed_C02 range already existed
Range("Feed_C02").Formula = "=Calc_C02_meqM
Range ( "Feed_C03 "). Formula = "=Calc_C03_meq"
' 11/1/99 add version AV total inorganic carbon
' just need to fix up the HC03
If boolTIC = True Then
Range("User_HC03").Value = Range(MHC03_FromTC_meqM).Value
End If
End Select
Case "mEq/L"
Select Case Range("Units_01dAnalytical")
Case "ppm"
Range("Input_cation").Value = Range("out_cations_ppm").Value
Range("Input_anion").Value = Range("out_anions_ppm").Value
Range("Input_balance").Value = Range("out_balance_ppm").Value
Range("Input_silica").Value = Range("out_silica_ppm").Value
' now fix up the C02 and C03 so they re-iterate properly
Range("Input_C02").Formula = "=Calc_C02"
Range("Input_C03").Formula = "=Calc_C03"
' 8 July 1999 change
' The Feed_C02 range already existed
Range("Feed_C02").Formula = "=Calc_C02"
Range("Feed_C03").Formula = "=Calc_C03"
' 11/1/99 add version AV total inorganic carbon
' just need to fix up the HC03
If boolTIC = True Then
Range("User_HC03").Value = Range("HC03_FromTC").Value
End If
Case "ppm CaC03" ' was meq now wants to be ppm as CaC03
Range("Input_cation").Value = Range("out_cations_caco3").Value
Range("Input_anion").Value = Range("out_anions_caco3").Value
Range("Input_balance").Value = Range("out_balance_caco3").Value
Range("Input_silica").Value = Range("out_silica_caco3").Value
' now fix up the C02 and C03 so they re-iterate properly
Range("Input_C02").Formula = "=Calc_C02_CaC03"
Range("Input_C03").Formula = "=Calc_C03_CaC03"
' 8 July 1999 change
' The Feed_C02 range already existed
21
-------
Range("Feed_C02").Formula = "=Calc_C02_CaC03"
Range("Feed_C03").Formula = "=Calc_C03_CaC03"
' 11/1/99 add version AV total inorganic carbon
' just need to fix up the HC03
If boolTIC = True Then
Range("User_HC03").Value = Range("HC03_FromTC_CaC03").Value
End If
Case "mEq/L" ' was meq and now wants to be meq/1
GoTo windup 'Exit Sub
End Select
End Select
' if we go directly to windup no need for this section to put back formula in heavy metals
summation
' 8 July 1999 changed row reference to fit move of A1 and Fe to upper section
' 8 July 1999 add write back to user_Sum_Metals formula
Range("User_Sum_Metals").Formula = "=Sum_Metals"
windup:
Range("Units_Analytical").Value = Range("Units_01dAnalytical").Value
Application.Calculation = xlAutomatic
Application.Iteration = True
If BoolLoading = False Then
Application.ScreenUpdating = True
End If
End Sub
Sub Flow_Select_Drop_Click()
Attribute Flow_Select_Drop_Click.VB_ProcData.VB_Invoke_Func = " \nl4"
Dim oldFlowUnits As Integer
Dim newFlowUnits As Integer
Dim myRange As Range
Dim FlowGpm As Double
Dim NewFlow As Double
oldFlowUnits = Range("select_oldflow").Value
newFlowUnits = Range("select_flow").Value
' first get the old flow rate in gpm then convert it to the new flow rate
' The old gpm rate is in Flow_gpm
FlowGpm = Range (,,FeedFlow_gpm"). Value
Select Case Range("FlowUnit")
Case 1
NewFlow = FlowGpm
Case 2 'to GPD
NewFlow = FlowGpm * 14 4 0
Case 3
NewFlow = FlowGpm * 60 / 264.2
Case 4
NewFlow = FlowGpm * 1440 / 264.2
Case 5
NewFlow = FlowGpm * 3.7 84
Case 6
NewFlow = FlowGpm * 60 * 3.784
End Select
' Now update the system design worksheet and reset the flows there
tempStop = True
Range("FeedFlow").Value = NewFlow
Worksheets("System Design").UpdateFlows 7
' now format the result
' this gives an error durng dataLoad - go around it
If BoolLoading = False Then
'Range("input_flow").NumberFormat = SetFormat(NewFlow)
Else ' save the format
strFlowFormat = SetFormat(NewFlow)
End If
22
-------
' now put the current flow selection value in the select_01dFlow
Range("old_FlowUnit").Value = Range("FlowUnit").Value
End Sub
The temperature units - C, F, K. Internal units are deg C.
Sub Temp_Select_click()
Attribute Temp_Select_click.VB_ProcData.VB_Invoke_Func = " \nl4"
Dim myRange As Range
Dim Temp_F As Double
Dim NewTemp As Double
Temp_F = Range (,,Temp_F"). Value
Select Case Range("select_temperature")
Case 1
NewTemp = Temp_F
Case 2 'to C
NewTemp = (Temp_F - 32) *5/9
Case 3 ' to deg K
NewTemp = (Temp_F - 32) * 5 / 9 + 273.15
End Select
Range("input_Temp").Value = NewTemp
' now put the selected temp back
Range("select_oldTemperature").Value = Range("select_temperature").Value
End Sub
Sub Acid_Select_Click()
Range (,,input_acid"). Value = Range ( "select_acid_name"). Value
If Range("input_acid").Value = "No Acid" Or Range("input_acid").Value = "Sodium Softener" Then
Worksheets("Analysis").Range("Input_Feed_pH").Font.ColorIndex = 16
Worksheets("Analysis").Range("Input_Feed_pH").Interior.ColorIndex = 15
Worksheets("Analysis").Range("Input_Feed_pH").Interior.Pattern = xlSolid
Else
Worksheets("Analysis").Range("Input_Feed_pH").Font.ColorIndex = 5
Worksheets("Analysis").Range("Input_Feed_pH").Interior.ColorIndex = xlNone
End If
End Sub
Sets target pH - ignored if no acid is selected.
Sub Spin_pH_Click()
Dim myRange As Range
Dim OldpH As Double
Dim NewpH As Double
Dim FeedpH As Double
' Sets ph for system - used to calculate treated Feed conditions and acid dose
' Spin_pH /10
OldpH = Range("01d_pH").Value
NewpH = Range("Spin_pH").Value / 10
If Abs(OldpH - NewpH) > 0.2 Then
' we had a value entered by the user, reset the spinner to the prior user entered value
Range("Spin_ph").Value = OldpH * 10
End If
Range("input_feed_ph").Formula = "=Spin_ph / 10"
End Sub
Database Module
This code saves and retrieves data from a stored data set- Sheet is "DataBase"
Option Explicit
!
' This is the Save amd Retrieve Module
' Puts or takes data from the workbooks.Sheets("Database") sheet
' Data is stored as a row of data.
Public FeedIons(52) As Double
Public BoolLoading As Boolean
Public BoolOptimizing As Boolean
Public Const MainSheet = "Analysis"
23
-------
Type ProjectData
ProjectName As String
Projectdate As Date
FeedGPM As Double
TempF As Double
Recovery As Double
Rejection As Double
AcidType As Integer
InputPH As Double
FeedTDS As Double
FeedUnits As String
nUnits As Integer
FlowUnits As String
nFlowUnits As Integer
End Type
Dim MyProjectData As
0)
0
u
a.
Public
Const
ION
NA =
0
Public
Const
ION
~K =
l
Public
Const
ION
"CA =
2
Public
Const
ION
~MG =
3
Public
Const
ION
"SR =
4
Public
Const
ION
~BA =
5
Public
Const
ION
~FE3
= 6
Public
Const
ION
~AL =
7
Public
Const
ION
~NH4
= 8
Public
Const
LAST CATION =
Public
Const
Ion
H =
9
Public
Const
FIRST ANION =
Public
Const
ION
HC03
= 10
Public
Const
ION
~C02
= 11
Public
Const
ION
~C03
= 12
Public
Const
ION
~BR =
13
Public
Const
ION
"CL =
14
Public
Const
ION
~F =
15
Public
Const
ION
~S04
= 16
Public
Const
ION
"NO 3
= 17
Public
Const
ION
~P04
= 18
Public
Const
LAST ANION =
Public
Const
Ion
OH =
19
Public
Const
ION~
~SI02
= 20
Public
Const
Ion
pH =
21
Public
Const
ION~
ORGANICS =
Public
Const
Ion
AG =
23
Public
Const
Ion
"as =
24
Public
Const
Ion
~AU =
25
Public
Const
Ion
"CD =
26
Public
Const
Ion
"CR =
27
Public
Const
Ion
"cu =
28
Public
Const
Ion
~FE2
= 29
Public
Const
Ion
~HG =
30
Public
Const
Ion
~MN =
31
Public
Const
Ion
~NI =
32
Public
Const
Ion
"PB =
33
Public
Const
Ion
"SE =
34
Public
Const
Ion
"SN =
35
Public
Const
Ion
~TI =
36
Public
Const
Ion
"v =
37
Public
Const
Ion
"ZN =
38
Public
Const
ION~
~B407
= 39
Public
Const
ION
"NO 2
= 40
Public
Const
ION
~S03
= 41
Public
Sub AssignlonsO
Feedlons(ION_NA) = Range("Feed_Na").Value
Feedlons(ION_K) = Range("Feed_K").Value
Feedlons(ION_CA) = Range("Feed_CA").Value
24
-------
Feedlons
Feedlons
Feedlons
Feedlons
Feedlons
Feedlons
Feedlons
Feedlons
Feedlons
Feedlons
Feedlons
Feedlons
Feedlons
Feedlons
Feedlons
Feedlons
Feedlons
Feedlons
Feedlons
Feedlons
Feedlons
Feedlons
Feedlons
Feedlons
Feedlons
Feedlons
Feedlons
Feedlons
Feedlons
Feedlons
Feedlons
Feedlons
Feedlons
Feedlons
Feedlons
Feedlons
Feedlons
Feedlons
Feedlons
End Sub
Public Sub AssignProjectData()
With MyProjectData
.ProjectName = Range("Input_projectname").Value
.Projectdate = Now ()
.FeedGPM = Range("FeedFlow_gpm").Value
.TempF = Range (,,Temp_F"). Value
.Recovery = Range("SystemRecovery").Value
.Rejection = Range("Input_Rejection").Value
.AcidType = Range (,,Select_acid"). Value
.InputPH = Range(nInput_Feed_pHn).Value
.FeedTDS = Range("Feed_TDS").Value
.FeedUnits = Range("Units_analytical").Value
.nUnits = Range("Select_Analytical").Value
.FlowUnits = Range("Units_Flow").Value
.nFlowUnits = Range("FlowUnit").Value
End With
End Sub
Sub DataSave()
Dim i As Integer
Dim nRow As Integer, nCol As Integer
Dim mySheet As Worksheet
' be sure we come back to where we started from
Set mySheet = ActiveWorkbook.ActiveSheet
' On Error Resume Next
' first make some room for the new data - add a new row
Application.ScreenUpdating = False
Assignlons
AssignProj ectData
Worksheets("DataBase") .Activate
ION
MG) =
Range( Feed MG )
Value
ION
SR) =
Range("Feed SR")
Value
ION
BA) =
Range("Feed BA")
Value
ION
NH4 )
= Range("Feed NH4
') .Value
Ion
~H) =
Range("Feed H").Value
ion"
HC03)
= Range("Feed HC03").Value
ION
C02)
= Range("Feed C02
') .Value
ION
C03)
= Range("Feed C03
') .Value
ION
BR) =
Range("Feed BR")
Value
ION
CL) =
Range("Feed CL")
Value
ION
~F) =
Range("Feed F").Value
ION
S04 )
= Range("Feed S04
') .Value
ION
N03)
= Range("Feed N03
') .Value
ION
P04 )
= Range("Feed P04
') .Value
Ion
OH) =
Range("Feed OH")
Value
ion"
SI02)
= Range("Feed SI02").Value
Ion
_pH) =
Range("Feed pH")
Value
ion"
ORGANICS) = Range("Feed ORGANICS
Ion
AG) =
Range("Feed AG")
Value
ion"
AL) =
Range("Feed AL")
Value
Ion
AS) =
Range("Feed AS")
Value
Ion
AU) =
Range("Feed AU")
Value
Ion
CD) =
Range("Feed CD")
Value
Ion
CR) =
Range("Feed CR")
Value
Ion
CU) =
Range("Feed CU")
Value
Ion
FE2)
= Range("Feed FE2
') .Value
ion"
~FE3)
= Range("Feed FE3
') .Value
Ion
HG) =
Range("Feed HG")
Value
Ion
MN) =
Range("Feed MN")
Value
Ion
NI) =
Range("Feed NI")
Value
Ion
PB) =
Range("Feed PB")
Value
Ion
SE) =
Range("Feed SE")
Value
Ion
SN) =
Range("Feed SN")
Value
Ion
TI) =
Range("Feed TI")
Value
Ion
V) =
Range("Feed V").Value
Ion
ZN) =
Range("Feed ZN")
Value
ion"
~B407 )
= Range("Feed B407").Value
ION
N02)
= Range("Feed N02
') .Value
ION
S03)
= Range("Feed S03
') .Value
25
-------
Range("Data_Start").Select
ActiveCell.Offset(1, 0) .Select
Selection.EntireRow.Insert
we are now at the date column, so we can just start putting in the data
With MyProjectData
Selection.Value = .Projectdate
ActiveCell.Offset(0, 1).Select
Selection.Value = .ProjectName
ActiveCell.Offset(0, 1).Select
Selection.Value = .FeedGPM
ActiveCell.Offset(0, 1) .Select
Selection.Value = .TempF
ActiveCell.Offset(0, 1).Select
Selection.Value = .Recovery
ActiveCell.Offset(0, 1).Select
Selection.Value = .Rejection
ActiveCell.Offset(0, 1).Select
Selection.Value = .AcidType
ActiveCell.Offset(0, 1).Select
Selection.Value = .InputPH
ActiveCell.Offset(0, 1).Select
Selection.Value = .FeedTDS
ActiveCell.Offset(0, 1).Select
' meq/ppm/caco3
Selection.Value = .FeedUnits
ActiveCell.Offset(0, 1) .Select
Selection.Value = .nUnits
' gpm/gpd/etc.
ActiveCell.Offset(0, 1).Select
Selection.Value = .FlowUnits
ActiveCell.Offset(0, 1).Select
Selection.Value = .nFlowUnits
End With
' now the ions
For i = ION_NA To I0N_S03
ActiveCell.Offset(0, 1) .Select
Selection.Value = Feedlons(i)
Next i
now include chlorine
ActiveCell.Offset(0, 1).Select
Selection.Value = Range("User_Chlorine").Value
' last the valid data checksum - this must be greater than 0
ActiveCell.Offset(0, 1).Select
'nRow = ActiveCell.Row
nCol = ActiveCell.Column - 2 ' subtract 1 for current column, and 1 to miss the
first row date
Selection.Formula = "=SUM(RC[-" & Format(nCol, "#") & "]:RC[-1])"
mySheet.Activate
Application.ScreenUpdating = True
MsgBox ("Data Saved to Sheet - Database")
End Sub
Sub ResetProject ()
Dim strBlanks As String
strBlanks = " "
Range("Input_projectname").Value = strBlanks
Range("Input_projectDate").Formula = Now()
' flow to gpm
Range("Select_flow").Value = 1
Range("Input_Flow").Value = 100#
Flow_Select_Drop_Click
26
-------
Range("Select_temperature").Value = 1
Range("Input_Temp").Value = 77
Temp_Select_click
Range("Select_analytical").Value = 1
An a1yi s Group_Click
Range (,,Select_acidM). Value = 1
Acid Select Click
End Sub
Sub DataLoad()
Dim nRow As Integer
Dim nCol As Integer
Dim i As Integer
Dim float_Checksum As Double
Dim nErrCount As Integer
nErrCount = 0
Application.ScreenUpdating = False
BoolLoading = True
nRow = ActiveCell.Row
Range(Cells(nRow, 1), Cells(nRow, 1)).Select
nCol = Range("checksum").Column
If Not IsNumeric(Cells(nRow, nCol).Value) Then
' check to see if he just sorted and stuck at top of column. If so move down one row to
first data set
If nRow = Range("checksum").Row Then
nRow = nRow + 1
ActiveCell.Offset(1, 0).Select
Else
MsgBox ("The data row selected does not appear to contain valid data. Please check the
selected row and try again.")
Exit Sub
End If
End If
float_Checksum = Cells(nRow, nCol).Value
If float_Checksum <= 0 Then
If MsgBox("The data row selected does not appear to contain valid data. Continuing may cause
invalid results. Continue Anyway?", vbOKCancel) _
= vbCancel Then Exit Sub
' Doesn't want to cancel
End If
' now at column one in the data row we want to load
' Put all of the data into the variables we need
With MyProjectData
If Not IsNumeric(Selection.Value) Then
nErrCount = nErrCount + 1
Selection.Value = Now ()
'ActiveCell.Font.Color = vbRed
End If
.Projectdate = Selection.Value
ActiveCell.Offset(0, 1).Select
.ProjectName = Selection.Value
ActiveCell.Offset(0, 1).Select
If Not IsNumeric(Selection.Value) Then
Selection.Value = 100
nErrCount = nErrCount + 1
ActiveCell.Font.Color = vbRed
Elself Selection.Value < 0 Then
Selection.Value = 100
nErrCount = nErrCount + 1
ActiveCell.Font.Color = vbRed
End If
.FeedGPM = Selection.Value
27
-------
ActiveCell.Offset(0, 1).Select
If Not IsNumeric(Selection.Value) Then
Selection.Value = 77
nErrCount = nErrCount + 1
ActiveCell.Font.Color = vbRed
Elself Selection.Value < 32 Then
Selection.Value = 77
nErrCount = nErrCount + 1
ActiveCell.Font.Color = vbRed
End If
.TempF = Selection.Value
ActiveCell.Offset(0, 1).Select
If Not IsNumeric(Selection.Value) Then
If MsgBox("The recovery data appears
vbOKCancel) = vbCancel Then Exit Sub
Selection.Value = 75
nErrCount = nErrCount +
ActiveCell.Font.Color =
Elself Selection.Value > 95 Or Selection.Value < 10 Then
Selection.Value = 75#
nErrCount = nErrCount +
ActiveCell.Font.Color =
End If
.Recovery = Selection.Value
invalid. Set to default of 75%?'
1
vbRed
1
vbRed
ActiveCell.Offset(0, 1).Select
If Not IsNumeric(Selection.Value) Then
Selection.Value = 98
nErrCount = nErrCount + 1
ActiveCell.Font.Color = vbRed
Elself Selection.Value < 10 Then
Selection.Value = 98
nErrCount = nErrCount + 1
ActiveCell.Font.Color = vbRed
Elself Selection.Value > 100 Then
Selection.Value = 98
nErrCount = nErrCount + 1
ActiveCell.Font.Color = vbRed
End If
.Rejection = Selection.Value
' acid type number
ActiveCell.Offset(0, 1).Select
If Not IsNumeric(Selection.Value) Then
Selection.Value = 1
nErrCount = nErrCount + 1
ActiveCell.Font.Color = vbRed
Elself Selection.Value < 1 Then
Selection.Value = 1
nErrCount = nErrCount + 1
ActiveCell.Font.Color = vbRed
Elself Selection.Value > 4 Then
Selection.Value = 1
nErrCount = nErrCount + 1
ActiveCell.Font.Color = vbRed
End If
.AcidType = Selection.Value
' now pH units
ActiveCell.Offset(0, 1).Select
If Not IsNumeric(Selection.Value) Then
Selection.Value = 7
nErrCount = nErrCount + 1
ActiveCell.Font.Color = vbRed
Elself Selection.Value < 4 Or Selection.Value > 10 Then
Selection.Value = 7
nErrCount = nErrCount + 1
ActiveCell.Font.Color = vbRed
End If
.InputPH = Selection.Value
28
-------
ActiveCell.Offset(0, 1).Select
' .FeedTDS = Selection.Value 'not used, bypass it
ActiveCell.Offset(0, 1).Select
' these are string - don't matter
.FeedUnits = Selection.Value
' feed units can range from 1 to 3 - ppm,
ActiveCell.Offset(0, 1).Select
If Not IsNumeric(Selection.Value) Then
Selection.Value = 1
nErrCount = nErrCount + 1
ActiveCell.Font.Color = vbRed
Elself Selection.Value < 1 Or Selection
Selection.Value = 1
nErrCount = nErrCount + 1
ActiveCell.Font.Color = vbRed
End If
.nUnits = Selection.Value
' now flow units
ActiveCell.Offset(0, 1) .Select
.FlowUnits = Selection.Value
ActiveCell.Offset(0, 1).Select
If Not IsNumeric(Selection.Value) Then
Selection.Value = 1
nErrCount = nErrCount + 1
ActiveCell.Font.Color = vbRed
Elself Selection.Value < 1 Or Selection.Value > 6 Then
Selection.Value = 1
nErrCount = nErrCount + 1
ActiveCell.Font.Color = vbRed
End If
.nFlowUnits = Selection.Value
caco3, meq
.Value > 3 Then
' now the ions
For i = ION_NA To I0N_S03
ActiveCell.Offset(0, 1).Select
If Not IsNumeric(Selection.Value) Then
Selection.Value = 0
ActiveCell.Font.Color = vbRed
Elself Selection.Value < 0 Then
Selection.Value = 0
ActiveCell.Font.Color = vbRed
End If
Feedlons(i) = Selection.Value
Next i
' now include chlorine
ActiveCell.Offset(0, 1).Select
If Not IsNumeric(Selection.Value) Then
Selection.Value = 0
ActiveCell.Font.Color = vbRed
nErrCount = nErrCount + 1
Elself Selection.Value < 0 Then
Selection.Value = 0
ActiveCell.Font.Color = vbRed
nErrCount = nErrCount + 1
End If
Range("User_Chlorine").Value = Selection.Value
' error check and stop
' if the nerrcount > 1 then we need to ask if we want to continue
If nErrCount > 1 Then
Dim strMsg As String
Dim nResponse As Integer
strMsg = "There were > " & nErrCount & " < errors during the load and
the data set you selected. Errors include invalid units, and negative or missing
&
validation of
or nonnumeric
29
-------
"values where numeric values were expected. The program tried to
correct them. " & _
"It might be useful to recheck the data row you selected and try
again now that the errors were corrected."
nResponse = MsgBox(strMsg, vbAbortRetrylgnore)
If nResponse = vbAbort Or nResponse = vbRetry Then
Range(Cells(nRow, 1), Cells(nRow, 1)).Select
Exit Sub
End If
End If
now put all of the data in tha variable back onto the spreadsheet
Range("Input_projectname").Value = .ProjectName
Range("Input_projectDate").Value = .Projectdate
9 Feb 2000 - last minute update
need to move the flow rate after the recovery, since recovery maintains constant perm flow
' now must first change to gpm units
Range("FlowUnit").Value = 1
Flow_Select_Drop_Click
Range("FeedFlow").Value = .FeedGPM
Range("FlowUnit").Value = .nFlowUnits
Flow_Select_Drop_Click
' Change to Deg F first
Range("Select_temperature").Value = 1
Temp_Select_click
Range("Input_Temp").Value = .TempF
' Recovery and Rejection are simple changes
Range("Input_Recovery").Value = .Recovery
Range("SystemRecovery").Value = .Recovery
Range("Input_Rejection").Value = .Rejection
9 feb 2000 - moved the flow update to after the recovery update
Range("FeedFlow").Value = .FeedGPM
now change flow units
Range("FlowUnit").Value = .nFlowUnits
Flow_Select_Drop_Click
' Change acid to new type from file
Range("Select_acid").Value = .AcidType
Acid_Select_Click
' change the set point for the pH - only useful if acid is specified
Range("Input_Feed_pH").Value = .InputPH
' need to handle the units section a little differently
Range("select_analytical").Value = .nUnits
An a1yi s Group_Click
End With
' Now put in the ions
' calculation must be
Application.Iteration =
Application.Calculation
off
False
= xlCalculationManual
July 1999 Changes
Added new names to spreadsheet called User_NA, User_MG etc.
Before 8 July 1999 copied from FeedlonsO to Range("Feed_NA")
Now to Range("User_NA") etc.
Range
Range
Range
Range
Range
Range
Range
'User_Na").Value =
'User_K").Value =
'User_CA").Value =
'User_MG").Value =
'User_SR").Value =
'User_BA").Value =
'User NH4").Value
Feedlons(ION_NA)
Feedlons(ION_K)
Feedlons(ION_CA)
Feedlons(ION_MG)
Feedlons(ION_SR)
Feedlons(ION_BA)
= Feedlons(ION NH4)
'calculated - Range("User_H").Value = Feedlons(Ion_H)
' 11/1/99 - reset the total inorganic entry flag to NO
30
-------
' 2/9/00 use the changecarbon routine here
Dim strflag As String
strflag = Range("TotallnorganicCarbonFlag").Value
strflag = Left(strflag, 1)
If strflag = "y" Or strflag = "Y" Then
ChangeCarbon
End If
'Range("TotallnorganicCarbonFlag")•Value = "N"
Range("User_HC03").Value = Feedlons(I0N_HC03)
'calculated - Range("User_C02").Value = Feedlons(Ion_C02)
'calculated - Range("User_C03").Value = Feedlons(Ion_C03)
Range("User_BR").Value = Feedlons(ION_BR)
Range("User_CL").Value = Feedlons(ION_CL)
Range("User_F").Value = Feedlons(ION_F)
Range("User_S04").Value = Feedlons(I0N_S04)
Range("User_N03").Value = Feedlons(I0N_N03)
Range("User_P04").Value = Feedlons(I0N_P04)
'calculated - Range("User_OH").Value = Feedlons(Ion_OH)
Range("User_SI02").Value = Feedlons(I0N_SI02)
Range("User_pH").Value = Feedlons(Ion_pH)
Range("User JDRGANICS") .Value = Feedlons(ION_ORGANICS)
Range("User_AG").Value = Feedlons(Ion_AG)
Range("User_AL").Value = Feedlons(ION_AL)
Range("User_AS").Value = Feedlons(Ion_AS)
Range("User_AU").Value = Feedlons(Ion_AU)
Range("User_CD").Value = Feedlons(Ion_CD)
Range("User_CR").Value = Feedlons(Ion_CR)
Range("User_CU").Value = Feedlons(Ion_CU)
Range("User_FE2").Value = Feedlons(Ion_FE2)
Range("User_FE3").Value = Feedlons(ION_FE3)
Range("User_HG").Value = Feedlons(Ion_HG)
Range("User_MN").Value = Feedlons(Ion_MN)
Range("User_NI").Value = Feedlons(Ion_NI)
Range("User_PB").Value = Feedlons(Ion_PB)
Range("User_SE").Value = Feedlons(Ion_SE)
Range("User_SN").Value = Feedlons(Ion_SN)
Range("User_TI").Value = Feedlons(Ion_TI)
Range("User_V").Value = Feedlons(Ion_V)
Range("User_ZN").Value = Feedlons(Ion_ZN)
Range("User_B407").Value = Feedlons(I0N_B407)
Range("User_N02").Value = Feedlons(I0N_N02)
Range("User_S03").Value = Feedlons(I0N_S03)
' End 8 July 1999 Changes
' turn on iteration & Calculation
Application.Iteration = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
BoolLoading = False
' set the flag for needs recalc
Worksheets("Summary").Range("SystemCalculated").Value = "Needs Recalc"
Worksheets("Streams").Range("SystemCalculatedl").Value = "Needs Recalc"
Worksheets(MainSheet).Activate
Range("Al").Select
Range("D4").Select
End Sub
Sub SortbyDateO
Range("All").Select
Selection.Sort keyl:=Range("A12"), orderl:=xlDescending, key2:=Range("B12"
), order2:=xlAscending, Header:=xlGuess, ordercustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom
End Sub
Sub SortbyProject()
Range("Bll").Select
Selection.Sort keyl:=Range("B12") , orderl:=xlAscending, key2:=Range("A12"
), order2:=xlDescending, Header:=xlGuess, ordercustom:=1, MatchCase:=
31
-------
False, Orientation:=xlTopToBottom
End Sub
Sub SortByFlowf)
Range("Cll").Select
Selection.Sort keyl:=Range("C12"), orderl:=xlDescending, key2:=Range( _
"B12"), order2:=xlAscending, Header:=xlGuess, ordercustom:=l, MatchCase
:=False, Orientation:=xlTopToBottom
End Sub
Sub SortbyFeedTDS ()
Range("111").Select
Selection.Sort keyl:=Range("I12"), orderl:=xlDescending, key2:=Range("A12"
), order2:=xlAscending, Header:=xlGuess, ordercustom:=l, MatchCase:= _
False, Orientation:=xlTopToBottom
End Sub
Sub SortbyRecovery()
Range("Ell").Select
Selection.Sort keyl:=Range("E12"), orderl:=xlDescending, key2:=Range("A12"
), order2:=xlAscending, Header:=xlGuess, ordercustom:=l, MatchCase:= _
False, Orientation:=xlTopToBottom
End Sub
RO/NF Ions
Attribute VB_Name = "RONFIons"
Option Explicit
These constants allow access to ions by using a meaningful value. For example, suppose the array Ions(l
to 25) contains the values of the different ions. To access the Ca concentration, we can use Ions(SP Ca)
rather than a call to Ions(3).
Public
Const
SP
Na =
1
Public
Const
sp"
K =
2
Public
Const
sp"
Ca =
3
Public
Const
sp"
Mg =
4
Public
Const
sp"
Sr =
5
Public
Const
sp"
Ba =
6
Public
Const
sp"
Fe3
= 7
Public
Const
sp"
A1 =
8
Public
Const
sp"
NH4
= 9
Public
Const
sp"
HC03
= 1
Public
Const
sp"
C02
= 11
Public
Const
sp"
C03
= 12
Public
Const
sp"
Br =
13
Public
Const
sp"
CI =
14
Public
Const
sp"
F =
15
Public
Const
sp"
S04
= 16
Public
Const
sp"
N03
= 17
Public
Const
sp"
P04
= 18
Public
Const
sp"
Si02
= 1
Public
Const
sp"
Organic :
Public
Const
sp"
Ag =
21
Public
Const
sp"
As =
22
Public
Const
sp"
Au =
23
Public
Const
sp"
Cd =
24
Public
Const
sp"
Cr =
25
Public
Const
sp"
Cu =
26
Public
Const
sp"
Fe2
= 27
Public
Const
sp"
~Hg =
28
Public
Const
sp"
Mn =
29
Public
Const
sp"
Ni =
30
Public
Const
sp"
Pb =
31
Public
Const
sp"
Se =
32
Public
Const
sp"
Sn =
33
Public
Const
sp"
Ti =
34
Public
Const
sp"
V =
35
Public
Const
sp"
Zn =
36
32
-------
Public Const SP_B407 = 37
Public Const SP_N02 = 38
Public Const SP_S03 = 39
Public Const LAST_ION =39
Type IonType
Charge As Double
MolWt As Double
Name As String
End Type
Public Ions(LAST_ION) As IonType
Similar approach to units
'Public Const FLOW_MSEC = 1 / 60 / 2 64.2
Public Const FLOW_M3SEC = 0
Public Const FLOW_GPM = 1
Public Const FLOW_GPD = 2
Public Const FLOW_M3HR = 3
Public Const FLOW_M3DAY = 4
' change to L/Min from L/sec
'Public Const FLOW_LSEC = 5
Public Const FLOW_LMIN = 5
Public Const FLOW_LHR = 6
Public Const PRESS_ATM = 0
Public Const PRESS_PSI = 1
Public Const PRESS KPA = 2
A function to convert to and from ATM to Psi or back, depending on what flow unit the user selected.
Public Function ATMtoPRESS(ATM, UserUnits As Integer) As Double
Dim Multiplier As Double
PSI if GPD or GPM, ATM else
Select Case UserUnits
Case FLOW_GPM
Multiplier = 14.69
Case FLOW_GPD
Multiplier = 14.69
Case Else
Multiplier = 1
End Select
ATMtoPRESS = Multiplier * ATM
End Function
Converts flow to m3/sec from the user flow rates on the sheet
Public Function FlowM3SEC(InputFlow As Double, UserUnits As Integer) As Double
' going from userunits to mA3/sec
Dim Multiplier As Double
Select Case UserUnits
Case FLOW GPM
Multiplier =
1#
/
15852# '60 * 2
Case FLOW GPD
Multiplier =
1#
/
22826880# '864
Case FLOW M3HR
Multiplier =
1#
/
3600#
Case FLOW M3DAY
Multiplier =
1#
/
86400#
'Case FLOW LSEC
' Multiplier =
: 0.
001
Case FLOW LMIN
Multiplier =
1#
/
60000#
Case FLOW LHR
Multiplier =
1#
/
3600000#
Case Else
Multiplier =
1
End Select
FlowM3SEC = Multiplier * InputFlow
End Function
33
-------
Goes back to User Flow units from m3/sec - the internal units
Public Function FlowUser(InputFlow As Double, UserUnits As Integer) As Double
' going from M3/sec to the User units
Dim Multiplier As Double
Select Case UserUnits
Case FLOW_GPM
Multiplier = 15852# '60 * 264.2
Case FLOW_GPD
Multiplier = 22826880# '86400 * 264.2
Case FLOW_M3HR
Multiplier = 3600#
Case FLOW_M3DAY
Multiplier = 86400#
'Case FLOW_LSEC
' Multiplier = 1000#
Case FLOW_LMIN
Multiplier = 60000#
Case FLOW_LHR
Multiplier = 3600000#
Case Else
Multiplier = 1
End Select
FlowUser = Multiplier * InputFlow
End Function
Used to calculate osmotic pressure from a water analysis. The values are passed in the form of meq/l in
an array ordered as shown in the list of variables above.
'=========================================================
' OsmoticPressureMEQ (IonValues, nCount, Temperature)
' Calculates the osmotic pressure for a stream of the
' specified ion composition as meq/l. Uses Van't Hoff method with
' modified Debye Huckel activity.
!
' Input variables:
' meqValuesO An Array with the concentration data as meq/l
' ncount The highest value of the Array
' Temperature The temperature of the stream in deg C
!
' Pi = cRT * Activity, where
' R = 0.082054 liter-atm/mol-DegK
' c = concentration, mol/liter (not equiv/liter)
' Activity = (10A(-0.5ZA2*IonStrA0.5/(1+IonStrA0.5)))A0.14
Public Function OsmoticPressureMEQ(meqValues() As Double, ByVal Temperature As Double) As Double
Dim i As Integer, z As Integer
Dim PITemp As Double, RT As Double
Dim ActCoef As Double, floatDummy As Double
PITemp = 0#
RT = 0.082054 * (Temperature + 273#) ' 14.69 converts answer to psi
RT = RT / 1000 ' convert to moles/1 from mm/1
floatDummy = Sqr (IonicStrengthfrorrMeq (meqValues ()) )
For i = 0 To LAST_ION
' skip C02
If i <> SP_C02 Then
' note when Z=0, ActCoef =1.0 (for Si02)
z = Ions(i).Charge
If z = 0 Then z = 1
ActCoef = (10# A (-0.5 * z * z * floatDummy /
PITemp = PITemp + ActCoef * RT * meqValues(i)
to get to moles/1, eq 2 meq/l Ca = 1 mm/1 Ca
End If
Next i
OsmoticPressureMEQ = PITemp
End Function
(1# + floatDummy))) A 0.14
/ Abs(z) ' divide by z
34
-------
The Ionic strength calculation.
IonicStrengthfromMeq (floatMeq,nCount)
Calculates the ionic strength for the stream ions
passed to the function.
IONIC STRENGTH = 1/2 * SUMMATION [CONC * CHARGEA2]
Concentration expressed as moles/1 (NB - not meq/1)
If already in meq then 1 mole/1 = (meq/1)/abs(Charge)
so IS = 1/2 * summation[cone * abs (charge)]
Since data is in meq and not eq/1
divide by 1000 at end
Input Variables
floatMeq - Array of concentrations in meq/1
Public Function IonicStrengthfromMeq(floatMeq() As Double) As Double
Dim floatlonStr As Double
Dim i As Integer
floatlonStr = 0#
For i = 0 To LAST_ION
If i <> SP_C02 Then
' ignores C02 and organics
If i <> SP_Organic Then
floatlonStr = floatlonStr + floatMeq(i) * Abs(Ions(i).Charge)
End If
End If
Next i
' Set some nonzero value just in case to avoid division by zero somewhere.
If floatlonStr < 0.0000001 Then floatlonStr = 0.0000001
IonicStrengthfromMeq = floatlonStr / (2# * 1000#)
End Function
Ionic strength from an analysis by ion. Included for convenience
IonicStrength (floatlons,nCount)
Calculates the ionic strength for the stream number
passed to the function.
IONIC STRENGTH = 1/2 * SUMMATION [CONC * CHARGEA2]
Concentration expressed as moles/1 (NB - not meq/1)
Since data is in ppm then converted to meq/1 need to
divide by 1000 at end
Input Variables
floatlons - Array of concentrations in ppmw
Public Function IonicStrength(floatlONS() As Double) As Double
Dim floatlonStr As Double
Dim i As Integer
floatlonStr = 0#
For i = 0 To LAST_ION
' first check to make sure we have non-zero values
If Ions(i) .MolWt <> 0# And Ions (i).Charge <> 0# Then
' Convert IonValuesO to moles/1 on the fly
floatlonStr = floatlonStr + floatlONS(i) / Ions(i).MolWt * Ions(i).Charge
End If
Next i
' Set some nonzero value just in case to avoid division by zero somewhere.
If floatlonStr < 0.0000001 Then floatlonStr = 0.0000001
IonicStrength = floatlonStr / (2# * 1000#)
End Function
Used for permeate and concentrate reconciliation after C02 passes through membrane
C02toPH(floatlonStr,Temperature,Hplus,HC03, C02, C03)
35
-------
Returns new distrbution of carbonates and new pH based TotalCarbon coming in, C02,
HC03 and C03 Concentrations
See also Carbonates, and CarbonateEquilibria
Carbonate Equilibria data using Stumm and Morgan, Aquatic Chemistry
pages 120-121 and 148-150
Linear Regression analysis of Stumm and Morgan data for K1 and K2 versus temperature
Estimation of Activity Change as Ionic strength from Stumm and Morgan, p 14 9.
Inputs are C02, HC03, and C03
N.B. Input data is in meq/1 !!
Outputs are pH and new distribution of HC03 and C03 to fit new pH
NB - HPlus in moles/liter is returned, not pH
Public Sub C02toPH(ByVal floatlonStr As Double, ByVal Temperature As Double, _
ByRef HPlus As Double, ByRef HC03 As Double, ByRef C02 As Double, ByRef C03 As Double)
' NB HPlus, C03, C02, HC03 are changed by this subroutine - using ByRef
Dim K1 As Double, K2 As Double
Dim pKl As Double, pK2 As Double
Dim MF_C02 As Double, MF_C03 As Double, MF_HC03 As Double 'MF = Mole Fraction
Dim TotalCarbon As Double
'DEAL WITH ZERO VALUES
If HC03 = 0 Then
C03 = 0
C02 = 0
HPlus = 10 A (-7) ' PH SET AT 7
Exit Sub
End If
' first correct K1 and K2 for temperature, using Excel derived equation
pKl = -0.007*(Temp+273)+8.4454
pK2 = -0.0101*(273+Temp)+13.368
pKl = -0.007 * (Temperature + 273#) + 8.4454
pK2 = -0.0101 * (Temperature + 273#) + 13.368
' now correct for ionic strength
pKl = pKl - 0.5 * Sqr(floatlonStr) / (1# + 1.4 * Sqr(floatlonStr))
pK2 = pK2 - 2# * Sqr(floatlonStr) / (1# + 1.4 * Sqr(floatlonStr))
K1 = 10# A (-pKl)
K2 = 10# A (-pK2)
' First get the total carbon figured
TotalCarbon = C03 / 2 + HC03 + C02 / 2
If HC03 <= 0 Then HC03 = 0.001639
' 2 meqs/mol for C03, others 1 to 1
minimum 0.1 ppm = .1/61
'Very high pH case
If C03 / 2 > HC03 Then
HPlus = K2 * HC03 / (C03 / 2)
Else
HPlus = K1 * (C02 / 2) / HC03
End If
' very high ph -10.33
' Do it normally with C02/HC03 ratio
' Now have the pH, figure out the new distribution of carbonates
' keeping C02 constant and changing the C03/HC03 ratio as needed.
MF_HC03 = 1# / (HPlus / K1 + 1# + K2 / HPlus) ' Stumm and Morgan, alphal, p 120
MF_C03 = 1# / (HPlus A 2 / (K1 * K2) + HPlus / K2 + 1)
MF_C02 = 1# / (1# + K1 / HPlus + (K1 * K2) / HPlus A 2)
' now re-assign C02, HC03, and C03 based on calculated distrbutions
HC03 = TotalCarbon * MF_HC03 ' 1 meq = 1 mmole so no change needed
C03 = TotalCarbon * MF_C03 * 2# '1 meq = 0.5 mmoles or 1 mmole = 2 meq!
C02 = TotalCarbon * MF_C02 * 2
End Sub
Returns the differential pressure for an element in atm. Needs the dpCoef and flows.
36
-------
'==========DELTA PRESSURE FUNCTION==========
DP = c * FLOW_AVERAGE A 1.5
' DP in atm
' FLOW_average in mA3/sec
!
' since FeedFlow and ConcFlow passed as mA3/sec
' dpCoef is taken from the Membrane Data sheet at calc time
' and is passed to this sub as a real value
' dpCoef is already computed in terms of mA3/sec and atm
' Since local units are atm and mA3/sec, function expects mA3/sec as flows
' To use other flows adjust as needed.
!
Public Function DeltaPressure(FeedFlow As Double, ConcFlow As Double, dpCoef As Double) As Double
Dim c As Double
Dim Flow_Average As Double
Dim dp As Double
c = dpCoef
If FeedFlow < 0 Then FeedFlow = 0
If ConcFlow <= 0 Then ConcFlow = FeedFlow
Flow_Average = (FeedFlow + ConcFlow) / 2
DeltaPressure = c * Flow_Average A 1.5
' done
End Function
Used to set up the charge and molecular weight data for each ion
' InitializelonData
!
' Sets initial values for all ionic species. Called by
' InitializeData
Public Sub InitializelonData()
' Initialize ion charge values
Ions
SP
Ca).Charge =
2
Ions
sp"
Mg).Charge =
2
Ions
sp"
Na) .Charge =
1
Ions
sp"
K) .Charge =
1
Ions
sp"
NH4).Charge
= 1
Ions
sp"
Sr).Charge =
2
Ions
sp"
Ba).Charge =
2
Ions
sp"
Fe3).Charge
= 3
Ions
sp"
A1) .Charge =
3
Ions
sp"
C03).Charge
= -2
Ions
sp"
HC03).Charge
= -1
Ions
sp"
C02).Charge
= -2
Ions
sp"
S04).Charge
= -2
Ions
sp"
CI).Charge =
-1
Ions
sp"
Br).Charge =
-1
Ions
sp"
N03).Charge
= -1
Ions
sp"
F).Charge =
-1
Ions
sp"
P04).Charge
= -3
Ions
sp"
Si02).Charge
= 2
Ions
SP
Organic).Charge =
Ions
sp"
Ag).Charge =
1
Ions
sp"
As) .Charge =
3
Ions
sp"
Au).Charge =
3
Ions
sp"
Cd).Charge =
2
Ions
sp"
Cr).Charge =
3
Ions
sp"
Cu).Charge =
2
Ions
sp"
Fe2).Charge
= 2
Ions
sp"
Hg).Charge =
2
Ions
sp"
Mn).Charge =
2
Ions
sp"
Ni) .Charge =
2
Ions
sp"
Pb).Charge =
2
Ions
sp"
Se).Charge =
6
Ions
sp"
Sn).Charge =
2
Ions
sp"
Ti).Charge =
2
Ions
sp"
V) .Charge =
2
37
-------
Ions(SP_Zn).Charge = 2
Ions(SP_B407).Charge = -2
Ions(SP_N02).Charge = -1
Ions(SP_S03).Charge = -2
' Initialize ion molecular wieght
Ions(SP_Ca).MolWt = 4 0.08
Ions(SP_Mg).MolWt = 24.305
Ions(SP_Na).MolWt = 22.99
Ions(SP_K).MolWt = 39.098
Ions(SP_NH4).MolWt = 18.04
Ions(SP_Sr).MolWt = 87.62
Ions(SP_Ba).MolWt = 137.34
Ions(SP_Fe3).MolWt = 55.847
Ions(SP_A1).MolWt = 26.98154
Ions(SP_C03).MolWt = 60.01
Ions(SP_HC03).MolWt = 61.01
Ions(SP_C02).MolWt = 4 4.04
Ions(SP_S04).MolWt = 9 6.06
Ions(SP_Br).MolWt = 7 9.904
Ions(SP_C1).MolWt = 35.45
Ions(SP_N03).MolWt = 62.005
Ions(SP_F).MolWt = 19#
Ions(SP_P04).MolWt = 94.9738
Ions(SP_Si02).MolWt = 60.08
Ions(SP_Organic).MolWt = 1
Ions(SP_Ag).MolWt = 107.868
Ions(SP_As).MolWt = 7 4.9216
Ions(SP_Au).MolWt = 196.9665
Ions(SP_Cd).MolWt = 112.4
Ions(SP_Cr).MolWt = 51.996
Ions(SP_Cu).MolWt = 63.54 6
Ions(SP_Fe2).MolWt = 55.847
Ions(SP_Hg).MolWt = 200.59
Ions(SP_Mn).MolWt = 54.938
Ions(SP_Ni).MolWt = 58.7
Ions(SP_Pb).MolWt = 207.2
Ions(SP_Se).MolWt = 78.96
Ions(SP_Sn).MolWt = 118.69
Ions(SP_Ti).MolWt = 4 7.9
Ions(SP_V).MolWt = 50.9419
Ions(SP_Zn).MolWt = 65.38
Ions(SP_B407).MolWt = 155.2358
Ions(SP_N02).MolWt = 46.0055
Ions(SP~S03).MolWt = 80.058
names
Ions(SP_Na).Name = "Na = 1"
Ions(SP_K).Name = "K = 2"
Ions(SP_Ca).Name = "Ca = 3"
Ions(SP_Mg).Name = "Mg = 4"
Ions(SP_Sr).Name = "Sr = 5"
Ions(SP_Ba).Name = "Ba = 6"
Ions(SP_Fe3).Name = "Fe3 = 7"
Ions(SP_A1).Name = "A1 = 8"
Ions(SP_NH4).Name = "NH4 = 9"
Ions(SP_HC03).Name = "HC03 = 10"
Ions(SP_C02).Name = "C02 = 11"
Ions(SP_C03).Name = "C03 = 12"
Ions(SP_Br).Name = "Br = 13"
Ions(SP_C1).Name = "CI = 14"
Ions(SP_F).Name = "F = 15"
Ions(SP_S04).Name = "S04 = 16"
Ions(SP_N03).Name = "N03 = 17"
Ions(SP_P04).Name = "PCM = 18"
Ions(SP_Si02).Name = "Si02 = 19"
Ions(SP_Organic).Name = "Organic
Ions(SP_Ag).Name = "Ag = 21"
Ions(SP_As).Name = "As = 22"
Ions(SP_Au).Name = "Au = 23"
-------
Ions
SP
Cd) .
Name =
"Cd
=
24"
Ions
sp"
Cr) .
Name =
"Cr
=
25"
Ions
sp"
Cu) .
Name =
"Cu
=
2 6"
Ions
sp"
Fe2)
.Name =
= "Fe2
= 27
Ions
sp"
~Hg) .
Name =
"Hg
=
28"
Ions
sp"
Mn) .
Name =
"Mn
=
2 9"
Ions
sp"
Ni) .
Name =
"Ni
=
30"
Ions
sp"
Pb) .
Name =
"Pb
=
31"
Ions
sp"
Se) .
Name =
"Se
=
32"
Ions
sp"
Sn) .
Name =
"Sn
=
33"
Ions
sp"
Ti) .
Name =
"Ti
=
34"
Ions
sp"
V).Name =
'V =
35"
Ions
sp"
Zn) .
Name =
"Zn
=
3 6"
Ions
sp"
B407).Name
= "B407 =
Ions
sp"
N02)
.Name =
"N02
= 38
Ions
sp"
S03)
.Name =
"S03
= 39
End Sub
Used to read in the water analysis from the Analysis sheet. Range names refer to the Treated column
Public Sub GetTreatedlons (ByRef floatlONSO As Double)
floatlONS
floatlONS
floatlONS
floatlONS
floatlONS
floatlONS
floatlONS
floatlONS
floatlONS
floatlONS
floatlONS
floatlONS
floatlONS
floatlONS
floatlONS
floatlONS
floatlONS
floatlONS
floatlONS
floatlONS
floatlONS
floatlONS
floatlONS
floatlONS
floatlONS
floatlONS
floatlONS
floatlONS
floatlONS
floatlONS
floatlONS
floatlONS
floatlONS
floatlONS
floatlONS
floatlONS
floatlONS
floatlONS
floatlONS
End Sub
SP Na)
Range("Treated_Na").Value
SP_K) = Range("Treated_K").Value
SP_Ca) = Range("Treated_CA").Value
SP_Mg) = Range("Treated_MG").Value
SP_Sr) = Range("Treated_SR").Value
SP_Ba) = Range("Treated_BA").Value
SP_NH4) = Range("Treated_NH4").Value
SP_HC03) = Range("Treated_HC03").Value
SP_C02) = Range("Treated_C02").Value
SP_C03) = Range("Treated_C03").Value
SP_Br) = Range("Treated_BR").Value
SP_C1) = Range("Treated_CL").Value
SP_F) = Range("Treated_F").Value
SP_S04) = Range("Treated_S04").Value
SP_N03) = Range("Treated_N03").Value
SP_P04) = Range("Treated_P04").Value
SP_Si02) = Range("Treated_SI02").Value
SP_Organic) = Range("Treated_ORGANICS").Value
SP_Ag) = Range("Treated_AG").Value
SP_A1) = Range("Treated_AL").Value
SP_As) = Range("Treated_AS").Value
SP_Au) = Range("Treated_AU").Value
SP_Cd) = Range("Treated_CD").Value
SP_Cr) = Range("Treated_CR").Value
SP_Cu) = Range("Treated_CU").Value
SP_Fe2) = Range("Treated_FE2").Value
SP_Fe3)
SP_Hg) =
S P_Mn) =
SP_Ni) =
SP_Pb) =
SP_Se) =
SP_Sn) =
SP Ti) =
= Range("Treated_FE3").Value
Range("Treated_HG").Value
Range("Treated_MN")
Range("Treated_NI")
Range("Treated_PB")
Range("Treated_SE")
Range("Treated_SN")
.Value
.Value
.Value
•Value
•Value
Range("Treated_TI").Value
SP_V) = Range("Treated_V").Value
SP_Zn) = Range("Treated_ZN").Value
SP_B407) = Range("Treated_B407").Value
SP_N02) = Range("Treated_N02").Value
SP_S03) = Range("Treated_S03").Value
ConvertMEQtoPPM (floatMEQ () As Double, ByRef floatlonsO As Double)
floatMeqO is the data coming in with the analysis as meq/1,
the floatlonsO variable gets the new data put in it as ppm
eg Ca as 2 meq/1 = 2 * 20 = 40 ppm
Si02 as 1 meq/1 = 1 * 60 = 60 ppm
39
-------
Public Sub ConvertMEQtoPPM (MEQ () As Double, ByRef floatlONSO As Double)
Dim i As Integer
For i = 1 To LAST_ION
If Ions(i).Charge <> 0 Then
floatlONS(i) = MEQ(i) * Ions(i).MolWt / Abs(Ions(i).Charge)
Else
floatlONS(i) = MEQ(i)
End If
Next i
End Sub
ConvertPPMtoMEQ(byref MEQ() As Double, Ions() As Double)
Ions() is the data coming in with the analysis as ppm
the MEQ() variable gets the new data put in it as meq/1
eg Ca as 40 ppm, 40 /20 = 2
Si02 as 90 ppm, 90 / 60 = 1.5
Public Sub ConvertPPMtoMEQ (ByRef MEQ() As Double, floatlONSO As Double)
Dim i As Integer
For i = 1 To LAST_ION
If Ions(i).Charge <> 0 Then
MEQ(i) = floatlONS(i) / (Ions(i) .MolWt / Abs(Ions(i).Charge))
'Debug.Print Ions(i).Name, floatlONS(i), MEQ(i)
Else
MEQ(i) = floatlONS(i)
End If
'If boolDebug Then Debug.Print i, floatlons(i), MEQ(i), Ions(i).MolWt, Ions(i).Charge
Next i
End Sub
Public Function TDSfromMEQ(floatMeq() As Double) As Double
Dim i As Integer
Dim floatTemp As Double
Dim floatDummy As Double
floatTemp = 0#
For i = 1 To LAST_ION
If Ions(i).Charge <> 0 Then
floatDummy = floatMeq(i) * Ions(i).MolWt / Abs(Ions(i).Charge)
End If
floatTemp = floatTemp + floatDummy
Next i
' now remove C02
TDSfrorrMEQ = floatTemp - (floatMeq (SP_C02) * Ions (SP_C02).MolWt / Abs (Ions (SP_C02). Charge)
End Function
Public Function TDS(floatlONS() As Double) As Double
Dim i As Integer
Dim floatTemp As Double
floatTemp = 0#
For i = 1 To LAST_ION
floatTemp = floatTemp + floatlONS(i)
Next i
TDS = floatTemp
End Function
This function is the blending function. It takes the recycle stream and blends it with the feed stream to
the "Net Feed" going to the membranes.
Public Sub GetNetFeed(ByRef Netlonsf) As Double, ByRef NewFlow As Double, _
FeedlonsO As Double, FlowFeed As Double, _
Recyclelons() As Double, FlowRecycle As Double)
Dim n As Integer
Dim Istr As Double
If FlowRecycle < 0 Then FlowRecycle = 0
NewFlow = FlowFeed + FlowRecycle
40
-------
If FlowRecycle <= 0 Then
For n = 1 To LAST_ION
Netlons(n) = Feedlons(n)
Next n
Else
For n = 1 To LAST_ION
Netlons(n) = (FlowFeed * Feedlons(n) + FlowRecycle * Recyclelons(n)) / NewFlow
Next n
' may need to deal with the change in pH and carbonates as the concentration changes
' but for calc purposes pH is really ignored as it is not in the list of ions
' iStr = IonicStrengthfromMeq(Netlons())
' C02toPH iStr, Temperature, FeedpH, Netlons(I0N_HC03), Netlons(I0N_C02), Netlons(I0N_C03)
End If
End Sub
Balances the permeate analysis based on both anions and cations (for RO) or for anions as the
controlling ions (NF)
Public Sub BalanceAnalysis(ByRef floatlONS () As Double, Optional boolNF As Boolean)
' used to balance the PermMeqO ions during the calculation phase
' argument is expected in the form of meq(s)
' the floationsO is directly modified
' Si02, C02, and organics are ignored.
Dim n As Integer
Dim Mult As Double
Dim sumCations As Double
Dim sumAnions As Double
sumAnions = 0
sumCations = 0
' cations
For n = 1 To SP_NH4
sumCations = sumCations + floatlONS(n)
Next n
For n = SP_Ag To SP_Zn
sumCations = sumCations + floatlONS(n)
Next n
' anions (skip C02)
For n = SP_C03 To SP_P04
sumAnions = sumAnions + floatlONS(n)
Next n
' go back for HC03 itself
sumAnions = sumAnions + floatlONS(SP_HC03)
For n = SP_B407 To SP_S03
sumAnions = sumAnions + floatlONS(n)
Next n
' now have the sums, if zero we are in trouble somewhere - get out to avoid an error message
If sumCations = 0 Or sumAnions = 0 Then Exit Sub
' how will we process the information
If boolNF Then
' balance ONLY on the anions. Anions control passage so the cations are raised or lowered to
match
' example sum cations = 9, sumanions = 6, mult = 6/9 to give equality
Mult = sumAnions / sumCations
For n = 1 To SP_NH4
floatlONS(n) = floatlONS(n) * Mult
Next n
For n = SP_Ag To SP_Zn
floatlONS(n) = floatlONS(n) * Mult
Next n
Else
' standard RO
' example sum cations = 9, sum anions = 6. 15/2 = 7.5, 7.5/9 is mult
Mult = ((sumCations + sumAnions) / 2) / sumCations
41
-------
For n = 1 To SP_NH4
floatlONS(n) = floatlONS(n) * Mult
Next n
For n = SP_Ag To SP_Zn
floatlONS(n) = floatlONS(n) * Mult
Next n
' now anions
Mult = ((sumCations + sumflnions) / 2) / sumflnions
For n = SP_C03 To SP_P04
floatlONS(n) = floatlONS(n) * Mult
Next n
floatlONS(SP_HC03) = floatlONS(SP_HC03) * Mult
For n = SP_B407 To SP_S03
floatlONS(n) = floatlONS(n) * Mult
Next n
End If
End Sub
Used to print intermediate values to the immediate window during debugging
Public Sub Debuglons(Flons() As Double, PIons() As Double, CIons() As Double)
Debug.Print "Descr", "Feed", "Perm", "Cone"
Dim n As Integer
For n = 1 To LAST_ION
Debug.Print Ions(n) .Name, Format(Flons(n), "#.##"), Format(Plons(n), "#.####"),
Format(Clons(n), "#.##")
Next n
End Sub
RONFCalc
This is the main engine for calculating the system values. Calculates the RO or NF design,
calculates the solubility values by calling the Solver, and puts the data on the spreadsheet.
Attribute VB_Name = "RONFCalc"
Option Explicit
Public Const boolDebug = False
Public Const boolDebug2 = False
Public boolAbortScaleCalcs As Boolean
Public Const TYPE_CA = 1
Public Const TYPE_TFC = 2
Public Const TYPE_NF = 3
Public ATempCorr As Double
Public BTempCorr As Double
Public Temperature As Double
Public FeedpH As Double
Public boolBadDesign As Boolean
Public boolFullyCalcd As Boolean
Dim FeedMeq(LAST_ION) As Double
Dim PerrrMeq (LAST_ION) As Double
Dim MembMeq (LAST_ION) As Double
Dim MeqDiff(LAST_ION) As Double
Dim ConcMeq(LAST_ION) As Double
Dim NetFeedMeq(LAST_ION) As Double ' this is the blend of feed and recycle
Dim FeedFlow As Double
Dim PermFlow As Double
Dim ConcFlow As Double
Dim RecycleFlow As Double
Dim NetFlow As Double
Public B_Ions(LAST_ION) As Double
' variables used for getting the three carbonate balances
42
-------
' 1 = netfeed, 2 = concentrate, 3 = permeate
Dim Istr(3) As Double
Dim HPlus(3) As Double
Dim PPI(3) As Double
The variable which corresponds to one (1) vessel in a bank
Type BigElementType
Area_M2 As Double
Avalue As Double
BValue As Double
B_Ion(LAST_ION) As Double
dpCoef As Double
BetaCoef As Double
' in the form of exp (BetaCoef * Recovery), typically dependent on element length
' 40 inch = 0.7, eg at 15% recovery, exp(0.7 *.15) = exp(.105)
' betaCoef = -0.3492Ln(x) + 2.0152 where x is length in inche
End Type
Dim MyElement As BigElementType
A whole bank variable
Type BankType
nVessels As Integer
FeedFlow As Double
PermFlow As Double
ConcFlow As Double
' pressure information
FeedPress As Double
ConcPress As Double
' now as ions
FeedMeq(LAST_ION) As Double
ConcMeq(LAST_ION) As Double
PermMeq(LAST_ION) As Double
ConcPolarization As Double
PTransMemb As Double
End Type
The complete system is contained in this variable
Type ArrayType
nBanks As Integer
Bank(4) As BankType
Element As BigElementType
' Flows in mA3/sec
FeedFlow As Double
PermFlow As Double
ConcFlow As Double
RecycleFlow As Double
' Press in atm
FeedPress As Double
ConcPress As Double
Feedlons(LAST_ION) As
NetFeedlons(LAST_ION)
Permlons(LAST_ION) As
Conclons(LAST_ION) As
End Type
Carray is the actual name as used. Can't use the word "Array " alone as it is a reserved word.
Dim CArray As ArrayType
Double
As Double
Double
Double
43
-------
Pressing the calculation button launches this routine
Sub MainCalc()
Dim Pressure As Double
Dim NewPressure As Double
Dim StartPressure As Double
Dim niters As Long
Dim boolCalcAlways As Boolean
boolAbortScaleCalcs = False
niters = 0
Get all of the data into the system
Initiali zelonData
GetElementData
' get element needs to run first!
GetSystemData
Guess a pressure in the "guess "function
Pressure = FirstGuess
StartPressure = Pressure
Call the main routine
NewPressure = CalcSystem(StartPressure)
Display the data for flows and pressure, etc.
PutDataOnSheets
boolFullyCalcd = False
If boolBadDesign = False Then
Run the solubility calcs - ask first if the user wants to proceed if not TRUE
boolCalcAlways = Range("CalcAlways").Value
If boolCalcAlways = True Then
RunSolver (0)
boolFullyCalcd = True
Else
If MsgBox("Calculate all saturation data at this time? This takes a few seconds.
To suppress this message set the F2 Cell on the Calc sheet to true.", vbOKCancel) = vbOK Then
RunSolver 0
boolFullyCalcd = True
End If
End If
End If
Worksheets("System Design").Activate
Range("FeedPressure").Select
ShowNotes
End Sub
Show status - all OK if calc'd OK, etc. Labels appear on the spreadsheet
Sub ShowNotes()
If boolFullyCalcd
Worksheets
Worksheets
Worksheets
Worksheets
Elself boolBadDesi
Worksheets
Worksheets
Worksheets
Worksheets
True Then
Summary")
Streams")
System Des
System Des
gn = True Th
Summary").
Streams").
System Des
System Des
Else
End If
End Sub
Worksheets
Worksheets
Worksheets
Worksheets
Range
Range
ign
ign
en
Range
Range
ign
ign
'Summary").Range
'Streams").Range
'System Design
'System Design
.culated")
.Value = '
"YES"
.culatedl";
).Value =
"YES"
Visible =
False
Visible =
False
.culated")
.Value = '
"Not Valid"
.culatedl";
).Value =
"Not Valid"
Visible =
True
Visible =
True
.culated")
.Value = '
"SI Not valid"
.culatedl";
).Value =
"SI Not valid
Visible =
False
Visible =
False
44
-------
This is the main calculation engine
' set up the counters
' Calc method - converge to a constant permeate Quality and balanced analysis
' Flow to bank must be divided by number of vessels to do one vessel
' 1. Set Loop variable - PermeateBal
' 2. calculate a Beta (concentration polarization) from BetaCoef and Recovery for this bank
' 3. Calculate a net membrane surface concentration by ion from
' log mean avg cone = (Cf - Cb) / ln(Cf/Cb)
' net membrane cone = membMeqO = Beta * LMAvgConc
' Difference between membrane surface and permeate = meqDiff()
' MeqDiffO = membMeqO - permMeqO
' 4. calculate each ion in the permeat using Avalue, Bvalue, B_Ion,
' PermMeqO = meqDifff) * B_Ion () * B / (P*A + B)
' 5. Since C02 is unchanged from Feed to permeate, we need to exclude the C02 from the Balance
' and then recalc the carbonate equilibirum based on the new distribution of HC03 and
C02
' 6. Adjust the permeate balance by adding some cations and subtracting and equal amount of
anions (or vice versa)
' ' for NF, only the anions are adjusted since cations are not really rejected only
dragged along for charge neutrality
' 7. With the new PermMeqO compute a new concMeqO using Perm and Feed flows
' 8. Compute a new ConcPi and PermPi to get a new Net PI (FeedPI - ConcPI)/log(FeedPi/ConcPI)
- PermPi
' Pnet = Pressure - NetPI - DP/2
' 9. Compute new PermFlow using DP from old FeedFlow and ConcFlow
' PermFlow = PNet * AValue * Area
' 10. Compute new ConcFlow and New Recovery froim PermFlow
' 11. Recompute DP with New flows
' 12. Check PermeateTDS - OldPermeateTDS, if less than 1% we are done with this bank
' 13. Compute Total bank PermFlow as PermFlow * NVessels, FeedFlow
' Bank 2-4 in same way
' 14. Add up all permFlows.
' 15. Compare desired TargetFlow with calculated PermFlow
' ' NewPressure = TargetFlow/PermFlow * OldPressure
' 16 If targetFlow - PermFlow < 1% then we are done, otherwise try again.
Function CalcSystem(LastPress As Double) As Double
Dim n As Integer
Dim nBank As Integer
Dim i As Integer
Dim nNumbanks As Integer
' convergence variables
Dim DeltaFlow As Double
Dim DeltaPercent As Double
Dim TargetPermFlow As Double
Dim CalcPermFlow As Double
Dim OldPressure As Double
Dim NewPressure As Double
Dim niters As Integer
Dim Area As Double
Dim A As Double
Dim B As Double
Dim Beta As Double
Dim BetaCoef As Double
Dim dp As Double
Dim dpCoef As Double
Dim PI As Double
Dim CF As Double
Dim PermeateBal As Double
Dim TDS As Double
Dim SaltPass As Double
Dim Pressure As Double
Dim PNet As Double
Dim Recovery As Double
Pressure = LastPress
NewPressure = Pressure
45
-------
boolBadDesign
False
set up values used throughout the system
nNumbanks = CArray.nBanks
Area = MyElement.Area_M2
A = MyElement.Avalue
B = MyElement.BValue
dpCoef = MyElement.dpCoef
BetaCoef = MyElement.BetaCoef
Area in One Vessel,
always the same
always the same
always the same
always the same
always the same in any system
' start with known flow data, which is already stored in Bank(l) from FirstGuess
' convert back to single vessel flow
FeedFlow = CArray.Bank(1).FeedFlow / CArray.Bank(1).nVessels
ConcFlow = CArray.Bank(1).ConcFlow / CArray.Bank(1).nVessels
PermFlow = FeedFlow - ConcFlow
TargetPermFlow = CArray.PermFlow
CalcPermFlow = 0
' get first dp
dp = DeltaPressure(FeedFlow, ConcFlow, dpCoef)
' ions in first bank
For i = 1 To LAST_ION
FeedMeq(i) = CArray.Bank(1).FeedMeq(i)
PermMeq(i) = CArray.Bank(1).PermMeq(i)
ConcMeq(i) = CArray.Bank(1).ConcMeq(i)
Next i
If boolDebug Then
Debug.Print
Debug.Print
Debug.Print
Debug.Print
Debug.Print
Debug.Print
Debug.Print
Debug.Print
Debug.Print
End If
60
60
area", Area, Area * 10.76
dp", dp, dp * 14.69
feedflow", FeedFlow, FeedFlow
permflow", PermFlow, PermFlow
Pi", PI, PI * 14.69
A", A
TempCorr", ATempCorr
temperature", Temperature
pressure", Pressure, Pressure * 14.69
264.2
264.2
niters = 0
Set the convergence test right here. 0.005 is the default as released - ie pressures agree within 0.5%
Do While Abs(TargetPermFlow - CalcPermFlow) / TargetPermFlow > 0.005 ' 1 percent convergence
niters = niters + 1
If niters > 100 Then
MsgBox ("The program failed too converge - the results may be inaccurate.")
Exit Do
End If
Pressure = NewPressure
' Bank 1 = must always be done
For nBank = 1 To nNumbanks
' banks are calculated for one (1) vessel only, so need to adjust flows at
beginning and end
' Area is area of one (1) vessel
' first get the data into the local variables
FeedFlow = CArray.Bank(nBank).FeedFlow / CArray.Bank(nBank).nVessels
' N.B if the following two lines are included, then no convergence is possible
' Don't try it. It will be done the next pass through
' ConcFlow = CArray.Bank(nBank).ConcFlow / CArray.Bank(nBank).nVessels
' PermFlow = FeedFlow - ConcFlow
' now calculate the neq values for the bank
Recovery = PermFlow / FeedFlow
Beta = Exp(BetaCoef * Recovery)
For i = 1 To LAST_ION
MeqDiff(i) = (FeedMeq(i) + ConcMeq(i)) / 2 - PerrrMeq(i)
Next i
46
-------
' Pressure will change each time
' get dp for this bank
dp = DeltaPressure(FeedFlow, ConcFlow, dpCoef)
PNet = Pressure - OsmoticPressureMEQ(MeqDiff(), Temperature) - dp / 2
The main flow rate equation
PermFlow = A * Area * PNet
'If boolDebug2 Then Debug.Print
' if permflow is going negative (we assume this is impossible)
If PermFlow <= 0 Then
PermFlow = 0
Recovery = 0
Beta = 1
Else
Recovery = PermFlow / FeedFlow
Beta = Exp(BetaCoef * Recovery)
End If
' now get a salt passage rate
' if this is less than 0 then this means the dp and osmotic pressure is higher than
' applied pressure. In reality this can happen so we need to deal with the problem
' because in low recovery but multiple bank situations this is frequently a result.
' Physically the situation is that at the applied pressure needed to make the water
' there is not enough pressure to force the desired flow through the array of
' Need to give the user the message and ask him/her to try again.
SaltPass = Beta * B / (PNet * A + B)
If boolDebug2 Then Debug.Print nBank, "DP = "; dp * 14.69, SaltPass, PNet, PermFlow *
60 * 264.2
For i = 1 To LAST_ION
' first get ion data for the bank
FeedMeq(i) = CArray.Bank(nBank).FeedMeq(i)
'If ConcMeq(i) < 0 Then MsgBox "Less than 0"
ConcMeq(i) = CArray.Bank(nBank).ConcMeq(i)
PermMeq(i) = CArray.Bank(nBank).PermMeq(i)
' deal with 0 values
If i = SP_C02 Then
' first fix up CO
PermMeq(SP_C02) = FeedMeq(SP_C02)
Else
' skip 0 values
If FeedMeq(i) = 0 Or ConcMeq(i) = 0 Then
MeqDiff(i) =0
PermMeq(i) = 0
Else
The main salt flux/rejection equations
MembMeq(i) = Beta * (FeedMeq(i) - ConcMeq(i)) / Log(FeedMeq(i) /
ConcMeq(i))
MeqDiff (i) = MembMeq(i) - PermMeq(i)
' now need to check for reverse perm flow and deal with it
If MeqDiff(i) > 0 And SaltPass > 0 Then
PerrrMeq(i) = MeqDiff (i) * B_Ions(i) * B / (PNet * A + B)
Else
' can't be negative so there is no change
PermMeq(i) = FeedMeq(i)
End If
End If
End If
Next i
now make sure there is an ionic balance, the True/false is for NF, True = NF,
false = RO
BalanceAnalysis PermMeq(), False
' now have all of the ions into the PermMeq() variable
47
the
flow
banks.
-------
' get ConcMeqO by mass balance
ConcFlow = FeedFlow - PermFlow
IF CONCFLOW LESS THAN ZERO THEN RESET
If ConcFlow < 0 Then ConcFlow = 0.1 * FeedFlow
For i = 1 To LAST_ION
ConcMeq(i) = (FeedFlow * FeedMeq(i) - PermFlow * PerrrMeq(i)) / ConcFlow
Next i
If boolDebug Then Debuglons FeedMeqO, PermMeqO , ConcMeqO
' we are done with the bank, need to pass data to next bank and update the present
bank
' Assign data to present Bank
With CArray.Bank(nBank)
.ConcFlow = ConcFlow * .nVessels
.PermFlow = PermFlow * .nVessels
.FeedFlow = FeedFlow * .nVessels
.FeedPress = Pressure
.ConcPress = Pressure - dp
.ConcPolarization = Beta
.PTransMemb = PNet
For i = 1 To LAST_ION
.ConcMeq(i) = ConcMeq(i)
.PermMeq(i) = PermMeq(i)
.FeedMeq(i) = FeedMeq(i)
Next i
If boolDebug Then Debuglons .FeedMeqO, .PermMeqO, .ConcMeqO
End With
' now assign values to next bank
If nNumbanks >= nBank + 1 Then
With CArray.Bank(nBank + 1)
.FeedFlow = CArray.Bank(nBank).ConcFlow
.FeedPress = CArray.Bank(nBank).ConcPress
For i = 1 To LAST_ION
.FeedMeq(i) = ConcMeq(i)
FeedMeq(i) = .FeedMeq(i)
' these are still there from the last iteration
PermMeq(i) = CArray.Bank(nBank + 1).PermMeq(i)
ConcMeq(i) = CArray.Bank(nBank + 1).ConcMeq(i)
Next i
Pressure = .FeedPress
FeedFlow = .FeedFlow / .nVessels
PermFlow = .PermFlow / .nVessels
ConcFlow = .ConcFlow / .nVessels
End With
' now recalculate the differential pressure loss for this NEXT bank
dp = DeltaPressure(FeedFlow, ConcFlow, dpCoef)
End If
Next nBank
' now redo the recycle if there is any
If CArray.RecycleFlow > 0 Then
' GetNetFeed(ByRef NetlonsO As Double, ByRef NewFlow As Double, _
FeedlonsO As Double, FlowFeed As Double, _
Recyclelons() As Double, FlowRecycle As Double)
GetNetFeed NetFeedMeq () , FeedFlow, FeedlonsO, CArray. FeedFlow, ConcMeqO,
RecycleFlow
For i = 1 To LAST_ION
FeedMeq(i) = NetFeedMeq(i)
Next i
End If
set up convergence test variables here and move pressure up or down as needed
CalcPermFlow = 0
For n = 1 To CArray.nBanks
CalcPermFlow = CalcPermFlow + CArray.Bank(n).PermFlow
Next n
If CalcPermFlow <= 0 Then
boolBadDesign = True
Exit Do
End If
48
-------
' calcpermflow is the perm flow we actually got
' targetpermflow is what we actually want
DeltaFlow = TargetPermFlow - CalcPermFlow
DeltaPercent = Abs(TargetPermFlow - CalcPermFlow) / TargetPermFlow
' use a brute force convergence - actually faster and more sure.
OldPressure = NewPressure
Select Case DeltaFlow
Case Is > 0 ' the pressure needs to go up
If DeltaPercent > 1 Then
NewPressure = OldPressure + OldPressure * (TargetPermFlow / CalcPermFlow)
Elself DeltaPercent > 0.1 Then
NewPressure = OldPressure + OldPressure * (TargetPermFlow / CalcPermFlow) / 10
Elself DeltaPercent > 0.01 Then
NewPressure = OldPressure + OldPressure * (TargetPermFlow / CalcPermFlow) / 200
Else
NewPressure = OldPressure + OldPressure * (TargetPermFlow / CalcPermFlow) / 500
End If
Case Is < 0 ' the pressure needs to go down
If DeltaPercent > 1 Then
NewPressure = OldPressure - OldPressure * (TargetPermFlow / CalcPermFlow)
Elself DeltaPercent > 0.1 Then
NewPressure = OldPressure - OldPressure * (TargetPermFlow / CalcPermFlow) / 10
Elself DeltaPercent > 0.01 Then
NewPressure = OldPressure - OldPressure * (TargetPermFlow / CalcPermFlow) / 200
Else
NewPressure = OldPressure - OldPressure * (TargetPermFlow / CalcPermFlow) / 500
End If
End Select
If boolDebug2 Then
Debug.Print "CalcPermFlow = ", CalcPermFlow, "Targetpermflow = ", TargetPermFlow
Debug.Print "Old pressure was = ", OldPressure, "New Pressure = ", NewPressure
End If
The comparison test takes place at this point. If the pressure this pass is close enough to the last pass we
are done.
Loop
' save the pressure
CArray.FeedPress = NewPressure
CArray.Bank(1).FeedPress = NewPressure
' now put the analytical back into the main variable as ppm - since all calcs above are meq()
' first the net feed stream
ConvertMEQtoPPM CArray.Bank(1).FeedMeq(), CArray.NetFeedlons()
' the last bank is the same as the concentrate
ConvertMEQtoPPM CArray.Bank(nNumbanks).ConcMeqO, CArray.Conclons()
' need to get the permeate by going thru all of the banks
For i = 1 To LAST_ION
If PerrrMeq(i) > 0 Then
PermMeq(i) = 0
For n = 1 To nNumbanks
PermMeq(i) = PermMeq(i) + CArray.Bank(n).PermFlow * CArray.Bank(n).PermMeq(i)
Next n
PermMeq(i) = PermMeq(i) / CArray.PermFlow
Else
' get rid of any inadvertent negative values
PerrrMeq(i) = 0
End If
Next i
' Now check if the Permeate TDS is 0 - this happens when there are too many membranes in a
system and
' there is not enough pressure to overcome the osmotic pressure
' Regular occurence with seawater systems and too many membranes
TDS = TDSfromMEQ(PermMeq())
If TDS <= 0 Then
MsgBox "The permeate TDS cannot be converged. Try reducing the number of elements or
reducing the number of vessesls. This situation often results from high TDS feed and too much
membrane area. The calculations are not valid at this time."
boolBadDesign = True
49
-------
End If
' now redo once again the carbonate balances for netfeed, perm, and concentrate
' C02toPH(ByVal floatlonStr As Double, ByVal Temperature As Double, _
ByRef HPlus As Double, ByRef HC03 As Double, ByRef C02 As Double, ByRef C03 As Double)
' NB HPlus, C03, C02, HC03 are changed by this subroutine - using ByRef, HPlus as [Hplus],
not pH
Istr(l) = IonicStrengthfrorrMeq (CArray.Bank (1) . FeedMeq () )
Istr(2) = IonicStrengthf rorrMeq (CArray. Bank (nNumbanks). ConcMeq () )
Istr(3) = IonicStrengthf rorrMeq (PerrrMeq () )
C02toPH Istr(l), Temperature, HPlus(1), CArray.Bank(1).FeedMeq(SP_HC03),
CArray.Bank(1).FeedMeq(SP_C02), CArray.Bank(1).FeedMeq(SP_C03)
C02toPH Istr(2), Temperature, HPlus(2), CArray.Bank(nNumbanks).ConcMeq(SP_HC03),
CArray.Bank(nNumbanks).ConcMeq(SP_C02), CArray.Bank(nNumbanks).ConcMeq(SP_C03)
C02toPH Istr(3), Temperature, HPlus (3), PerrrMeq (SP_HC03) , PerrrMeq (SP_C02) , PerrrMeq (SP_C03)
ConvertMEQtoPPM PerrrMeq (), CArray. Permlons ()
PPI(l) = 14.69 * OsmoticPressureMEQ(CArray.Bank(1).FeedMeq(), Temperature)
PPI(2) = 14.69 * OsmoticPressureMEQ(CArray.Bank(nNumbanks).ConcMeq(), Temperature)
PPI(3) = 14.69 * OsmoticPressureMEQ(PermMeq(), Temperature)
CalcSystem = CArray.Bank(1).FeedPress
End Function
Subs to display the data on the Summary and Streams Sheets
Sub PutDataOnSheets()
' Puts the calculated data back onto the spreadsheet
Dim nCol As Integer, Pcol As Integer, Ccol As Integer
Dim CRow As Integer
Dim n As Integer
Dim c As Integer
Dim dummy As Double
Dim S As Worksheet
' turn off updating to avoid slowing down the update
Application.ScreenUpdating = False
Set S = Worksheets("Streams")
nCol = Range("StartNetFeed").Column
Ccol = Range("StartConcentrate").Column
Pcol = Range("StarrtPermeate").Column
CRow = Range("StartNetFeed").Row
With CArray
CRow = CRow + 1
S.Cells(CRow, nCol)
S.Cells(CRow, Ccol)
S.Cells(CRow, Pcol)
' Potassium -k
CRow = CRow + 1
S.Cells(CRow, nCol)
S.Cells(CRow, Ccol)
S.Cells(CRow, Pcol)
' Calcium -Ca
CRow = CRow + 1
S.Cells(CRow, nCol)
S.Cells(CRow, Ccol)
S.Cells(CRow, Pcol)
' Magnesium -Mg
CRow = CRow + 1
S.Cells(CRow, nCol)
S.Cells(CRow, Ccol)
S.Cells(CRow, Pcol)
' Strontium -Sr
CRow = CRow + 1
S.Cells(CRow, nCol)
S.Cells(CRow, Ccol)
S.Cells(CRow, Pcol)
' Barium -Ba
Value = .NetFeedlons(SP_Na)
Value = .Conclons(SP_Na)
Value = .Permlons(SP Na)
Value = .NetFeedlons(SP_K)
Value = .Conclons(SP_K)
Value = .Permlons(SP K)
Value = .NetFeedlons(SP_Ca)
Value = .Conclons(SP_Ca)
Value = .Permlons(SP Ca)
Value = .NetFeedlons(SP_Mg)
Value = .Conclons(SP_Mg)
Value = .Permlons(SP_Mg)
Value = .NetFeedlons(SP_Sr)
Value = .Conclons(SP_Sr)
Value = .Permlons(SP Sr)
50
-------
CRow
CRow + 1
S.Cells(CRow,
nCol)
•Value =
•NetFeedlons(SP Ba)
S.Cells(CRow,
Ccol)
•Value =
.Conclons(SP Ba)
S.Cells(CRow,
Pcol)
•Value =
.Permlons(SP Ba)
Aluminum - A1+++
CRow = CRow +
1
S.Cells(CRow,
nCol)
•Value =
•NetFeedlons(SP Al)
S.Cells(CRow,
Ccol)
•Value =
.Conclons(SP Al)
S.Cells(CRow,
Pcol)
•Value =
.Permlons(SP Al)
Ferric Iron - Fe+++ (as
Fe)
CRow = CRow +
1
S.Cells(CRow,
nCol)
•Value =
•NetFeedlons(SP Fe3)
S.Cells(CRow,
Ccol)
•Value =
•Conclons(SP Fe3)
S.Cells(CRow,
Pcol)
•Value =
•Permlons(SP Fe3)
Ammonia - NH4 (as
NH4 )
CRow = CRow +
1
S.Cells(CRow,
nCol)
•Value =
•NetFeedlons(SP NH4)
S.Cells(CRow,
Ccol)
•Value =
•Conclons(SP NH4)
S.Cells(CRow,
Pcol)
•Value =
•Permlons(SP NH4)
Hydrogen Ion - H
CRow = CRow +
1
S.Cells(CRow,
nCol)
•Value =
HPlus(1)
S.Cells(CRow,
Ccol)
•Value =
HPlus (2)
S.Cells(CRow,
Pcol)
•Value =
HPlus(3)
Sum all cations (incl metals)
CRow = CRow +
3
S.Cells(CRow,
nCol)
Value
=
S.Cells(CRow,
Ccol)
Value
=
S.Cells(CRow,
Pcol)
Value
=
Carbon Dioxide -
C02 (calc'd)
CRow = CRow +
1
S.Cells(CRow,
nCol)
Value
=
S.Cells(CRow,
Ccol)
Value
=
S.Cells(CRow,
Pcol)
Value
=
Carbonate - C03
calc'd)
CRow = CRow +
1
S.Cells(CRow,
nCol)
Value
=
S.Cells(CRow,
Ccol)
Value
=
S.Cells(CRow,
Pcol)
Value
=
Bromide -Br
CRow = CRow +
1
S.Cells(CRow,
nCol)
Value
=
S.Cells(CRow,
Ccol)
Value
=
S.Cells(CRow,
Pcol)
Value
=
Chloride -CI
CRow = CRow +
1
S.Cells(CRow,
nCol)
Value
=
S.Cells(CRow,
Ccol)
Value
=
S.Cells(CRow,
Pcol)
Value
=
Fluoride -F
CRow = CRow +
1
S.Cells(CRow,
nCol)
Value
=
S.Cells(CRow,
Ccol)
Value
=
S.Cells(CRow,
Pcol)
Value
=
Sulfate -S04
CRow = CRow +
1
S.Cells(CRow,
nCol)
Value
=
S.Cells(CRow,
Ccol)
Value
=
S.Cells(CRow,
Pcol)
Value
=
Nitrate -N03
CRow = CRow +
1
S.Cells(CRow,
nCol)
Value
=
S.Cells(CRow,
Ccol)
Value
=
S.Cells(CRow,
Pcol)
Value
=
Phosphate - P04
as P04)
CRow = CRow +
1
S.Cells(CRow,
nCol)
Value
=
S.Cells(CRow,
Ccol)
Value
=
.NetFeedlons(SP_HC03)
.Conclons(SP_HC03)
.Permlons(SP HC03)
.NetFeedlons(SP_C02)
.Conclons(SP_C02)
.Permlons(SP C02)
.NetFeedlons(SP_C03)
.Condons (SP_C03)
.Permlons(SP C03)
•NetFeedlons(SP_Br)
• Condons (SP_Br)
.Permlons(SP Br)
•NetFeedlons(SP_C1)
• Condons (SP_C1)
.Permlons(SP CI)
•NetFeedlons(SP_F)
.Conclons(SP_F)
.Permlons(SP F)
•NetFeedlons(SP_S04)
•Conclons(SP_S04)
.Permlons(SP S04)
•NetFeedlons(SP_N03)
•Conclons(SP_N03)
.Permlons(SP N03)
•NetFeedlons(SP_P04)
• Condons (SP P04)
51
-------
S.Cells(CRow, Pcol).Value
Hydroxide -OH
CRow = CRow + 1
S.Cells(CRow, nCol).Value
S.Cells(CRow, Cool).Value
S.Cells(CRow, Pcol).Value
.Permlons(SP P04)
10
10
10
-14 / HPlus(1)
-14 / HPlus(2)
-14 / HPlus(3)
Sum Total Anions
done on the sheet
Silica (as Si02)
CRow = CRow +
S.Cells(CRow,
S.Cells(CRow,
S.Cells(CRow,
PH
CRow = CRow +
S.Cells(CRow,
S.Cells(CRow,
S.Cells(CRow,
TDS (in ppm)
CRow = CRow +
Osmotic Pressure
CRow = CRow +
S.Cells(CRow,
S.Cells(CRow,
S.Cells(CRow,
Ionic Strength
CRow = CRow +
S.Cells(CRow,
S.Cells(CRow,
S.Cells(CRow,
3
nCol).Value
Ccol).Value
Pcol).Value
.NetFeedlons(SP_Si02)
.Conclons(SP_Si02)
.Permlons(SP Si02)
1
nCol).Value
Ccol).Value
Pcol).Value
1
(estimated psi)
1
nCol).Value =
Ccol).Value =
Pcol).Value =
-Log(HPlus(1))
-Log(HPlus(2))
-Log(HPlus(3))
PPI (1)
PPI (2)
PPI(3)
1
nCol).Value
Ccol).Value
Pcol).Value
Log (10#)
Log (10#)
Log (10#)
Istr(1)
Istr (2)
Istr(3)
Solubility Indices, expressed
percent of calculated solubility.
-Silica(Si02)
- Calcium Sulfate (CaS04)
- Strontium Sulfate (SrS04)
- Barium Sulfate (BaS04)
- Calcium Fluoride(CaF2)
- Analcime - NaAlSi206.H20
- Gibbsite - A1(OH)3
- Am A1 (OH) 3 - A1 (OH) 3
- Fe(OH)3 (am) - Fe(OH)3
- Goethite - FeO(OH)
- Siderite - FeC03
- Vivianite - Fe3(P04)2:8H20
- Hydroxyapatite - Ca5(P04)30H
Metals/Others
Organics As TOC, row 55
CRow = CRow + 17
S.Cells(CRow,
S.Cells(CRow,
S.Cells(CRow,
Silver -Ag
CRow = CRow + 1
S.Cells(CRow,
S.Cells(CRow,
S.Cells(CRow,
Arsenic - As (as Arseni
CRow = CRow + 1
S.Cells(CRow,
S.Cells(CRow,
S.Cells(CRow,
Gold -Au
CRow = CRow + 1
S.Cells(CRow,
S.Cells(CRow,
S.Cells(CRow,
Cadmium -Cd
CRow = CRow + 1
S.Cells(CRow, nCol).Value
nCol)
Ccol)
Pcol)
nCol)
Ccol)
Pcol)
nCol)
Ccol)
Pcol)
nCol)
Ccol)
Pcol)
.Value
•Value
•Value
•Value
•Value
•Value
c)
•Value
•Value
•Value
•Value
•Value
•Value
•NetFeedlons(SP_Organic)
•Conclons(SP_Organic)
.Permlons(SP_Organic)
•NetFeedlons(SP_Ag)
.Condons (SP_Ag)
.Permlons(SP_Ag)
•NetFeedlons(SP_As)
.Condons (SP_As)
.Permlons(SP As)
•NetFeedlons(SP_Au)
.Condons (SP_Au)
.Permlons(SP Au)
•NetFeedlons(SP Cd)
52
-------
CRow = CRow +
1
S.Cells(CRow,
nCol
.Value
=
S.Cells(CRow,
Ccol
.Value
=
S.Cells(CRow,
Pcol
•Value
=
Copper -Cu
CRow = CRow +
1
S.Cells(CRow,
nCol
•Value
=
S.Cells(CRow,
Ccol
•Value
=
S.Cells(CRow,
Pcol
•Value
=
Ferrous Iron - Fe++ (a
Fe)
CRow = CRow +
1
S.Cells(CRow,
nCol
•Value
=
S.Cells(CRow,
Ccol
•Value
=
S.Cells(CRow,
Pcol
•Value
=
Mercury -Hg
CRow = CRow +
1
S.Cells(CRow,
nCol
•Value
=
S.Cells(CRow,
Ccol
•Value
=
S.Cells(CRow,
Pcol
•Value
=
Manganese -Mn
CRow = CRow +
1
S.Cells(CRow,
nCol
•Value
=
S.Cells(CRow,
Ccol
•Value
=
S.Cells(CRow,
Pcol
•Value
=
Nickel -Ni
CRow = CRow +
1
S.Cells(CRow,
nCol
•Value
=
S.Cells(CRow,
Ccol
•Value
=
S.Cells(CRow,
Pcol
•Value
=
Lead -Pb
CRow = CRow +
1
S.Cells(CRow,
nCol
•Value
=
S.Cells(CRow,
Ccol
•Value
=
S.Cells(CRow,
Pcol
•Value
=
Selenium - Se (as
Selenium)
CRow = CRow +
1
S.Cells(CRow,
nCol
•Value
=
S.Cells(CRow,
Ccol
•Value
=
S.Cells(CRow,
Pcol
•Value
=
Tin -Sn
CRow = CRow +
1
S.Cells(CRow,
nCol
•Value
=
S.Cells(CRow,
Ccol
•Value
=
S.Cells(CRow,
Pcol
•Value
=
Titanium -Ti
CRow = CRow +
1
S.Cells(CRow,
nCol
•Value
=
S.Cells(CRow,
Ccol
•Value
=
S.Cells(CRow,
Pcol
•Value
=
Vanadium -V
CRow = CRow +
1
S.Cells(CRow,
nCol
•Value
=
S.Cells(CRow,
Ccol
•Value
=
S.Cells(CRow,
Pcol
•Value
=
Zinc -Zn
CRow = CRow +
1
S.Cells(CRow,
nCol
•Value
=
S.Cells(CRow,
Ccol
•Value
=
S.Cells(CRow,
Pcol
•Value
=
Sum heavy metals:
Borate - B407= row 71
CRow = CRow +
2
S.Cells(CRow,
nCol
•Value
=
S.Cells(CRow,
Ccol
•Value
=
S.Cells(CRow,
Pcol
•Value
=
Nitrite - N02-
CRow = CRow +
1
S.Cells(CRow,
nCol
•Value
=
.Conclons(SP_Cd)
.Permlons(SP Cd)
•NetFeedlons(SP_Cr)
.Conclons(SP_Cr)
.Permlons(SP Cr)
.NetFeedlons(SP_Cu)
.Conclons(SP_Cu)
.Permlons(SP Cu)
.NetFeedlons(SP_Fe2)
• Condons (SP_Fe2)
.Permlons(SP Fe2)
•NetFeedlons(SP_Hg)
• Condons (SP_Hg)
.Permlons(SP_Hg)
•NetFeedlons(SP_Mn)
•Conclons(SP_Mn)
.Permlons(SP Mn)
•NetFeedlons(SP_Ni)
•Conclons(SP_Ni)
.Permlons(SP Ni)
•NetFeedlons(SP_Pb)
•Conclons(SP_Pb)
.Permlons(SP Pb)
•NetFeedlons(SP_Se)
• Condons (SP_Se)
.Permlons(SP Se)
•NetFeedlons(SP_Sn)
• Condons (SP_Sn)
.Permlons(SP Sn)
•NetFeedlons(SP_Ti)
.Conclons(SP_Ti)
.Permlons(SP Ti)
•NetFeedlons(SP_V)
.Conclons(SP_V)
.Permlons(SP V)
•NetFeedlons(SP_Zn)
•Conclons(SP_Zn)
.Permlons(SP Zn)
•NetFeedlons(SP_B407)
• Condons (SP_B407)
•Permlons(SP B407)
•NetFeedlons(SP N02)
53
-------
S.Cells(CRow, Ccol).Value
S.Cells(CRow, Pcol).Value
Sulfite - S03=
CRow = CRow + 1
S.Cells(CRow, nCol).Value
S.Cells(CRow, Ccol).Value
S.Cells(CRow, Pcol).Value
Sum Other Anions:
.Conclons(SP_N02)
.Permlons(SP N02)
.NetFeedlons(SP_S03)
.Conclons(SP_S03)
.Permlons(SP S03)
' now do the total carbon for Cone and netfeed
dummy = .NetFeedlons(SP_C02) / 4 4.00 99
dummy = dummy + .NetFeedlons(SP_C03) / 60.0092
dummy = dummy + .NetFeedlons(SP_HC03) / 61.0171
Range("TC_NetFeed").Value = dummy
dummy = .Conclons(SP_C02) / 44.0099
dummy = dummy + .Conclons(SP_C03) / 60.0092
dummy = dummy + .Conclons(SP_HC03) / 61.0171
Range("TC_Conc").Value = dummy
' pressure values
dummy = .FeedPress * 14.69
Range("FeedPressure")•Value = dummy
' Done on the sheet
End With
PutDataOnSummary
Worksheets("System Design").Activate
Range("Al").Select
Range("FeedPressure").Select
Application.ScreenUpdating = True
End Sub
Continue display
Sub PutDataOnSummary()
Dim dummy As Double
Dim Dummy2 As Double
Dim n As Integer, i As Integer
Dim FlowUnit As Integer
Dim S As Worksheet
Dim CRow As Integer
Dim ColFeed As Integer, ColNet As Integer, ColConc As Integer, ColPerm As Integer
Dim ColBank(4) As Integer
' NOW DO THE SUMMARY WORKSHEET
' these are the place holder names from the spreadsheet
Set S = Worksheets("Summary")
S.Activate
ColFeed = S. Range("ColFeed").Column
ColNet = S.Range("ColNet").Column
ColConc = S.Range("ColConc").Column
ColPerm = S.Range("ColPerm").Column
ColBank(l) = S.Range("ColBankl").Column
ColBank(2) = S.Range("ColBank2").Column
ColBank(3) = S.Range("ColBank3").Column
ColBank(4) = S.Range("ColBank4").Column
' the first row
CRow = S.Range("ColFeed").Row
With S
' now the labels are shown commented out and 1 row is added for each
' this is mostly to maintain readability
' Chemistry Information
CRow = CRow + 1
54
-------
Flow rate - Units are gpm
FlowUnit = Range("FlowUnit").Value
dummy = FlowUser(CArray.FeedFlow, FlowUnit)
S.Cells(CRow, ColFeed).Value = dummy
dummy = FlowUser(CArray.RecycleFlow, FlowUnit) + dummy
S.Cells(CRow, ColNet).Value = dummy
dummy = FlowUser(CArray.ConcFlow, FlowUnit)
S.Cells(CRow, ColConc).Value = dummy
dummy = FlowUser(CArray.PermFlow, FlowUnit)
S.Cells(CRow, ColPerm).Value = dummy
TDS
CRow = CRow + 1
Dummy = TDS(CArray.Feedlons())
S.Cells(Crow, ColFeed).Value = Dummy
Dummy = TDS(CArray.NetFeedlons())
S.Cells(Crow, ColNet).Value = Dummy
Dummy = TDS(CArray.Conclons())
S.Cells(Crow, ColConc).Value = Dummy
Dummy = TDS(CArray.Permlons())
S.Cells(Crow, ColPerm).Value = Dummy
PH
CRow = CRow + 1
Acid Required - ppm
CRow = CRow + 1
Acid Required - lb / Day
CRow = CRow + 1
Langelier Saturation Index (LSI)
CRow = CRow + 1
Silica (Si02)
CRow = CRow + 1
Calcium Sulfate(CaS04)
CRow = CRow + 1
Strontium Sulfate(SrS04)
CRow = CRow + 1
Barium Sulfate(BaS04)
CRow = CRow + 1
Percent - Calcium Fluoride (CaF2)
CRow = CRow + 1
SI -Analcime - NaAlSi206.H20
CRow = CRow + 1
SI - Gibbsite - A1(OH)3
CRow = CRow + 1
SI - Am A1 (OH) 3 - A1 (OH) 3
CRow = CRow + 1
SI - Fe (OH) 3 (am) - Fe (OH) 3
CRow = CRow + 1
SI -Goethite - FeO(OH)
CRow = CRow + 1
SI -Siderite - FeC03
CRow = CRow + 1
SI - Vivianite - Fe3(P04)2:8H20
CRow = CRow + 1
SI - Hydroxyapatite - Ca5(P04)30H
CRow = CRow + 1
55
-------
Blank Line
CRow = CRow + 1
Estimated System Information
CRow = CRow + 1
Element/Membrane Type
CRow = CRow + 1
Number of Elements
CRow = CRow + 1
Number of Pressure Vessels
CRow = CRow + 1
Water Flux Rate (GFD - average)
CRow = CRow + 1
dummy = 0
For n = 1 To 4
If n <= CArray.nBanks Then
dummy = dummy + (10.76 * CArray.Element.Area_M2 * CArray.Bank(n).nVessels)
End If
Next n
dummy = FlowUser(CArray.PermFlow, FLOW_GPD) / dummy
S.Cells(CRow, ColFeed).Value = dummy
Water Recovery (%)
CRow = CRow + 1
Overall Salt Rejection
CRow = CRow + 1
Blank Line
CRow = CRow + 1
' Estimated Bank by Bank Data
CRow = CRow + 1
' Number of Elements
CRow = CRow + 1
' Number of Pressure Vessels
CRow = CRow + 1
' Water Flux Rate (GFD - average)
CRow = CRow + 1
' gfd is in MA3/sec/(mA2)
For n = 1 To 4
If n <= CArray.nBanks Then
dummy = FlowUser(CArray.Bank(n).PermFlow, FLOW_GPD) / (10.76 *
CArray.Element.Area_M2 * CArray.Bank(n).nVessels)
S.Cells(CRow, ColBank(n)).Value = dummy
Else
S.Cells(CRow, ColBank(n)).Value = "N/A"
End If
Next n
' Concentration Polarization(Average)
CRow = CRow + 1
For n = 1 To 4
If n <= CArray.nBanks Then
dummy = CArray.Bank(n).ConcPolarization
S.Cells(CRow, ColBank(n)).Value = dummy
Else
S.Cells(CRow, ColBank(n)).Value = "N/A"
End If
Next n
' Pressure at bank inlet - psi
56
-------
CRow = CRow + 1
For n = 1 To 4
If n <= CArray.nBanks Then
dummy = ATMtoPRESS(CArray.Bank(n).FeedPress, FlowUnit)
S.Cells(CRow, ColBank(n)).Value = dummy
Else
S.Cells(CRow, ColBank(n)).Value = "N/A"
End If
Next n
' Transmembrane (Net) Pressure - psi
CRow = CRow + 1
For n = 1 To 4
If n <= CArray.nBanks Then
dummy = ATMtoPRESS(CArray.Bank(n).PTransMemb, FlowUnit)
S.Cells(CRow, ColBank(n)).Value = dummy
Else
S.Cells(CRow, ColBank(n)).Value = "N/A"
End If
Next n
' Bank Differential pressure Loss - psi
CRow = CRow + 1
For n = 1 To 4
If n <= CArray.nBanks Then
dummy = ATMtoPRESS((CArray.Bank(n)
FlowUnit)
S.Cells(CRow, ColBank(n)).Value =
Else
S.Cells(CRow, ColBank(n)).Value =
End If
Next n
.FeedPress - CArray.Bank(n).ConcPress),
dummy
"N/A"
Flow Rate to First Element in Bank
CRow = CRow + 1
For n = 1 To 4
If n <= CArray.nBanks Then
dummy = FlowUser(CArray.Bank(n).FeedFlow, FlowUnit) / CArray.Bank(n).nVessel
S.Cells(CRow, ColBank(n)).Value = dummy
Else
S.Cells(CRow, ColBank(n)).Value = "N/A"
End If
Next n
Flow Rate to Last Element in Bank
CRow = CRow + 1
For n = 1 To 4
If n <= CArray.nBanks Then
dummy = FlowUser(CArray.Bank(n).ConcFlow, FlowUnit) / CArray.Bank(n).nVessel
S.Cells(CRow, ColBank(n)).Value = dummy
Else
S.Cells(CRow, ColBank(n)).Value = "N/A"
End If
Next n
Permeate Flow
CRow = CRow + 1
For n = 1 To 4
If n <= CArray.nBanks Then
dummy = FlowUser(CArray.Bank(n).PermFlow, FlowUnit)
S.Cells(CRow, ColBank(n)).Value = dummy
Else
S.Cells(CRow, ColBank(n)).Value = "N/A"
End If
Next n
Water Recovery (%)
CRow = CRow + 1
For n = 1 To 4
If n <= CArray.nBanks Then
dummy = CArray.Bank(n).PermFlow / CArray.Bank(n).FeedFlow
57
-------
S.Cells(CRow, ColBank(n)).Value = dummy
Else
S.Cells(CRow, ColBank(n)).Value = "N/A"
End If
Next n
Feed TDS
CRow = CRow + 1
For n = 1 To 4
If n <= CArray.nBanks Then
dummy = TDSfrorrMEQ (CArray. Bank (n) . FeedMeq () )
S.Cells(CRow, ColBank(n)).Value = dummy
Else
S.Cells(CRow, ColBank(n)).Value = "N/A"
End If
Next n
Concentrate TDS
CRow = CRow + 1
For n = 1 To 4
If n <= CArray.nBanks Then
dummy = TDSfrorrMEQ (CArray. Bank (n) . ConcMeq () )
S.Cells(CRow, ColBank(n)).Value = dummy
Else
S.Cells(CRow, ColBank(n)).Value = "N/A"
End If
Next n
Permeate TDS
CRow = CRow + 1
For n = 1 To 4
If n <= CArray.nBanks Then
dummy = TDSfrorrMEQ (CArray. Bank (n) . PerrrMeq () )
S.Cells(CRow, ColBank(n)).Value = dummy
Else
S.Cells(CRow, ColBank(n)).Value = "N/A"
End If
Next n
Blank Line
CRow = CRow + 1
Nominal System Parameters*
CRow = CRow + 1
Water Flux Rate (GFD - average)
CRow = CRow + 1
Concentration Polarization(Average)
CRow = CRow + 1
Applied Pressure (average psi)
CRow = CRow + 1
Flow Rate to First Element in Bank
CRow = CRow + 1
Flow Rate to Last Element in Bank
CRow = CRow + 1
Blank Line
CRow = CRow + 1
End With
The Guess routine.
End Sub
» =======================pj_rst Guess =======
' Area As Double - Total System Area
58
-------
A As Double - Effective (calculated A value)
B As Double
FeedFlow as double - The inlet flow in mA3/sec
ConcFlow as double - The cone flow in mA3/sec
AvgFlow As Double
Beta As Double - CP Guess as 1.1
Function FirstGuessO As Double
Dim n As Integer, i As Integer
Dim Area As Double
Dim A As Double
Dim B As Double
Dim Beta As Double
Dim dp As Double
Dim dpBank(4) As Double
Dim dpCoef As Double
Dim PI As Double
Dim CF As Double
Dim floatDummy As Double
' FeedMeq(last_ION) is a publis so that next calc routine can use it as well
' Dim FeedMeq(LAST_ION) As Double
Dim TDS As Double
Dim SaltPass As Double
Dim PermTDS As Double
Dim ConcTDS As Double
Dim Pressure As Double
Dim nTotalVessels As Integer
' first get the whole system area in mA2
Area = 0
For n = 1 To CArray.nBanks
Area = Area + CArray.Bank(n).nVessels * MyElement.Area_M2
nTotalVessels = nTotalVessels + CArray.Bank(n).nVessels
Next n
' Analysis, this also gets the analysis into the FeedMeq() variable
ConvertPPMtoMEQ FeedMeq(), CArray.Feedlons()
TDS = TDSfromMEQ(FeedMeq())
PI = OsmoticPressureMEQ(FeedMeq(), Temperature) ' 1 meq/1 NaCl is 58.5 mg/1,
about 0.5 psi Pi = .5/14.69 = 0.035
' dp, dpcoef is in form of atm = dpcoef * elementLen in inches * (flow in MA3/sec) A 1.5
' MyElement has already been scaled for element length * number of elements in a vessel.
' eg. eight inch elements, ElementLen = 40 inch, 6 elements/vessel, then dpcoef is 6 * 40 *
range("dp80_lnch").value
dpCoef = MyElement.dpCoef
' do a full system dp and flow calc
' see if we have recycle
RecycleFlow = CArray.RecycleFlow
' need the feed flow for the system divided by number of vessels in fisrt bank, flow to 1
vessel !
FeedFlow = (CArray.FeedFlow + RecycleFlow) / CArray.Bank(1).nVessels
' assume the perm flow is evenly divided by number of vessels (not entirely correct but OK for
the first cut)
PermFlow = CArray.PermFlow / nTotalVessels
dp = 0
For n = 1 To CArray.nBanks
' the concflow includes the recycle flows
' overall concflow is less the recycleflow
ConcFlow = FeedFlow - PermFlow
dpBank(n) = DeltaPressure(FeedFlow, ConcFlow, dpCoef)
dp = dp + dpBank(n)
' use this loop to assign flows to banks
' assign current bank flows, this will include the recycle if any
CArray.Bank(n).FeedFlow = FeedFlow * CArray.Bank(n).nVessels
CArray.Bank(n).PermFlow = PermFlow * CArray.Bank(n).nVessels
CArray.Bank(n).ConcFlow = ConcFlow * CArray.Bank(n).nVessels
59
-------
If CArray.nBanks >= n + 1 Then
' assign next bank flows
CArray.Bank(n + 1).FeedFlow = ConcFlow * CArray.Bank(n + 1).nVessels
CArray.Bank(n + 1).PermFlow = PermFlow * CArray.Bank(n + 1).nVessels
CArray.Bank(n + 1).ConcFlow = CArray.Bank(n + 1).FeedFlow - CArray.Bank(n + 1).PermFlow
' Now setup for the next iteration
' the new feedflow is the old concflow * number of vessels in current bank divided by
number in next bank
FeedFlow = ConcFlow * CArray.Bank(n).nVessels / CArray.Bank(n + 1).nVessels
' The new permflow is already done, it is the same for each bank
End If
Next n
' now find the first pressure by using total area, permflow, and dp
B = MyElement.BValue
A = MyElement.Avalue
Pressure = CArray.PermFlow / (Area * A)
Pressure = Pressure + dp / 2 + PI
' now assign/partition pressure and flow to
values
CArray.Bank(1).FeedPress = Pressure
For n = 1 To CArray.nBanks
CArray.Bank(n).ConcPress = CArray.Bank(n)
If CArray.nBanks >= n + 1 Then
CArray.Bank(n + 1).FeedPress = CArray
End If
Next n
' get first guess salt values -
' the recycle concentration and flow is ignored here
' first redo ourlocal variables
FeedFlow = CArray.FeedFlow
PermFlow = CArray.PermFlow
ConcFlow = CArray.ConcFlow
SaltPass = B / (Pressure * A + B)
For i = 1 To LAST_ION
PerrrMeq(i) = SaltPass * B_Ions(i) * FeedMeq(i)
ConcMeq(i) = (FeedMeq(i) * FeedFlow - PerrrMeq(i) * PermFlow) / ConcFlow
Next i
' now fix up C02
PermMeq(SP_C02) = FeedMeq(SP_C02)
ConcMeq(SP_C02) = FeedMeq(SP_C02)
' now redo but include the recycle concentration = the concentrate concentration
' GetNetFeed(ByRef Netlonsf) As Double, ByRef NewFlow As Double, _
FeedlonsO As Double, FlowFeed As Double, _
Recyclelons() As Double, FlowRecycle As Double)
' NB NewFlow is now Recycle + Feed but not used in FirstGuess
GetNetFeed NetFeedMeq(), floatDummy, FeedMeq(), CArray.FeedFlow, ConcMeq(), CArray.RecycleFlow
' redo just for the first bank
For i = 1 To LAST_ION
PerrrMeq(i) = SaltPass * B_Ions(i) * NetFeedMeq (i)
CArray. Bank (1) . ConcMeq (i) = (NetFeedMeq (i) * FeedFlow - PerrrMeq(i) * CArray.Bank (1) . PermFlow)
/ CArray.Bank(1).ConcFlow
' get the values into the first bank FeedMeq()
CArray.Bank(1).FeedMeq(i) = NetFeedMeq(i)
CArray.Bank(1).PermMeq(i) = PermMeq(i)
Next i
' now we data in bank 1 as needed
' Next - need to estimate the data for the remaining banks
If CArray.nBanks = 1 Then
FirstGuess = Pressure
Exit Function
End If
For n = 2 To CArray.nBanks
With CArray.Bank(n)
' .FeedFlow = CArray.Bank(n - 1).ConcFlow
the first bank so we have some reasonable starting
.FeedPress - dpBank(n)
.Bank(n).ConcPress
60
-------
' .PermFlow = CArray.PermFlow * .nVessels / nTotalVessels
' .ConcFlow = .FeedFlow - PermFlow
For i = 1 To LAST_ION
.FeedMeq(i) = CArray.Bank(n - 1).ConcMeq(i)
.PermMeq(i) = CArray.Bank(n - 1).PermMeq(i)
.ConoMeq(i) = (.FeedFlow * .FeedMeq(i) - .PermFlow * . PerrrMeq (i) ) / .ConcFlow
Next i
End With
Next n
If boolDebug Then
PermTDS = TDSfromMEQ(PermMeq())
ConcTDS = TDSfromMEQ(ConcMeq())
Debug.Print
Debug.Print
Debug.Print
Debug.Print
Debug.Print
Debug.Print
Debug.Print
Debug.Print
Debug.Print
Debug.Print
End If
FirstGuess = Pressure
area", Area, Area * 10.76
dp", dp, dp * 14.69
feedflow", FeedFlow, FeedFlow * 60
permflow", PermFlow, PermFlow * 60
Pi", PI, PI * 14.69
A", A
TempCorr", ATempCorr
temperature", Temperature
pressure", Pressure, Pressure * 14.69
TDS - Perm, Cone", PermTDS, ConcTDS
264 .
264 .
End Function
Sub GetSystemData()
Dim n As Integer
' This sub collects the input data about the system to start the calculations
' It needs to run AFTER the GetElementData to fill in all of the variables
With CArray
.Element = MyElement
.nBanks = Range("NumberBanks").Value
.Bank(1).nVessels = Range("Bankl").Value
.Bank(2).nVessels = Range("Bank2").Value
.Bank(3).nVessels = Range("Bank3").Value
.Bank(4).nVessels = Range("Bank4").Value
' Flows in system Units
.FeedFlow = Range("FeedFlow").Value
.PermFlow = Range("ProductFlow").Value
.ConcFlow = Range("ConcentrateFlow").Value
.RecycleFlow = Range("RecycleFlow").Value
'convert to mA/sec
n = Range("FlowUnit").Value
.FeedFlow = FlowM3SEC(.FeedFlow, n)
.PermFlow = FlowM3SEC(.PermFlow, n)
.ConcFlow = FlowM3SEC(.ConcFlow, n)
.RecycleFlow = FlowM3SEC(.RecycleFlow, n)
' pressures not yet known, set to 10 for starting point
' Press in atm
.FeedPress = 10
.ConcPress = 5
' feed water
GetTreatedlons .FeedlonsO
End With
End Sub
A data collection sub
Sub GetElementData()
' This sub takes the generic element data and bank data and creates a new master element
' The master element is in equivalent length of the whole vessl. IE if the vessel contains
61
-------
' each, 40 inch elements, then the effective length is 240 inches , and the total area is 6 *
400 = 2400 ftA2.
' 240 inches is used for the dp calculation. The relevant data is contained in the named
ranges
' DP25Inch (2.5 inch elements)
' DP40Inch (4 inch elements)
' DP80Inch (8 inch elements)
' The constant is multiplied by the effective length to calculate a DP value.
' See Function DeltaPressure(Flow, dpCoef) as double
!
Dim n As Integer
Dim nRow As Long
Dim nCol As Long
Dim typElem As BigElementType
Dim typMemb As Membrane
Dim nElem As Integer '(elements per vessel)
Dim M3Sec As Double ' flow in mA3/sec
Dim ATM As Double ' press in atm
Dim saltFlow As Double 'kg/sec of salt
Dim BValue As Double
Dim strType As String
Temperature = Range ( "Temp^"). Value
n = Worksheets("System Design").IstSelectMembrane.Listlndex + 1
ATempCorr = Range("tempcorr").Offset(0, n).Value
If ATempCorr = 0 Then ATempCorr = 2900
ATempCorr = Exp(ATempCorr * (1 / (273 + Temperature) - 1 / 298))
' Ph - this can change if there is recycle in place
FeedpH = Range("Input_ph_Treated").Value
' get number of elements/vessel
nElem = Range("elementspervessel").Value
With typMemb
.Description = Range("Description").Offset(-1, 0).Value
.Diameter = Range("diameter").Offset(-1, 0).Value
.Length = Range("Length").Offset(-1, 0).Value
.Type = Range("Type").Offset(-1, 0).Value
.NaCl_rejection = Range("NaCl_Rejection").Offset(-1, 0).Value
.MgS04_rejection = Range("MgS04_Rejection").Offset(-1, 0).Value
.TestPressure = Range("Test_Pressure").Offset(-1, 0).Value
.TestTDS = Range("Test_Tds"7.Offset(-1, 0).Value
.TestFlow = Range("Test_Flow_Rate").Offset(-1, 0).Value
.TestTemperature = Range("Test_Temperature").Offset(-1, 0).Value
.Area = Range("Elem_Area").Offset(-1, 0).Value
' now the calculated values
ATM = .TestPressure - .TestTDS * 0.01 'estimate osmotic pressure of test solution = 2000 tds
= 20 psi osmotic pressure
ATM = ATM / 14.69 ' net pressure in atm
M3Sec = .TestFlow / 22826880# '86400 * 264.2 GPD to MA3/sec
If .NaCl_rejection > 0 Then
saltFlow = .TestTDS * (1 - .NaCl_rejection / 100) ' perm TDS in mg/1
saltFlow = saltFlow / 1000 * M3Sec ' flow in kg/sec - mg/1 * lg/lOOOmg *
1GGGL/1MA3 * mA3/sec = lkg/sec
Elself .MgS04_rejection > 0 Then 'using MgS04
saltFlow = .TestTDS * (1 - .MgS04_rejection / 100) ' perm TDS in mg/1
saltFlow = saltFlow / 1000 * M3Sec ' flow in kg/sec - mg/1 * lg/lGGGmg *
1000L/1MA3 * mA3/sec = lkg/sec
Else
' use default B value
saltFlow = 0.0000052569
'eg 6000 gpd = 4.38A10-8 * 6000 = 2.628*10A-4 MA3/sec
'rejection = 99%, TestTDS = 2000
'2000 * (1-99/100) / 1000 * 2.628e-4 = 5.25696 e-6
End If
End With
1 kg/lOOOg *
1 kg/lOOOg *
62
-------
'now create the big element
With typElem
.Area_M2 = nElem * typMemb.Area / 10.76
.Avalue = nElem * M3Sec / .Area_M2 / ATM '(mA3/sec)/(mA2*atm) - NB must divide by atm actual
value to get PER atm
' now correct for temperature
' Atempcorr is less than one for temp below 25 C so Avalue must be divided
.Avalue = .Avalue / ATempCorr
.BValue = nElem * saltFlow / .Area_M2 ' (kg/sec)/mA2 (actually the units are
MA3/Sec/(mA2)) from original data
' Units cancel to m/sec for B, and m/(sec*atm) for
' so that salt pass = dC * B/(A*Pnet + B) where dC
' all done by ion
A
= Bulk Cone * Beta - Perm Cone
'.BValue = .BValue * BTempCorr
betacoeff is used for concentration polarization over the element
assumption is ConcPolarization ~= exp(BetaCoef * Recovery),
eg, 50 % recovery in a 6 element system, CP about 1.1 max, Length = 40 *6 =240
-.3492 * log (240) + 2.0152 = -1.54
CP = exp(-1.54 * .5) = .46
BetaCoef = -0.3492 * Log(typMemb.Length * nElem) + 2.0152
Select Case typMemb.Diameter
Case 2.5
.dpCoef = Range("DP25_Inch").Value
Case 4
.dpCoef = Range("DP40_lnch").Value
Case 8
.dpCoef = Range("DP80_Inch").Value
Case Else
.dpCoef = 5266.1 * typMemb.Diameter A (-2.6791) ' see DPCoef vs Diameter.xls for
curve fit
End Select
' now multiply by number of elements per vessel and length of each element
.dpCoef = .dpCoef * nElem * typMemb.Length
' now get the B_Ion data from Membrane Data Sheet
' first find where the data is sitting
strType = "Type" & Format(typMemb.Type, "#")
nCol = Worksheets("Membrane Data").Range(strType).Column
nRow = Worksheets("Membrane Data").Range("Beta_Na").Row
For n = 1 To LAST_ION
.B_Ion(n) = Worksheets("Membrane Data").Cells(nRow + n - 1, nCol).Value
' the global variable - set up for the first pass
B_Ions(n) = .B_Ion(n)
Next n
End With
MyElement = typElem
MyMembrane = typMemb
End Sub
The solubility runs using the Solver
Sub RunSolver(Optional nSections As Integer)
Dim n As Integer
Dim nLoops As Integer
Dim boolTreatedDone As Boolean
Dim nbtnWidth As Double
' boolean to bypass the NetFeed calc if TreatedFeed is calculated AND RecycleFlow = 0
' NetFeed is then the same as the treated Feed so no need to calculate twice
boolTreatedDone = False
If IsMissing(nSections) Then
nSections = 0
63
-------
End If
DoEvents
If boolAbortScaleCalcs = True Then
boolAbortScaleCalcs = False
If MsgBox("Abort Scaling Calcs?", vbOKCancel) = vbOK Then
GoTo OuttaHere
End If
End If
We have found that if the analysis changes dramatically - ie from 0 to some value, two passes of
theSolver get a better convergence.
nLoops = 1
' nLoops needs to be set at 2 if any of the multipliers are set at 0
If Range("$H$44").Value = 0 And Range("Treated_P04").Value > 0 Then ' check P04 first
nLoops = 2
Elself Range
"$N$3 8"
.Value =
0
And
Range
"Treated
_A1")
•Value > 0 Then
nLoops =
2
Elself Range
"$N$3 9"
.Value =
0
And
Range
"Treated
FE2 "
).Value > 0 Then
nLoops =
2
Elself Range
"$N$ 4 0"
•Value =
0
And
Range
"Treated
FE3 "
).Value > 0 Then
nLoops =
2
Elself Range
"$N$34"
•Value =
0
And
Range
"Treated
Na")
•Value > 0 Then
nLoops =
2
Elself Range
"$N$35"
•Value =
0
And
Range
"Treated
_K") .
Value > 0 Then
nLoops =
2
Elself Range
"$N$3 6"
•Value =
0
And
Range
"Ca Treated")
.Value > 0 Then
nLoops =
2
Elself Range
"$N$37"
•Value =
0
And
Range
"Treated
_Mg")
•Value > 0 Then
nLoops =
2
Elself Range
"$C$ 4 4"
•Value =
0
And
Range
"Treated
_C1")
•Value > 0 Then
nLoops =
2
Elself Range
"$D$ 4 4"
•Value =
0
And
Range
"Treated
S04"
).Value > 0 Then
nLoops =
2
Elself Range
"$F$ 4 4"
•Value =
0
And
Range
"Treated
HC03
").Value > 0 Then
nLoops = 2
End If
'On Error Resume Next
Worksheets("Calc").Activate
Setup the progress button
nbtnWidth = Worksheets("Calc").btnRunSolver.Width
Worksheets("Calc").btnRunSolver.Caption = "Setting Solver Config"
Worksheets("Calc").btnRunSolver.BackColor = &HFF& 'This is Red
Worksheets("Calc").btnRunSolver.Width = nbtnWidth / 2
SolverOk SetCell:="$M$3 4", MaxMinVal:=2, ValueOf:="G", ByChange:="$N$3 4"
SolverOptions MaxTime:=10 0, Iterations:=lGGGG, Precision:=0.00000001, _
AssumeLinear:=True, StepThru:=False, Estimates:=1, Derivatives:=1, SearchOption
:=1, IntTolerance:=0.001, Scaling:=True, Convergence:=0.001, AssumeNonNeg:=True
Need to make
sure old
criteria are deleted
SolverDelete
CellRef:=
"$M$3 4
, Relation
=3,
FormulaText:=
¦0"
SolverDelete
CellRef:=
"$M$35'
, Relation
= 3,
FormulaText:=
¦0"
SolverDelete
CellRef:=
"$M$3 6
, Relation
= 3,
FormulaText: =
¦0"
SolverDelete
CellRef:=
"$M$37
, Relation
= 3,
FormulaText:=
¦0"
SolverDelete
CellRef:=
"$M$3 8
, Relation
= 3,
FormulaText:=
¦0"
SolverDelete
CellRef:=
"$M$3 9
, Relation
= 3,
FormulaText: =
¦0"
SolverDelete
CellRef:=
"$M$ 4 0
, Relation
= 3,
FormulaText:=
¦0"
SolverDelete
CellRef:=
"$D$ 4 3'
, Relation
= 3,
FormulaText:=
¦0"
SolverDelete
CellRef:=
"$F$ 4 3'
, Relation
= 3,
FormulaText: =
¦0"
SolverDelete
CellRef:=
"$H$ 4 3
, Relation
= 3,
FormulaText:=
¦0"
DoEvents
' $N$38 keeps appearing so
get rid of
it
just in case.
SolverDelete
CellRef:=
"$N#68
, Relation
= 3,
FormulaText: =
¦0"
SolverDelete
CellRef:=
"$M#64
, Relation
= 3,
FormulaText:=
¦0"
SolverDelete
CellRef:=
"$M#65'
, Relation
= 3,
FormulaText:=
¦0"
SolverDelete
CellRef:=
"$M#66'
, Relation
= 3,
FormulaText: =
¦0"
SolverDelete
CellRef:=
"$M#67
, Relation
= 3,
FormulaText:=
¦0"
64
-------
SolverDelete
CellRef:=
¦$M#68",
Relation:
=3,
FormulaText:=
¦0"
SolverDelete
CellRef:=
' $M#6 9",
Relation:
=3,
FormulaText:=
¦0"
SolverDelete
CellRef:=
'$M$7 0",
Relation:
=3,
FormulaText: =
¦0"
SolverDelete
CellRef:=
'$D$7 3 ",
Relation:
= 3,
FormulaText:=
¦0"
SolverDelete
CellRef:=
'$F$73 ",
Relation:
= 3,
FormulaText:=
¦0"
SolverDelete
CellRef:=
'$H$7 3 ",
Relation:
= 3,
FormulaText: =
¦0"
DoEvents
SolverDelete
CellRef:=
'$M$ 94",
Relation:
= 3,
FormulaText:=
¦0"
SolverDelete
CellRef:=
'$M$ 95",
Relation:
= 3,
FormulaText:=
¦0"
SolverDelete
CellRef:=
'$M$ 9 6",
Relation:
= 3,
FormulaText: =
¦0"
SolverDelete
CellRef:=
'$M$ 97 ",
Relation:
= 3,
FormulaText:=
¦0"
SolverDelete
CellRef:=
'$M$ 98",
Relation:
= 3,
FormulaText:=
¦0"
SolverDelete
CellRef:=
'$M$ 9 9",
Relation:
= 3,
FormulaText: =
¦0"
SolverDelete
CellRef:=
'$M$10 0'
, Relation
: =3
FormulaText:
= "0
SolverDelete
CellRef:=
' $D$103'
, Relation
: =3
FormulaText:
= "0
SolverDelete
CellRef:=
' $F$103'
, Relation
: =3
FormulaText:
= "0
SolverDelete
CellRef:=
' $H$103'
, Relation
: =3
FormulaText:
= "0
DoEvents
Worksheets("Calc").btnRunSolver.Caption = "In Solution Phase"
Select Case nSections
Case 0, 1
GoTo SectionTreatedFeed
Case 2
boolTreatedDone = False
GoTo SectionNetFeed
Case 3
GoTo SectionConcentrate
End Select
SectionTreatedFeed:
Worksheets("Calc").btnRunSolver .Width = nbtnWidth / 1.8
Worksheets("Calc").btnRunSolver.Caption = "Solving Feed"
For n = 1 To nLoops ' loops if necessary
DoEvents
If boolAbortScaleCalcs = True Then
boolAbortScaleCalcs = False
If MsgBox("Abort Scaling Calcs?", vbOKCancel) = vbOK Then
GoTo OuttaHere
End If
End If
Na
Worksheets("Calc").btnSolverTreated.Caption = "Solving Na - Treated Feed"
If Range("$L$34").Value <= 0 Then
Range("$N$3 4").Value = 0
Else
This is the prototype Solver setup call
SolverOk SetCell:="$M$34", MaxMinVal:=2, ValueOf:="0", ByChange:="$N$34
SolverAdd CellRef:="$M$34", Relation:=3, FormulaText:="0"
SolverOk SetCell:="$M$34", MaxMinVal:=2, ValueOf:="0", ByChange:="$N$34
' This UserFinish:=True causes the last section to be bypassed
' This is an undocumented feature of the Excel Solver
Solversolve UserFinish:=True
SolverDelete CellRef:="$M$34", Relation:=3, FormulaText:="0"
End If
K
If Range("$L$35").Value <= 0 Then
Range("$N$35")•Value = 0
Else
Worksheets("Calc").btnSolverTreated.Caption = "Solving K - Treated Feed
SolverOk SetCell:="$M$35", MaxMinVal:=2, ValueOf:="0", ByChange:="$N$35
SolverAdd CellRef:="$M$35", Relation:=3, FormulaText:="0"
SolverOk SetCell:="$M$35", MaxMinVal:=2, ValueOf:="0", ByChange:="$N$35
Solversolve UserFinish:=True
SolverDelete CellRef:="$M$35", Relation:=3, FormulaText:="0"
65
-------
End If
DoEvents
Ca
If Range("$L$36").Value <= 0 Then
Range("$N$36")•Value = 0
Else
Worksheets("Calc").btnSolverTreated.Caption = "Solving Ca - Treated Feed"
SolverOk SetCell:="$M$36", MaxMinVal:=2, ValueOf:="0", ByChange:="$N$36"
SolverAdd CellRef:="$M$36", Relation:=3, FormulaText:="0"
SolverOk SetCell:="$M$36", MaxMinVal:=2, ValueOf:="0", ByChange:="$N$36"
Solversolve UserFinish:=True
SolverDelete CellRef:="$M$36", Relation:=3, FormulaText:="0"
End If
Mg
If Range("$L$37").Value <= 0 Then
Range("$N$37").Value = 0
Else
Worksheets("Calc").btnSolverTreated.Caption = "Solving Mg - Treated Feed"
SolverOk SetCell:="$M$37", MaxMinVal:=2, ValueOf:="0", ByChange:="$N$37"
SolverAdd CellRef:="$M$37", Relation:=3, FormulaText:="0"
SolverOk SetCell:="$M$37", MaxMinVal:=2, ValueOf:="0", ByChange:="$N$37"
Solversolve UserFinish:=True
SolverDelete CellRef:="$M$37", Relation:=3, FormulaText:="0"
End If
A1
If Range("$L$38").Value <= 0 Then
Range("$N$38").Value = 0
Else
Worksheets("Calc").btnSolverTreated.Caption = "Solving A1 - Treated Feed"
SolverOk SetCell:="$M$38", MaxMinVal:=2, ValueOf:="0", ByChange:="$N$38"
'SolverDelete CellRef:="$M$37", Relation:=3, FormulaText:="0"
SolverAdd CellRef:="$M$38", Relation:=3, FormulaText:="0"
SolverOk SetCell:="$M$38", MaxMinVal:=2, ValueOf:="0", ByChange:="$N$38"
Solversolve UserFinish:=True
SolverDelete CellRef:="$M$38", Relation:=3, FormulaText:="0"
End If
Fe2
If Range("$L$39").Value <= 0 Then
Range("$N$39").Value = 0
Else
Worksheets("Calc").btnSolverTreated.Caption = "Solving Fe2 - Treated Feed
SolverOk SetCell:="$M$39", MaxMinVal:=2, ValueOf:="0", ByChange:="$N$39"
SolverAdd CellRef:="$M$39", Relation:=3, FormulaText:="0"
SolverOk SetCell:="$M$39", MaxMinVal:=2, ValueOf:="0", ByChange:="$N$39"
Solversolve UserFinish:=True
SolverDelete CellRef:="$M$39", Relation:=3, FormulaText:="0"
End If
Fe3
If Range("$L$40").Value <= 0 Then
Range("$N$40").Value = 0
Else
Worksheets("Calc").btnSolverTreated.Caption = "Solving Fe3 - Treated Feed
SolverOk SetCell:="$M$40", MaxMinVal:=2, ValueOf:="0", ByChange:="$N$40"
SolverAdd CellRef:="$M$40", Relation:=3, FormulaText:="0"
SolverOk SetCell:="$M$40", MaxMinVal:=2, ValueOf:="0", ByChange:="$N$40"
Solversolve UserFinish:=True
SolverDelete CellRef:="$M$40", Relation:=3, FormulaText:="0"
End If
DoEvents
Now Anions
CI
If Range("$C$42").Value <= 0 Then
Range ( "$C$44") .Value = 0
66
-------
Else
Worksheets("Calc").btnSolverTreated.Caption = "Solving CI - Treated Feed"
SolverOk SetCell: = "$C$43", MaxMinVal:=2, ValueOf: = "0", ByChange: = "$C$44"
SolverAdd CellRef:="$C$43", Relation:=3, FormulaText:="0"
SolverOk SetCell:="$C$43", MaxMinVal:=2, ValueOf:="0", ByChange:="$C$44"
Solversolve UserFinish:=True
SolverDelete CellRef:="$C$43", Relation:=3, FormulaText:="0"
End If
S04
If Range("$D$42").Value <= 0 Then
Range("$D$44").Value = 0
Else
Worksheets("Calc").btnSolverTreated.Caption = "Solving S04 - Treated Feed"
SolverOk SetCell:="$D$43", MaxMinVal:=2, ValueOf:="0", ByChange:="$D$44"
SolverAdd CellRef:="$D$43", Relation:=3, FormulaText:="0"
SolverOk SetCell: = "$D$43", MaxMinVal:=2, ValueOf: = "0", ByChange: = "$D$44"
Solversolve UserFinish:=True
SolverDelete CellRef:="$D$43", Relation:=3, FormulaText:="0"
End If
HC03
If Range("$F$42").Value <= 0 Then
Range("$F$44").Value = 0
Else
Worksheets("Calc").btnSolverTreated.Caption = "Solving HC03 - Treated Feed"
SolverOk SetCell:="$F$43", MaxMinVal:=2, ValueOf:="0", ByChange:="$F$44"
SolverAdd CellRef:="$F$43", Relation:=3, FormulaText:="0"
SolverOk SetCell: = "$F$43", MaxMinVal:=2, ValueOf: = "0", ByChange: = "$F$44"
Solversolve UserFinish:=True
SolverDelete CellRef:="$F$43", Relation:=3, FormulaText:="0"
End If
DoEvents
HP04
If Range("$H$42").Value <= 0 Then
Range("$H$44").Value = 0
Else
Worksheets("Calc").btnSolverTreated.Caption = "Solving HP04 - Treated Feed"
SolverOk SetCell:="$H$43", MaxMinVal:=2, ValueOf:="0", ByChange:="$H$44"
SolverAdd CellRef:="$H$43", Relation:=3, FormulaText:="0"
SolverOk SetCell: = "$H$43", MaxMinVal:=2, ValueOf: = "0", ByChange: = "$H$44"
Solversolve UserFinish:=True
SolverDelete CellRef:="$H$43", Relation:=3, FormulaText:="0"
End If
H2P04
If Range("$I$42").Value <= 0 Then
Range("$I$44").Value = 0
Else
Worksheets("Calc").btnSolverTreated.Caption = "Solving H2P04 - Treated Feed"
SolverOk SetCell:="$1$43", MaxMinVal:=2, ValueOf:="0", ByChange:="$1$44"
SolverAdd CellRef:="$1$43", Relation:=3, FormulaText:="0"
SolverOk SetCell: = "$1$43", MaxMinVal:=2, ValueOf: = "0", ByChange: = "$1$44"
Solversolve UserFinish:=True
SolverDelete CellRef:="$1$43", Relation:=3, FormulaText:="0"
End If
Worksheets("Calc").btnSolverTreated.Caption = "Treated Feed - Complete Pass " & Format(n,
"#") & " of " & Format(nLoops, "#")
boolTreatedDone = True
Next n
If nSections <> 0 Then GoTo OuttaHere
SectionNetFeed:
Now do the NET FEED
Worksheets("Calc").btnRunSolver.Width = nbtnWidth * 0.75
Worksheets("Calc").btnRunSolver.Caption = "Solving Net Feed"
67
-------
If boolTreatedDone = True And Range("RecycleFlow").Value <= 0 Then
' Treated Feed is the same as the recycle, just copy over the mulitpliers
Worksheets
"Calc"
.Range(
"$N$ 64"
.Value
= Worksheets(
"calc"
.Range(
"$N$34"
.Value
Worksheets (
"Calc"
.Range(
M$N$ 65"
.Value
= Worksheets(
"calc"
.Range(
M$N$35M
.Value
Worksheets
"Calc"
.Range(
"$N$ 6 6"
.Value
= Worksheets(
"calc"
.Range(
M$N$3 6"
.Value
Worksheets
"Calc"
.Range(
M$N$ 67"
.Value
= Worksheets(
"calc"
.Range(
M$N$37"
.Value
Worksheets
"Calc"
.Range(
"$N$ 68"
.Value
= Worksheets(
"calc"
.Range(
M$N$3 8"
.Value
Worksheets
"Calc"
.Range(
M$N$ 6 9"
.Value
= Worksheets(
"calc"
.Range(
M$N$3 9"
.Value
Worksheets (
"Calc"
.Range(
M$N$7 0"
.Value
= Worksheets(
"calc"
.Range(
M$N$ 4 0"
.Value
' Anions
Worksheets
"Calc"
.Range(
M$C$7 4"
.Value
= Worksheets(
"calc"
.Range(
M$C$ 4 4"
.Value
Worksheets
"Calc"
.Range(
M$D$7 4"
.Value
= Worksheets(
"calc"
.Range(
M$D$ 4 4"
.Value
Worksheets
"Calc"
.Range(
"$F$7 4"
.Value
= Worksheets(
"calc"
.Range(
M$F$ 4 4"
.Value
Worksheets
"Calc"
.Range(
M$H$7 4"
.Value
= Worksheets(
"calc"
.Range(
M$H$ 4 4"
.Value
Worksheets (
"Calc"
.Range(
"$I$74"
.Value
= Worksheets(
"calc"
.Range(
"$!$ 4 4"
.Value
Worksheets("Calc").btnSolverNet.Caption = "Net Feed - Complete"
Else
For n = 1 To nLoops ' loops if necessary
DoEvents
If boolAbortScaleCalcs = True Then
boolAbortScaleCalcs = False
If MsgBox("Abort Scaling Calcs?", vbOKCancel) = vbOK Then
GoTo OuttaHere
End If
End If
Worksheets("Calc").btnSolverNet.Caption = "Solving Na - Net Feed"
SolverOk SetCell:="$M$64", MaxMinVal:=2, ValueOf:="0", ByChange:="$N$64"
SolverOptions MaxTime:=100, Iterations:=10000, Precision:=0.00000001, _
AssumeLinear:=True, StepThru:=False, Estimates:=1, Derivatives:=1, SearchOption
:=1, IntTolerance:=0.001, Scaling:=True, Convergence:=0.001, AssumeNonNeg:=True
Need to make sure old criteria are deleted
SolverDelete
CellRef:=
,$H$43"
, Relation:
=3,
FormulaText:=
"0"
SolverDelete
CellRef:=
'$M$34 "
, Relation:
=3,
FormulaText:=
'QM
SolverDelete
CellRef:=
'$M$35M
, Relation:
=3,
FormulaText:=
'0"
SolverDelete
CellRef:=
'$M$3 6 "
, Relation:
=3,
FormulaText:=
'QM
' $N$68 keeps appearing so
get rid of
it
just in case.
SolverDelete
CellRef:=
T$N$ 68 "
, Relation:
=3,
FormulaText:=
'0"
SolverDelete
CellRef:=
'$H$7 3 "
, Relation:
=3,
FormulaText:=
'0"
SolverDelete
CellRef:=
'$M$ 64 "
, Relation:
=3,
FormulaText:=
,Q„
SolverDelete
CellRef:=
'$M$ 65 "
, Relation:
=3,
FormulaText:=
'QM
SolverDelete
CellRef:=
'$M$ 6 6"
, Relation:
=3,
FormulaText:=
'0"
SolverDelete
CellRef:=
'$N$ 68 "
, Relation:
=3,
FormulaText:=
'0"
SolverDelete
CellRef:=
'$H$103
", Relation
: =3
, FormulaText:=
= "0
SolverDelete
CellRef:=
'$M$ 94 "
, Relation:
=3,
FormulaText:=
'0"
SolverDelete
CellRef:=
'$M$ 95 "
, Relation:
= 3,
FormulaText:=
'0"
SolverDelete
CellRef:=
'$M$ 9 6"
, Relation:
= 3,
FormulaText:=
'0"
SolverDelete
CellRef:=
'$N$ 98 "
, Relation:
= 3,
FormulaText:=
'0"
DoEvents
If Range("$L$64").Value <= 0 Then
Range("$N$64").Value = 0
Else
SolverAdd CellRef:="$M$64", Relation:=3, FormulaText:="0"
SolverOk SetCell: = "$M$64", MaxMinVal:=2, ValueOf: = "0", ByChange: = "$N$64"
' This UserFinish:=True causes the last section to be bypassed
' This is an undocumented feature of the Excel Solver
Solversolve UserFinish:=True
SolverDelete CellRef:="$M$64", Relation:=3, FormulaText:="0"
End If
K
If Range("$L$65").Value <= 0 Then
Range("$N$65").Value = 0
Else
Worksheets("Calc").btnSolverNet.Caption = "Solving K - Net Feed"
SolverOk SetCell:="$M$65", MaxMinVal:=2, ValueOf:="0", ByChange:="$N$65"
68
-------
SolverAdd CellRef:="$M$65", Relation:=3,
SolverOk SetCell:="$M$65", MaxMinVal:=2,
Solversolve UserFinish:=True
SolverDelete CellRef:="$M$65", Relation:
End If
Ca
If Range("$L$66").Value <= 0 Then
Range("$N$66").Value = 0
Else
Worksheets("Calc").btnSolverNet.Caption = "Solving Ca - Net Feed"
SolverOk SetCell:="$M$66", MaxMinVal:=2, ValueOf:="0", ByChange:="$N$66
SolverAdd CellRef:="$M$66", Relation:=3, FormulaText:="0"
SolverOk SetCell:="$M$66", MaxMinVal:=2, ValueOf:="0", ByChange:="$N$66
Solversolve UserFinish:=True
SolverDelete CellRef:="$M$66", Relation:=3, FormulaText:="0"
End If
DoEvents
Mg
If Range("$L$67").Value <= 0 Then
Range("$N$67").Value = 0
Else
Worksheets("Calc").btnSolverNet.Caption = "Solving Mg - Net Feed"
SolverOk SetCell:="$M$67", MaxMinVal:=2, ValueOf:="0", ByChange:="$N$67
SolverAdd CellRef:="$M$67", Relation:=3, FormulaText:="0"
SolverOk SetCell:="$M$67", MaxMinVal:=2, ValueOf:="0", ByChange:="$N$67
Solversolve UserFinish:=True
SolverDelete CellRef:="$M$67", Relation:=3, FormulaText:="0"
End If
A1
If Range("$L$68").Value <= 0 Then
Range("$N$68").Value = 0
Else
Worksheets("Calc").btnSolverNet.Caption = "Solving A1 - Net Feed"
SolverOk SetCell:="$M$68", MaxMinVal:=2, ValueOf:="0", ByChange:="$N$68
'SolverDelete CellRef:="$M$67", Relation:=3, FormulaText:="0"
SolverAdd CellRef:="$M$68", Relation:=3, FormulaText:="0"
SolverOk SetCell:="$M$68", MaxMinVal:=2, ValueOf:="0", ByChange:="$N$68
Solversolve UserFinish:=True
SolverDelete CellRef:="$M$68", Relation:=3, FormulaText:="0"
End If
Fe2
If Range("$L$69").Value <= 0 Then
Range("$N$69").Value = 0
Else
Worksheets("Calc").btnSolverNet.Caption = "Solving Fe2 - Net Feed"
SolverOk SetCell:="$M$69", MaxMinVal:=2, ValueOf:="0", ByChange:="$N$69
SolverAdd CellRef:="$M$69", Relation:=3, FormulaText:="0"
SolverOk SetCell: = "$M$69", MaxMinVal:=2, ValueOf: = "0", ByChange: = "$N$69
Solversolve UserFinish:=True
SolverDelete CellRef:="$M$69", Relation:=3, FormulaText:="0"
End If
Fe3
If Range("$L$70").Value <= 0 Then
Range("$N$70").Value = 0
Else
Worksheets("Calc").btnSolverNet.Caption = "Solving Fe3 - Net Feed"
SolverOk SetCell:="$M$70", MaxMinVal:=2, ValueOf:="0", ByChange:="$N$70
SolverAdd CellRef:="$M$70", Relation:=3, FormulaText:="0"
SolverOk SetCell: = "$M$70", MaxMinVal:=2, ValueOf: = "0", ByChange: = "$N$70
Solversolve UserFinish:=True
SolverDelete CellRef:="$M$70", Relation:=3, FormulaText:="0"
End If
DoEvents
FormulaText:="0"
ValueOf: = "0", ByChange: = "$N$65
=3, FormulaText:="0"
69
-------
Now Anions
CI
If Range("$C$72").Value <= 0 Then
Range("$C$74").Value = 0
Else
Worksheets("Calc").btnSolverNet.Caption = "Solving CI - Net Feed"
SolverOk SetCell:="$C$73", MaxMinVal:=2, ValueOf:="0", ByChange:="$C$74"
SolverAdd CellRef:="$C$73", Relation:=3, FormulaText:="0"
SolverOk SetCell: = "$C$73", MaxMinVal:=2, ValueOf: = "0", ByChange: = "$C$74"
Solversolve UserFinish:=True
SolverDelete CellRef:="$C$73", Relation:=3, FormulaText:="0"
End If
S04
If Range("$D$72").Value <= 0 Then
Range("$D$74").Value = 0
Else
Worksheets("Calc").btnSolverNet.Caption = "Solving S04 - Net Feed"
SolverOk SetCell:="$D$73", MaxMinVal:=2, ValueOf:="0", ByChange:="$D$74"
SolverAdd CellRef:="$D$73", Relation:=3, FormulaText:="0"
SolverOk SetCell: = "$D$73", MaxMinVal:=2, ValueOf: = "0", ByChange: = "$D$74"
Solversolve UserFinish:=True
SolverDelete CellRef:="$D$73", Relation:=3, FormulaText:="0"
End If
HC03
If Range("$F$72").Value <= 0 Then
Range("$F$74").Value = 0
Else
Worksheets("Calc").btnSolverNet.Caption = "Solving HC03 - Net Feed"
SolverOk SetCell:="$F$73", MaxMinVal:=2, ValueOf:="0", ByChange:="$F$74"
SolverAdd CellRef:="$F$73", Relation:=3, FormulaText:="0"
SolverOk SetCell: = "$F$73", MaxMinVal:=2, ValueOf: = "0", ByChange: = "$F$74"
Solversolve UserFinish:=True
SolverDelete CellRef:="$F$73", Relation:=3, FormulaText:="0"
End If
HP04
If Range("$H$72").Value <= 0 Then
Range("$H$74").Value = 0
Else
Worksheets("Calc").btnSolverNet.Caption = "Solving HP04 - Net Feed"
SolverOk SetCell:="$H$73", MaxMinVal:=2, ValueOf:="0", ByChange:="$H$74"
SolverAdd CellRef:="$H$73", Relation:=3, FormulaText:="0"
SolverOk SetCell: = "$H$73", MaxMinVal:=2, ValueOf: = "0", ByChange: = "$H$74"
Solversolve UserFinish:=True
SolverDelete CellRef:="$H$73", Relation:=3, FormulaText:="0"
End If
DoEvents
H2P04
If Range("$I$72").Value <= 0 Then
Range("$I$74").Value = 0
Else
Worksheets("Calc").btnSolverNet.Caption = "Solving H2P04 - Net Feed"
SolverOk SetCell:="$I$73", MaxMinVal:=2, ValueOf:="0", ByChange:="$I$74"
SolverAdd CellRef: = "$I$73", Relation:=3, FormulaText: = "0"
SolverOk SetCell:="$I$73", MaxMinVal:=2, ValueOf:="0", ByChange:="$I$74"
Solversolve UserFinish:=True
SolverDelete CellRef: = "$I$73", Relation:=3, FormulaText: = "0"
End If
Worksheets("Calc").btnSolverNet.Caption = "Net Feed - Complete Pass " & Format(n, "#") &
" of " & Format(nLoops, "#")
Next n
End If
If nSections <> 0 Then GoTo OuttaHere
SectionConcentrate:
70
-------
CONCENTRATE
Worksheets("Calc").btnRunSolver.Width = nbtnWidth * 0.9
Worksheets("Calc").btnRunSolver.Caption = "Solving Cone."
For n = 1 To nLoops ' loops if necessary
DoEvents
If boolAbortScaleCalcs = True Then
boolAbortScaleCalcs = False
If MsgBox("Abort Scaling Calcs?", vbOKCancel) = vbOK Then
GoTo OuttaHere
End If
End If
Worksheets("Calc").btnSolverConc.Caption = "Solving Na - Concentrate"
SolverOk SetCell:="$M$94", MaxMinVal:=2, ValueOf:="0", ByChange:="$N$94"
SolverOptions MaxTime:=100, Iterations:=10000, Precision:=0.00000001, _
AssumeLinear:=True, StepThru:=False, Estimates:=1, Derivatives:=1, SearchOption
:=1, IntTolerance:=0.001, Scaling:=True, Convergence:=0.001, AssumeNonNeg:=True
Need to make
sure old
criteria
are deleted
SolverDelete
CellRef:=
"$H$ 4 3",
Relation:
=3,
FormulaText:=
¦0"
SolverDelete
CellRef:=
"$M$3 4",
Relation:
=3,
FormulaText:=
¦0"
SolverDelete
CellRef:=
"$M$35",
Relation:
= 3,
FormulaText: =
¦0"
SolverDelete
CellRef:=
"$M$36",
Relation:
= 3,
FormulaText:=
¦0"
' $N$38 keeps appearing so get rid of
it
just in case.
SolverDelete
CellRef:=
"$N$3 8",
Relation:
= 3,
FormulaText: =
¦0"
SolverDelete
CellRef:=
"$H$7 3",
Relation:
= 3,
FormulaText:=
¦0"
SolverDelete
CellRef:=
"$M$ 64 ",
Relation:
= 3,
FormulaText:=
¦0"
SolverDelete
CellRef:=
"$M$ 65 ",
Relation:
= 3,
FormulaText: =
¦0"
SolverDelete
CellRef:=
"$M$ 6 6",
Relation:
= 3,
FormulaText:=
¦0"
SolverDelete
CellRef:=
"$N$ 68",
Relation:
= 3,
FormulaText:=
¦0"
SolverDelete
CellRef:=
"$H$103"
Relation
: =3
, FormulaText:
= "0
SolverDelete
CellRef:=
"$M$ 94",
Relation:
= 3,
FormulaText:=
¦0"
SolverDelete
CellRef:=
"$M$ 95",
Relation:
= 3,
FormulaText:=
¦0"
SolverDelete
CellRef:=
"$M$ 9 6",
Relation:
= 3,
FormulaText:=
¦0"
SolverDelete
CellRef:=
"$N$ 98",
Relation:
= 3,
FormulaText:=
¦0"
DoEvents
If Range("$L$94").Value <= 0 Then
Range("$N$94").Value = 0
Else
SolverAdd CellRef:="$M$94", Relation:=3, FormulaText:="0"
SolverOk SetCell: = "$M$94", MaxMinVal:=2, ValueOf: = "0", ByChange: = "$N$94"
' This UserFinish:=True causes the last section to be bypassed
' This is an undocumented feature of the Excel Solver
Solversolve UserFinish:=True
SolverDelete CellRef:="$M$94", Relation:=3, FormulaText:="0"
End If
K
If Range("$L$95").Value <= 0 Then
Range("$N$95").Value = 0
Else
Worksheets("Calc").btnSolverConc.Caption
SolverOk SetCell:="$M$95", MaxMinVal:=2,
SolverAdd CellRef:="$M$95", Relation:=3,
SolverOk SetCell:="$M$95", MaxMinVal:=2,
Solversolve UserFinish:=True
SolverDelete CellRef:="$M$95", Relation:=3, FormulaText:="0
End If
= "Solving K - Concentrate"
ValueOf: = "0", ByChange: = "$N$95"
FormulaText:="0"
ValueOf: = "0", ByChange: = "$N$95"
Ca
If Range("$L$96").Value <= 0 Then
Range("$N$96").Value = 0
Else
Worksheets("Calc").btnSolverConc.Caption = "Solving Ca
SolverOk SetCell:="$M$96", MaxMinVal:=2, ValueOf:="0",
SolverAdd CellRef:="$M$96", Relation:=3, FormulaText:='
SolverOk SetCell:="$M$96", MaxMinVal:=2, ValueOf:="0",
Solversolve UserFinish:=True
SolverDelete CellRef:="$M$96", Relation:=3, FormulaText
- Concentrate"
ByChange: = "$N$ 96"
0"
ByChange: = "$N$ 96"
¦•0"
71
-------
End If
If Range("$L$97").Value <= 0 Then
Range("$N$97").Value = 0
Else
Worksheets("Calc").btnSolverConc.Caption = "Solving Mg - Concentrate"
SolverOk SetCell:="$M$97", MaxMinVal:=2, ValueOf:="0", ByChange:="$N$97"
SolverAdd CellRef:="$M$97", Relation:=3, FormulaText:="0"
SolverOk SetCell:="$M$97", MaxMinVal:=2, ValueOf:="0", ByChange:="$N$97"
Solversolve UserFinish:=True
SolverDelete CellRef:="$M$97", Relation:=3, FormulaText:="0"
End If
DoEvents
If Range("$L$98").Value <= 0 Then
Range("$N$98").Value = 0
Else
Worksheets("Calc").btnSolverConc.Caption = "Solving A1 - Concentrate"
SolverOk SetCell:="$M$98", MaxMinVal:=2, ValueOf:="0", ByChange:="$N$98"
'SolverDelete CellRef:="$M$97", Relation:=3, FormulaText:="0"
SolverAdd CellRef:="$M$98", Relation:=3, FormulaText:="0"
SolverOk SetCell:="$M$98", MaxMinVal:=2, ValueOf:="0", ByChange:="$N$98"
Solversolve UserFinish:=True
SolverDelete CellRef:="$M$98", Relation:=3, FormulaText:="0"
End If
Fe2
If Range("$L$99").Value <= 0 Then
Range("$N$99").Value = 0
Else
Worksheets("Calc").btnSolverConc.Caption = "Solving Fe2 - Concentrate"
SolverOk SetCell:="$M$99", MaxMinVal:=2, ValueOf:="0", ByChange:="$N$99"
SolverAdd CellRef:="$M$99", Relation:=3, FormulaText:="0"
SolverOk SetCell:="$M$99", MaxMinVal:=2, ValueOf:="0", ByChange:="$N$99"
Solversolve UserFinish:=True
SolverDelete CellRef:="$M$99", Relation:=3, FormulaText:="0"
End If
Fe3
If Range("$L$100").Value <= 0 Then
Range("$N$100").Value = 0
Else
Worksheets("Calc").btnSolverConc.Caption = "Solving Fe3 - Concentrate"
SolverOk SetCell: = "$M$100", MaxMinVal:=2, ValueOf: = "0", ByChange: = "$N$100
SolverAdd CellRef:="$M$100", Relation:=3, FormulaText:="0"
SolverOk SetCell:="$M$100", MaxMinVal:=2, ValueOf:="0", ByChange:="$N$100
Solversolve UserFinish:=True
SolverDelete CellRef:="$M$100", Relation:=3, FormulaText:="0"
End If
DoEvents
Now Anions
CI
If Range("$C$102").Value <= 0 Then
Range("$C$104").Value = 0
Else
Worksheets("Calc").btnSolverConc.Caption = "Solving CI - Concentrate"
SolverOk SetCell: = "$C$103", MaxMinVal:=2, ValueOf: = "0", ByChange: = "$C$104
SolverAdd CellRef:="$C$103", Relation:=3, FormulaText:="0"
SolverOk SetCell:="$C$103", MaxMinVal:=2, ValueOf:="0", ByChange:="$C$104
Solversolve UserFinish:=True
SolverDelete CellRef:="$C$103", Relation:=3, FormulaText:="0"
End If
S04
If Range("$D$102").Value <= 0 Then
Range("$D$104").Value = 0
72
-------
Else
Worksheets("Calc").btnSolverConc.Caption = "Solving S04 - Concentrate"
SolverOk SetCell: = "$D$103", MaxMinVal:=2, ValueOf: = "0", ByChange: = "$D$104"
SolverAdd CellRef:="$D$103", Relation:=3, FormulaText:="0"
SolverOk SetCell: = "$D$103", MaxMinVal:=2, ValueOf: = "0", ByChange: = "$D$104"
Solversolve UserFinish:=True
SolverDelete CellRef:="$D$103", Relation:=3, FormulaText:="0"
End If
DoEvents
HC03
If Range("$F$102").Value <= 0 Then
Range("$F$104").Value = 0
Else
Worksheets("Calc").btnSolverConc.Caption = "Solving HC03 - Concentrate"
SolverOk SetCell: = "$F$103", MaxMinVal:=2, ValueOf: = "0", ByChange: = "$F$104"
SolverAdd CellRef:="$F$103", Relation:=3, FormulaText:="0"
SolverOk SetCell:="$F$103", MaxMinVal:=2, ValueOf:="0", ByChange:="$F$104"
Solversolve UserFinish:=True
SolverDelete CellRef:="$F$103", Relation:=3, FormulaText:="0"
End If
HP04
If Range("$H$102").Value <= 0 Then
Range("$H$104").Value = 0
Else
Worksheets("Calc").btnSolverConc.Caption = "Solving HP04 - Concentrate"
SolverOk SetCell: = "$H$103", MaxMinVal:=2, ValueOf: = "0", ByChange: = "$H$104"
SolverAdd CellRef:="$H$103", Relation:=3, FormulaText:="0"
SolverOk SetCell:="$H$103", MaxMinVal:=2, ValueOf:="0", ByChange:="$H$104"
Solversolve UserFinish:=True
SolverDelete CellRef:="$H$103", Relation:=3, FormulaText:="0"
End If
H2P04
If Range("$I$102").Value <= 0 Then
Range("$I$104").Value = 0
Else
Worksheets("Calc").btnSolverConc.Caption = "Solving H2P04 - Concentrate"
SolverOk SetCell: = "$1$103", MaxMinVal:=2, ValueOf: = "0", ByChange: = "$1$104"
SolverAdd CellRef:="$1$103", Relation:=3, FormulaText:="0"
SolverOk SetCell: = "$1$103", MaxMinVal:=2, ValueOf: = "0", ByChange: = "$1$104"
Solversolve UserFinish:=True
SolverDelete CellRef:="$1$103", Relation:=3, FormulaText:="0"
End If
Worksheets("Calc").btnSolverConc.Caption = "Concentrate - Complete Pass " & Format(n, "#") &
" of " & Format(nLoops, "#")
Next n
' we're done
OuttaHere:
.btnRunSolver.Caption = "Start Solver - ALL"
.btnRunSolver.BackColor = &HE0E0E0
.btnRunSolver.Width = nbtnWidth
.btnSolverTreated.Caption = "Start Solver - Treated Feed"
.btnSolverNet.Caption = "Start Solver - Net Feed"
.btnSolverConc.Caption = "Start Solver - Concentrate"
End Sub
Worksheets ( Calc
Worksheets("Calc"
Worksheets("Calc"
Worksheets("Calc"
Worksheets("Calc"
Worksheets("Calc"
ModMembranes
This module contains the code used by the list boxes on the System Design sheet and the Add/Edit
membrane functions.
Attribute VB_Name = "ModMembranes"
Option Explicit
Global tempStop As Boolean
73
-------
Public ListLabels (10) As String
Public MembraneList() As String
' The main variable for the membrane Element type
' The naming is confusing, since the membrane and element are combined
Public Type Membrane
Description As String
Diameter As Single
Length As Single
Type As Integer
NaCl_rejection As Double
MgS04_rejection As Double
TestPressure As Double
TestTDS As Double
TestFlow As Double
TestTemperature As Double
Area As Double
' The values below as Calculated Values from the spec sheet data
Avalue As Double
BValue As Double
Blons(LAST_ION) As Double
' These are set according to the type of membrane
BTempCorr As Double
ATempCorr As Double
End Type
Public MembAvailO As Membrane
Public MyMembrane As Membrane
The "tempstop " variable is used at many places to avoid looping through a control or function.
Activating or selecting a cell or button calls the "click" event. This variable allows the click event to be
ignored or used as called for.
' Sub to load the available types - uses MembraneData sheet information
Public Sub LoadTypeList()
tempStop = True
Dim n As Integer, NT As Integer
Worksheets("Membrane Data").Activate
Worksheets("Membrane Data").Range("ListLabels").Select
NT = Worksheets("Membrane Data").Range("NumberTypes").Value
For n = 1 To NT
ActiveCell.Offset(1, 0).Select
ListLabels(n) = ActiveCell.Value
Next
Worksheets("System Design").IstMembraneType.Clear
For n = 1 To NT
Worksheets("System Design").IstMembraneType.Addltem Trim(ListLabels(n))
Next
Worksheets("System Design").IstMembraneType.Addltem "Create New Element Type"
Worksheets("System Design").Activate
If Worksheets("System Design").Range("FileOpened").Value = "True" Then
Worksheets("System Design").IstMembraneType.Listlndex = 0
Worksheets("System Design").Range("Typelndex").Value = 0
Else
Worksheets("System Design").IstMembraneType.Listlndex = Worksheets("System
Design").Range("Typelndex").Value
End If
tempStop = False
End Sub
Public Sub GetMembraneList(mt As Integer)
tempStop = True
Dim dummy As Double
Dim n As Integer, m As Integer
Dim BeginRecord As Long, EndRecord As Long, TypeCount As Integer
' If boolDebug2 Then MsgBox "loading list"
TypeCount = Worksheets("Membrane Data").Range("TypeCount").Offset(mt, 0).Value
BeginRecord = Worksheets("Membrane Data").Range("BeginRow").Offset(mt, 0).Value
74
-------
EndRecord = BeginRecord + (TypeCount - 1)
ReDim MembraneList(4, TypeCount)
ReDim MembAvail(TypeCount) As Membrane
' now rebuild the element type list box with the right elements
Worksheets("System Design").IstSelectMembrane.Clear
If TypeCount = 0 Then
m = MsgBox("There are no membranes of the selected type in the" + Chr(13) + _
"membrane database.", vbOKOnly, "List Empty")
Worksheets("System Design").IstSelectMembrane.Listlndex = -1
tempStop = False
Exit Sub
End If
' get a brief description into the record and save it into the membrane variable MembAvail(n)
' This will later use the Listlndex of the IstSelectMembrane as the array pointer to get the
data
For n = 1 To TypeCount
With MembAvail(n)
MembraneList(1, n) = Trim(Range("Description").Offset(BeginRecord + 2 + n - 1, 0).Value)
'Trim(ActiveCell.Value)
MembraneList(2, n) = Trim(Range("Diameter").Offset(BeginRecord + 2 + n - 1, 0).Value)
'Trim(ActiveCell.Value)
MembraneList(3, n) = Trim(Range("Length").Offset(BeginRecord + 2 + n - 1, 0).Value)
'Trim(ActiveCell.Value)
MembraneList(4, n) = Trim(Range("Elem_Area").Offset(BeginRecord + 2 + n - 1, 0).Value)
as they
Description = MembraneList(1, n)
Diameter = MembraneList(2, n)
Length = MembraneList(3, n)
Type = Range("Type").Offset(BeginRecord + 2 + n - 1, 0).Value
NaCl_rejection = Range("NaCl_Rejection").Offset(BeginRecord + 2 + n - 1, 0).Value
MgS04_rejection = Range("MgS04_Rejection").Offset(BeginRecord + 2 + n - 1, 0).Value
TestPressure = Range("Test_Pressure").Offset(BeginRecord + 2 + n - 1, 0).Value
TestTDS = Range("Test_Tds").Offset(BeginRecord + 2 + n - 1, 0).Value
TestFlow = Range("Test_Flow_Rate").Offset(BeginRecord + 2 + n - 1, 0).Value
TestTemperature = Range("Test_Temperature").Offset(BeginRecord + 2 + n - 1, 0).Value
Area = Range("Elem_Area").Offset(BeginRecord + 2 + n - 1, 0).Value
The values below as Calculated Values from the spec sheet data and are not needed now
are the same for all elements of this type mem,brea
Avalue As Double
BValue As Double
Blons(MAX_IONS) As Double
These are set according to the type of membrane but can
BTempCorr As Double
ATempCorr As Double
End With
Next n
Worksheets("System Design").Activate
For n = 1 To TypeCount
Worksheets("System Design").IstSelectMembrane.Addltem Trim(MembraneList(1, n)) & " &
Trim(MembraneList (2, n)) & " in. Dia., " & _
Trim(MembraneList(3, n)) & " in. Len., " & Trim(MembraneList(4, n) ) & " ftA2"
Next
If Worksheets("System Design").Range("FileOpened").Value = "True" Then
Worksheets("System Design").IstSelectMembrane.Listlndex = 0
Else
Worksheets("System Design").IstSelectMembrane.Listlndex = Worksheets("System
Design").Range("Membranelndex").Value
End If
n = Worksheets("System Design").IstSelectMembrane.Listlndex + 1
' now put the selected element in the 1st row of Membrane Data so the calculation engine
can grab it.
75
-------
With MembAvail(n)
Range("Description").Offset(-1, 0).Value = .Description
Range("Diameter").Offset(-1, 0).Value = .Diameter
Range("Length").Offset (-1, 0).Value = .Length
Range("Type").Offset(-1, 0).Value = .Type
Range("NaCl_Rejection").Offset(-1, 0).Value = .NaCl_rejection
Range("MgS04_Rejection").Offset(-1, 0).Value = .MgS04_rejection
Range("Test_Pressure").Offset(-1, 0).Value = .TestPressure
Range("Test~Tds").Offset(-1, 0).Value = .TestTDS
Range("Test_Flow_Rate").Offset(-1, 0).Value = .TestFlow
Range("Test_Temperature").Offset(-1, 0).Value = .TestTDS
Range("Elem_Area").Offset (-1, 0).Value = .Area
End With
tempStop = False
End Sub
When the user selects the membrane - eg a 4" diameter, TFC membrane element. The specifics of that
element are copied to the first row of the Membrane Data sheet. The calculation engine then looks in this
first row to get the data it needs to complete the system design.
Public Sub GetMembraneData(MembraneType As Integer, Membranelndex As Integer)
Dim n As Integer
Dim nOffset As Integer
Dim dummy As Double
n = Worksheets("System Design").IstSelectMembrane.Listlndex + 1
' now put the selected element in the 1st row of Membrane Data so the calculation
engine can grab it.
On Error Resume Next
With MembAvail(n)
' Err.Raise 9 ' test to be sure we have initialized the variables
If Err.Number = 9 Then
nOffset = Worksheets("System Design").IstMembraneType.Listlndex + 1
GetMembraneList nOffset
On Error GoTo 0
End If
Range("Description").Offset(-1, 0).Value = .Description
Range("Diameter").Offset(-1, 0).Value = .Diameter
Range("Length").Offset(-1, 0).Value = .Length
Range("Type").Offset(-1, 0).Value = .Type
Range("NaCl_Rejection").Offset(-1, 0).Value = .NaCl_rejection
Range("MgS04_Rejection").Offset(-1, 0).Value = .MgS04_rejection
Range("Test_Pressure").Offset(-1, 0).Value = .TestPressure
Range("Test~Tds").Offset(-1, 0).Value = .TestTDS
Range("Test_Flow_Rate").Offset(-1, 0).Value = .TestFlow
Range("Test_Temperature").Offset(-1, 0).Value = .TestTDS
Range("Elem_Area").Offset(-1, 0).Value = .Area
End With
tempStop = False
End Sub
System Design Sheet Code
The code associated and stored on this sheet is used for several important calculations. In addition,
this section contains an explanation of how the bank and flow graphics work.
Option Explicit
Constants used to enter the flow graphic calculation
Const ENTER_FEED = 1
Const ENTER_INTRECOVERY = 2
Const ENTER_SYSRECOVERY = 3
Const ENTER_PRODUCT = 4
Const ENTER_CONC = 5
Const ENTER_RECYCLE = 6
Const ENTER_UNITS = 7 ' used for changing units - called from Flow_Select_Drop_Click
Public NewType As Boolean
76
-------
Private Sub btnAddMembrane_Click()
Worksheets("Membrane Data").Activate
frmMembraneData.Show
End Sub
Public Sub btnCalculate_Click()
MainCalc
End Sub
Private Sub CommandButtonl_Click()
lblRecalc.Visible = False
msgRecalc.Visible = False
End Sub
Private Sub btnRestoreDefault_Click()
Worksheets("Membrane Data").Activate
Worksheets("Membrane Data").Range("Type" & Trim(Str(Worksheets("Membrane Data").Range
("NumberTypes").Value + 1))).Offset(1, 0).Select
End Sub
Private Sub btnStreams_Click()
Worksheets("Streams").Activate
Worksheets("Streams").Range("BIO").Select
End Sub
Private Sub btnSummary_Click()
Worksheets("Summary").Activate
Worksheets("Summary").Range("BIO").Select
End Sub
The list box for membrane types - ie nanofiltration, cellulose acetate, etc.
Private Sub lstMembraneType_Change()
If tempStop Then Exit Sub
Application.ScreenUpdating = False
If IstMembraneType.Listlndex + 1 = IstMembraneType.ListCount Then
NewType = True
tempStop = True
IstMembraneType.Listlndex = 0
tempStop = False
Worksheets("System Design").Range("Typelndex").Value = 0
Worksheets("Membrane Data") .Activate
Application.ScreenUpdating = True
Worksheets("Membrane Data").Range("Type" & Trim(Str(Worksheets("Membrane Data").Range
("NumberTypes").Value + 1))).Offset(1, 0).Select
Exit Sub
Else
NewType = False
' need to remember Listlndex starts at 0 so need to add 1 if first number is 1
GetMembraneList IstMembraneType.Listlndex + 1
Worksheets("System Design").Range("Typelndex").Value = IstMembraneType.Listlndex
setChanged
End If
Application.ScreenUpdating = True
Worksheets("System Design").Range("J10").Select
End Sub
Private Sub lstSelectMembrane_Change()
If Not NewType Then setChanged
GetMembraneData IstMembraneType.Listlndex + 1, IstSelectMembrane.Listlndex + 1
If Worksheets("System Design").Range("FileOpened").Value = "True" Then Worksheets("System
Design").Range("Membranelndex").Value = IstSelectMembrane.Listlndex
End Sub
Private Sub spNumberBanks_Change()
Dim n As Integer
n = Range("NumberBanks").Value
setChanged
77
-------
End Sub
Private Sub spNumberBanks_SpinDown()
Dim n As Integer
n = Range("NumberBanks").Value
SendToBack n
End Sub
Private Sub spNumberBanks_SpinUp()
Dim n As Integer
n = Range("NumberBanks").Value
BringToFront n
End Sub
Private Sub spNumberElementsPerVessel_Change()
setChanged
End Sub
Change the number of banks using the spinners. The rectangles (Ac tive Sheet. Shapes) are always in
existence. They either hide behind or move to the front. Since Excel assigns an index to each shape as it
is created, we had no control over the index. Thus the first bank shapes start at 196, Bank 2 at 216, and
so on. The .Zorder command is the command for front or back.
Private Sub spBankl_SpinDown()
Dim n As Integer
Dim basel As Integer
Dim strName As String
setChanged
n = Range ( "Bankl").Value
If n < 1 Then n = 1
basel = 196
If n > 19 Then Exit Sub
strName = Trim(Str(basel + n))
ActiveSheet.Shapes(strName).Select
Selection.ShapeRange.ZOrder msoSendToBack
Range("Bankl").Select
End Sub
Private Sub spBankl_SpinUp()
Dim n As Integer
Dim basel As Integer
Dim strName As String
setChanged
n = Range("Bankl").Value
basel = 195
If n > 20 Then Exit Sub
strName = Trim(Str(basel + n) )
ActiveSheet.Shapes(strName).Select
Selection.ShapeRange.ZOrder msoBringToFront
Range("Bankl").Select
End Sub
Private Sub spBank2_Change()
setChanged
End Sub
Private Sub spBank2_SpinDown()
Dim n As Integer
Dim base2 As Integer
Dim strName As String
setChanged
n = Range("Bank2").Value
If n < 1 Then n = 1
base2 = 216
If n > 19 Then Exit Sub
strName = Trim(Str(base2 + n) )
ActiveSheet.Shapes(strName).Select
Selection.ShapeRange.ZOrder msoSendToBack
Range("Bank2").Select
78
-------
End Sub
Private Sub spBank2_SpinUp()
Dim n As Integer
Dim base2 As Integer
Dim strName As String
setChanged
n = Range("Bank2").Value
base2 = 215
If n > 20 Then Exit Sub
strName = Trim(Str(base2 + n) )
ActiveSheet.Shapes(strName).Select
Selection.ShapeRange.ZOrder msoBringToFront
Range("Bank2").Select
End Sub
Private Sub SendToBack(n As Integer)
Dim m As Integer, base As Integer
Dim strName As String
If n < 4 Then
base = 256
For m = base To base + 19
strName = "Rectangle " + Trim(Str(m))
'On Error Resume Next
ActiveSheet.Shapes(strName).Select
Selection.ShapeRange.ZOrder msoSendToBack
Next
spBank4.Enabled = False
Range("Bank4").Value = 0
Range("NumberBanks").Select
End If
If n < 3 Then
base = 236
For m = base To base + 19
strName = "Rectangle " + Trim(Str(m))
ActiveSheet.Shapes(strName).Select
Selection.ShapeRange.ZOrder msoSendToBack
Next
spBank3.Enabled = False
Range("Bank3").Value = 0
Range("NumberBanks").Select
End If
If n < 2 Then
base = 216
For m = base To base + 19
strName = "Rectangle " + Trim(Str(m))
ActiveSheet.Shapes(strName).Select
Selection.ShapeRange.ZOrder msoSendToBack
Next
spBank2.Enabled = False
Range("Bank2").Value = 0
Range("NumberBanks").Select
End If
If n < 1 Then
base = 196
For m = base To base + 19
strName = "Rectangle " + Trim(Str(m))
ActiveSheet.Shapes(strName).Select
Selection.ShapeRange.ZOrder msoSendToBack
Next
spBankl.Enabled = False
Range("Bankl").Value = 0
Range("NumberBanks").Select
End If
End Sub
Private Sub BringToFront(n As Integer)
Dim strName As String
Select Case n
Case 1
strName = "Rectangle " + Trim(Str(196))
79
-------
ActiveSheet.Shapes(strName).Select
Selection.ShapeRange.ZOrder msoBringToFront
Range ( "Bankl").Value = 1
spBankl.Enabled = True
Range("NumberBanks").Select
Case 2
strName = "Rectangle " + Trim(Str(216))
ActiveSheet.Shapes(strName).Select
Selection.ShapeRange.ZOrder msoBringToFront
Range("Bank2").Value = 1
spBank2.Enabled = True
Range("NumberBanks").Select
Case 3
strName = "Rectangle " + Trim(Str(236))
ActiveSheet.Shapes(strName).Select
Selection.ShapeRange.ZOrder msoBringToFront
Range("Bank3").Value = 1
spBank3.Enabled = True
Range("NumberBanks").Select
Case 4
strName = "Rectangle " + Trim(Str(256))
ActiveSheet.Shapes(strName).Select
Selection.ShapeRange.ZOrder msoBringToFront
Range("Bank4").Value = 1
spBank4.Enabled = True
Range("NumberBanks").Select
End Select
End Sub
Private Sub spBank3_Change()
setChanged
End Sub
Private Sub spBank3_SpinDown()
Dim n As Integer
Dim base3 As Integer
Dim strName As String
setChanged
n = Range("Bank3").Value
If n < 1 Then n = 1
base3 = 236
If n > 19 Then Exit Sub
strName = Trim(Str(base3 + n) )
ActiveSheet.Shapes(strName).Select
Selection.ShapeRange.ZOrder msoSendToBack
Range("Bank3").Select
End Sub
Private Sub spBank3_SpinUp()
Dim n As Integer
Dim base3 As Integer
Dim strName As String
setChanged
n = Range("Bank3").Value
base3 = 235
If n > 20 Then Exit Sub
strName = Trim(Str(base3 + n) )
ActiveSheet.Shapes(strName).Select
Selection.ShapeRange.ZOrder msoBringToFront
Range("Bank3").Select
End Sub
Private Sub spBank4_Change()
setChanged
End Sub
Private Sub spBank4_SpinDown()
Dim n As Integer
Dim base4 As Integer
Dim strName As String
setChanged
80
-------
n = Range("Bank4").Value
If n < 1 Then n = 1
base4 = 256
If n > 19 Then Exit Sub
strName = Trim(Str(base4 + n) )
ActiveSheet.Shapes(strName).Select
Selection.ShapeRange.ZOrder msoSendToBack
Range("Bank4").Select
End Sub
Private Sub spBank4_SpinUp()
Dim n As Integer
Dim base4 As Integer
Dim strName As String
setChanged
n = Range("Bank4").Value
base4 = 255
If n > 20 Then Exit Sub
strName = Trim(Str(base4 + n) )
ActiveSheet.Shapes(strName).Select
Selection.ShapeRange.ZOrder msoBringToFront
Range("Bank4").Select
End Sub
The flow rate calls begin here. We first determine which spinner or flow rate was changed then
recalculate accordingly.
Private Sub spConcentrateFlow_Change()
If tempStop Then Exit Sub
tempStop = True
Range("ProductFlow").Value = Range("FeedFlow").Value - Range("ConcentrateFlow").Value
Range("SystemRecovery").Value = (Range("ProductFlow").Value / Range("FeedFlow").Value) * 100
Range("InternalRecovery").Value = CInt((Range("ProductFlow").Value /
(Range("RecycleFlow").Value + Range("FeedFlow").Value)) * 100)
tempStop = False
setChanged
End Sub
Private Sub spFeedFlow_Change()
If tempStop Then Exit Sub
UpdateFlows ENTER_FEED
End Sub
Private Sub spInternalRecovery_Change()
If tempStop Then Exit Sub
UpdateFlows ENTER_INTRECOVERY
End Sub
Private Sub spProductFlow_Change()
If tempStop Then Exit Sub
UpdateFlows ENTER_PRODUCT
End Sub
Private Sub spRecycleFlow_Change()
If tempStop Then Exit Sub
UpdateFlows ENTER_RECYCLE
End Sub
Private Sub spSystemRecovery_Change()
If tempStop Then Exit Sub
UpdateFlows ENTER_SYSRECOVERY
End Sub
Private Sub Worksheet_Activate()
Dim n As Integer
If Worksheets("System Design").Range("FileOpened").Value = "False" And Not tempStop Then
LoadTypeList
81
-------
tempStop = True
IstMembraneType.Listlndex = Range("Typelndex").Value
GetMembraneList Range("Typelndex").Value + 1
n = CInt(Range("Membranelndex").Value)
Worksheets("System Design").IstSelectMembrane.Listlndex = n
tempStop = False
Worksheets("System Design").Range("FileOpened").Value = "True"
Elself Not tempStop And IstMembraneType.ListCount <> Worksheets("Membrane
Data").Range("NumberTypes").Value + 1 Then
LoadTypeList
GetMembraneList 1
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim nCount As Integer
nCount = Range("nCount").Value
If nCount = 1 Then Exit Sub
If lblRecalc.Visible Then
Exit Sub
Else
msgRecalc.Visible = True
lblRecalc.Visible = True
'MsgBox ("Data has changed - You will need to redo the calculations")
Range("Ncount").Value = 1
End If
End Sub
Private Sub setChangedO
Range("nCount").Value = 0
Worksheets("Summary").Range("SystemCalculated").Value = "Needs Recalc"
Worksheets("Streams").Range("SystemCalculatedl").Value = "Needs Recalc"
End Sub
Flow rates are calculated here depending on entry value. nEntering is one of the constants defined at the
start of the module.
Sub UpdateFlows(nEntering As Integer)
Dim fFeedTDS As Double
Dim fnetFeedTDS As Double
Dim fConcTDS As Double
Dim fFeed As Double
Dim fProduct As Double
Dim fConc As Double
Dim fRecycle As Double
Dim fSysRecov As Double
Dim flntRecov As Double
Dim fNetFeed As Double
fFeed = Range("Feedflow").Value
fProduct = Range("productflow").Value
fConc = Range("ConcentrateFlow").Value
fRecycle = Range("Recycleflow").Value
fSysRecov = Range("SystemRecovery").Value / 100
flntRecov = Range("InternalRecovery").Value / 100
fNetFeed = fFeed + fRecycle
Select Case nEntering
Case ENTER_UNITS
' only changed units and now have a new feed value to work from
' recoveries unchanged, recalc flows from new feed
fProduct = fFeed * fSysRecov
fConc = fFeed - fProduct
If Abs(flntRecov - fSysRecov) <= 0.001 Or flntRecov = fSysRecov Then
fRecycle = 0#
Else
fRecycle = (fProduct - flntRecov * fFeed) / flntRecov
End If
fNetFeed = fFeed + fRecycle
82
-------
Case ENTER_FEED
' recovery is unchanged, flows to match recovery
fProduct = fFeed * fSysRecov
fConc = fFeed - fProduct
fNetFeed = fFeed + fRecycle
flntRecov = fProduct / fNetFeed
Case ENTER_PRODUCT
' product flow has changed, Hold recovery constant, change the feed as needed, frecycle
unchanged
fFeed = fProduct / fSysRecov
fNetFeed = fFeed + fRecycle
flntRecov = fProduct / fNetFeed
fConc = fFeed - fProduct
Case ENTER_CONC
' changed the concentrate, modify the recovery as needed, other flows unchanged
' but first check for max recovery - 95%
If fConc / fFeed < 0.05 Then fConc = 0.05 * fFeed
' now catch the case where concentrate is too high
If fConc / fFeed > 0.95 Then fConc = 0.95 * fFeed
fSysRecov = 1 - fConc / fFeed
Case ENTER_RECYCLE
' recycle flow is changed
fNetFeed = fFeed + fRecycle
flntRecov = fProduct / fNetFeed
Case ENTER_SYSRECOVERY
' hold the permeate flow constant
fFeed = fProduct / fSysRecov
fConc = fFeed - fProduct
fNetFeed = fFeed + fRecycle
flntRecov = fProduct / fNetFeed
If Abs(flntRecov - fSysRecov) <= 0.0001 Or flntRecov = fSysRecov Then
flntRecov = fSysRecov
fRecycle = 0#
fNetFeed = fFeed
End If
Case ENTER_INTRECOVERY
If Abs(flntRecov - fSysRecov) <= 0.001 Or flntRecov = fSysRecov Then
fRecycle = 0#
flntRecov = fSysRecov
' internal recovery cannot be greater than the system recovery
Elself fSysRecov < flntRecov Then
flntRecov = fSysRecov
fRecycle = 0#
fNetFeed = fFeed
Else
fRecycle = (fProduct - flntRecov * fFeed) / flntRecov
fNetFeed = fFeed + fRecycle
End If
End Select
' now fill in the data and edit boxes
tempStop = True
Range("Feedflow").Value = fFeed
' txtFeed.Text = SetFormat(fFeed)
Range("productflow").Value = fProduct
'txtProduct.Text = SetFormat(fProduct)
Range("ConcentrateFlow").Value = fConc
'txtConc.Text = SetFormat(fConc)
Range("Recycleflow").Value = fRecycle
'txtRecycle.Text = SetFormat(fRecycle)
Range("SystemRecovery").Value = fSysRecov * 100
'txtSysRecovery.Text = SetFormat(fSysRecov * 100)
Range("InternalRecovery").Value = flntRecov * 100
'txtlntRecovery.Text = SetFormat(flntRecov * 100)
lblNetFeed.Caption = "Net = " & SetFormat(fFeed + fRecycle)
fFeedTDS = Worksheets("Analysis").Range("TDS_Treated").Value
83
-------
fConcTDS = (fFeedTDS - 0.01 * fFeedTDS * fSysRecov) / (1 - fSysRecov)
If fRecycle > 0 Then
fnetFeedTDS = (fFeedTDS * fFeed + fConcTDS * fRecycle) / (fFeed + fRecycle)
Else
fnetFeedTDS = fFeedTDS
End If
Range("ProductEstimated").Value = 0.01 * (fnetFeedTDS + fConcTDS) / 2
Range("Net_Feed_Estimated").Value = fnetFeedTDS
setChanged
tempStop = False
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
' need this to update if only a small change to recovery is made
Dim nRow As Integer, nCol As Integer
nRow = Target. Row
nCol = Target.Column
If nRow = Range("SystemRecovery").Row And nCol = Range("SystemRecovery").Column Then
UpdateFlows ENTER_SYSRECOVERY
Elself nRow = Range("InternalRecovery").Row And nCol = Range("InternalRecovery").Column Then
UpdateFlows ENTER_INTRECOVERY
End If
End Sub
84
------- |