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.