Tangent line to two circles with VBA

[excelvraag]

Dear VBA profs,

I've a small issue. I created an excelsheet which works great and now i'm facing a small problem.

I have 2 curves. One with fixed X, Y and radius and the other one is variable in all those settings.
See attachment.(i've removed the confidential data but it's enough for now)

I've already searched the internet and forum and i found this website which shows a VBA code:

http://www.vb-helper.com/howto_circl..._tangents.html

How can i enter this code into my sheet?

Regards,

Erwin

[patel45]

are your curves circles with known radius and center ?

[excelvraag]

Yes, see my attached file. Both circles are show in the graph.

[patel45]

then you have to understand very well that code, for sure you are much better than me

[MrShorty]

How can i enter this code into my sheet?

I can't view the xlsm file, so I may be out in left field. Shouldn't it be as easy as:
1) open VB editor
2) Insert module in project
3) Copy/Paste code from website to module
4) If needed, clean up any extraneous characters/carriage returns/etc. or missed characters from the pasted code.
5) To use as a UDF in a spreadsheet, call function with =functionname(function,arguments)

[excelvraag]

Mr, Shorty, Do i need to save it in other format for you?

I opened VB editor and copy paste the code. But i can't figure out how to use the code.

What are the arguments that i need?

Please Login or Register  to view this content.

I need to fill in =FindCircleCircleTangens(1?,2?,3?,4?,5?,6?) 6 values i suppose....because each circle has 3 values X,Y and Radius.

I'm dying!

[MrShorty]

I haven't gone through the code in any detail. Are you trying to call this from a spreadsheet or from another VBA procedure? The code itself contains examples of calling one function procedure from inside of another function (even this function calling itself), so you should be able to see several examples of how to call one function from another function by studying the code itself.

If you are trying to call this function from a spreadsheet, the function call should basically look like calling any of Excel's built in functions with multiple parameters.

In either case, as with built in Excel or VBA functions, one of the important parts of using any function is understanding what the arguments are, their data types, and other necessary information about the arguments to allow you to successfully call the function. Unfortunately, scanning through the website and the code, I don't see any good description of the arguments. In addition, several of the arguments are typed as a user-defined type (which I've never used in a UDF so I'm not familiar with the nuances around using them) with no description that I can see of what the user-defined type should be. Next steps I see in getting this to work:

1) You are going to have to wade through the website to make sure you copy all of the other functions that this function depends on.
2) You are going to need to figure out what a PointF data type is supposed to be. Perhaps you can find the definition on the website somewhere, or you can infer what it is supposed to be by how it is used in this function.
3) You are going to have to play around with the nuances (and whether it even works or not) of using a user-defined type as an argument in a UDF (assuming you want to call this from a spreadsheet).

[excelvraag]

I haven't gone through the code in any detail. Are you trying to call this from a spreadsheet or from another VBA procedure? The code itself contains examples of calling one function procedure from inside of another function (even this function calling itself), so you should be able to see several examples of how to call one function from another function by studying the code itself.

If you are trying to call this function from a spreadsheet, the function call should basically look like calling any of Excel's built in functions with multiple parameters.

In either case, as with built in Excel or VBA functions, one of the important parts of using any function is understanding what the arguments are, their data types, and other necessary information about the arguments to allow you to successfully call the function. Unfortunately, scanning through the website and the code, I don't see any good description of the arguments. In addition, several of the arguments are typed as a user-defined type (which I've never used in a UDF so I'm not familiar with the nuances around using them) with no description that I can see of what the user-defined type should be. Next steps I see in getting this to work:

1) You are going to have to wade through the website to make sure you copy all of the other functions that this function depends on.
2) You are going to need to figure out what a PointF data type is supposed to be. Perhaps you can find the definition on the website somewhere, or you can infer what it is supposed to be by how it is used in this function.
3) You are going to have to play around with the nuances (and whether it even works or not) of using a user-defined type as an argument in a UDF (assuming you want to call this from a spreadsheet).

1) I did, if you go to http://www.vb-helper.com/howto_circl..._tangents.html you see ''download'' at the bottom of the page.
the you see the following files:
CircleStuff.bas
howto_circle_circle_tangents.html
PointF.cls
Form1.frm
howto_net_find_circle_circle_tangents_fig1.png
howto_net_find_circle_circle_tangents_fig2.png
howto_net_find_circle_circle_tangents_fig3.png
Project1.vbp

2)The pointF as above can be pasted into excel as a ''classmodule''. When i open it it shows:

Please Login or Register  to view this content.

3) Ok i will do that, but i need a certain ''direction'' to play around.

Thanks

[MrShorty]

3) Ok i will do that, but i need a certain ''direction'' to play around.

I wish I could give a good direction. All I know is that, a few years ago, I was writing a couple of UDF's and thought that a user-defined type might be a useful tool for passing information between VBA and Excel. All I can recall from that exercise was that I quickly abandoned the idea of using a user-defined type for arguments being passed to a UDF. I don't remember if I determined that it was impossible, or if I determined that it was too much more difficult than passing an argument as an array of doubles or as a range object. So, at this point, my first question is whether VBA can read a set of Excel cells and put the data into a user-defined data type. As I don't recall anything in the documentation I have that even addressed this question, I really don't know what to tell you.

The easiest approach might be to set up a simple UDF that calls this procedure with additional code for putting Excel's double type data into the user-defined types. something like this pseudocode:

Please Login or Register  to view this content.

In writing the procedure here, I would guess that the author(s) at VB helper are using VB as a stand alone programming language. They may not have considered how this VB code would be used inside of Excel (or other application).