Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Feb 2001
    Location
    Glasgow, Scotland
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Setting a cell value (Excel 2002)

    I am trying to get round the limit of 7 if by creating a function. My problem is that when I try to set the cell to zero I keep getting an error. Have tried a number of different combinations to remove the error without success. The code also has a circular reference which I also find confusing.

    Cheers

    Angus

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Setting a cell value (Excel 2002)

    I see a number of problems with your function:

    1- At the end of the function code you fall right into the err_handler: code. You should put a:

    <code>
    Exit Function
    </code>

    statement in front of the err_handler: label.

    2- You only pass the function one parameter, and the code picks up a number of other values from the worksheet directly. This means that the function will only recalculate when the cell passed as the parameter is changed. If any of the cells that are picked up directly are changed, the function will not recalculate and the result will be incorrect until you manually force a recalculate. You should pass all values used in the function as parameters.

    3- You use a statement like this:

    <code>
    Set ActiveSheet.Range("start_cell").Value = 0
    </code>

    to try to return results from the function. There are two problems with this. First, Set is only used when assigning a value to an Object variable, and ActiveSheet.Range("start_cell").Value is not an Object variable. However, that is not the major problem. Functions are not allowed to change cells in a worksheet directly, only Subroutines (which are not executed from formulas on the worksheet) can directly change the value in a cell. Functions can only return a value to the cell formula which called the function. To return a value of zero to the formula that called the function, you would use:

    <code>
    True_Percentage = 0
    </code>

    4- in conjunction with 3, you should declare what kind of value the function will return like this:

    <code>
    Function True_Percentage(start_cell As Range) As Double
    </code>

    If the exact type can not be determined, then it can be declared as a Variant.

    I unfortunately do not have time at the moment to figure out what your code does and rewrite it. If the above does not solve all of your problems, come back with some more problems.
    Legare Coleman

  3. #3
    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: Setting a cell value (Excel 2002)

    Does the attached do what you want? I've added Legare's changes, altered the argument list as necessary and moved the formula into R2.
    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    Lounger
    Join Date
    Feb 2001
    Location
    Glasgow, Scotland
    Posts
    46
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Setting a cell value (Excel 2002)

    Thanks Legare your comments allowed me to clarify what i required to do and also helped me discover an error with the underlying spreadsheet calculation that i was trying to replicate with the function. It is good to know that a resource such as this exists and people are happy to share their knowledge.
    I attach the updated spreadsheet .

    Cheers

    Angus

Posting Permissions

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