## 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

## 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