Engineering Software: INTEGRATED API - Excel Macros used in the General Utilities Sample
Uploaded by IntegratedEngSoft on 23.11.2011
Transcript:
In order to program an API function within Excel, it is necessary to have the Developer
tab available. By default, your copy of Excel will probably not have this tab on the ribbon.
To enable the Developer tab in Excel, go to the File menu, down to Options, Customize
Ribbon, and check the box that says Developer. This actives the Developer tab in the ribbon
so you can access the design mode insertion functions and get to the Visual Basic and
macro sections. In the Quick Utilities sample, in the worksheet
labeled “General”, there is a button whose only purpose is to start Electro and to set
your selected unit. The code for the subroutine invoked by clicking this button begins by
defining the required variables. These are the string used to set the unit and an integer
to inform the program if there are any errors while executing the function. Electro is invoked
by defining an object as “IES.document”. This refers to a Windows registry entry which
is linked to Electro. Thus, defining the object opens up the program. It can also open up
other INTEGRATED programs depending on what you have registered as “IES.document”.
The code has a pause of a few seconds to make sure that the program is open before sending
commands to it. The length unit is read out of the selection list inside the spreadsheet.
The selection list works by a combination of the combo box you see, plus the values
that are written into a hidden column. If we expose the column F by unhiding it, then
you can see that there is an association made between the selection box and values written
into column F. The value that is selected gets updated as a number in cell F2 and the
values appearing in the combo box are written inside of cells F4 to F9. For example, we
could have a km unit instead of a m unit at the top.
The apply unit combo box will automatically update Electro when you switch it. Switching
the value within this box automatically invokes another subroutine called apply unit. This
subroutine will also invoke IES.document though in this case it is expected that Electro is
already open and therefore the pause is shorter. The unit selection code works in the same
way as when the start button is clicked. The Run button in the General worksheet will
invoke a routine called ‘polyline’. The polyline subroutine needs to define more variables
than the Start or Unit subroutines because it is going to be drawing lines from X1 Y1
to X2 Y2 and is going to cycle through a loop looking for pairs of points to connect with
a line. It also needs to receive information back from the INTEGRATED program so the program
is invoked, there is a tiny pause to wait for it to open, and then a switch set is valid
and a counter set is as 1. While valid is set as True we are going to
perform a do loop. At the top of the do loop, we invoke a string which is A combined with
the counter number plus the value 3 so it counter equals 1 that will put us at A4, where
you see -10. It continues for Y1. Then a string will also be created to look at the location
1 further down. If that location contains a number, then Electro will leave valid as
True and will also read an X2 and Y2 value. If the next cell down does not contain a number,
then it will set valid as False and the do-loop will end without trying to draw a line. While
it is True, a line will be drawn by invoking Electro as OBJ, and invoking the Create Line
function. This is a command that can be found in the documentation for the INTEGRATED API.
The format of the command is that it gives the points X1 Y1, X2 Y2, then it gets back
from Electro the number of the segment created and the code that will let you know if there
were any errors encountered. After drawing the line, the counter is incremented by 1
so that the loop in total will step through from A4 – A8 and at that point will check
as False. It will thus not draw a line and finish the routine.
The rectangle tab contains 3 different rectangle creation tools. The first one is going to
give you 2 opposite points, X1 Y1 and X2 Y2, a rounding radius and then draw the corresponding
rectangle. The function that is invoked by clicking the Run button is called Rounded
Rectangle. The rounded rectangle tool, like the other functions, defines necessary variables
to invoke Electro again with a short pause, and will then read values out of the worksheet
X1 out of the location B3, X2 out of B4 and so on. It will then draw the rectangle as
a series of straight lines, doing a little bit of math on the command itself so that
the line begins one radius away from the actual corner point. There are 4 straight line creation
commands, then there are 4 corner radius commands, all invoking X Y locations for the center
of the arc, then appropriate angles to round the corners off.
The second geometry creation tool defines a rectangle based on the center and then the
length and the width. In this case, the function that is invoked is going to read again the
values from the corresponding cells; B10 – B13. It then invokes the 2D line creation command
in order to draw the rectangle. The final creation tool is a combination of
the first two. It defines the center, length, and width of the rectangle and the rounding
radius of the corners. From this tool we have a function which reads the values out of the
appropriate cells from the worksheet, performs the math to get the X1 Y1 values, but then
from there it has all the same information from the original routine for rounding corners
so it simply reproduces the same code as in the first function.
The polygon creation tab gives you 2 different ways of drawing a polygon, which is useful
depending on how you think about the geometry or which data you have. Both contain a multiple
number of sides, both define the center, but one then defines the size of the polygon by
the length of the side, the other by the radius as defined as being the center point to any
of the corner points. The radius method for creating the polygon works as usual - by reading
a number of variables out of the spreadsheet the number of sides X and Y centers and the
radius. It then performs a number of calculations that divides 2 Pi radians by the number of
sides to get the angle of the triangle corresponding to each side. It then assumes that we want
to have a horizontal bottom segment so it starts by drawing a horizontal segment centered
at the angle of -Pi by 2. It finds the points half the angle away on either side of –Pi
by 2 as X1 Y1 and X2 Y2, then draws the connecting line. After drawing 1 line the For loop cycles
back. The counter “I” is incremented from 0 to 1 then at the end we are going to add
1 full angle onto the angles used in the previous step. It performs the same thing over again,
all the way up to the appropriate number of sides, which will then complete drawing the
polygon. The other polygon command based on a side length simply works by running the
same routine but it needs to begin by working out what the radius is. When you look at the
code, the length is read, then divided by 2, then divided by the sine of half the angle
in order to work out what the radius is, then the same code is used.
Note that accessing the number pi depends on the details of your Excel installation.
So for convenient universal code it was simply defined as a constant in this example.
tab available. By default, your copy of Excel will probably not have this tab on the ribbon.
To enable the Developer tab in Excel, go to the File menu, down to Options, Customize
Ribbon, and check the box that says Developer. This actives the Developer tab in the ribbon
so you can access the design mode insertion functions and get to the Visual Basic and
macro sections. In the Quick Utilities sample, in the worksheet
labeled “General”, there is a button whose only purpose is to start Electro and to set
your selected unit. The code for the subroutine invoked by clicking this button begins by
defining the required variables. These are the string used to set the unit and an integer
to inform the program if there are any errors while executing the function. Electro is invoked
by defining an object as “IES.document”. This refers to a Windows registry entry which
is linked to Electro. Thus, defining the object opens up the program. It can also open up
other INTEGRATED programs depending on what you have registered as “IES.document”.
The code has a pause of a few seconds to make sure that the program is open before sending
commands to it. The length unit is read out of the selection list inside the spreadsheet.
The selection list works by a combination of the combo box you see, plus the values
that are written into a hidden column. If we expose the column F by unhiding it, then
you can see that there is an association made between the selection box and values written
into column F. The value that is selected gets updated as a number in cell F2 and the
values appearing in the combo box are written inside of cells F4 to F9. For example, we
could have a km unit instead of a m unit at the top.
The apply unit combo box will automatically update Electro when you switch it. Switching
the value within this box automatically invokes another subroutine called apply unit. This
subroutine will also invoke IES.document though in this case it is expected that Electro is
already open and therefore the pause is shorter. The unit selection code works in the same
way as when the start button is clicked. The Run button in the General worksheet will
invoke a routine called ‘polyline’. The polyline subroutine needs to define more variables
than the Start or Unit subroutines because it is going to be drawing lines from X1 Y1
to X2 Y2 and is going to cycle through a loop looking for pairs of points to connect with
a line. It also needs to receive information back from the INTEGRATED program so the program
is invoked, there is a tiny pause to wait for it to open, and then a switch set is valid
and a counter set is as 1. While valid is set as True we are going to
perform a do loop. At the top of the do loop, we invoke a string which is A combined with
the counter number plus the value 3 so it counter equals 1 that will put us at A4, where
you see -10. It continues for Y1. Then a string will also be created to look at the location
1 further down. If that location contains a number, then Electro will leave valid as
True and will also read an X2 and Y2 value. If the next cell down does not contain a number,
then it will set valid as False and the do-loop will end without trying to draw a line. While
it is True, a line will be drawn by invoking Electro as OBJ, and invoking the Create Line
function. This is a command that can be found in the documentation for the INTEGRATED API.
The format of the command is that it gives the points X1 Y1, X2 Y2, then it gets back
from Electro the number of the segment created and the code that will let you know if there
were any errors encountered. After drawing the line, the counter is incremented by 1
so that the loop in total will step through from A4 – A8 and at that point will check
as False. It will thus not draw a line and finish the routine.
The rectangle tab contains 3 different rectangle creation tools. The first one is going to
give you 2 opposite points, X1 Y1 and X2 Y2, a rounding radius and then draw the corresponding
rectangle. The function that is invoked by clicking the Run button is called Rounded
Rectangle. The rounded rectangle tool, like the other functions, defines necessary variables
to invoke Electro again with a short pause, and will then read values out of the worksheet
X1 out of the location B3, X2 out of B4 and so on. It will then draw the rectangle as
a series of straight lines, doing a little bit of math on the command itself so that
the line begins one radius away from the actual corner point. There are 4 straight line creation
commands, then there are 4 corner radius commands, all invoking X Y locations for the center
of the arc, then appropriate angles to round the corners off.
The second geometry creation tool defines a rectangle based on the center and then the
length and the width. In this case, the function that is invoked is going to read again the
values from the corresponding cells; B10 – B13. It then invokes the 2D line creation command
in order to draw the rectangle. The final creation tool is a combination of
the first two. It defines the center, length, and width of the rectangle and the rounding
radius of the corners. From this tool we have a function which reads the values out of the
appropriate cells from the worksheet, performs the math to get the X1 Y1 values, but then
from there it has all the same information from the original routine for rounding corners
so it simply reproduces the same code as in the first function.
The polygon creation tab gives you 2 different ways of drawing a polygon, which is useful
depending on how you think about the geometry or which data you have. Both contain a multiple
number of sides, both define the center, but one then defines the size of the polygon by
the length of the side, the other by the radius as defined as being the center point to any
of the corner points. The radius method for creating the polygon works as usual - by reading
a number of variables out of the spreadsheet the number of sides X and Y centers and the
radius. It then performs a number of calculations that divides 2 Pi radians by the number of
sides to get the angle of the triangle corresponding to each side. It then assumes that we want
to have a horizontal bottom segment so it starts by drawing a horizontal segment centered
at the angle of -Pi by 2. It finds the points half the angle away on either side of –Pi
by 2 as X1 Y1 and X2 Y2, then draws the connecting line. After drawing 1 line the For loop cycles
back. The counter “I” is incremented from 0 to 1 then at the end we are going to add
1 full angle onto the angles used in the previous step. It performs the same thing over again,
all the way up to the appropriate number of sides, which will then complete drawing the
polygon. The other polygon command based on a side length simply works by running the
same routine but it needs to begin by working out what the radius is. When you look at the
code, the length is read, then divided by 2, then divided by the sine of half the angle
in order to work out what the radius is, then the same code is used.
Note that accessing the number pi depends on the details of your Excel installation.
So for convenient universal code it was simply defined as a constant in this example.