Intro to Coding for Physics Classes Using Excel

Educators and students can get a competitive edge for the future by learning to code using MS Excel. The data generated will be used to graph Physics motions by using a pre-written macro in Visual Basic for Applications (VBA) to solve Physics problems. A VBA macro is computer code that is available to do repetitive math operations. VBA is available for all Microsoft Office Products such as Excel, Word, Access, etc. You can modify this macro to solve other Physics problems.

We will use some pre-written VBA computer code of a spring mass system to demonstrate just how useful this knowledge can be. You will be able to run circles around those that practice Physics the old-fashioned way with pencil and paper.

Excel files with macros have the extension .xlsm. If you have a computer anti-virus program, it will likely quarantine an .xlsm file when you download it. Some macros can do your computer harm if you are not careful to guard against malicious code.

Math Strategy for Solving Physics Problems:

This strategy is the same for all Newtonian mechanics problems.

In the equation F=M*a, the acceleration term, a, is the second derivative of position:

  1. In Macro, solve for the acceleration term in your [F=M_a, F=M_XDD equation], “a “or “XDD” term where D represents the derivative
  2. Integrate the acceleration term, XDD, to find the velocity, XD
    a. Backwards rectangular integration is used for this example, where DT represents the time step
    b. You can play around with this variable a bit to see if the answer changes appreciably when going up or down in size.
  3. Integrate the velocity term, XD, to find the position, X
  4. Record data to Excel spreadsheet columns using Cells(Row, Col).Value function
    a. column 1 represents Time in seconds
    i. adjust “Time_” variable in macro to represent how long the motion will occur
    b. column 2 represents acceleration, a, XDD
    c. column 3 represents velocity, XD
    d. column 4 represents position, X
  5. Don’t record every point from your calculations (too much data)
    a. Instead, pick an appropriate sample rate, Tsamp, that shows trends of motion
    b. Adjust variable to sample motion every “Tsamp” seconds
  6. Use Excel to plot position vs. time, for example, or any other parameter vs time.
  7. Check if simulation frequency matches theoretical equation for frequency
    a. 2 π freq= ω= sqrt(K/m)= sqrt(1000/32/12)= 1.61 rad/sec
    b. Freq= 1.61/(2*π)= 0.257 Hz
    c. From graph, Figure 1, above and screenshot of data below, Figure 2, one cycle ends in 3.89 seconds
    d. Frequency = 1 cycle/(3.89 seconds) = 0.257 Hz,
    e. Excellent agreement!

To get to the VBA macro in Excel, you have to go to the “Developer Tab”. I suggest that you Google Excel Macros to get some more background on how to access the “Developer Tab.” Some Excel Settings may need to be changed. Click Visual Basic Icon in Developer Tab to see the VBA source Code. Comments about each line are provided in green as explanations that correlate to the Math Strategy above.

Press Run icon to run the macro. Press Stop icon to stop the macro. Enjoy. May you run circles (loops) around your classmates with your newly found skills to solve Physics problems in Excel. You can download the Spring Mass Demonstration Excel file here.