top of page

Physics & VBA

My enthusiasm for physics simulations & VBA programming was re-kindled by Ivan Savov in February 2018, and ultimately led to the creation of the Laos Village Project as noted on the "About" page.

The VBA notes and the applications linked below are still pretty good 14 years after I stopped teaching the course, and 9 years after retiring. I've included them on the project site in the off-chance that someone will find them as exciting as I do.

The Newton's Laws explanations were put together in February 2018 in anticipation of finding a few school children in Laos & Vietnam who would be irresistibly charmed by creating their own physics simulations -- which of course never happened. Nevertheless, I believe that they are the best notes on the topic that I ever made, and hope that they benefit someone. Perhaps even my own adult children will read them, and finally understand why I wanted to "teach" them about the Laws, which regrettably never happened despite all my attempts.

Contents.

1.  Coding for kids 9 to 11 years old (and younger).

2.  Physics simulations running off Excel and using Visual Basic.

3.  Kinematics and Newton's Laws refreshers.

4.  How and why the simulations work.

5.  Simulations, programming notes, other applications.

1.  Coding for kids 9 to 11 years old (and younger).

Coding skills are becoming recognized as a third type of literacy essential for career success. Elementary school curricula have been slow to adapt due to a lack of teacher training and space in the curriculum, but mostly the absence of a kid-friendly programming language. 

The Scratch programming language, developed by the Lifelong Kindergarten Group at MIT Media Lab(www.scratch.mit.edu) is, without hyperbole, a brilliant language that allows kids to intuitively build programs without typing actual code, but by moving colourful blocks containing the main coding elements: loops, if/then/else statements, etc. The concepts learned can later be easily applied in "real" programming languages.  

Scratch is free; all you have to do is join the MIT Scratch website, and there are plenty of tutorials. However, for those of us who still relish hard-copies there are a number of good books. One such book that I found extremely well put together is "Coding Games in Scratch" by Jon Woodcock, DK books. I picked up a copy at Chapters (in Canada). 

Your kids will really enjoy the projects in this book, and so will you.

2.  Physics simulations running off Excel and using Visual Basic.

Some people "diss" Visual Basic as being too old school -- yesterday's language, but in terms of syntax it's the simplest programming language (though I'm certain there might be debate). It's portable and universally available since it comes with Excel, there's no need to be connected to the internet, and it runs off the spreadsheet where data is either being stored, or created, so that there's no need to export data. 

In physics the study of mechanics lends itself particularly well to visual simulations: things are often moving and thus described by Newton's 1st and 2nd Laws along with the kinematics equations. Objects can be tracked by their position-time data and plotted in real-time on an Excel graph.  

Pausing the simulations enables comparison of the simulated position, and forces -- such as tensions and normal forces, with theoretically determined values. 

Pretty much any situation can be simulated provided the net force can be functionally described.

 

But even certain situations in electricity and magnetism, such as mapping out E field lines and equipotential lines by allowing a test charge to move incrementally at constant speed, can be simulated. And of course charges moving in electric or magnetic fields, or interacting with each other, can be modelled.

Given below are some sample simulations and applications -- a few outside the realm of physics, and notes describing the programming. These notes were created for a computer applications course that I taught at a first year university level from 2000 to 2005. Be warned that the notes apply to Excel 97 so the layout of the VB editor has changed a little, but the coding remains the same. 

3.  Kinematics & Newton's Laws.

Click here for a short refresher on the kinematics equations used in the simulations.

Click here for an explanation of Newton's 1st, 2nd, and 3rd Laws; there are more nuances than you may have appreciated.

4.  How and why the simulations work.

The kinematics equations that describe an object's motion are simple and straightforward, but they can only be applied if the acceleration is constant. Since acceleration depends on the net force and the mass of the object, it will not be constant if either varies.

Varying net forces are found in situations such as an object moving over a circular track in a vertical plane, any situation involving a spring, the presence of air resistance (drag), a swinging pendulum, gravitational forces in cases where the object is not close to the Earth’s surface and is moving over large distances, Coulombic forces between charged particles, etc. Although energy methods or differential equations are typically applied to analyze some of these situations theoretically, they cannot be used to simulate an object’s motion. 

Objects with varying mass are most commonly found in rocket propulsion: as the fuel is burned to produce thrust, the mass of the rocket decreases. Even if the thrust is constant the acceleration will vary with the varying mass. 

What allows a computer simulation to work in the presence of varying acceleration is that objects can be moved over small distances by using small and often incremental time intervals. Though the acceleration still varies, it doesn't vary much, and is approximately constant. In the limit as the time interval is reduced the error approaches zero. (If you remember your calculus, this is precisely the limit process that underlies integration.)

The object's trajectory is thus broken down into a series of movements over suitably small time intervals. The process is known as incremental iteration. The length of a time interval is typically in milliseconds, or less.  But in the case of an object orbiting the Earth, or travelling to the Moon, the time intervals can be hundreds of seconds since the gravitational force will not have changed much for the velocities involved and distances moved in each interval.   

The final position and velocity at the end of each small movement, or iteration, are used as the initial conditions for the next iteration (cycle), over and over again. 

The process is programmed using a loop, which is a code structure that repeats itself until some condition is satisfied -- say the total allowable time is reached, or the user presses a pause or stop button.

The code used to simulate the motion of a rocket car moving in a straight line along a road (ignoring air resistance and the varying mass for the time being) can be viewed by clicking here

5.  Simulations, applications, and programming notes.

Physics simulations

Note: when you open these Excel files you will usually receive two possible warnings: either press the "Enable macros" button, or the "Enable content" button. To view the code go into the Developer tab at the top of the worksheet and click on "View code"; probably located just to the right of the triangle-ruler-pencil symbol. Unless you know what you're doing, don't mess with the code. To return to the worksheet click on the green Excel symbol at the top left under the File tab. 

CAUTION For some reason the applications will only run once if you try to save in a format other than Excel 97-2003, so if you press "Save", or "Save as" you will likely get a "Compatibility checker" message about a "Minor loss of fidelity" -- just press "Continue", don't try to save as a different version. 

 

Clicking on a simulation will send it to your "Downloads" file; it will not automatically open it in Excel. Interestingly, if you click once, it downloads, click again (not too quickly) and it opens 2 Excel files as well as downloading a second time.

Oh No!! A new version of Excel that came with a laptop that I purchased in 2019 would not allow these sample apps to be run. There must be a setting somewhere, but after a cursory search I have not found it. Please email me if you find what the fix is. 

2-D projectile motion with drag

Ball bouncing in a closed box with energy loss

Object orbiting the Earth

Rocket ship controlled by tangential & perpendicular thrusters

Swinging pendulum (SHM)

Block moving on a frictionless hemisphere into freefall

Mass on spring -- simple harmonic motion (Lab data only)

Charge moving in perpendicular magnetic field

Charged mass held by a thread in an E field -- with damping

 

Programming notes

Contents

Chapter 1a -- Introduction to programming

Chapter 1b  -- Introduction to programming (continued)

Chapter 2  -- Message box function, vectors, introduction to loops

Chapter 3a  -- Loops & running totals

Chapter 3b  -- Loops (continued)

Chapter 4  -- Arrays & matrices (never completed -- stopped teaching the course)

Chapter 5  -- Graphs

Chapter 6a  -- Incremental iteration applied to trajectories Part 1

Chapter 6b  -- Incremental iteration applied to trajectories (Part 1 continued)

Chapter 7  --  2-D Dynamics: Incremental iteration applied to more complex trajectories

Chapter 8 -- Iterative techniques applied to integration

Chapter 9  -- Debugging & error messages

Appendices and haikus

 

Other applications

Random colour generator

The following application has not been activated

Colouring worksheet cells to make a spiral 

bottom of page