View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Tom Ogilvy Tom Ogilvy is offline
external usenet poster
 
Posts: 27,285
Default 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?