+ Reply to Thread
Results 1 to 4 of 4

circular reference error in user defined function

Hybrid View

  1. #1
    Registered User
    Join Date
    05-11-2016
    Location
    Seattle
    MS-Off Ver
    MS Office for Mac 2011 Version 14.6.0
    Posts
    2

    circular reference error in user defined function

    Hello,

    I'm new to Excel and trying to figure out how to define my own function. I'm getting a circular reference error when I try to run my function, but I can't see any problem with the code. Any help would be very much appreciated! Thanks! Here is the code:
    ----------------------------------
    Option Explicit

    Function MELT(S As Double, albedo As Double, beta As Double, D As Double)
    Dim A As Double
    Dim B As Double
    Dim T As Double
    Dim sigma As Double
    Dim L As Double
    Dim dt As Double

    sigma = 5.67 * (10 ^ -8) 'stefan boltzmann constant (W m^-2 K^-4)
    T = 273.15 'surface temperature of ice during melt season (K)
    A = -3 * sigma * T ^ 4 'blackbody radiation constant (W m^-2)
    B = 4 * sigma * T ^ 3 'blackbody radiation constant (W m^-2 K^-1)
    L = 3 * 10 ^ 8 'Latent heat of fusion (J m^-3)
    dt = 60 * 60 * 24 * 31 * 3 'length of summer in seconds
    MELT = dt * (S * (1 - albedo) + (A + B * T) * (beta - 1) + D / 2) / L 'total melt = melt rate * length of summer in seconds
    End Function
    --------------------------

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,646

    Re: circular reference error in user defined function

    Can you post an example of how you are using the function in a cell?

    PS Can you add code tags when posting code?
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    05-11-2016
    Location
    Seattle
    MS-Off Ver
    MS Office for Mac 2011 Version 14.6.0
    Posts
    2

    Re: circular reference error in user defined function

    Thanks for the quick reply! I'm simply going to a cell and typing something like this: =MELT(300,.6,.5,100)
    Sorry if there is a very simple mistake I'm making.

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,646

    Re: circular reference error in user defined function

    When I try the formula posted in a cell I get a #VALUE! error, caused by this line which generates an overflow.
    dt = 60 * 60 * 24 * 31 * 3 'length of summer in seconds
    However I don't get a circular reference/warning.

    Are there any other formulas on the spreadsheet?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Passing a table reference to a user defined function?
    By WGAshmore in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-24-2012, 06:32 PM
  2. Replies: 1
    Last Post: 08-21-2007, 07:22 PM
  3. Worksheet_Change function and circular reference error message
    By nicole_2007 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-15-2007, 07:35 AM
  4. [SOLVED] #Name? Error in User Defined VB Function
    By George Nicholson in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 09-06-2005, 05:05 PM
  5. #Name? Error in User Defined VB Function
    By idgity in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 04:05 PM
  6. [SOLVED] #Name? Error in User Defined VB Function
    By idgity in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 04:05 AM
  7. [SOLVED] #Name? Error in User Defined VB Function
    By idgity in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 AM
  8. [SOLVED] #Name? Error in User Defined VB Function
    By idgity in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-30-2005, 05:05 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1