circular reference within a cell using VBA

Don't VBA .Formulas have to be in double quotes?

It does not seem like VBA should be necessary for this. A couple of suggestions.

I don't like single cell circular references like this. I would spread this calculation out over two cells (don't worry, you have 1E6 by 16000 cells in a single tab, you are not going to run out of cells). In the first cell (A1), the formula would be something like =second cell (=B1). The second cell (B1) then become =1/(-2*LOG(k/D*3,7) + 2,51/(D39*WURZEL(A1+1E-300))))^2

As I noted in your previous thread (https://www.excelforum.com/excel-gen...ba-macros.html ), I usually include some kind of reset on error (usually an IFERROR() function) in my circular references/iterative calculations. I would try that. Something like =IFERROR(second cell,typical value for f) (=IFERROR(B1,0.5) maybe). I really don't know if 0.5 is a good typical value for f, but I assume that you know what f will typically converge to and can supply that value.

If you looked at the Newton-Raphson and other examples of circular references I have on the forum, I also frequently include some other reset toggle in my circular references, so I can reset the calculation manually. You may want to consider that as well.

I would try those two suggestions before resorting to some VBA trickery to get this circular reference to work.

moreover - if you use RC style of addressing, you probably shall use formular1c1 not just formula? and final comment square root in formulas is SQRT. so try:


And going back to basics: The situation you described: "but everytime i Change the constant, it Shows an error and i Need to go to the formula bar to hit enter again and the value appears again" shall not happen and would be worth to work on it.

Please Login or Register  to view this content.

PS. Note that I changed k to k_ - in my version of excel k is not valid name for name manager (Ctrl+F3). May be in your version of Excel (looking on a sqrt function name - you use a German one) - it is valid.

edit: started answering, and had to go out for few minutes, so my answer partially repeats earlier comments. As I am editing a post I'd add also more administrative comment - please use code tags - see rule 3 in https://www.excelforum.com/forum-rul...rum-rules.html and edit your post accordingly

As mentioned by MrShorty, I kinda have to use VBA since the bulk of the calculations are being carried out using vba.

If the bulk of the calculations are already being carried out in VBA, why not move this calculation into VBA as well? Then you will avoid these kind of issues with the Excel circular reference, and you also avoid the overhead and performance penalties whenver Excel and VBA "talk" to each other. It should be a relatively simple loop

Please Login or Register  to view this content.

I was under the impression that VBA was not previously part of the spreadsheet, and that you were introducing VBA in an effort to resolve the problems with the circular reference. If VBA is already being used for other calculations, I would be inclined to just perform this calculation in VBA as well. In some ways, I prefer to perform iterative calculations in VBA because I can get better control over convergence criteria and more easily test if the algorithm is diverging.

I think a "divide and conquer" strategy will be helpful here.

First observation: I thought that equation looked familiar: https://www.excelforum.com/excel-for...equations.html Even back then, a successive approximations algorithm like you are using was suggested and seemed to work just fine.

IMO, the core of the problem is the root finding algorithm for that equation, which seems to work sometimes and not work others. The first thing I would do is get the root finding problem solved reliably and robustly. Here's a quick sheet I put together. I don't know what values for k, d, or Re are typical for this problem, but it seemed to converge reliably for the few values I tested. You will need to test it with values you expect and know the result for to see if it will reliably work for your applications. I would not worry about the other parts of the problem until I was satisfied that the root finding algorithm was working.

the second part is how to fill in a table of different Re values. I thought that ChemistB's solution in the other thread was reasonable, but we can look at this part in more detail once you are satisfied with the root finding part.

The final part of the problem is to use VBA to build the spreadsheet. I admit that I am not good at this kind of VBA programming, since I prefer to simply open an old spreadsheet and edit it or create a template and open that file rather than use VBA to build a spreadsheet. I don't think there is much value in worrying about this part of the problem until you have the other two parts figured out. Of course, after getting the spreadsheet put together without VBA, you may decide that you don't need VBA to build the spreadsheet, and this part will get skipped.

Do you understand how this spreadsheet works? Test it for several different values of k, d, and Re. For which input values does it work, and which does it fail? Can you see how the "reset" column works to reset the loop?

I would probably nest the existing code inside of a For..Next loop (https://msdn.microsoft.com/en-us/lib.../gg251601.aspx ).
1) Count the number of worksheets (if you have not already) using the .count property https://msdn.microsoft.com/en-us/lib.../gg251601.aspx
2)

Please Login or Register  to view this content.