expressions, in the same way that many of Excel's built in functions

can be used? If it is a simple example would be much appreciated.

to reference functions in a different project/add-in, from VBA Editor

you must go to Tools|References and check the source (adding it to the

list if necessary).

Jerry

> expressions, in the same way that many of Excel's built in functions

> can be used? If it is a simple example would be much appreciated.

able to speed up the procedure.

> to reference functions in a different project/add-in, from VBA Editor

> you must go to Tools|References and check the source (adding it to the

> list if necessary).

> Jerry

> > expressions, in the same way that many of Excel's built in functions

> > can be used? If it is a simple example would be much appreciated.

Checking the Add-In makes the functions available to Excel, but does not

make them available to VBA. As previously stated, Tools|References

from the VBA editor makes the functions available to VBA but does not

make them available to Excel. You must do both if you want them

available both places.

Jerry

> able to speed up the procedure.

>>to reference functions in a different project/add-in, from VBA Editor

>>you must go to Tools|References and check the source (adding it to the

>>list if necessary).

>>Jerry

>>>expressions, in the same way that many of Excel's built in functions

>>>can be used? If it is a simple example would be much appreciated.

Thanks again for your interest. I hadn't checked the add-in from

within the VBA editor, I've now done this but still can't get it to

work. I get the following error message:

Sub or Function not defined

> Checking the Add-In makes the functions available to Excel, but does not

> make them available to VBA. As previously stated, Tools|References

> from the VBA editor makes the functions available to VBA but does not

> make them available to Excel. You must do both if you want them

> available both places.

> make them available to VBA. As previously stated, Tools|References

> from the VBA editor makes the functions available to VBA but does not

> make them available to Excel. You must do both if you want them

> available both places.

> Jerry

> > able to speed up the procedure.

> >>to reference functions in a different project/add-in, from VBA Editor

> >>you must go to Tools|References and check the source (adding it to the

> >>list if necessary).

> >>Jerry

> >>>expressions, in the same way that many of Excel's built in functions

> >>>can be used? If it is a simple example would be much appreciated.

1. Using worksheet function "TREND" in VBA and assigning returned values to VBA variables?

Bonjour,

I have the following problem:

In my VBA code, I would like to use the worksheet function TREND in my

VBA code and assign whatever the function returns to a scalar or an

array VBA variable.

When I use a worksheet function such as "Application.MIN" that returns a

scalar, it works as expected. It's only when I try to use functions such

as "Application.TREND" or "Application.LINEST" that return several

values at once that I have problems.

Below is the code that refuses to work:

'--------------------------------------------------

Function myTrend() ' NOT WORKING

Dim returnedValues As Variant

Dim myArray as variant

Dim myArg as String

myArg = "A1:D1,,5" 'hard-coded argument for testing purposes

returnedValues = Application.Trend(myArg)

myArray = Array(returnedValues) 'here I am trying to coerce

' the returnedValues into an array

myTrend = myArray(1) 'trying to return the first element of

'the array. I get #NAME? as a result...

End Function

'---------------------------------------------------

Now, if in a worksheet, I put the formula "=TREND(A1:D1,,5)" in a cell,

I get the correct answer. Why can't I retrieve that same return value

using VBA???

Thank you for any help you can offer, I have been breaking my head at

this problem for several days now.

Carlos Tremblay

tremblac/at/fonorola/dot/com

Systems Analyst

Fonorola Inc., Montreal, Quebec

2. cmex and standard I/O functions

3. Excel 5 - How to use Add-Ins Analysis functions in VBA

4. script or command to check...

5. Using user-defined functions as add-ins?

6. ld options for static vs. dynamic link

7. Using a function in index expression with CDX driver

8. Using the trim function with VBA

10. Using a standard Excel Function in VBA

12. Getting Subdirectory names using the Excel 97 VBA Dir(path, type) function

5 post • Page:**1** of **1**