Help figuring out circular reference in function that works as sub?

Howdy,

I apologize for having zero VBA skills! I have cobbled together some code that works when I run it as a sub and add a line at the bottom to write the "SellPrice" value to the starting cell. But when I try to use it as a function that I just call from the spreadsheet itself I get a #VALUE error and a warning about a circular reference.

Note that if I run the function from inside the VBA editor it ends on the cell containing the correct value, but it doesn't return that value to the cell that calls the function. I'm baffled...

Thanks in advance for your help!

Please Login or Register  to view this content.

It is very difficult to diagnose this without knowing what your data looks like, and it would take too long to reverse engineer your code to figure it out. I will just make a few points, which may help.

You seem to assume that ActiveCell will be in Column E when the code is called, but the code doesn't enforce that. That suggests that you may have a problem with how you are using ActiveCell. ActiveCell is whatever cell the user has selected at the moment. That will cause all kinds of problems using it in a user-defined function called from a spreadsheet formula. Do you instead mean to refer to the cell containing the formula that calls this function? If you so you want to use Application.Caller instead of ActiveCell. I haven't changed that because I don't know what you have in mind.

The programming concept of a Function is that its purpose in life is to return a value. A Function can, of course, do other things, but we call these other things "side effects" and they are often undesirable. In your function, you keep changing the active cell. That is a side effect, and is also unnecessary. You don't have to change the active cell and operate on the active cell in VBA, you can operate directly on the desired cell.

I have declared CurrentCell to use to refer to the cell of interest, rather than activating another cell each time. That prevents distraction to the user and also speeds things up.

You also have a few other problems. For example

Range(ActiveCell, ActiveCell)

is exactly equivalent to the simpler

ActiveCell

You set StartingCell but never use it, so I have removed it.

You jump through hoops to set up rngBegin and rngEnd and when you can bake those into a single range that I have called rngLoop.

I don't know if this will solve your problem but it might, and it will certainly makes things easier to diagnose.

Please Login or Register  to view this content.