Pool Water Chemistry (Warning: Can Get Technical)

[chem geek]

This data is very interesting. Thank you for doing this research.

I downloaded the Excel spreadsheet and began playing with it. Only problem is when I click on the "Calculate pH/TA" button, I get an error message "Compile Error - Can't find project or library". When I click OK, it takes me to the debugger. It highlights "SolverReset" in the "Sub Calculate_pH_TA_FC_CYA()" section of Module1. Any idea what the problem is on my end?

Thanks,
Jim

Jim,

You might have downloaded the spreadsheet before I edited my post to give the following instruction:

The “Pool Equations” spreadsheet uses macros to compute the reverse computations (calculating pH and alkalinity when you add chemicals) so to get this to work on a PC using an older version of Excel you will need to go into your “Tools – Add-Ins...” menu in Excel and check the box for “Solver Add-In”.

The error you are getting sounds like the Solver toolpak is not installed so try adding it in as I describe above and let me know what happens.

Richard

[jereece]

I am using Office 2003, the latest version. I have all the addins turned on, including "Solver Add-In" and still get this error.

[chem geek]

I am using Office 2003, the latest version. I have all the addins turned on, including "Solver Add-In" and still get this error.

I'm sorry you are having trouble getting it to work. I created it on a Mac in Microsoft Excel 2004 for Mac Version 11.2.5 (060620) and I also tried it on a PC that has Microsoft Excel 2000 (9.0.6926 SP-3) running Windows 2000 Professional. It worked for me on the PC though I had to add the Solver Add-In and I also had to change the security level to Medium and accept the warning about macros running.

[EDIT]I have been very frustrated with trying to get Solver to work consistently when running from a macro on the PC. So, I'm going to delete my later posts that tried to explain workarounds since they are a pain and may not work. Instead, I'm going to try to rewrite the macro to do the Solver function myself. I've only got two "goals" with two "variables" so that shouldn't be too hard to do with a 2-variable partial differentiation (Newton-Raphson) approach. I hope that doesn't end up being as hard as it sounds![END-EDIT]

[SECOND-EDIT] Since around 8/23/06 (see later posts) Solver is no longer used. [END-EDIT]

Richard

[chem geek]

I updated the PoolEquations.zip spreadsheet as well as the graphs and charts in the first post in this thread (and the links) to reflect the fact that FC (and CC and TC) are measure in ppm of Cl2 equivalent and not as HOCl. I also changed the HOCl concentration to also be in ppm of Cl2 equivalent so all chlorine is measured in the same way and this made the changes to everything rather minor.

I also added the Borax and Borates (ppm Boron) to the spreadsheet, though I need to do some cleanup to make it easier to use. I still need to replace the Solver calculation with my own iteration so that the spreadsheet will work reliably on a PC (it works fine on my Mac).

[EDIT] I made an initial error and have now corrected it so be sure that the first line of the spreadsheet not only gives the date 8/15/06, but also has the word "CORRECTED2" after this date. [END-EDIT]

Richard

[ubalr1]

I updated the PoolEquations.zip spreadsheet as well as the graphs and charts in the first post in this thread (and the links) to reflect the fact that FC (and CC and TC) are measure in ppm of Cl2 equivalent and not as HOCl. I also changed the HOCl concentration to also be in ppm of Cl2 equivalent so all chlorine is measured in the same way and this made the changes to everything rather minor.

I also added the Borax and Borates (ppm Boron) to the spreadsheet, though I need to do some cleanup to make it easier to use. I still need to replace the Solver calculation with my own iteration so that the spreadsheet will work reliably on a PC (it works fine on my Mac).

[EDIT] I made an initial error and have now corrected it so be sure that the first line of the spreadsheet not only gives the date 8/15/06, but also has the word "CORRECTED2" after this date. [END-EDIT]

Richard

Darn! The previous version was working fine for me (PC). Now this version gives me the "solver error". I must have installed it wrong, or did not remove the first one correctly. Any other issues reported with this new version.

Double darn!! The original version is no longer available. I can't even go back to that one. I'm drifting aimlessly now, I was getting very attached to that 'working' spreadsheet.

[chem geek]

ubalr1,

I'm sorry you now get the Solver error. I assume this only occurs when clicking on the "Calculate pH/TA" button, right? This has been reported as very flaky on a PC and I was able to reproduce the problem on a PC and it sometimes would work and then it wouldn't, so I don't think it's only the version change but something very strange with PC's solver getting a spreadsheet created on the Mac.

I plan to replace the use of Solver, but am going on vacation (to Canada) for 2-1/2 weeks. I'm taking my computer and one of the things I'll do during down time (on the train, for example) is work on the spreadsheet. Yes, I consider that fun (shows how geeky I am).

I don't have the earlier version myself anymore. As soon as I replace the Solver with my own iterative code, I'll post it and let everyone know by a post to this thread. Again, sorry you got "hooked" with a working version. It's pretty neat when it works, eh?

I just put up the latest version just in case that one works for you. You can download it and try it out.

Richard

[chem geek]

ubalr1 and others,

I just removed the Solver dependence and uploaded a new (modified) spreadsheet. Give it a try and let me know if it works. Unfortunately, it will still "think" that Solver is being used when you load the spreadsheet so it will give you two macro warnings and may require you to add the Solver Add-In, but it won't actually use the Solver itself (which was the main problem).

[EDIT] As of around 8/30/06 I removed the apparent reference link to Solver that made Excel think that Solver was used even if it wasn't. [END-EDIT]

Richard

[ubalr1]

ubalr1 and others,

I just removed the Solver dependence and uploaded a new (modified) spreadsheet. Give it a try and let me know if it works. Unfortunately, it will still "think" that Solver is being used when you load the spreadsheet so it will give you two macro warnings and may require you to add the Solver Add-In, but it won't actually use the Solver itself (which was the main problem).

Richard

Hope you had a fine vacation Richard.

The speadsheet is working fine for me now. I had to uncheck the Solver in "Add-Ins". Thanks.

Couple of questions:
1) Can I change the concentration of Sodium Hypo? You have 11.9% in there. I use the 6% bleach.
2) I don't know what my Total Sulfates or Total Borates are. Can I still get accurate results using the spreadsheet?

[chem geek]

Hope you had a fine vacation Richard.

The speadsheet is working fine for me now. I had to uncheck the Solver in "Add-Ins". Thanks.

Couple of questions:
1) Can I change the concentration of Sodium Hypo? You have 11.9% in there. I use the 6% bleach.
2) I don't know what my Total Sulfates or Total Borates are. Can I still get accurate results using the spreadsheet?

I finally figured out how to remove Excel's thinking that there were references to Solver (when there weren't any more) so will update the spreadsheet with that fixed soon [EDIT] just uploaded an update today 8/27/06 with the Solver references removed [END-EDIT] [EDIT] and again today 8/28/06 with further improvements listing different concentrations for bleach in a pull-down menu. [END-EDIT]

Yes, you can get accurate results without the Total Sulfates or Borates entered, unless you have intentionally added lots of Borates (say, more than 30 ppm). The sulfates are there in case one has very high sulfate water (> 100 ppm) in which case this starts to affect the calcium carbonate water balance.

Richard

[fcfrey]

Richard,
I've been following this thread for quite a while and am wondering what is the latest date (version) on your spreadsheet. I've downloaded it several times but I'm not sure I have the latest.

I have printed out all the charts and graphs so I can study them --- my old brain take awhile sometimes.

I really appreciate the time you have put into this.