A thought experiment to test the general validity of available mash pH software

[Big Monk]

@ajdelange I'm in the process of compiling my cell formulas and VBA code into a .Dll library. The library is then referenced as an Excel Add-in and can be called from any version of Excel. The compiled native Windows byte code is very fast and its functions can be entered into cells or called from VBA. You may find this approach interesting.

Im now officially out of my element!

 

[ajdelange]

Yeah, me too - but I've ordered a book! I just realized that .Value is a property and .Formula is a method which makes Range an object. They had me fooled for about a week. We're in the middle of the arcana of OOP here - a world where I have never gone before (I cut my programming eye teeth on FORTRAN).

But I do think I understand what Vince is talking about and it sounds like what I had in mind.

For the moment I'm stuck. If I gin up a water profile on the water page and send it to the malt page everything goes smoothly except that the function that returns the pH estimate returns #Value! Double clicking on it at hitting return causes it to evaluate properly. Pressing a button on the malt page which tells the program to calculate that page causes it to evaluate. Sending the same commands from the button on the water page does not cause it to evaluate properly. So I'm trying to come up with a fix for that.

 

[SteveH aka shetc]

Bill Gates thanks you

 

[ScrewyBrewer]

For the moment I'm stuck. If I gin up a water profile on the water page and send it to the malt page everything goes smoothly except that the function that returns the pH estimate returns #Value! Double clicking on it at hitting return causes it to evaluate properly. Pressing a button on the malt page which tells the program to calculate that page causes it to evaluate. Sending the same commands from the button on the water page does not cause it to evaluate properly. So I'm trying to come up with a fix for that.

AJ if you can send me an example of the issue I will help debug it for you.

 

[schematix]

AJ -

This is a shot in the dark but sometimes I have found with macros there is odd behavior due to the order of execution when a sheet recalculates.

Try adding:
Worksheets("WorksheetName").Calculate

At the end of the macro


Some other useful functions, although not necessarily applicable here:

Application.ScreenUpdating = False (at beginning of macro)
Application.ScreenUpdating = True (at end of macro)
ThisWorkbook.RefreshAll (at end of macro)

 

[ScrewyBrewer]

Some other useful functions, although not necessarily applicable here:

Application.ScreenUpdating = False (at beginning of macro)
Application.ScreenUpdating = True (at end of macro)
ThisWorkbook.RefreshAll (at end of macro)

These are helpful too...

Sheets("myRecipes").Visible = False
Application.Calculation = xlCalculationManual
Worksheets("ezRecipe").Select

...do some work here....

Worksheets("ezRecipe").Select
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

 

[ajdelange]

Bill Gates thanks you

I do want to point out that I did not order the book from Microsoft nor did they get another nickle out of me for the new Excel. I had apparently subscribed earlier and that came with 5 licenses which I did not realize. So I just used one of those!

 

[ajdelange]

AJ -

This is a shot in the dark but sometimes I have found with macros there is odd behavior due to the order of execution when a sheet recalculates.

Brother, you can say that again!

What does one do when he is lost in the woods? Turn to the internet where I found posts from lots of similarly frustrated folks. There were dozens of posts

A: I can't get this to work
B: Oh, piece of cake! Try this...
A: Nope doesn't work!
C: B is full of it. Try this..
A: Nope, doesn't work!

Eventually I stumbled on

Worksheets("Sheet1").Activate
Sheets("Sheet1").Calculate

and that works (but will it work on days of the week that don't start with T?)

So those of you waiting it's ready for your review.

Thanks to both of you for the tips!

 

[schematix]

Worksheets("Sheet1").Activate
Sheets("Sheet1").Calculate

and that works (but will it work on days of the week that don't start with T?)

The functions work on any day ending in -ay as long as you don't rename your sheets. When you do that you'll get a really confusing Subscript Out of Range error.

I have found google is the best programming resource out there for any language. Everything has been done before. Everyone has encountered the same problems. The solutions are all out there and can be found quickly if you know how to craft the right search terms.

I think sometimes people mistake my googling skills for actual intelligence.

 

[ajdelange]

I just sent it out - as a binary (.xlsb), hope that's OK. @mrmekon need your e-mail address.

 

[ScrewyBrewer]

I just sent it out - as a binary (.xlsb), hope that's OK. @mrmekon need your e-mail address.

AJ please email me a copy as well. Thank you.

 

[ajdelange]

Don't know how you fell off the list but you are definitely back on and a copy is on the way. In fact a copy is on the way to everyone as I was trying it out on the German water report problem (fantastic example as you get to use mixed units - dH, mg/L, mEq/L) and found two errors.

 

[pricelessbrewing]

I'm super excited about this.

Once I have some free time, I'll definitely be working on a port to JS / JSON for a web app, and including it in my software if I ever end up releasing it. If anyone is interested in following along, contributing etc, send me a PM and when I get to it I'll send you a link to the github repo.

 

[ajdelange]

Everyone who has asked for a copy of the voltmeter/trouble shooter standby as a new version is on the way. Anyone who doesn't get a e-mail soon who requested a copy let me know. The new version fixes a problem with the malt data base (all the buffering values calculated from Kai's measurements were wrong). I also extended the size of the malt data base down to line 70 so there is more space to fill in new malt data as you get it or invent it or whatever. I've used the empty space under the curve to display the parameters for the malts selected from the data base and, more significantly, to display the influence of each malt selection on the mash pH.

Finally, I put the sheet tabs in order and added a fourth sheet Henderson. It has been noted that the project has lost some of its original "voltmeter" quality as the calculations that were line by line visible in the original (no. 144) are now hidden in VBA functions. The Henderson sheet has restored those for one acid and one malt. You can now see exactly what goes on with the ion ratios, species fractions and charges as you vary pKs or malt parameters and mash pH. There is a pretty large text box there too which explains in some detail how acid and malt data can be put together to estimate and control mash pH. Understand what that text box is telling you by playing around with numbers and I think you will be able to understand how the more complicated stuff works. It's just an extension of the principle illustrated on the Henderson sheet.

 

[ajdelange]

Once I have some free time, I'll definitely be working on a port to JS / JSON for a web app, and including it in my software if I ever end up releasing it. If anyone is interested in following along, contributing etc, send me a PM and when I get to it I'll send you a link to the github repo.

Just so everyone understands my level of programming sophistication: I had to look up those terms on the net to figure out what he is talking about.

 

[ScrewyBrewer]

I'm super excited about this.

Once I have some free time, I'll definitely be working on a port to JS / JSON for a web app, and including it in my software if I ever end up releasing it. If anyone is interested in following along, contributing etc, send me a PM and when I get to it I'll send you a link to the github repo.

I'm still busy unpacking everything AJ has provided up to now and learning how it works. My research on compiling VBA code into an Excel Add-in is coming along nicely. The approach let's you save a spreadsheet as an .xlsx file but still use the new VBA functions. Very interesting stuff.

 

[ajdelange]

BTW, you all may be interested in my latest paper "A Homebrewing Perspective on Mash pH III: Distilled-Water pH and Buffering Capacity of the Grist", which can also be found on my blog. The paper includes new data on a number of previously unmeasured malts. It also summarizes all the data I could find on the web. Happy reading!

Cheers!

@dmr: Please check your private messages. I have commented on both papers and gotten no response.

 

[dmr]

@dmr: Please check your private messages. I have commented on both papers and gotten no response.

Apologies for taking so long to get back to you. I have responded to both of your messages. Thanks again!

 

[Big Monk]

Reviving an old thread.

I've revised my version of A.J.'s original troubleshooter and reformatted it. It is a good tool that uses the Proton Deficit/Charge Conservation Model (Gen II).

Link is in my signature.

 

[Silver_Is_Money]

Are you going to repeat your multiple recipe scenario comparisons of the most typically available mash pH software packages again. That was most helpful the last time around.

 

[Big Monk]

Are you going to repeat your multiple recipe scenario comparisons of the most typically available mash pH software packages again. That was most helpful the last time around.

Nope. It’s the same engine that powers all my other stuff. I just eliminated all the non essentials items.

 

[ScrewyBrewer]

Over the past several months I've been posting the results of recipes I've brewed and their actual mash pH values in the thread below.
Brewing Tomorrow - Need Advice On Grain DIpH. I'd like to see others post their recipes and results when using their mash pH software too.