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

[Bize]

If you don't know how to do any of the following steps, we'll talk about it on the Forum.<br><br>1) In Excel, give names to all of your input and output<br>&nbsp;&nbsp;&nbsp;&nbsp;cells.<br>2) Make the &quot;Control Toolbox&quot; toolbar visible (View, <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Toolbars)<br>3) Click on the &quot;Button button&quot; (fourth down, first column)<br>&nbsp;&nbsp;&nbsp;&nbsp;and draw one somewhere on the worksheet just as you <br>&nbsp;&nbsp;&nbsp;&nbsp;would draw any graphic. <br>4) RIGHT click on the new button and choose &quot;View Code&quot;<br>5) A whole new kind of screen shows up (To return to Excel,<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;press click the Excel button on the upper lefthand <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;corner of the screen.)<br>6) Your program will be between the lines that say<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&quot;Private Sub CommandButton1_Click()&quot; and &quot;End Sub&quot;<br>7) Assume you named an input cell on your<br>&nbsp;&nbsp;&nbsp;&nbsp;worksheet &quot;Height&quot; and another one &quot;Radius.&quot; If you<br>&nbsp;&nbsp;&nbsp;&nbsp;type H = [Height] and R =[Radius] as your first two <br>&nbsp;&nbsp;&nbsp;&nbsp;lines in you program then you can write a program just<br>&nbsp;&nbsp;&nbsp;&nbsp;as you would in Fortran or Basic. For instance <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;V = H * R * R * 3.14159<br>8) Assume you named a cell &quot;Volume&quot; on your worksheet. Then<br>&nbsp;&nbsp;&nbsp;&nbsp;to print the results above to the worksheet, add the<br>&nbsp;&nbsp;&nbsp;&nbsp;line [Volume] = V to your program.<br>9) Return to the worksheet (by pressing the Excel button on<br>&nbsp;&nbsp;&nbsp;the upper lefthand corner)<br>10) MOST IMPORTANT Get out of the &quot;design mode&quot; by pressing<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;the button with a blue triangle on it that is located<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;at the top of the Control Toolbox toolbar.<br>11) When you click on your new button, it should take the<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;input from the named cells on the worksheet and output<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;the results to the named cells on the worksheet<br>

 

[tigrek]

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]

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

 

[dsi]

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:

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.

 

[Bize]

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 &quot;View Code&quot;. 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