Unable to access complex arithmetic functions in VBA for Excel 200

[jimthompson5802]

I'm trying to use the complex math functions (e.g., Complex(), ImSum,
ImProduct(), etc.) in VBA. However, I encounter the error message "Sub or
Function not defined."

In earlier verions of Excel (2000, 2003), I reference the Analysis ToolPak -
VBA (atpvbaen.xla) in the macro module and macro works correctly.

However, in Excel 2007 when I do the same thing I encounter the problem
described before. I specify Tool > Reference and click on atpvbaen.xls
entry. This code causes the problem
Sub mysub()
[a1] = Complex(1, 2)
End Sub

The error message "Sub or Function not defined." appears and the debugger
highlights the text "Complex".

Any suggestions will be appreciated. In particular, whatever code is
suggested needs to work in earlier versions of Excel.

TIA

Jim T

 

[Thomas Ramel]

Grüezi Jim

jimthompson5802 schrieb am 20.11.2008

I'm trying to use the complex math functions (e.g., Complex(), ImSum,
ImProduct(), etc.) in VBA. However, I encounter the error message "Sub or
Function not defined."

In earlier verions of Excel (2000, 2003), I reference the Analysis ToolPak -
VBA (atpvbaen.xla) in the macro module and macro works correctly.

In xl2007 those funktions were added to the Standard-Functions. You don't
need the reference to the Analysis Toolpack anymore.

However, in Excel 2007 when I do the same thing I encounter the problem
described before. I specify Tool > Reference and click on atpvbaen.xls
entry. This code causes the problem
Sub mysub()
[a1] = Complex(1, 2)
End Sub

The error message "Sub or Function not defined." appears and the debugger
highlights the text "Complex".

Try it with the following Code:

Sub mysub()
Range("A1").Value = Application.WorksheetFunction.Complex(1, 2)
End Sub



Mit freundlichen Grüssen
Thomas Ramel