A macro is a sequence of operations with keystrokes and mouse actions that may be recorded and stored for repeated use. The procedure for creating macros in Excel is most easily demonstrated with a specific example.
Example 5.7: Macro to solve f(x)-0
Create a macro to obtain the roots to f(x) = 0, where f(x) appears in cell B4 of the worksheet.
Obtain the solution using Goal Seek, iterating the values of x contained in cell B3. Once the macro is created, different functions may be entered in B4 and a solution obtained with a single click action.
The procedure is as follows:
1. Set up a worksheet for the way it will be used — in this case, for a Goal Seek solution of the function in cell B4, with variable x in cell B3. If any toolbars are
required, see that they are displayed at this time by clicking VIEW/TOOLBARS/
DRAWING, etc.
2. This step consists of the following actions:
a. Click TOOLS/MACRO/Record New Macro. The Record Macro dialog box will appear as shown in Figure 5.17. Assign a name to the macro (Marcro1, in this case). The first character of the macro name must be a letter, followed by your choice of other letters, numbers, or symbols. Spaces are not permitted, but an underscore may be used instead of a space between words.
FIGURE 5.15
=(100+B1)/2 =(A1+C1)/2 =(B1+D1)/2 =(C1+200)/2
=(100+B2)/2 =(A2+C2)/2 =(B2+D2)/2 =(C2+200)/2
=(100+B3)/2 =(A3+C3)/2 =(B3+D3)/2 =(C3+200)/2
=(100+B4)/2 =(A4+C4)/2 =(B4+D4)/2 =(C4+200)/2
=(100+B5)/2 =(A5+C5)/2 =(B5+D5)/2 =(C5+200)/2
=(100+B6)/2 =(A6+C6)/2 =(B6+D6)/2 =(C6+200)/2
=(100+B7)/2 =(A7+C7)/2 =(B7+D7)/2 =(C7+200)/2
=(100+B8)/2 =(A8+C8)/2 =(B8+D8)/2 =(C8+200)/2
=(100+B9)/2 =(A9+C9)/2 =(B9+D9)/2 =(C9+200)/2
=(100+B10)/2 =(A10+C10)/2 =(B10+D10)/2 =(C10+200)/2
=(100+B11)/2 =(A11+C11)/2 =(B11+D11)/2 =(C11+200)/2
=(100+B12)/2 =(A12+C12)/2 =(B12+D12)/2 =(C12+200)/2
=(100+B13)/2 =(A13+C13)/2 =(B13+D13)/2 =(C13+200)/2
=(100+B14)/2 =(A14+C14)/2 =(B14+D14)/2 =(C14+200)/2
=(100+B15)/2 =(A15+C15)/2 =(B15+D15)/2 =(C15+200)/2
=(100+B16)/2 =(A16+C16)/2 =(B16+D16)/2 =(C16+200)/2
=(100+B17)/2 =(A17+C17)/2 =(B17+D17)/2 =(C17+200)/2
=(100+B18)/2 =(A18+C18)/2 =(B18+D18)/2 =(C18+200)/2
=(100+B19)/2 =(A19+C19)/2 =(B19+D19)/2 =(C19+200)/2
=(100+B20)/2 =(A20+C20)/2 =(B20+D20)/2 =(C20+200)/2
=(100+B21)/2 =(A21+C21)/2 =(B21+D21)/2 =(C21+200)/2
=(100+B22)/2 =(A22+C22)/2 =(B22+D22)/2 =(C22+200)/2
=(100+B23)/2 =(A23+C23)/2 =(B23+D23)/2 =(C23+200)/2
=(100+B24)/2 =(A24+C24)/2 =(B24+D24)/2 =(C24+200)/2
=(100+B25)/2 =(A25+C25)/2 =(B25+D25)/2 =(C25+200)/2
=(100+B26)/2 =(A26+C26)/2 =(B26+D26)/2 =(C26+200)/2
=(100+B27)/2 =(A27+C27)/2 =(B27+D27)/2 =(C27+200)/2
=(100+B28)/2 =(A28+C28)/2 =(B28+D28)/2 =(C28+200)/2
=(100+B29)/2 =(A29+C29)/2 =(B29+D29)/2 =(C29+200)/2
=(100+B30)/2 =(A30+C30)/2 =(B30+D30)/2 =(C30+200)/2
=(100+B31)/2 =(A31+C31)/2 =(B31+D31)/2 =(C31+200)/2
=(100+B32)/2 =(A32+C32)/2 =(B32+D32)/2 =(C32+200)/2
=(100+B33)/2 =(A33+C33)/2 =(B33+D33)/2 =(C33+200)/2
=(100+B34)/2 =(A34+C34)/2 =(B34+D34)/2 =(C34+200)/2
=(100+B35)/2 =(A35+C35)/2 =(B35+D35)/2 =(C35+200)/2
=(100+B36)/2 =(A36+C36)/2 =(B36+D36)/2 =(C36+200)/2
=(100+B37)/2 =(A37+C37)/2 =(B37+D37)/2 =(C37+200)/2
=(100+B38)/2 =(A38+C38)/2 =(B38+D38)/2 =(C38+200)/2
=(100+B39)/2 =(A39+C39)/2 =(B39+D39)/2 =(C39+200)/2
=(100+B40)/2 =(A40+C40)/2 =(B40+D40)/2 =(C40+200)/2
Solution of Equations 115
FIGURE 5.16
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47
ABCDEFGHIJKLMNOPQRS 00002002002002003003003003001000100010001000 500010015020020020020030030025055010001000600 502550100150175200200200250275250550775800600 62.55062.5125137.5175187.5200175237.5250237.5437.5675687.5500 7562.587.5131.25137.5162.5187.5193.75168.75212.5237.5225387.5562.5587.5443.75 81.2581.2596.875143.75131.25162.5178.125193.75156.25203.125218.75218.75331.25487.5503.125393.75 90.62589.0625112.5148.4375131.25154.6875178.125189.0625151.5625187.5210.9375209.375293.75417.1875440.625351.5625 94.53125101.5625118.75156.25127.3438154.6875171.875189.0625143.75181.25198.4375205.4688258.5938367.1875384.375320.3125 100.7813106.6406128.9063159.375127.3438149.6094171.875185.9375140.625171.0938193.3594199.2188233.5938321.4844343.75292.1875 103.3203114.8438133.0078164.4531124.8047149.6094167.7734185.9375135.5469166.9922185.1563196.6797210.7422288.6719306.8359271.875 107.4219118.1641139.6484166.5039124.8047146.2891167.7734183.8867133.4961160.3516181.8359192.5781194.3359258.7891280.2734253.418 109.082123.5352142.334169.8242123.1445146.2891165.0879183.8867130.1758157.666176.4648190.918179.3945237.3047256.1035240.1367 111.7676125.708146.6797171.167123.1445144.1162165.0879182.5439128.833153.3203174.292188.2324168.6523217.749238.7207228.0518 112.854129.2236148.4375173.3398122.0581144.1162163.3301182.5439126.6602151.5625170.7764187.146158.8745203.6865222.9004219.3604 114.6118130.6458151.2817174.2188122.0581142.6941163.3301181.665125.7813148.7183169.3542185.3882151.8433190.8875211.5234211.4502 115.3229132.9468152.4323175.6409121.347142.6941162.1796181.665124.3591147.5677167.0532184.6771145.4437181.6833201.1688205.7617 116.4734133.8776154.2938176.2161121.347141.7633162.1796181.0898123.7839145.7062166.1224183.5266140.8417173.3063193.7225200.5844 116.9388135.3836155.0468177.1469120.8817141.7633161.4265181.0898122.8531144.9532164.6164183.0612136.6531167.2821186.9453196.8613 117.6918135.9928156.2653177.5234120.8817141.1541161.4265180.7133122.4766143.7347164.0072182.3082133.6411161.7992182.0717193.4727 117.9964136.9785156.7581178.1326120.577141.1541160.9337180.7133121.8674143.2419163.0215182.0036130.8996157.8564177.636191.0358 118.4893137.3773157.5556178.3791120.577140.7554160.9337180.4668121.6209142.4444162.6227181.5107128.9282154.2678174.4461188.818 118.6886138.0224157.8782178.7778120.3777140.7554160.6111180.4668121.2222142.1218161.9776181.3114127.1339151.6871171.5429187.2231 119.0112138.2834158.4001178.9391120.3777140.4944160.6111180.3056121.0609141.5999161.7166180.9888125.8436149.3384169.4551185.7714 119.1417138.7057158.6112179.2001120.2472140.4944160.4180.3056120.7999141.3888161.2943180.8583124.6692147.6493167.5549184.7275 119.3528138.8765158.9529179.3056120.2472140.3236160.4180.2120.6944141.0471161.1235180.6472123.8247146.1121166.1884183.7775 119.4382139.1528159.091179.4764120.1618140.3236160.2618180.2120.5236140.909160.8472180.5618123.056145.0066164.9448183.0942 119.5764139.2646159.3146179.5455120.1618140.2118160.2618180.1309120.4545140.6854160.7354180.4236122.5033144.0004164.0504182.4724 119.6323139.4455159.4051179.6573120.1059140.2118160.1713180.1309120.3427140.5949160.5545180.3677122.0002143.2768163.2364182.0252 119.7228139.5187159.5514179.7025120.1059140.1386160.1713180.0857120.2975140.4486160.4813180.2772121.6384142.6183162.651181.6182 119.7593139.6371159.6106179.7757120.0693140.1386160.1121180.0857120.2243140.3894160.3629180.2407121.3091142.1447162.1182181.3255 119.8185139.685159.7064179.8053120.0693140.0907160.1121180.0561120.1947140.2936160.315180.1815121.0724141.7137161.7351181.0591 119.8425139.7625159.7451179.8532120.0454140.0907160.0734180.0561120.1468140.2549160.2375180.1575120.8568141.4037161.3864180.8676 119.8812139.7938159.8078179.8726120.0454140.0594160.0734180.0367120.1274140.1922160.2062180.1188120.7019141.1216161.1356180.6932 119.8969139.8445159.8332179.9039120.0297140.0594160.048180.0367120.0961140.1668160.1555180.1031120.5608140.9188160.9074180.5678 119.9223139.8651159.8742179.9166120.0297140.0389160.048180.024120.0834140.1258160.1349180.0777120.4594140.7341160.7433180.4537 119.9325139.8982159.8908179.9371120.0194140.0389160.0314180.024120.0629140.1092160.1018180.0675120.3671140.6013160.5939180.3716 119.9491139.9117159.9177179.9454120.0194140.0254160.0314180.0157120.0546140.0823160.0883180.0509120.3007140.4805160.4865180.297 119.9558139.9334159.9285179.9588120.0127140.0254160.0206180.0157120.0412140.0715160.0666180.0442120.2402140.3936160.3887180.2432 119.9667139.9422159.9461179.9643120.0127140.0166160.0206180.0103120.0357140.0539160.0578180.0333120.1968140.3145160.3184180.1944 119.9711139.9564159.9532179.9731120.0083140.0166160.0135180.0103120.0269140.0468160.0436180.0289120.1572140.2576160.2544180.1592 119.9782139.9622159.9647179.9766120.0083140.0109160.0135180.0067120.0234140.0353160.0378180.0218120.1288140.2058160.2084180.1272 TransientTemperaturesinaSlab Row1listsinitialtemperaturesof0,200,300,and1000 Notethatlongtimetemperaturesapproachsamevalues forallinitialconditions(Row41).
b. If desired, a shortcut key may be assigned for the macro at this time. The key must take the form of Ctrl + letter. Numbers are not permitted.
c. Specify the storage place of the macro. “This Workbook” is chosen for this example. If the macro is to be available for all Excel workbooks, store it in the Personal Macro Workbook in the XLStart folder.
d. Enter a description of the macro.
3. Click OK. The Stop Rec dialog box should appear as shown in Figure 5.17. If it does not appear, click VIEW/TOOLBARS/Stop Record. If no action is taken, the recording of the macro will be based on absolute cell references. Click the relative cell button to record on a relative cell basis. If desired, the absolute and relative reference cell basis may be alternated during the recording process.
4. Execute the procedure to obtain the roots. In this case, the solution to B4 = 0 is performed as shown in the discussion of Goal Seek solutions. Obviously, the solution to this example is very simple (B3 = 6). When the procedure execution is completed, click Stop in the Stop Rec box, or click TOOLS/MACRO/Stop Recording.
a. If the recording is done on an absolute cell basis (relative cell button not clicked), the solution will be obtained for B4 = 0 by changing the values of B3.
The solution appears in B3 and the residual value of f(x) appears in B4.
b. If the macro is recorded on a relative cell basis (relative cell button activated), the function cell must be activated (clicked) before starting the macro. For
FIGURE 5.17
Solution of Equations 117
example, if the function is entered in cell M9, that cell should be activated.
The solution will then appear in the cell just above (or M8), therefore that cell should be reserved for display of the solution or initial guesses for the iterative Goal Seek calculation.
5. The macro is executed by pressing the shortcut key assigned (Ctrl + letter) or by clicking TOOLS/MACRO/MACROS/select Macro1, then click Run. In addition, the macro may be attached to an object or button on the worksheet that will run the macro when clicked. Two examples are shown in Figure 5.18 for this macro.
a. A rectangle drawing object is created at B6 and the macro name Macro1 typed inside. The macro is assigned by activating the rectangle, right clicking, and then followed by Assign Macro with Macro1 selected in the dialog box.
b. A special button is created at B8 by clicking VIEW/TOOLBARS/FORMS/
Button (second row, second column). The Assign Macro box will appear. Make the assignment and then click OK. The button will remain activated; if not, activate by pressing Ctrl while clicking on the periphery of the button. Type the title of the macro in the button using a desired style and font.
c. For both the rectangle and button, click FORMAT/CONTROL/PROPERTIES/
select choice for object (button) positioning. If object (button) is to be shown on the printout of the worksheet, click Print Object.
Click X in the Forms dialog box to remove it from the screen.
Example 5.8: Solution of the Transcendental Equation from Section 5.2 Create the aforementioned macro and apply it to the solution for the roots of the tran-scendental equation in Section 5.2. Note that the trantran-scendental equation x tanx – 2 has multiple roots. Use several initial guesses (both positive and negative) in cell B3 to produce values of these roots. Initial guesses of 1.0, 2.0, and 10.0 produce results of 1.07684, 3.64361, and 9.62964, respectively. Initial guesses of −1.0, −2.0, and −10.0 will display the corre-sponding negative values for the roots.
FIGURE 5.18
Problems
5.1 Solve the following equations using both Goal Seek and Solver:
a. x = 0.09[1 − (1+x)−n] for n = 5, 10, 15, and 20 b. 4x3 − 3x2 + 2x − 87 = 0
c. xsinx − 1 = 0 d. xe−0.1x = 1
e. x3 − (0.647)2[(1 − x)2(2 + x)] = 0 f. 3.587(1 + 0.04x2/3) = 0.0668x g. 8.3(302 − x) = 5.102 × 10−8(x4 − 278)
h. 4.74(300 − x)1/4 + 5.102 × 10−8(5004 − x4) = 0
5.2 Solve the following sets of linear equations using both the iterative technique and matrix inversion:
a. 10x2 + 10x5 + 12.5 = 21.25x1 5x1 + 5x3 + 10x6 + 12.5 = 21.25 x2
5x2 + 5x4 + 10x7 + 12.5 = 21.25x3 10x3 + 12.5 = 11.25x4
10x1 + 50x6 + 12000 = 100x5 25x5 + 25x7 + 10x2 + 12000 = 100x6
50x6 + 20x3 = 70x7 b. 1100 + x3 + x4 = 4x1
600 + x3 + x4 = 4x2
900 + x1 + x2 = 4x3 800 + x1 + x2 = 4x4
c. 75 + 2x5 +x2/4 + 16 = 3.3x1
x1/4 + x3/4 + 2x6 + 16 = 3.3x2 x2/4 +x4/4 + 2x7 + 16 = 3.3x3
x3/4 + x8 + 8 + 4 = 1.85x4 4x1 + x6/2 + 150 = 5x5
4x2 + x7/2 + x5/2 = 5x6 4x3 + x6/2 + x8/2 = 5x7
2x4 + x7/2 + 5 = 2.9x8 d. x2/2 + 50 + 16x3 =17.75x1
x1/2 + 16x4 +50 = 17.75x2
x4 + 100 + 16x5 + 16x1 = 34x3 x3 + 100 + 16x6 + 16x2 = 34x4
Solution of Equations 119
x6/2 + 50 + 16x3 = 17x5 x5/2 + 50 + 16x4 = 17x6
e. (57000 − x1)/4 + (460 − x1)/90 + (x2 − x1)/19 = 0 (x1 − x2)/19 + (460 − x2)/31 + (x3 − x2)/64 = 0 (460 − x3)/8 +(x2 − x3)/64 = 0
5.4 Solve the following sets of nonlinear equations using Solver:
a. 1300(T2 − T1) + 1.42[ABS(300 − T1)]1/4 + 5.7 × 10−8(3004 − T14) = 0 T3 + T1 − 2T2 = 0
500 + T2 − 2T3 = 0
with the restriction that 300 < T < 500
b. 1300[1 + 0.00025(T2 + T1)](T2 − T1) + 1.42[ABS(300 − T1)]1/4 + 5.7×10-8(3004 − T14
= 0
[1 + 0.00025(T3 + T2)](T3 − T2) + [1 + 0.00025(T1 + T2)](T1 − T2) = 0 [1 + 0.00025(1000+T3)](1000 − T3) + [1 + 0.00025(T2 + T3)](T2 − T3) = 0 with the restriction that 300 < T < 1000
c. x12 + sinx − 2x2 = 1.4674 x1x2 + x23 = 2.5708
with the restriction that x1, x2 > 0 d. x12 + x22 = 5
x1 + 3x2 = 7 x2 + x32 − x1 =5 for all x > 0
e. 3.38 − p + [(101.3 − p))(310 − T)]/(1538 − T) = 0 ln(p/2337) = 6789(1/293.15 − 1/T)
p and T are positive values
5.5 The following set of equations describes the performance of a crossflow finned-tube heat exchanger:
e = 1 − exp{[exp(−NCn) − 1]/Cn}
n = N−0.22 C = 2100/Cmin
DTh = 0.67e DTh = 40300/Cmin N = 2100/Cmin Determine the values of the six variables. All values must be positive.
5.6 The temperature ratio in a pin fin is described by the equation
Tr = [cosh m(L − x) + (h/mk)sinh m(L − x)]/[cosh mL + (h/mk)sinh mL]
In a fin with d = 0.01 m, and L = 0.1 m, the temperature ratios are measured at two x locations giving
Tr = 0.56 at x = 0.04 m Tr = 0.365 at x = 0.08 m Using Solver, determine the values of h and k.
5.7 The amplitude response for a seismic instrument is described by the equation:
a = x2/[(1-x2)2 + (2Cx)2]1/2 where x = ω/ωn, C = c/cc, cc = (4mk)1/2, and ωn = (k/m)1/2 Three amplitude measurements are taken giving:
a = 0.98 at ω = 75 Hz a = 2 at ω = 100 Hz a = 1.5 at ω = 166 Hz
Using these data and Solver, determine values of m and k.
121