Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel Toolkit Functions

    How can I use Excel Toolkit Functions in VBA code. The Add-ins collection only allows you to load the Add-in, but the functions, such as NetWorkDays, don't appear under WorkSheetFunction. Andy.

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Excel Toolkit Functions

    If you are referring to the Analysis ToolPak Add-In, there is a special VBA version (Analysis ToolPak - VBA) which you should install. If you go to Tools,Add-Ins, you should see it, just just make sure the box is checked. You will notice in the function wizard that 2 versions of the AddIn functions appear, one in caps the other (VBA) in proper case.

    Hope that helps,

    Andrew C

  3. #3
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Toolkit Functions

    Although there is the VBA Toolkit Add-in, I need to know how to refer to the functions in my code. For example, Application.WorksheetFunction.NetWorkDays(...)?

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Excel Toolkit Functions

    Try the Evaluate Method, e.g x = Evaluate("NetworkDays(Now(),Now()+100)"), which can be shortened to x = [NetworkDays(Now(), Now()+100)].

    Hope that helps,

    Andrew

  5. #5
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Toolkit Functions

    Cheers, this works Ok. However, I feel sure I should be able to reference the function directly, without creating a string. The function must reside in the Funcres.xla Add-in, although I don't know the name of the module. I'm look for something like 'Workbooks("funcres.xla")...NetWorkDays(..)'?!

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Excel Toolkit Functions

    Actually these functions are part of Atpvbaen.xla. Howe.ver there can be problems depending on the the version of XL you have.

    Check article <A target="_blank" HREF=http://support.microsoft.com/support/kb/articles/Q165/1/34.ASP?LN=EN-GB&SD=gn&FR=0>Q165134</A> at Microsoft's knowledge base, and see if it can help you to resolve the problem.

    Andrew C

  7. #7
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Toolkit Functions

    Sorted! I hadn't included a Reference to Aptbean.xla. By including this the Toolkit functions appear in the Object Browser. I don't then need to qualify the reference to the use the function(s). For example, 'MsgBox Networkdays(Now(),Now()+10)' works beautifully!! Cheers.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •