Using Excel's Solver and VBA For Repetitive Table Calculation

Quote of the Day

A belief is not merely an idea the mind possesses; it is an idea that possesses the mind.

— Robert Oxton Bolt, screenwriter and playwright

---

Introduction

I will be providing some employee training on Excel in January, and I need an example of how to automate the use of Excel's Solver add-in – a powerful optimization tool that few engineers use effectively. When I give a training seminar, I make a serious effort to show how I use Excel on real problems. While I generally use Mathcad for most optimization applications, Mathcad does not support integer programming – an optimization method where some or all variables are restricted to be integers. Here is where Solver shines – it supports integer programming.

While reading about how to size footings for decks, I used the table in Figure 1 to determine my footing sizes. To ensure that I understood the physics behind the table, I decided to use Excel and Solver to generate it on my own. In this post, I will be using Excel's VBA with Solver to generate the table of footing sizes for given deck tributary areas shown in Figure 1.

I am going to apologize right from the start for using US customary units – the table is in square feet and inches. Adding metric conversion would have complicated a relatively simple problem.

Here is my source for those who are interested.

Note: I am not a structural engineer. I am only using some basic math to generate a table of values. If you have a structural question, hire a structural engineer. If you have an electrical engineering question, you can give me a try.

Background

Caveats

• There are many valid ways to fill in this table.
• I do not know what optimization criteria was used by the standards body that created the table.
• My objective is to minimize the amount of concrete required while meeting the code requirements (described below).
• My results are very close to those in Figure 1, but not exactly the same.

Assumptions

There are some basic assumptions that one needs to keep in mind when computing footing sizes.

• The table says that decks must support a live load of 40 lbf per square foot (psf) and a dead load of 10 psf, for a total deck load of 50 psf. This agrees with the building codes in my area.
• Each footing is assumed to have a certain area of deck it is assigned to supporting that is called the tributary area.
• The soil must support the weight of the deck tributary area and the weight of the concrete footing. The weight of the concrete footing is proportional to its volume. For this exercise, I am only interested in round footings. The table in Figure 1 includes values for square footings. I don't use square footings, so I am not interested in these values.
• I assume a concrete density of 150 lbf per cubic foot (pcf).
• Different soils can support different amounts of weight per square foot. In the table of Figure 1, the assumed soil loading values are 1500 psf, 2000 psf, 2500 psf, and 3000 psf.
• I am assuming that the footing thickness is restricted to integer values of 6 or greater inches.
• The concrete footing thickness (τ inches) is related to the diameter of the pad (D inches) by formula , where τ and D are expressed in inches.
• I then use Solver to compute the minimum required footing diameter.

While my focus in this post is on the use of Solver, you can solve this problem using Mathcad – just no integer programming. See Appendix A for the details.

Analysis

Formula Derivation

Equation 1 is the key formula for this post.

Eq. 1

where

• A_Footing is the area of the footing.
• A_Deck is the tributary area of the deck being served by the footing.
• σ_Deck is deck loading factor required by the applicable building code.
• σ_Soil is soil loading factor required by the applicable building code.
• ρ_Concrete is density of the concrete.
• τ is the thickness of the footing. The footing thickness must conform to the constraint , where τ and D are expressed in inches. The thickness of a footing can never be less than 6 inches.

I derive this formula in Figure 2 (see the yellow highlight).

Excel VBA

The following VBA code snippet shows my VBA routine. I generated this routine by using the macro recorder to provide me clues on how to invoke the Solver routine.

Sub Deck_Table_Optimizer()

 Dim deckArea As Range
 Set deckArea = Range("_biff")

 Dim rngObjectCell As Range
 For Each rngObjectCell In deckArea
 SolverReset 'Clear out all Solver settings
 Range("_A").Value = rngObjectCell.Value 'This is the footing thickness we will be varying
 SolverOk SetCell:="_V", MaxMinVal:=2, ValueOf:=0, ByChange:="_t", Engine:=1, EngineDesc:="GRG Nonlinear"
 SolverAdd CellRef:="_t", Relation:=4, FormulaText:="integer" 'I want integer footing thickness
 SolverAdd CellRef:="_A", Relation:=2, FormulaText:=CStr(rngObjectCell.Value) 'Allowed soil pressure
 SolverAdd CellRef:="_t", Relation:=3, FormulaText:="6" 'The footing thickness greater than 6 inches.
 SolverAdd CellRef:="_t", Relation:=3, FormulaText:="_constraint"
 SolverSolve True
 rngObjectCell.Offset(0, 1).Value = Round(Range("_D").Value + 0.45, 0)
 rngObjectCell.Offset(0, 2).Value = Range("_t").Value
 Next

End Sub

Figure 3 shows the equivalent Solver dialog for this routine. Here is my key for the variables:

• _V is the concrete volume, which we will minimize.
• _t is the footing thickness
• _A is the deck area
• _D is the diameter of the footing

Results

Figure 4 shows my Solver results. The number in red indicate where my results differ from the corresponding results in Figure 1. Both Figure 1 and Figure 4 are "correct" in that they meet the requirements – I assume the optimization criteria was different for Figure 1. In fact, Figure 1 may have been generated without an optimization criteria at all.

Conclusion

In my seminar next month, I will be presenting this exercise as an example of how to use Solver and VBA to solve a repetitive nonlinear optimization problem. It surprised me how easy it was to use Solver for this type of application.

Appendix A: Mathcad Solution.

While Mathcad does not provide an integer programming feature, you can solve this problem using Mathcad. Let it solve the problem using non-integers and then round up. It may not be optimal, but it will be close. Figure 5 show my Mathcad algorithm.

Figure 6 show the final result with Figure 1 right beside.

Save

Save

Save

Save

Save

Save

Save

Save