Complex Variables 1

[ESPcomposites]

Does anyone have code, or know of public domain code, that will allow use of complex variables via VBA?

I can also do it with the analysis toolpak, but am looking for a pure VBA solution.

Brian

http://www.espcomposites.com

 

[electricpete]

Regarding tool-pak - The tricky thing when you do it with the toolpak is that the variables are of type “string”.
I can understand why you want to avoid the tool-pak – tough to set up and tough to share spreadsheet.
I think Alg-lib will do what you want.

Alglib is a free math package has a complex data type and set of complex arithmetic. The specific module that contains all that is called AP.BAS. I think it would be fairly straightforward to load that although I remember when I started using their functions it took awhile to figure out which modules were needed. The advantage is there is a large library of other math functions and routines you may be interested in. See Doug / IDS' page or posts for more info on Alglib.

It is not that hard to program the basic complex operations in vba from scratch either.

=====================================
(2B)+(2B)' ?

 

[IDS]

Following Pete's post, I have written a spreadsheet with User Defined Functions to allow the Alglib complex number functions to be called from the spreadsheet (took a couple of hours, including the blog post). More details and download file from here:

http://newtonexcelbach.wordpress.com/wp-admin/post.php?post=1824&action=edit&message=6

If you don't need to use the routines as User Defined Functions in the spreadsheet you can just import the AlgLib ap.bas module into a VBA module and call them direct using the Complex data type where required.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[electricpete]

If I try to make a nested call, I get an error.

For example in you Sheet1 if I highlight 2 cells and enter
=AL_Conj(AL_C_add($B$5:$C$5,$B$6:$C$6))
followed by ctrl-shift-enter,
then I get #Value

I think the function Vara2Complex is not equipped to react properly when the argument passed into it already datatype complex.


=====================================
(2B)+(2B)' ?

 

[electricpete]

I think the function Vara2Complex is not equipped to react properly when the argument passed into it already datatype complex.

No that's not it. I see you have already modified the function so they return an array instead of complex (of course... otherwise it couldn't return to a spreadsheet). There must be some other reason for the error.

=====================================
(2B)+(2B)' ?

 

[IDS]

Pete - the problem was that the Vara2Complex function assumed a range or 2D array, but the ALGLIB functions return a 1D array. I have fixed it now; you can download from:

http://interactiveds.com.au/software/AL-Complex.xls

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[electricpete]

Thanks. I think the one you linked is still the old version ? (same Vara2Complex function and same problem)

Also for the benefit of the forum, the link you posted above goes to an editing page which is not directly accesible without a username/password. The link should be:

http://newtonexcelbach.wordpress.com/2010/08/11/alglib-complex-number-functions/

=====================================
(2B)+(2B)' ?

 

[IDS]

Pete - I'm not sure why you got the old one, maybe the link was connecting to a cached page or something. I've just followed the blog link and it now links to the revised version. I have added a revision record and deleted the blank sheet, so you will know if you are getting the latest.

The blog download link just links to the address I gave in my previous post here, so I'm not sure why it asks you for a password from one but not the other.

Anyway, let me know if you still have a problem.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[electricpete]

Thanks - it works now.

The link that doesn't work is the one posted 10 Aug 10 22:06 ... it has "edit" and "admin" in the address so I assume it is limited to people with access to edit the page. But I found it by searching "complex" at your main page.

On your page you said "The use of strings to represent complex numbers seems to me to be cumbersome and inefficient, and for Excel versions before 2007 it requires the installation of the Analysis Tool Pack."

I agree and have expressed similar sentiment. But one thing occurs to me - it is a little easier to manage formulas treating complex numbers as one cell than 2 cells, so I can see why they made that decision.... but not a big deal either way.

I have a spreadsheet done with tool-pak that I'd like to share in a format that does not require tool-pak installation, so this will be useful.



=====================================
(2B)+(2B)' ?

 

[IDS]

The link that doesn't work is the one posted 10 Aug 10 22:06 ... it has "edit" and "admin" in the address

Ah, yes that a Wordpress edit page.

Don't know why I posted that one.

Glad it's working now anyway.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/