Introduction

Hello everyone, this is my first article here. I have gained a lot from The Code Project and it is my time to give something here. What is this article about? It is just about a simple control on Excel so that the position of the arrow in the dashboard will change whenever the value of a cell changes. That means, the control is linked with the cell in Excel. From the figure, you can see that whenever the slider changes its position, the arrow will change its position too. It is because the cell value "A1" is changed by the slider, and so, because my control is linked to the cell, the position of the arrow in my control is changing too. So, don't be confused that my control is linked with the slider. It is indeed linked with the cell "A1". It is not a great article or application. After reading this article, I hope that you will learn the following few things:

How to use GDI+?

How to create ActiveX applications using MFC 6.0?

How to add properties and link cell to Excel?

How to use what we learnt in secondary school mathematics (Geometry, I studied in Hong Kong)?

Background

Why there is such an application? I have been a programmer since my university time, and been a professional C++ programmer after graduation. One day, my lovely boss asked me to create a dashboard control in Excel so that users of Excel will always be notified by such a control of how their cell values are changing. Useful or not? For me, I don't think it is useful, but it was a challenge for me. However, for some business, like some resource planning software, data is important, and if there are over hundreds of values, a manager can't just use his eyes to clarify which resources are under critical condition. However, with the help of dashboard, they can be notified easily and they can make decisions right away. This application is just the first step. After I created the application, I started to create a traffic light which needed multi-threading technology to flash users for their greater attention. I will not post the code of the traffic light here, let's see how the reader response to what is here first . That is all why there is such a control. I know nothing about ActiveX, Excel programming, COM... etc. I can only find very little resources talking about this on the Internet. So, from the learning phase to the design phase, to the implementation phase, all was stiff work to me and gave me a very hard time. I hope everyone reading this article can be benefited.

How to use

Extract the file to some location that you know.

Open a command prompt (by choosing "Start"->"Run" and type "cmd" in the pop up dialog ... sorry, I am not treating you guys as fools here ... but there maybe someone you know ... ).

Show the "control toolbars" by clicking on the menu "View"->"Toolbars"->"Control Toolbars".

A new toolbar is shown, try to find the "Hammer" like icon on the toolbar and click on it.

A list of controls in text format is shown, locate the item named "ActiveXArrow Control" and click on it.

Good, you did it. Now, your mouse icon should become a cross rather than an arrow. Try to drag a square on the Excel sheet and a control will be shown.

Right click on the control, and in the popup menu, try to choose "Properties".

A Properties list box is shown. Try to find the item "LinkedCell" in the list box and type "A1" on its properties. (Remember to push "enter" once you typed it, to activate the changes).

On the "Control Toolbars", there is a ruler like icon (always on the top left corner), click on it to finish the design phase (before you are designing).

Try to change the value of cell "A1", and see if there are changes to the arrow position of the control. (There should be, otherwise, re-do the process again).

In order to uninstall the control, you have to type "regsvr32 /u ActiveXArrow.ocx" in the command prompt described in step 2..

Using the code

Before talking about the code, I would like to introduce the structure of the whole control first. When you open the project using VC++ 6.0, you will find that there are a lot of classes. For those who are new to ActiveX, it may look strange. In fact, there are only three main classes that implement the main features of the controls. They are "CActiveXArrowCtrl", "CArrowObj", and "CPieForm".

CActiveXArrowCtrl is the main class to handle the drawing of the control. You can find there is a member class function:

Of course, it is not just that simple. GDI+ will draw the image in EMF format, but that is not compatible with the printing structure of Excel (as Excel can only recognize image in WMF format). So, we have to find a way to convert the GDI+ image to WMF format. The full code of Draw function can be found below:

How to use GDI+

When I first designed the projects, I was thinking which graphic library I should use. In using MFC, DirectX, OpenGL, GDI, GDI+ can be employed. Finally, I chose to use GDI+ as what I wanted to show to the user at most just a transparent arrow. In using GDI+, I am benefited by one of the CodeProject contributors (Author: Ryan Johnston, see article). He does all the trouble work in initializing the GDI+ library for us. Thanks a lot. In initializing the GDI+ library, what we have to do is just create a member variable using his class and call just a few lines of code to do the initialization, that is, see below:

How to create ActiveX application using MFC 6.0

It is simple, I am benefited from a book called "ActiveX Inside Out" (something like that, I forgot the exact name). It is a very good book. For anyone who want to learn ActiveX, I recommend this book. OK, below are the steps to create an ActiveX application using MFC 6.0. The procedure is specific to this application only, for other kinds of ActiveX controls, there may be something different.

Open VC++ 6.0.

Click "File"->"New", and choose "MFC ActiveX Control Wizard" in the project list, type the project name. In here, let's set the name as "MyFirstTest".

On the first popup wizard, just click "Next", leave all the things to be default.

On the next wizard, you will see a button "Advanced", click on it and there will be a popup dialog, and you should enable the option "Flicker-Free Activation".

Click Finish. You did it! ... Try to compile the projects first.

Run Excel and do the same steps as creating my control to create your "MyFirstTest" object in Excel. You will see an ellipse drawn on the Excel sheet.

How to add properties and link cell to Excel

If you succeed to create your "MyFirstTest" ActiveX control, you can try to right click the control and choose "Properties". You will find that there are some default properties. However, you will never see the properties "LinkedCell" as shown in my control's properties list and also other properties like "Max", "Min" .. etc. In order to add custom properties, you have to follow the steps below:

Press "Ctrl-W" to popup the class wizard.

Choose the page "Automation".

Click on the button "Add Property ..."

On the popup dialog, in the field "External name", try to type "Max". In the field "Type", choose it to be type "long".

In the middle of the dialog, choose "Get/Set Method" rather than the default "Member variable".

Click OK.

Then, you will see there is a new property added. You can try to create your new control in Excel and right click to see if the new property was added. However, you may find that the property was not added in Excel Properties list, why?? It took me a week to find the reason. The answer is that, MS Excel will always save a copy of Properties list to somewhere in your hard disk; whenever you create a control, it will try to load the Properties list from that location rather than from the control. Therefore, you have to delete the file. The file is always located in "C:\DOCUME~1\LIU\LOCALS~1\Temp\Excel8.0\". You can find that there are a lot of files with extension ".exd". You have to delete the .exd file with the name same as your control name. Notice that you don't have to delete it every time, just in case you have new properties added, you have to do such an action in order to allow Excel to load Properties list properly. By using the same way, you can add properties that can link to the cell of the Excel. Following extra steps have to be taken:

Try to add one more property named "Value" of type "long" by following steps 1 to 5. Try to select the property and click the button "Data Binding ...", there will be a dialog popup, try to enable "Default bindable property".

Recompile the project and remember to delete the corresponding .exd file.

Open Excel and create the control. Have a look on the Properties list, a new property was added, it is the "LinkedCell".

It is finished. So, whenever there is change of property's values (for example, the "Value" property), the Set method will be called (in fact, the Set method is a callback function). Therefore, the programmer should try to write code to handle the change of properties' values so that the drawing can be updated live. In doing so, we can just add a code "InvalidateControl()" to force the control to redraw itself by applying all the new values.

How to use what we learnt in secondary school mathematics (Geometry, I studied in Hong Kong)

OK, it is a point of interest. I like mathematics and physics very much. However ... in HK, it is hard for me to choose the way of being a pure science student. The reason why I chose computing is that ... I even didn't know how to turn off a PC when I was in my last year of high school...

The circle above is the simple layout of the dashboard. What is interesting is that it will not always be a circle, it can be an ellipse when the user drags the control as a rectangle. So, by using a simple ellipse formula, we can calculate the "a" and "b" values and pass them to the GDI+ function to draw the ellipse.

For the arrow, there is a rule that the arrow angle θ is always kept constant. And for me, as a programmer, I have to know three points in order for the GDI+ function to draw an arrow. The three points are (Px, Py), two tangent points (Ux, Uy) (<- there are two points of Ux, Uy). So, what is the known value here? What is the unknown here?

Known Value:

Center Pt (Cx, Cy) // how to get Cx, Cy? So easy .. think about it .. hint is .. using simple geometry.

Vertex Pt (Px, Py) // how to get Px, Py? So easy, think about it, hint is .. using simple ellipse equation.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

I tried to google arround about the problem. Let me quote a comment from the Internet about this problem:

--------------------------------------------
This is typically the result after compiling the ocx/dll on a machine with later versions of critical system files than those on the target machine where you try to run (or even just register) it. The "usual suspects" include olepro32.dll, oleaut32.dll, and comcat.dll, but many others could be
implicated as well.

I suggest you do as we have, and set up a "clean" install of W95 on a machine where you install nothing (I mean NOTHING) that isn't actually needed by your application, and always do your compiles on that clean machine.

Jim Deutch
MS Dev MVP
----------------------------------------------

My suggestion is, try to compile the whole project in your machine or use the Microsoft "ActiveX Control Test Container" which is shipped with VC++6.0 to try again.

Please tell me any problem you meet again, thx a lot. I am using Win2000 also but didn't get any problem.