This section explains explains how to implement the formulas that define the emergent (reflected) rays into a custom VBA function.
Though geometrically the last two presentation might look a little elaborate, just be patient and follow the presentation, or even better try to just sneak peek and do it yourself.
All this derivation is done based on first principles and some basic geometry. Take all the time you need, a day, a week or a month, it is worth it and you will be rewarded.
With minor upgrades, this function can later be used to model complex cascaded optical systems.
[sociallocker]
[/sociallocker]
Introduction to Geometrical Optics – a 2D ray tracing Excel model for spherical mirrors – Part 3
by George Lungu
-While the previous section dealt with defining the parameters and geometry of the input
beam and solving the system of equation to find the formulas of the coordinates of the points
where the incident rays meet the mirror, this section creates a VBA custom function to
calculate those coordinates.
– This is an exact model in the sense that no geometrical approximations are used, however
the model does not take into consideration diffraction effects.
Reflect() – new VBA function:
– In the previous section we solved the quadratic
system of equations leading to finding the exact
Cartesian coordinates of the incidence points on the
mirror surface.
– Now we will write a custom VBA function to
implement those results and call it “Reflect”.
– This custom spreadsheet function will return four
numbers: two x-y Cartesian coordinates of the point
in space where the ray hits the mirror surface, the
tangents of the incident angle and emergent angle.
– The next version of this function must be easy to
be cascaded when later used for successive reflection mia nufactured by fusing hexagonal pieces of ULE zero-expansion
glass in a high-temperature furnace.
and refraction models
Review of the formulas behind the “Reflect()” custom function:
The constants a, b, c used in the formulas to the left of the page are defined below:
-Based on the previous presentation if the mirror is convex (R>0) it means we choose the smaller x (the solution with minus) and vice
versa. This is obvious also from the diagram to the right. We can see that the smaller x corresponds to light hitting a convex mirror and
a larger x corresponds to light hitting a concave mirror.
– In this case the formulas become:
Even if there might be solutions determined by the ray intersecting the mirror circle, maybe
the diameter of the mirror “d” will prevent that to happen and the ray will pass undeviated.
We will deal with this situation later and for now let’s implement a preliminary custom VBA
function.
<excelunusual.com>
2
Implementation of a preliminary “Reflect_1()” custom VBA function:
– Let’s implement a preliminary function which has as arguments the Cartesian coordinates
of the light source L (xL, yL), the coordinates of the mirror vertex M (xM, yM), the angle of the
incident ray with respect to the horizontal “alpha_incident” and the mirror radius “R”.
– For now, this function will only return the Cartesian coordinates of the of the point of
incidence I (xI, yI).
– Rename the current worksheet “Tutorial_1+2”
– Copy the current worksheet and rename the copy “Tutorial_3”
– In the VBA editor insert a module and in that module write the following code:
– These are just the formulas from the previous page set up as a custom VBA function.
Function Reflect_1(xL, yL, xM, yM, alpha_incident, R)
Dim a, b, c As Double
-The output of this function is a 2D vector array and we will need to be careful when we type it in (using F2 + Ctrl + c = (xM + R) ^ 2 + (yL – yM – xL * Tan(alpha_incident)) ^ 2 – R ^ 2
Shift + Enter).
a = 1 / Cos(alpha_incident) ^ 2
b = 2 * (Tan(alpha_incident) * (yL – yM – xL * Tan(alpha_incident)) – xM – R)
x = (-b – Sgn(R) * Sqr(b ^ 2 – 4 * a * c)) / (2 * a)
– The correctness of the function will be verified by plotting a series of incident point coordinates on the same chart with the mirror.
y = Tan(alpha_incident) * x + yL – xL * Tan(alpha_incident)
End Function
Reflect_1 = Array(x, y)
Verifying the functionality of the Reflect_1() custom function:
– In the worksheet “Tutorial_3” in the range E42:E66 we will create a “Ray_Index” column
with integer numbers running from 0 to 24. I have chosen a 25 ray input beam since 25 is
different from 21 which is the number of hachure lines on the back of the mirror (I didn’t want
people to inadvertently associate a ray to each hachure dash).
– Cell E42: “=0”, cell E43: “=E42+1”, then copy E43 down to cell E66.
– Cell F24: “=Reflect_1(xL,yL,xM,yM,alpha_min+E42*delta_alpha,Radius)
– Select range F42:G42 and then holding F2 down press Ctrl+Shift+Enter
and you will have the 2D output vector of the custom function in range
F42:G42.
– Copy range F42:G42 down to range F66:G66
– Add a series to the chart having the x-data taken from range F42:F66
and the y-data taken from range G421:G66.
– We can see that the new data (red points in the
snapshot) overlap with the mirror profile and this
is what we intended.
– While deriving the formula I made a small
mistake forgetting the R2 in the formula of “c”. It
took me 2 hours to figure it out and I recommend
that you don’t give up if things don’t work for the
first time. Persist and you will be rewarded!
<excelunusual.com>
4
Deriving the emergent ray angle formulas:
– Emergent rays are the rays leaving the mirror after reflection.
– We need these formulas to write the equations of the emerging rays so that we can plot them.
– We named the center of curvature of the mirror “C” and the incidence point “P”.
– Since C is the center of curvature, the distance CP is equal to the radius of the mirror.
– We also know that the radius of a circle is normal to its circumference.
From the reflection laws we have:
From the diagram we can write
the following angle equality: I
and since triangles CPP’ and LPP” are straight
triangles we can write the following relationships:
from the above
expressions we can
therefore write:
And expressing the
We get the new emergent ray angle as:
emergent ray angle:
We know for every ray and we can calculate p from the coordinate
P M
of point P (which we know too). There is a sign change in front of the
e i
number “2” and it comes from the negative R value in the depicted case:
– Looking at the formula in the previous page we
realize that the sign is correct since for a positive angle the emerging ray climbs.
– Let’s see how to add this formula to the new
“Reflect()” custom VBA function.
Upgrading the “Reflect_1()” custom VBA function to “Reflect()”:
Function Reflect(xL, yL, xM, yM, alpha_incident, R)
Dim a, b, c As Double
– The code to the left
a = 1 / Cos(alpha_incident) ^ 2 creates the new
custom function which
b = 2 * (Tan(alpha_incident) * (yL – yM – xL * Tan(alpha_incident)) – xM – R)
will return not only
c = (xM + R) ^ 2 + (yL – yM – xL * Tan(alpha_incident)) ^ 2 – R ^ 2
the x-y coordinates of
x = (-b – Sgn(R) * Sqr(b ^ 2 – 4 * a * c)) / (2 * a)
the incidence point but
y = Tan(alpha_incident) * x + yL – xL * Tan(alpha_incident) also the slope of the
emergent (reflected
z = alpha_incident + 2 * Application.Asin((y – yM) / R)
ray).
Reflect = Array(x, y, z)
End Function
to be continued…
<excelunusual.com>
6
