Engineering Problem Solving Course工程问题解决过程.pptVIP

Using Visual Basic for Applications (VBA) Jake Blanchard University of Wisconsin Spring 2010 VBA Macros Macros allow one to add significant power to Excel They are small programs that can be called from a spreadsheet You can create functions or subroutines If you want to get fancy, you can add a user interface as well Using Macros Macros are written in a Basic-like language called Visual Basic for Applications Excel comes with a separate macro editor To create a macro, go to Tools/Macro/Visual Basic Editor, then within the Editor go to Insert/Module You should get this... Creating a Function Suppose we want to create an Excel function that takes a temperature in Celsius and converts to Fahrenheit We would type the following in a module: Function ctof(temp) ctof = 9 / 5 * temp + 32 End Function Now we have this... Using the function Now go to the spreadsheet and type =ctof(100) Or, you can put the value of “100” into cell A1 and then type =ctof(A1) into some other cell In fact, this function can be used just as any built-in Excel function can be used The Macro Language Operators: +, -, *, /, ^, Mod Comparison: =, , , =, =, Logical Operators: And, Eqv, Imp, Not, Or, Xor Intrinsic Functions: Abs, Cos, Sin, Tan, Atn (arc tangent), Exp, Log (natural), Sgn, Sqr (square root), Rnd (random number) Flow Control If condition Then statements Else statements End If If x=0 Then f=1 Else f=sin(x)/x End If Flow Control For counter=start To end statements Next For i=1 To 100 sum=sum+i Next Flow Control Do Until condition statements Loop i=1 x=1 Do Until i=50 x=x*i i=i+1 Loop Flow Control Do While condition statements Loop i=1 x=1 Do While i50 x=x*i i=i+1 Loop Practice Write an Excel function that calculates the sum of cubes of the first N integers Then write an Excel function that calculates the sum of cubes of the first N even integers My solution Function sumofcubes(N) ans = 0 For i = 1 To N ans = ans + i ^ 3