Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Jun 2006
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Add-in (2000 sr-1)

    I have a worksheet that must be distributed to about 200 employees. However, some important formulas won't work on any of their machines without having them enable their "Analysis Toolpack" add-in. Other than providing instructions on how to turn it on, Is there an easier way to do this? Perhaps a macro that forces this as soon as they open the spreadsheet? Perhaps something our IT department can do?

    Thoughts??

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Add-in (2000 sr-1)

    You can put code in the Workbook_Open event procedure in the ThisWorkbook module:

    Private Sub Workbook_Open()
    With Application.AddIns("Analysis Toolpak")
    If .Installed = False Then
    .Installed = True
    Application.Calculate
    End If
    End With
    End Sub

    This assumes that the Analysis Toolpak is present on each PC, and that macros are enabled.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Add-in (2000 sr-1)

    One possibility is to only use formulas that do not require the analysis toolpack

    You could also create custom ones if simple workarounds are not found and add them as user function in the workbook. This would give the macro warning, but you do not seem worried about that.

    Steve

  4. #4
    Star Lounger
    Join Date
    Jun 2006
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add-in (2000 sr-1)

    I was trying to find a workaround for the EOMONTH(date, 0) function. Basically, I want the date to be the last day of the month for watever date has been entered. If someone enters 9/6/06, I want the date to actually be 9/30/06. The user never sees this, but is important in other formula calculations. If you can think of some formulation to achieve this without the Analysis Toolpack, that would be great!

  5. #5
    Star Lounger
    Join Date
    Jun 2006
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add-in (2000 sr-1)

    Thanks Hans! I had already considered the assumption you mentioned. Everyone is using the same version of Excel and I can tell them to click Enable Macros upon opening. However, it has occurred to me that since I'm dealing with such a large volume of employees, it is quite possible someone has gotten in and "accidentally" changed their security settings to High and will not even get the prompt. Perhaps a workaround is the better approach... Time to dig into the closet of old math skills!

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Add-in (2000 sr-1)

    If you have a date in A1, the formula
    =DATE(YEAR(A1),MONTH(A1)+1,0)
    will return the last day of the month.

  7. #7
    Star Lounger
    Join Date
    Jun 2006
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add-in (2000 sr-1)

    Interesting... The 0 for the day parameter is not in the help file.

    Thanks!

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Add-in (2000 sr-1)

    It mentions that the 3rd parameter is the day.

    The "zeroth" day is just the day before the first day of the month.

    The "day" (like the other parameters) can be any number that will yield a valid date. The days can be negative, greater than 31etc. it just goes that many days from the the last day of the previous month. It is a calculation not requiring valid months (1-12) or days(1-31).

    Steve

  9. #9
    Star Lounger
    Join Date
    Jun 2006
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add-in (2000 sr-1)

    Too funny!! I knew you could do any number, positive, but never thought about 0 or negative! Thanks all!

Posting Permissions

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