How to use the Visual Basic in Excel 97 for technical calculations.
How to use the Visual Basic in Excel 97 for technical calculations.
(OP)
If you don't know how to do any of the following steps, we'll talk about it on the Forum.
1) In Excel, give names to all of your input and output
cells.
2) Make the "Control Toolbox" toolbar visible (View,
Toolbars)
3) Click on the "Button button" (fourth down, first column)
and draw one somewhere on the worksheet just as you
would draw any graphic.
4) RIGHT click on the new button and choose "View Code"
5) A whole new kind of screen shows up (To return to Excel,
press click the Excel button on the upper lefthand
corner of the screen.)
6) Your program will be between the lines that say
"Private Sub CommandButton1_Click()" and "End Sub"
7) Assume you named an input cell on your
worksheet "Height" and another one "Radius." If you
type H = [Height] and R =[Radius] as your first two
lines in you program then you can write a program just
as you would in Fortran or Basic. For instance
V = H * R * R * 3.14159
8) Assume you named a cell "Volume" on your worksheet. Then
to print the results above to the worksheet, add the
line [Volume] = V to your program.
9) Return to the worksheet (by pressing the Excel button on
the upper lefthand corner)
10) MOST IMPORTANT Get out of the "design mode" by pressing
the button with a blue triangle on it that is located
at the top of the Control Toolbox toolbar.
11) When you click on your new button, it should take the
input from the named cells on the worksheet and output
the results to the named cells on the worksheet
1) In Excel, give names to all of your input and output
cells.
2) Make the "Control Toolbox" toolbar visible (View,
Toolbars)
3) Click on the "Button button" (fourth down, first column)
and draw one somewhere on the worksheet just as you
would draw any graphic.
4) RIGHT click on the new button and choose "View Code"
5) A whole new kind of screen shows up (To return to Excel,
press click the Excel button on the upper lefthand
corner of the screen.)
6) Your program will be between the lines that say
"Private Sub CommandButton1_Click()" and "End Sub"
7) Assume you named an input cell on your
worksheet "Height" and another one "Radius." If you
type H = [Height] and R =[Radius] as your first two
lines in you program then you can write a program just
as you would in Fortran or Basic. For instance
V = H * R * R * 3.14159
8) Assume you named a cell "Volume" on your worksheet. Then
to print the results above to the worksheet, add the
line [Volume] = V to your program.
9) Return to the worksheet (by pressing the Excel button on
the upper lefthand corner)
10) MOST IMPORTANT Get out of the "design mode" by pressing
the button with a blue triangle on it that is located
at the top of the Control Toolbox toolbar.
11) When you click on your new button, it should take the
input from the named cells on the worksheet and output
the results to the named cells on the worksheet
Talk To Other Members
RE: How to use the Visual Basic in Excel 97 for technical calculations.
Thanks.
RE: How to use the Visual Basic in Excel 97 for technical calculations.
RE: How to use the Visual Basic in Excel 97 for technical calculations.
1. The calculated volume is not dynamically updated. You would have to hit the calculation button each time you made a change to one of the input values. In most instances, if an input is changed, you would want the result to be updated automatically.
2. This calculation is only available on one sheet and only handles the single set of named inputs. Creating user-defined functions will allow calculations to be accessed from any sheet in a workbook. You can also export a global module that contains all of your engineering calculations. Then, just import that module into any workbook and have all of the functions available for use.
Expanding on the above example:
1. Go to the Visual Basic Editor.
2. Select Insert > Module.
if you are going to export the module:
select View > Properties Window
rename Module1 to modFunctions, or something of the sort
right-click the module in the Project window
Select Export > File
Save the module
In the module, insert this code:
Option Explicit
Public Const PI = 3.14159
Public Function CylinderVolume(rRad As Double, rHgt As Double) As Double
CylinderVolume = PI * rRad ^ 2 * rHgt
End Function
Now, the value for PI will not have to be redefined since it is declared as a Public variable in a global module. In addition, the CylinderVolume calculation can be used in any worksheet and can be used multiple times, independant of your naming convention.
Let's say cell D1 contains the Radius, D2 contains the Height and you want the results in cell D3:
Insert one of these formulas into cell D3
Without Naming:
=CylinderVolume(D1,D2)
With Naming:
=CylinderVolume(Radius,Height)
Now, anytime that you change the Radius or Height values, the calulated Volume is automatically updated. This gives you more flexiblity in your engineering spreadsheets and lends itself to reuse.
RE: How to use the Visual Basic in Excel 97 for technical calculations.
I personally prefer the button method. Functions work great if the answer is only one number. I've tried making functions that return arrays, but I find them to be awkward and I don't usually want all of the answers to be output in a column.
Also, for the long engineering calculations I work with, it is good that the calculations don't dynamic updating after each new number input. If you do want auto calculation, however, you can right click on the worksheet tab and select "View Code". Delete any partial code segments and then write the program like this:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
D = [Diameter] : H = [Height]
V = 3.14159*D*D*H
[Volumn] = V
End Sub