Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations cowski on being selected by the Eng-Tips community for having the most helpful posts in the forums last week. Way to Go!
What do you use for Application Dev. in Excel 1
- Thread starter Gearhead
- Start date
GregLocock
Automotive
I generally use formulae in cells, but due to the inherent non-checkability of them I think I am going to switch to VBA and Matlab for repetitive tasks.
Cheers
Greg Locock
Cheers
Greg Locock
I generally use excel as a tool when I want to visualise data. It is so good for seeing what happens to statistics etc if you change any input variables.
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.
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.
i've created a module with equations/functions that are frequently used. depending upon objectives, i will use both techniques for computations, that is cell formula and functions. sometimes, customization is desired.
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
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
Looking at my pevious post shows why you don't do a post while trying to do other work.
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.
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.
GregLocock
Automotive
So how do you check every cell for malicious code like
=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
=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
I find Mathcad very good for presentation and visualisation - you write the equations as you would on paper, and the unit checking means that if you get weird units at the end you did something wrong somewhere (say ohms instead of volts, then you have an extr division by amps you didn't intend). It is also easy to pass things back and forward to Excel. And Excel knocks the socks off anything else for tables of data but is a dog when trying to do complex numbers and still stay sane. Mathcad is good with arrays, vectors etc but not really so good with just tables of data.
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...
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...
I sometimes copy critical formulas to another sheet which I than hide from the user. I than write a VBA routine to copy these formulas where they belong.
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.
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.
-
1
- #10
A very long time ago I settled on making my spreadsheets as "self-documenting" as my hand calculations. They are self-documenting in the sense that for each calculation I express the algebraic formula in one cell, the cell below has the formula with the numerical values of the variables, and the last cell has the numerical value of the expression. This method, though causing me to have to enter three times as much information as entering only the formula to be calculated, has the advantage that the spreadsheet page can be printed and inserted into a set of calculations and no further reference need be made to the computer, the printed worksheet contains all the necessary information. I finally settled on this method after hassling back and forth with people checking my calculation packages, they brought up the very good point that without access to the electronic spreadsheet it was difficult or impossible to check the work. The extra time and care that it takes me to do this is rewarded many times over by making it easier for checkers and other users not to have to try to figure out how numbers were determined.
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.
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.
GregLocock
Automotive
And what is to prevent you, or anyone else, from changing B6 to
=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
=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
I found an interesting VBA function that displays actual numbers in the formula, so it looks like handcalcs. You can find it at
I use a somewhat different approach. I sometimes develop an application with Mathcad, other times with Excel. I always show the equation in its "natural" state; eg; even if in Excel I "paste" a MathCad equation adjacent to the cell with the calculation. These applications are used by many, many folks.
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
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
bltseattle
Civil/Environmental
Continuing with digression from the original post in this thread...
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).
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).
I have posted an example of my spreadsheet method for anyone interested (the actual problem is a UG-41 weld path strength calculation for a nozzle on a pressure vessel per ASME Section VIII Division 1; feel free to use it). Like bltseattle I use colored cells to identify the "input" cells and then protect the worksheet so that the Tab key conveniently jumps through the "input" fields. Download the file from (case sensitive).
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.
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.
- Thread starter
- #16
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
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.
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
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.
Now I remember just WHY I was copying and pasting those protected worksheets some years ago: Most of the detailed calculations were not documented where the user could see them. It was a blackbox...I didn't know what really went on inside to get the end values. I wanted to confirm those detail calculations so that I could verify the end result.
GearHead: thanks for the tip. I will check out that utility.
GearHead: thanks for the tip. I will check out that utility.
electricpete
Electrical
To make excel more check-able I use the following approach:
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:
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
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:
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
I do as bltseatle does to stop me from altering the calcs that have already been tested and validated.
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
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
- Status
Similar threads
- Locked
- Question