To use VBA Macros or not to use VBA Macros...

[saaadi1616]

Hi all,
I am currently working on creating an excel calculation tool for some scientific calculations. The basic design of the Excel Workbook is as follow.

Input: As an input, the user provides flow rate range i.e. assigns values to two specified cells (x and y) for example (10 and 12 or 10 and 100)
Output: Input range is made into a column with x being the first column cell value and y being the last column cell value with an increasing increment of 1. (For example for the range 10 to 12 will generate a column 10,11,12).

Problem: For the corresponding column values, the rest of the calculations are carried out that propagate along the row, calculating many other variables.(for example one set of calculations for 10,next for 11,next 12 and so on). Some of the variables even include circular references with in the cell and some use circular refernces among different sheets.

1)Everything should work fine and values should be calculated on their own but everytime i change the main range in the first sheet(two input variable x and y), cells having the circular reference provide error.
2)Also, for example i want the calculation for the range 10 to 15 so I will have 6 set of row. But next time if I input the range 10 to 12 so first 3 rows will give updated results but rows from the previous calculations show error whereas they should be empty.

I know its not big of a problem and may well easily be tackled using excel VBA. But I would appreciate any help in this regard.

Have a nice day
Cheer!!!

[ChemistB]

I'd need more information on your formulas and why some include circular references (maybe they can be avoided). Can you upload an example spreadsheet (Go Advanced>Manage Attachments)?

In general seems like this should be simple with formulas where the formula checks the cell first before performing calculations

A1 = Min = 10
A2 = Max = 15
B2 = "=A1"
B3 downward = IF(ROWS($B$3:$B3)<= $A$2, B2+1, "")
In C2 copied downward
=IF(ISNUMBER(B2), perform this calculation, "")
and so on.

[saaadi1616]

Thanks for the reply. My main problem is once a column is creating with start and end limit values (values input by user). I want excel to automaticaly perform the calculations (propagating along the row) against each perticular value of flowrange. So i want to generate new set of calculations once i enter a new flow range. (while completely getting rid of the old ones). But for this, I cant know for sure how many rows will a certain flowrange occupy hence error will come since not every cell will have a formula there.

I tried to create the sample sheet but its almost impossible to create a sample workbook since all every value is somehow linked to another and if i miss one, the whole workbook (containing 10 sheets) will give error in the cells where the values are supposed to be. Should I send the file anyway?

Sorry if i am not able to explain my problem in a better way.

[MrShorty]

I won't speak for ChemistB, but I tend to pass over large files that require a lot of reverse engineering in order to understand what is going on. If you decide to upload a large file, be sure to include good comments in the sheet explaining where things are working, where the errors are, where circular references are, etc. The more you can simplify the problem and explain the problem you are having, the more likely someone here will be able to make sense of it and provide some useful suggestions.

As ChemistB suggests, it is probably advantageous to try to eliminate circular references/logic from your spreadsheet. Circular logic significantly increases the complexity of spreadsheet programming. I recognize that sometimes this is not possible, because some problems require circular/iterative calculations.

Reading between the lines, it sounds like part of your concern is how to get rid of those errors values and "reset" the circular loop. If you see my examples here https://www.excelforum.com/tips-and-...ind-roots.html you will note that I almost always include some kind of IFERROR(circular reference,reset value) in my loops (see rows 28 and 29 of the first spreadsheet) so that if the loop ever errors (while changing input data for example), then the loop will have a built mechanism for resetting the loop to a non-error value. I don't know if that is applicable to what you are doing, but, if you have legitimate circular references that normally work, it might apply.

[saaadi1616]

Hi all,

Thanks for your response. I have treid to create a sample dummywork to explain the complexity of the Problem that i am facing. I hope I am able to make my self understand. Please see the attached file.

Have a nice day.
Cheer!!!