Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Location
    Charlotte, NC
    Posts
    79

    Unanswered: Using complex numbers in VBA

    Hi

    I am trying to perform some math in VBA which is failing. I am creating a user-defined function for someone that takes a number of cell references as input, performs some math, and then outputs the final figure. I want them to be able to call this function from a cell so that the output value is placed in the cell.

    This all works fine until near the end of the code where I am trying to get the cube root of a number that can be negative. It is possible to get the cube root of a negative number, and if you do this in a cell

    i.e. -1 ^ (1/3)

    you get (1.00), which is the real component of the complex number.

    However, if I try it in VBA and attempt this code:

    Code:
    T = CDbl(Application.WorksheetFunction(IMREAL(T ^ (1 / 3))))
    I get the error:

    "Sub or Function not defined"

    for the IMREAL function, so I don't even know if this will work.

    Does anyone know if there is a problem using these functions (part of the Analysis Toolpak) in VBA? Also, does anyone know if this will work?

    Thanks
    Make something idiot proof and someone will make a better idiot...

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    Quote Originally Posted by robojam
    Hi

    I am trying to perform some math in VBA which is failing. I am creating a user-defined function for someone that takes a number of cell references as input, performs some math, and then outputs the final figure. I want them to be able to call this function from a cell so that the output value is placed in the cell.

    This all works fine until near the end of the code where I am trying to get the cube root of a number that can be negative. It is possible to get the cube root of a negative number, and if you do this in a cell

    i.e. -1 ^ (1/3)

    you get (1.00), which is the real component of the complex number.
    Just tyring this in Excel, I get -1 for the cube root, not 1.

    However, if I try it in VBA and attempt this code:

    Code:
    T = CDbl(Application.WorksheetFunction(IMREAL(T ^ (1 / 3))))
    I get the error:

    "Sub or Function not defined"

    for the IMREAL function, so I don't even know if this will work.

    Does anyone know if there is a problem using these functions (part of the Analysis Toolpak) in VBA? Also, does anyone know if this will work?

    Thanks
    Not sure, but it appears that you have T on the left side of your equation and T on the right side of the equation. Don't think that will work in this case. Is there another way to reference the cell so that it isn't duplicating itself?
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Feb 2004
    Location
    Charlotte, NC
    Posts
    79
    Thanks for the reply. I don't know where the problem was as I was calling the function from a cell and therefore it doesn't break into the editor.

    It doesn't matter if the value is on the right of the equation, as this is a common practice and shouldn't matter, but I found another solution.

    I took the absolute value of T and then took the cube root and multiplied it by the sign of T.

    Thanks anyway.
    Make something idiot proof and someone will make a better idiot...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •