Problem with circular reference in a custom function

**mongoose36** · 01-16-2014, 11:27 AM

Hello all!

I have a custom function that is giving me a circular reference error. Below is the function... The function detects if a certain text is in the cell and then does some simple computation. The function is in cells in the M column and is fired by entering a certain code in the C column. What I'm having problem with is the use of Range("M55").Value... There is a circular reference here because the formula in this cell is affected by what gets computed in the column above. But I was hoping that the VBA would take a snap shot of the cell and do the calculation, but that is not working out. Also when I try to see what the contents are of Range("M55").Value (using MsgBox) it is empty even though on my spreadsheet the value is 37. I thought of copy and paste special values only into an adjacent cell, but couldn't get that to work either. Any help is appreciated!!!! I can upload a stripped down copy of the file if need be. Thank you in advance!

Please Login or Register  to view this content.

**MrShorty** · 01-16-2014, 12:47 PM

It will probably be difficult to provide any specific recommendations, without seeing this function in the context of a spreadsheet. That said, a couple of thoughts, assuming this is intended as a UDF called from a spreadsheet cell:

1) A UDF should receive all of the information it needs from the spreadsheet through the argument list. WT should be added to the argument list instead of being hard coded into the function. The function call could then include an absolute reference to M55 ($M$55) for this argument. I also note a lot of other cells being referenced in the commented out sections. I would recommend rethinking this so that everything the function needs comes through the argument list.
2) If this function is being called from M55, then that is where the circular reference is occurring. Perhaps it will work as you expect if you enable iteration with an appropriate number of maxiterations (in the Excel Options dialog). I would do this carefully, because any time you program something "recursively" like this, you need to think through exactly what it is going to do, how you are going to control for infinite loops, and otherwise control it so that it performs the desired number of iterations.

**mongoose36** · 01-16-2014, 01:02 PM

MrShorty,

Thanks for your reply! I have uploaded a sample workbook. Let me know what you think. I am a VBA beginner and would benefit from any advice or correction of what I am trying to do.

The custom function is currently just in cells M35:M50. It is fired by entering an "s" in the Special Code column (column C).

Test.xlsm

**MrShorty** · 01-16-2014, 01:36 PM

What I see: M55=a sum involving M35:M50. through the hard coded references in the function, each entry in M35:M50 is dependent on the sum in M55.

I think my first suggestion is to think through the flow of information in these time cards and figure out how the logic should flow. I don't do time card programming, so I'm not really sure, but it doesn't seem like there should be any need for circular logic in this kind of thing. Can you explain how the circular logic part of this spreadsheet should work?

**mongoose36** · 01-16-2014, 02:50 PM

Ok... There was a slight issue with the test file I uploaded. I have corrected to so hopefully my following explanation makes more sense.

Looking at the section "16 - End of Month" You will see the fictitious time entered. The formula's in Row 55 Columns I, K and L only calculate the total hours (regular, holiday, Sick, and Vacation) that are entered in the work week (B55 - D55)... In this case December 21 - December 27. The issue I'm having is that lets say the individual was sick on the 24th. They would enter an "s" in cell C43. Normally when one is gone for a whole day you would expect to take 8 hrs of sick leave, but because the total hours for the week so far is 33. They would only take 7 hrs for a total of 40 hrs worked + sick.

Here is where the circular reference occurs. The formula in M43 calls the custom function which I want to do the following

Check the value of M55 (that week's total) and see if adding 8 to it would put it over 40 and if so
Subtract 40 from the weeks total to get the amount of sick time to take

Obviously once the sick time has been entered the week total value would readjust to 40

I had found a way around this issue potentially by placing the Special Code Column in a worksheet_Change sub, but I couldn't get that to be dynamic.
Example 2.xlsm

**MrShorty** · 01-16-2014, 04:13 PM

So, if I may re-write the logic (in the form of one of those story problems from algebra), the real logic is:
"Anna works 3 11 hour days and 1 4 hour day in a given week and takes one day off sick. If Anna always works a 40 hour work week, how many hours of sick leave should Anna use?" How would you solve this? 40-(11*3+4)=3 hours. I would suggest a similar approach in your spreadsheet. Assuming she will never ever work anything but a 40 hour work week (at least on weeks that she uses sick leave), I would do something like:

=if(isblank(C40),"",40-I$55)

Because there will sometimes be weeks where multiple sick days are taken, and I assume you don't want to assign more than 8 hours/sick day, I would probably extend the above formula with something like =if(isblank(c40),"",min(40-sum($c$40:c40),8)) (note the use of the absolute reference to make it a running total as the cell is copied down). This will cover situations where Anna works 25 hours and takes two sick days -- the first sick day will be assigned 8 hours and the second sick day will be for 7 hours.

If you need to code that in VBA instead of a spreadsheet function, it should be easy to convert to VBA syntax. The idea here is to use algebra (and you told your algebra teacher you would never use it in real life

) to avoid the circular logic/circular reference thing all together.

**mongoose36** · 01-16-2014, 04:26 PM

Ok I see the logic... I think you meant the formula to be

Please Login or Register  to view this content.

Rather than

Please Login or Register  to view this content.

However, the time card is not simply a summation of the total sick hours for the whole pay period. Instead I need to factor in the work week. So in other words in any given work week (Sun - Sat) if Anna is taking sick leave her total hours will max out at 40. So in the case of December 21nd through December 27th -- if she takes sick time she will only get 3 hours because she has already worked 37, BUT....

This does not translate to the next week. December 28 on... if she takes Monday December 29th as a sick day it will again be 8 hours etc.....

**mongoose36** · 01-16-2014, 04:31 PM

Going back to my original custom function.

What about using an array and sum product formula to actually get the total hours worked for any given week within that pay period.

E.g....

If the date to the left of the cell I enter the code in equal to or within the range of dates (either B54 -- D54 or B55 -- D55 etc...) then take those dates (e.g. B55 and D55) and do a sumproduct of the total hours, sick time, vacation time and holiday pay for the dates in that range in Column B.

This is essentially what M55 is, but it would avoid the circular reference issue because the hours would be calculated at their source. Every time.

**MrShorty** · 01-16-2014, 05:10 PM

further correction

Please Login or Register  to view this content.

You are correct, it would need something to account for multiple weeks on the same time card. I would probably just manually adjust the absolute references (i$55 to i$54 or i$56 and m$35 to m$40 or m$47) to point to the correct cells. More elaborate solutions would use a lookup or index function to check the correct sums.

I guess I would have to see how you plan to implement this last thinking. It still sounds circular to me (sum all hours for a given week, then adjust sick hours until total equals 40). It can work, if you think it through right.

**mongoose36** · 01-16-2014, 05:11 PM

I got this to work, but only if all the offsets have a number value. Is there any way to skip the offset if the value = ""

Please Login or Register  to view this content.

**mongoose36** · 01-16-2014, 05:14 PM

Originally Posted by MrShorty

further correction

Please Login or Register  to view this content.

You are correct, it would need something to account for multiple weeks on the same time card. I would probably just manually adjust the absolute references (i$55 to i$54 or i$56 and m$35 to m$40 or m$47) to point to the correct cells. More elaborate solutions would use a lookup or index function to check the correct sums.

I guess I would have to see how you plan to implement this last thinking. It still sounds circular to me (sum all hours for a given week, then adjust sick hours until total equals 40). It can work, if you think it through right.

This could work except the whole workbook (which was to large for me to upload) is dynamic. It's meant to be used over and over again as a template and the dates automatically adjust depending on the year...

I could email it to you if you want to look at the whole thing.

**mongoose36** · 01-16-2014, 05:17 PM

I guess I would have to see how you plan to implement this last thinking. It still sounds circular to me (sum all hours for a given week, then adjust sick hours until total equals 40). It can work, if you think it through right.

you are right!! It is circular because the sick hours, vacation hours will constantly be changing until the last time is entered. At leas I think....

**MrShorty** · 01-16-2014, 05:46 PM

Originally Posted by cplettner

I got this to work, but only if all the offsets have a number value. Is there any way to skip the offset if the value = ""

Please Login or Register  to view this content.

Is empty string "" the only choice for blank? One solution I see is to have those cells be blank or 0, so VBA will see their value as 0 (there are a couple of different ways to hide 0 values, if you don't want to see the 0's).
The only other solution I see would be to include a check for ""?

Please Login or Register  to view this content.

**mongoose36** · 01-16-2014, 05:49 PM

Ok thanks!!!! An additional question!. How much does this bog down excel to have loops within loops

**mongoose36** · 01-16-2014, 05:52 PM

Please Login or Register  to view this content.

Sadly this doesn't work what am I missing?

**mongoose36** · 01-16-2014, 05:53 PM

Never mind I see my mistake!

**mongoose36** · 01-16-2014, 05:58 PM

Finally success!!!! This works for the small test area

Please Login or Register  to view this content.

I had to exclude the day that the time was being calculated for.

**mongoose36** · 01-16-2014, 06:15 PM

I got this to finally work:

Please Login or Register  to view this content.

However when I put the loop into my larger function. It still gives me a circular reference:

Please Login or Register  to view this content.

**mongoose36** · 01-16-2014, 07:18 PM

The following Function works, but I would now like to fire the function from within a Workbook_Change sub on each sheet. First the is the Functions Second is the Private Workbook_Change Sub

I'm not quite sure how to get this to work. See rg5 section... I have the Public tar as Target because I want to be able to clear the target cell if needed.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Problem with circular reference in a custom function

LinkBack

Thread Tools

Rate This Thread

Display

Problem with circular reference in a custom function

Re: Problem with circular reference in a custom function

Re: Problem with circular reference in a custom function

Re: Problem with circular reference in a custom function

Re: Problem with circular reference in a custom function

Re: Problem with circular reference in a custom function

Re: Problem with circular reference in a custom function

Re: Problem with circular reference in a custom function

Re: Problem with circular reference in a custom function

Re: Problem with circular reference in a custom function

Re: Problem with circular reference in a custom function

Re: Problem with circular reference in a custom function

Re: Problem with circular reference in a custom function

Re: Problem with circular reference in a custom function

Re: Problem with circular reference in a custom function

Re: Problem with circular reference in a custom function

Re: Problem with circular reference in a custom function

Re: Problem with circular reference in a custom function

Re: Problem with circular reference in a custom function

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Circular reference problem

Offset function problem - returns circular reference

Tracing a Circular Reference in Custom Formula

Problem with Circular Reference

Circular reference problem

Bookmarks

Bookmarks

Posting Permissions