ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   VB won't recognize function in Analysis Tool Pak (https://www.excelbanter.com/excel-programming/297026-vbulletin-wont-recognize-function-analysis-tool-pak.html)

Barry Wolfson

VB won't recognize function in Analysis Tool Pak
 
I am running Microsoft Office 2000 professional edition. I wrote a VB macro for an excel spreadsheet. I need to use some functions like LCM (least common multiple) and GCD (greatest common divisor). I have an old Function Reference Manual which states that these functions may be accessible only by loading the Analysis Tool Pak. I did that and loaded the Analysis ToolPak, Analysis ToolPak - VBA, and Update Add-in Links. (Tools menu, then Add-Ins). The system asked for the CD rom which I insereted - so it looked like it did add it in. Unfortunately when I try to execute the macro I keep getting the message Run-time error '438'. Object doesn't support this property or method. When I click on the debug option the system points to the exact statement with the LCM function. Am I missing something? Any ideas



Tom Ogilvy

VB won't recognize function in Analysis Tool Pak
 
in the VBE you should see in the project explorer

atpvbaen.xls(ATPVBAEN.XLA)
funcres(FUNCRES.XLA)

The first shows Analysis toolpak - vba is loaded
The second show Analysis toolpak is loaded

If you don't see them go to Excel and in tools=Addins, select Analysis
toolpak and Analysis toolpak - vba

Now you can call those function using application.Run (as an example from
the immediate window)

? application.Run("ATPVBAEN.XLA!lcm",24,36)
72


vVal = application.Run("ATPVBAEN.XLA!lcm",24,36)

to reference a range

vVal = application.Run("ATPVBAEN.XLA!lcm",Range("A11"),Ra nge("A12"))

as an example.

--
Regards,
Tom Ogilvy


"Barry Wolfson" wrote in message
...
I am running Microsoft Office 2000 professional edition. I wrote a VB

macro for an excel spreadsheet. I need to use some functions like LCM (least
common multiple) and GCD (greatest common divisor). I have an old Function
Reference Manual which states that these functions may be accessible only by
loading the Analysis Tool Pak. I did that and loaded the Analysis ToolPak,
Analysis ToolPak - VBA, and Update Add-in Links. (Tools menu, then Add-Ins).
The system asked for the CD rom which I insereted - so it looked like it did
add it in. Unfortunately when I try to execute the macro I keep getting the
message Run-time error '438'. Object doesn't support this property or
method. When I click on the debug option the system points to the exact
statement with the LCM function. Am I missing something? Any ideas?






All times are GMT +1. The time now is 10:43 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com