Pie charts all the way down

Posted on 07/02/2010 by dougaj4

xkcd post number 688 shows charts of the proportion of black ink in the charts in xkcd post number 688, and it has in the chart on the right a plans of the locations of the charts in xkcd post number 688, which  has in the chart on the right a plans of the locations of the charts in xkcd post number 688, and so on …

Self description (click to visit xkcd)

And for the benefit of those without infinite resolution monitors:

Pie charts all the way down (click for full size)

 Resources for those who don’t get xkcd:

http://xkcdexplained.com/

and

http://xkcdexplainedexplained.com/

and

http://xkcdexplainedexplainedexplained.tumblr.com/

and so on …

Posted in Maths, Newton | Tagged , , | 1 Comment

Hello World (again)

Posted on 02/02/2010 by dougaj4

Over at Daily Dose of Excel Dick has been asking for suggestions for excellent training, to which Jan Karel Pietersen replied that training should be relevant to the task at hand, and that “hello world” examples are useless.

John Walkenbach responded with the mysterious code below:

Sub Test()
Dim x As Long, y As Long
Dim x1 As String, x2 As String
For x = 0 To 5
x1 = x1 & Chr(x * (x * (x * (x * (-0.75 * x + 7.2917) - 22.5) + 16.708) + 28.25) + 72)
Next x
For x = 0 To 6
x2 = x2 & Chr(x * (x * (x * (x * (x * (0.425 * x - 6.8667) + 40.833) - 109.58) + 122.24) - 23.05) + 87)
Next x
MsgBox x1 & x2
End Sub

Paste it into the Visual Basic Editor and run it, and all will be revealed.

I have modified this valuable routine to work as a function that will accept a spreadsheet range of six columns by any number of rows as input, with the results shown below:

Messages from numbers

The code for the function is:
Function TextVal(CharVals As Variant) As String
Dim i As Long, x As Long, NumRows As Long
CharVals = CharVals.Value2
NumRows = UBound(CharVals)
For i = 1 To NumRows
For x = 0 To 5
TextVal = TextVal & Chr(x * (x * (x * (x * (CharVals(i, 6) * x + CharVals(i, 5)) + CharVals(i, 4)) + CharVals(i, 3)) + CharVals(i, 2)) + CharVals(i, 1))
Next x
Next i
End Function

The spreadsheet can be downloaded from Hworld-JW.xls

Those interested in how the numbers are derived, have a look at the second page of the spreadsheet (warning; some mathematics required).  The hint was given by JKP in this comment:

“John:
Yes nice one, isn’t it.
Proves you can fit a 5th order polynomial through six points exactly.”

And just in case fitting a 5th order polynomial to a string of Ascii text is not enough for you, I have now modified the code so it will handle the extended character set, so we can get:

The download spreadsheet now also includes this short UDF:

Function Charw(CVal As Long)
Charw = ChrW(CVal)
End Function

which gives access to the VBA Chrw function from a worksheet.

That could conceivably be of some practical use to someone 🙂

Posted in Excel, Maths, UDFs, VBA | Tagged , , , , | 2 Comments

She Moved Through the Fair

Posted on 27/01/2010 by dougaj4

“She Moved Through the Fair” (or “She Moves Through the Fair”) is a traditional Irish folk song.

Here are three very different versions.

The first, simple perfection, unaccompanied from Anne Briggs

The second an orchestrated version from Harpist Loreena McKennitt, with a hint of Eastern overtones

And finally guitarist Davy Graham’s instrumental version takes us to the Bazarr and back. 

Posted in Bach | Tagged , , , | 1 Comment

Reinforced Concrete Moment-Curvature – 3; Restrained Shrinkage and Creep

Posted on 23/01/2010 by dougaj4

The previous post in this series provided Excel User Defined Functions (UDFs) to find the strain in a concrete section due to unrestrained creep and shrinkage.  In this post the effect of restraint of the creep and shrinkage strains by the section reinforcing steel will be discussed, and in the following post a UDF will be presented that allows the stresses and strains in the section to be calculated allowing for creep and shrinkage strains and variation in the applied load and prestress over time.

Both shrinkage and creep result in a shrinkage strain in the concrete, with no change in stress for an unrestrained section, but for a reinforced section the reinforcement partially restrains the concrete strain, resulting in a transfer of compressive stress from the concrete to the reinforcement for zones in compression, or an increase in the tensile stress in the concrete for uncracked zones in tension.

A simple way to visualise the effect of shrinkage on the strains and stresses in a reinforced section is to apply an imaginary external compressive axial force to the reinforcement, so that it follows the unrestrained shrinkage strain of the concrete, with no change to the concrete stress.  If the external force is then removed the state of stress and strain in the section will be the same as for a section that has undergone shrinkage with no change to the external loads (except for the effects of creep, which will be discussed later).  This analysis is illustrated below:

Before Shrinkage

Concrete shrinkage + external load to reinforcement

Remove external load to reinforcement

Apply bending below cracking moment

Apply bending greater than cracking moment

Analysis with “negative” prestress; click to view full size

Note that:

  • For an uncracked section with symmetrical reinforcement the shrinkage strain does not affect the curvature of the section.
  • For a cracked section there is a significant change in curvature due to shrinkage strains, even for symmetrical reinforcement.
  • The restraint of the shrinkage strain causes a significant increase in the tensile stress in the concrete, which results in a reduction of the cracking moment.
  • The effect of shrinkage may be analysed by applying a compressive prestress to the reinforcement, equal to the free concrete strain multiplied by the reinforcement elastic modulus.
  • The change in stress in the concrete due to shrinkage is reduced by the effects of creep.  Analysis of this effect is discussed below.

Creep is the increase over time of strain due to applied load.  Creep typically increases the strain in the concrete by a factor of the order of 3, and must therefore be considered in assessment of the long term behaviour of concrete structures.  The reinforcing steel is also subject to creep strains, but at typical reinforcement stresses the effects are negligible (except for prestressed reinforcement, where the relaxation of prestress due to creep must be considered).  Since shrinkage results in a change of stress in the concrete, this interacts with creep effects.

The effect of creep strains could in principle be analysed in the same way as shrinkage, by the application of a virtual prestress to the reinforcement, but since the magnitude of the creep strain is proportional to the stress in the concrete, the magnitude of the virtual prestress is dependent on the stress in the concrete after completion of creep, so an iterative procedure would be required to find the correct level of prestress.  A simpler approach is to use an “age adjusted modulus” for the concrete, so that the analysed strain in the concrete includes both elastic and creep strains.  For a creep coefficient of Ø the age adjusted modulus is given by Et / (1+ Ø), where Et is the elastic modulus of the concrete at the time of loading.

Having found the concrete stresses after creep using the age adjusted modulus method, it is possible to find the equivalent virtual prestress that will result in the same stresses and curvature in the section.  The virtual prestress for creep is given by Es ec Ø / (1+ Ø), where Es is the elastic modulus of the reinforcing steel, and ec is the total strain in the concrete at the level of the reinforcement, as determined by the age adjusted modulus method.  This prestress is then applied in conjunction with the unreduced concrete modulus, giving exactly the same end results for stresses in the concrete and reinforcement, and strains in the reinforcement.  Examples of these methods of calculating creep strains are shown below:

Age adjusted modulus method; click to view full size

Virtual Prestress Method; click to view full size

To combine creep and shrinkage effects, a simple method that is satisfactory for many purposes is to use the age adjusted modulus method for creep, and the virtual prestress method for shrinkage.  Whilst this method will usually give a result within the order of accuracy of standard materials properties, it does introduce several avoidable inaccuracies:

  • It does not take account of the variation of creep stresses over time.
  • It does not take account of the interaction between shrinkage stresses and creep strains.
  • If shrinkage results in the section transitioning from an uncracked to a cracked state at some time after initial loading, the initial stress and strain conditions will be very different to those assumed, and this may introduce significant inaccuracies.
  • If the loading and/or prestress applied to the section changes significantly over time it is not possible to take account of these changes in the simple analysis.

All of these factors can be included in a time step analysis as described below:

  1. Set up a table of logarithmic time steps, with shrinkage strain and creep factor for each time step.  Optionally add changes to applied loads, and changes to prestress loads.
  2. Analyse the strains and stresses in the section at the end of the first time step, using the virtual prestress method for shrinkage and the age adjusted modulus method for creep.
  3. For each succeeding time step add creep strains from the previous stages to the cumulative shrinkage strain for calculation of the virtual prestress.  Find the creep strain increment using the age adjusted modulus, using the increment in the Ø value, and adjust applied loads and applied prestress where applicable.

A spreadsheet performing this analysis, with examples based on real projects, will be included in the next post in this series.

Posted in Concrete, Newton | Tagged , , , , | 7 Comments

Good practice, best practice, or just practice?

Posted on 15/01/2010 by dougaj4

Browsing the ‘net this morning I found the “Golden Rules” listed below at Ozgrid.com

The rules are described as things that “should not be optional”, which seemed to me in some cases to be going way too far, since for many of them their use or not really depends on what you are doing.  I’ve given my comments below.  If anyone else wants to chip in, please do 🙂

VBA CODE GOLDEN RULES
1.  Turn on Option Explicit. Tools>Options – Editor – Require variable declaration in the VBE. Then Educate Yourself on Their Proper Use and Data Types.

This one I agree; I can’t think of any occasion when Option Explicit is not worth doing.  I would add always declare the variable type when you dimension a variable (Option Explicit does not require this, but you should do it)

  

2.  Split Procedures Into Logical Separate Procedures and use Call or Run When Needed.

3.  Make Good use Of Functions so They Can be Called When Needed and With Variable Elements Passed.

I agree with both of these as well.  I would add that in many cases it is worth writing functions so that they can be used as User Defined Functions as well, so they can be called either from the spreadsheet or from another VBA routine.  This is not only useful in practice, it can be an excellent aid to checking and de-bugging.


4.  Try & Keep all Related Procedures in the Same Module. Excel Compiles Each Module as a Procedure in the Module in Run.

OK, but watch out for the 64 kB module limit.

 
5.  You Rarely Need to Select or Activate any Objects to Change Their Properties or Access Their Methods. If you MUST Select a Range Object, Use GoTo.

OK


6.  Use a Sheet CodeName Over The Tab Name or Index Number.

OK


7.  Avoid Loops. Make Good use of Much Faster Alternatives Like Find (Find Method), AutoFilter, AdvancedFilter, SpecialCells etc.

It depends what you are doing.  Consider if there is a better way of doing it, but for many purposes a loop is the obvious and right way to do what needs doing.  “Avoiding” loops strikes me as strange advice.


8.  Loops Through Object Collections are the Fastest, Compared to Other Loop Types.

I’m skeptical about this one, but if speed is critical then check and see what’s quickest.


9.  Don’t Assume Code is Needed. Often a Worksheet Function is FAR better and More Efficient.

I’d say: don’t assume a worksheet function is best, often VBA is more efficient, faster, more easily maintainable, more easily reusable, and overall FAR better.


10.  Avoid Using Custom Functions When a Built-in Worksheet Function can be Used. Even Deeply Nested Worksheet Function are Often a Lot More Efficient Than Custom Function Written in VBA.

No!  Sure there are cases where worksheet functions may be quicker, but that’s far from always the case.  I’d say avoid deeply nested worksheet functions and always consider whether the application could be done more efficiently with a UDF.


11.  Avoid Macros That Delete Rows/Column/Cells. Make Use of AutoFilter, AdvancedFilter or Sort.

OK


12.  Turn Off Calculations via Code for Slow Macros. See Macro Code via Manual Calculation. Assume Your Macro Will Fail, so Turn Back on In any Error Trapping.

OK – but also avoid writing data back to the spreadsheet as far as possible (except for the end result!), which is the prime cause of triggering recalculations which can dramatically slow down macro performance.


13.  Use VbNullString Over “”

OK


14.  Turn off Sheet/Workbook Events if They are Not Needed While Macro is Running. That is, Application.EnableEvents = False and Don’t Forget to Turn Back on and Assume Your Macro Will Fail, so Turn Back on In any Error Trapping.

OK, but efficiently written code will not be unduly slowed down by having enabled events, so I’d suggest only switch off if you need to.


15.  Make Good use of With Statements When Working With Objects.

OK


16.  Select Case is Often Better Than Mutiple If Else Statements.

OK


17. IIf is Slower Than If Else.

OK, but if you like Iif the difference would rarely be significant.


18.  Use Boolean Logic Over If Statements. E.g bYesNo = Range(“MyValue”) = 5.

Not sure about the reasoning behind this one, my suggestion would be to use what you find most readable, which for me would be an If statement.


19.  Use Named Ranges Over Cell Addresses.

OK in general, the exception being for debugging or one-of coding, where writing to a range specified by cell addresses is quicker and won’t appear in the final (or re-used) code.


20.  Use Meaningful Variable Names and Precede Them With Their Data Type. E.g lngRowCount NOT Meaningless Names Like x, y, z etc.

Certainly use (short) meaningful names in general, but I have no problem with single character names for counters for instance.  Just do what you find most efficient.  As for preceding names with their data type (or an even more elaborate code system), this for me just makes the code significantly less readable, for very little gain.


21.  Capitalize at Least 1 Character in Variable Names and When Using in a Procedure use ALL Lower Case. Excel Will Convert as To the Case Used When Dimensioned.

If this means entering code in lower case, so you can instantly see a typo (because the variable name does not become capitalised when you press enter), then I agree.  A very useful technique with no downside that I can see.

Posted in Excel, UDFs, VBA | Tagged , , | 15 Comments