Using @Risk functions in VBA expressions?

Using @Risk functions in VBA expressions?

Post by Alla » Tue, 01 Jul 2003 17:39:24




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.
 
 
 

Using @Risk functions in VBA expressions?

Post by Jerry W. Lewi » Tue, 01 Jul 2003 21:16:01



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.


 
 
 

Using @Risk functions in VBA expressions?

Post by Alla » Wed, 02 Jul 2003 01:59:45





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.

 
 
 

Using @Risk functions in VBA expressions?

Post by Jerry W. Lewi » Wed, 02 Jul 2003 11:29:54


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.

 
 
 

Using @Risk functions in VBA expressions?

Post by Alla » Wed, 02 Jul 2003 18:09:03


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.

> 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

9. Replace function using VBA

10. Using a standard Excel Function in VBA

11. Using Functions in VBA

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