How to use the Visual Basic in Excel 97 for technical calculations. 8

Bize · Jun 28, 2000

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

tigrek · Oct 9, 2000

The naming of Excel columns and using those names as range names is a great gimmick, not known to many who would just love it.

Thanks.

Mike26 · Jan 24, 2001

How cam I realise forms with strange shapes.For examples how can I do a circular form.

dsi · Feb 8, 2001

There are a couple of pitfalls with the methods proposed above:

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:

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

Code:

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.

Bize · Feb 9, 2001

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

Eng-Tips is the largest engineering community on the Internet

Intelligent Work Forums for Engineering Professionals

How to use the Visual Basic in Excel 97 for technical calculations. 8

Bize

Mechanical

tigrek

Mechanical

Mike26

Structural

dsi

Mechanical

Bize

Mechanical

Similar threads

Part and Inventory Search

Sponsor