Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Texas, USA
    Posts
    208
    Thanks
    0
    Thanked 1 Time in 1 Post

    Conditional Custom Functions (2000/2002)

    The subject line may not be the best description but here is what I mean. If I have a custom function that connects to a back end database based on a test value and then performs calculations and returns the results of those calculations as the result of the function then how can I set up the function so that the current cells value is left alone if the variable indicating the connection to the database is false? An example is that I have a custom function that returns a value from a database if I am on line with the database engine. One that function has been evaluated and the value stored in the cell I then send the xls file to a user that does not have access to the database. If she opens the report and the function is called again I want it to leave the current value in the cell alone since the user is not connected to the database.
    Every attempt by myself so far has resulted in circular reference errors.

    Thanks

  2. #2
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Custom Functions (2000/2002)

    I would try the following methods:

    1. Insert an End statement in the function when you determine you do not wish to change the value.

    2. Have the function save, and if necessary, restore the current value.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Custom Functions (2000/2002)

    Puting the End statement in will yield VALUE# as the function's result.

    My guess is not to use a function called directly from a worksheet, but rather a normal sub that fills a (named) range when connect is successful. If no success, just don't do anything...
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Conditional Custom Functions (2000/2002)

    Hi Ed,
    If you're only dealing with one or two values, I would define names in the workbook and have the function assign values to the names on successful connection, otherwise simply return the current value of the name.
    [Later edited as I remembered a function on a worksheet can't assign a value to a name <img src=/S/doh.gif border=0 alt=doh width=15 height=15>]
    You could have the function return either the new value to the cell or the existing defined name value and then have a workbook event write the new value to the name in question.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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