## What do you use for Application Dev. in Excel

## What do you use for Application Dev. in Excel

(OP)

I was wondering what percent of engineering Excel users put thier equations in cells or do you use VBA? Are there other tools you use to build applications that will run in Excel?

## RE: What do you use for Application Dev. in Excel

Cheers

Greg Locock

## RE: What do you use for Application Dev. in Excel

Excel is also easy to visualise. I have great issues with trying to visualise in Matlab, as matrixes are harder to veiw, and outputing tables for printing is a major ask.

For some tasks I use Excel just for ease of use. This is mostly for one off style calculations. If I have to use this one off calculation many times the I then move straight into Matlab.

I find Matlab good for intensive calculations, but you have to be carefull when setting up involved processes, as it is oh so easy to make mistakes.

I find Matlab good for calculations, but when it comes to presenting the data it is a mission. Generally my code is 2/3 presentation and 1/3 actual calculations.

## RE: What do you use for Application Dev. in Excel

Good Luck

johnwm

## RE: What do you use for Application Dev. in Excel

the creator of functions will need to know the limitations, input variables, and clarify the units of measurement.

basically, the decision/choice is user preference.

good luck!

-pmover

## RE: What do you use for Application Dev. in Excel

One thing I am concerned about with using VBA and user defined functions is the ability to share them. As engineers we should all be subject to peer reveiw, but if reveiwers don't have the ability to check the calculations etc, then reveiw doesn't effectively happen. Yes, the calculations are available in the VBA, but not where the average excel user can see them.

I have also had issues with maintenance of programs etc. I have created programs for personal use that are particular to a certain problem. Next thing is you adapt it to others, and other people find out about it. Next thing you find is other people are using it, and you are always providing help and constantly having to sort out bugs etc. Very soon, looking after the code becomes a big problem.

Even though the two issues above cause problems, I still use VBA in my work, but mostly for speeding up repetitive tasks, and not for straight calculations.

## RE: What do you use for Application Dev. in Excel

=if(argument is approximately bad number, badanswer,rightanswer)

?

It seems to me that things like that are more easily caught in VBA than in the formulae in the cells.

Cheers

Greg Locock

## RE: What do you use for Application Dev. in Excel

I only use VBA for user forms, talking to other programs via an API, etc, rather than for customised functions or formulae.

Bung

Life is non-linear...

## RE: What do you use for Application Dev. in Excel

If, for instance, you want the user to be able to add as many rows as he wants, but want to make sure the formulas update the way they should, you might do all the formulas in the main sheet, and then copy the formula structure to the hidden formula sheet. Than, when the VBA code scans down through the table, and sees where the formulas are required, it adds them.

Anybody with a basic understanding of excel can understand the formulas, and also you are assured that the correct formulas are there.

## RE: What do you use for Application Dev. in Excel

For example, if I have to calculate the area of a circle that is 7.78" in diameter, the cell formulas and text entries would be as below:

A B

1

2 R = 7.78

3

4 area = pi * R ^ 2

5 = = "pi * " & $B$2 & " ^ 2"

6 = =PI() * $B$2

Notice how cell B4 expresses the algebraic formula, cell B6 contains formula for the numerical value, and cell B5 contains a concatenated expression for the algebraic formula with numerical values. In this example cell B5 evaluates as: pi * 7.78 ^ 2

This method seems tedious at first and can be a pain in the neck when the cell formula is particularly long. But it is worth the effort because it makes the calcuation totally self-documenting, no one can argue with it and no one's left in the dark wondering how some cryptic number was arrived at.

## RE: What do you use for Application Dev. in Excel

=if(and(B2>7.78,B2<7.79),1.0001,1)*pi()*b2^2

?

You still have to manually check every computed cell.

Cheers

Greg Locock

## RE: What do you use for Application Dev. in Excel

## RE: What do you use for Application Dev. in Excel

Then there are instances I will use VBA to store an equation that if the cell is double-clicked on it is restored to its original state. For instance using an input cell for specific volume of a vapor/gas in a calculation. I will calculate this value if it is a perfect gas, but leave the cell unprotected to allow entry if for instance you are calculating superheated steam. If the user saves the spreadsheet with this input it overwrites the underlying formula. I have VBA restore it.

Don Coffman

## RE: What do you use for Application Dev. in Excel

Greg,

Per your first post, I use data validation to prevent bad arguments, by limiting which values are accepted as input by the user. Specific "input cells" are the only cells on the worksheet that are not locked (and they are color coded so that the user knows they are the intended cells for input). Then the worksheet is protected, so it is not possible to change the equations in the other cells, and the user input values (mostly) do not cause errors if the validation is set up correctly.

This approach is not fool-proof, for example: one can copy and paste data into the input cells which will bypass the validation function of Excel, but this approach seems to work pretty good.

TomBarsh,

Thanks for your insight. I agree that the extra time to clearly show what math operations are performed saves inthe time it takes to explain the worksheet to others at a later date (or to remember for oneself, if it's been a while since the sheet was setup).

## RE: What do you use for Application Dev. in Excel

At one time I bought the MathType software to add mathematical expressions to Excel (MathType is the full version of what Microsoft provides as the Equation Writer). I love this software but I never use it; I wish that it would work "live" to take on the numerical values for a particular calculation.

Addressing GregLocock’s concerns: Of course anyone can change anything. Even a compiled Fortran, etc, program can be back-engineered, changed and recompiled. I depend on the users of my spreadsheets (and I include myself here too) to take responsibility for any changes that they may make after unlocking the spreadsheet. It’s my responsibility to make certain that it works in the beginning, what someone else does from that point on is their problem. If I were distributing my spreadsheets to other users I would password protect them to protect their integrity. But even that isn’t 100% effective. I myself have copied and pasted entire protected worksheets so that I could more readily work with the information; was the original author responsible then?

Spreadsheets can be fantastic time- and effort-saving tools for repetitive work. But certainly some effort has to be put into making them reliable.

## RE: What do you use for Application Dev. in Excel

WOW

I guess I started something. I found a add-in for Excel which displays formulas within the worksheet as you see them in a text and even finds the cell relationships and diplays the complete formula. It's available for free trial download at www.uts.com.

As I understand everyones concerns. You want to be able to build a spreadsheet where the end user finds it easy to use and understand. In some cases you want to lock out the end user from making changes.

It also is important to be able to make changes and verfy answers.

## RE: What do you use for Application Dev. in Excel

GearHead: thanks for the tip. I will check out that utility.

## RE: What do you use for Application Dev. in Excel

Column 1 = variable names.

Column 2 = variable values/formula's

Column 3 = display of formula.

Let me explain how easy it is to do that.

Type all variable names into column 1.

Then highlight the entries of columns 1 and 2 and select insert/name/create/left-column to automatically name the cells in column 2 by their name in column 1.

Now type in formula's. When you point to a named variable, the name automatically appears in the formula (instead of the cell reference).

Now use the getformula function in the third column to easily display the formula's in the 2nd column. Getformula has been described in this forum before. It sounds similar to what Gearhead described except you don't have to download, just type in one line of vba code.

Example:

http://www.brazosport.edu/~pschimpf/forums/engtips/formula_test.xls

http://www.brazosport.edu/~pschimpf/forums/engtips/formula_test.xls

=====================================

Eng-tips forums: The best place on the web for engineering discussions.

## RE: What do you use for Application Dev. in Excel

I also add comments to the input and main output cells (critical results) to remind me of what is needed or what limits may apply to the result. I also use the comments to remind me of the relevant standards and codes that are related to the particular calc.

sc